Bases de Données Relationnelles et SQL

Ludovic Deneuville

Objectives

• What is a relational database?
• How are tables built?
• Know how to write SQL queries
• Use the CloudBeaver service

What is a database?

• Organized collection of data
• Stored electronically
• Structured to facilitate access and management
• Designed to minimize redundancy

• Can be used by applications
• Can be queried and modified by users

Relational databases

• Organizes data into Tables
• Interconnected tables
• Structured tables

• Table = spreadsheet

DBMS

A Database Management System (DBMS) is software that allows you to:

• store
• organize
• manage data in a structured way

Examples of DBMS:

• relational: PostgreSQL, MySQL, Oracle Database
• NoSQL: MongoDB, Cassandra

• DBMS: main interface with data
• For us, PostgreSQL, free, open source, installed on a VM
• NoSQL: Not Only SQL

What is it?

• PostgreSQL: DBMS
• DBeaver: client tool that allows connection to a DBMS
• SQL: programming language designed to manage and manipulate relational databases.

SQL will be discussed afterwards

Data types

There are many different data types that can be stored, the main ones are:

• Character strings
• Numeric values
• Boolean values
• Date and time
• Geometric data

More details in the PostgreSQL documentation.

• Date and time -> many functions
• Geometric -> geographic boundaries

Types - example

CREATE TABLE personne (
    id_personne      SERIAL PRIMARY KEY,
    nom              VARCHAR(100) NOT NULL,
    prenom           VARCHAR(100) NOT NULL,
    date_naissance   DATE,
    email            VARCHAR(255) UNIQUE,
    nb_enfants       INT CHECK (nb_enfants >= 0),
    taille_m         DECIMAL(3, 2)
    est_actif        BOOLEAN,
    last_updated     TIMESTAMPTZ DEFAULT NOW() 
);

• SERIAL: sequence

• NOT NULL: mandatory

• UNIQUE: distinct values, but does not prevent NULL values

• DECIMAL(3, 2): from -9.99 to 9.99

  • 3 significant digits including 2 decimal places

Well-aligned code, keywords in uppercase

Table

A Table is composed of rows and columns:

• a row represents a specific record

• a column represents a particular attribute of these records

• Primary key (PK): a column or set of columns that uniquely identifies each record in a table

  • allows a row to be identified unambiguously

PK:

• mandatory (not null)
• unique

Table - example

• Physical Data Model
• Data

Physical Data Model (UML)

classDiagram
  class Joueuse {
    id_joueuse: INT PK
    nom: VARCHAR
    prenom: VARCHAR
    date_naissance: DATE
    pays: VARCHAR
  }

Joueuse

id_joueuse (PK)	nom	prenom	date_naissance	pays
1	Sebag	Marie	1986-10-15	France
2	Polgar	Judit	1976-07-23	Hungary
3	Hou	Yifan	1994-02-27	China
4	Kosteniuk	Alexandra	1984-04-23	Switzerland
5	Ju	Wenjun	1991-01-31	China

Relationships between tables

Foreign key

A foreign key (FK):

• establishes a link between 2 tables
• is a column of table A
• corresponds to the primary key of table B

Types of relationships

• 1..1: A Person has a Passport and a Passport belongs to a single Person

  • foreign key in one of the 2 tables

• 1..*: A Player plays for a single Team. A Team is composed of several Players

  • foreign key in the Player table

• *..*: A Student attends several Courses and a Course is attended by several Students

  • association table between Student and Course

Link between 2 tables through a foreign key

• Diagram
• Player table
• Country table

classDiagram
  direction LR
  
  class Joueuse {
    id_joueuse: INT PK
    nom: VARCHAR
    prenom: VARCHAR
    date_naissance: DATE
    code_pays: VARCHAR FK
  }
  
  class Pays {
    code_pays: VARCHAR PK
    nom: VARCHAR
  }

  Joueuse "*" -- "1" Pays : BelongsTo

id_joueuse	nom	prenom	date_naissance	code_pays
1	Sebag	Marie	1986-10-15	FR
2	Polgar	Judit	1976-07-23	HU
3	Hou	Yifan	1994-02-27	CN
4	Kosteniuk	Alexandra	1984-04-23	CH
5	Ju	Wenjun	1991-01-31	CN

code_pays	nom
CH	Switzerland
CN	China
FR	France
HU	Hungary

We assume here:

• A player has one country
• A country has several players

Link between 2 tables through an association table

• Diagram
• Player table
• Participation table
• Tournament table

classDiagram
  direction LR

  class Joueuse {
    id_joueuse: INT PK
    nom: VARCHAR
    prenom: VARCHAR
    date_naissance: DATE
    code_pays: VARCHAR FK
  }
  
  class Tournoi {
    id_tournoi: INT PK
    nom: VARCHAR
    ville: VARCHAR
  }

  
  class Participation {
    id_joueuse: INT FK
    id_tournoi: INT FK
  }

  Joueuse "*" .. "1" Participation
  Participation "1" .. "*" Tournoi

id_joueuse	nom	prenom	date_naissance	code_pays
1	Sebag	Marie	1986-10-15	FR
2	Polgar	Judit	1976-07-23	HU
3	Hou	Yifan	1994-02-27	CN
4	Kosteniuk	Alexandra	1984-04-23	CH
5	Ju	Wenjun	1991-01-31	CN

id_joueuse	id_tournoi
1	1
1	2
3	2
4	1
4	2

id_tournoi	nom	ville
1	Norway Chess	Oslo
2	Tata Steel	Wijk aan Zee

• A player can participate in several tournaments
• A tournament hosts several players

SQL

• SQL: Structured Query Language
• Invented in 1970 by Edgar F. Codd
• Programming language
• Used to manage and manipulate relational databases

• Very, very, very widely used
• All data-related programming languages can embed SQL

CRUD operations

SQL allows CRUD operations:

• SELECT: retrieve data from a table
• INSERT: insert new data into a table
• UPDATE: update existing data
• DELETE: delete data from a table

CRUD

Create, Read, Update, Delete

Actions on a table

Create a Table

• Query
• Result

-- Creation of the personne table (this line is a comment)

CREATE TABLE personne (
    id_personne      INT         PRIMARY KEY,
    nom              VARCHAR(30) NOT NULL,
    prenom           VARCHAR(40),
    date_naissance   DATE,
    adresse          TEXT
);

The table is created but empty.

id_personne	nom	prenom	date_naissance	adresse

Best practice

A good practice is to store tables in a schema to properly organize your database.

CREATE SCHEMA ran;

CREATE TABLE ran.personne (
    id_personne      INT         PRIMARY KEY,
    ...
);

Insert data

• Query
• Result

INSERT INTO personne (id_personne, nom, prenom, date_naissance, adresse)
VALUES
    (1, 'Gatore' , 'Ali'  , '1990-05-15', 'Amiens'),
    (2, 'Dure'   , 'Laure', '1985-09-22', 'Auxerre'),
    (3, 'Erateur', 'Maud' , '1995-03-10', 'Lille');

id_personne	nom	prenom	date_naissance	adresse
1	Gatore	Ali	1990-05-15	Amiens
2	Dure	Laure	1985-09-22	Auxerre
3	Erateur	Maud	1995-03-10	Lille

Delete a table

DROP TABLE personne;

If you then try:

SELECT *
  FROM personne;

ERROR: relation “personne” does not exist

Actions on rows

Select all

To display the entire contents of a table.

SELECT *
  FROM personne;

• A query ends with a ;
• It is possible to limit the number of rows displayed
  • LIMIT 5

Filter rows

• Query
• Result
• LIKE

SELECT *
  FROM personne
 WHERE adresse LIKE 'A%'
   AND prenom = 'Laure'
   AND adresse IN ('Amiens', 'Auxerre')
   AND adresse IS NOT NULL
   AND adresse != 'Lille'
   AND id_personne BETWEEN 2 AND 3;

Tip

For better readability, align your code!

id_personne	prenom	nom	date_naissance	adresse
2	Laure	Dure	1985-09-22	Auxerre

The LIKE clause is used to search for specific text in a text column.

• % represents zero, one, or more characters
• _ represents a single character

Update rows

• Query
• Result

UPDATE personne
   SET adresse = 'Amiens'
 WHERE id_personne = 2;

If you run SELECT * FROM personne; again:

id_personne	prenom	nom	date_naissance	adresse
1	Ali	Gatore	1990-05-15	Amiens
2	Laure	Dure	1985-09-22	Amiens
3	Maud	Erateur	1995-03-10	Lille

• single quotes in SQL
• not double quotes

Delete rows

• Query
• Result

DELETE FROM personne
 WHERE prenom = 'Ali';

To bring Ali back:

INSERT INTO personne (id_personne, nom, prenom, date_naissance, adresse)
VALUES (1, 'Gatore', 'Ali', '1990-05-15', 'Amiens');

id_personne	prenom	nom	date_naissance	adresse
2	Laure	Dure	1985-09-22	Amiens
3	Maud	Erateur	1995-03-10	Lille

Actions on columns

Select columns

• Query
• Result

SELECT nom,
       prenom
  FROM personne;

nom	prenom
Gatore	Ali
Dure	Laure
Erateur	Maud

Rename a column

• Query
• Result

ALTER TABLE personne
RENAME COLUMN date_naissance TO dnais;

id_personne	nom	prenom	dnais	adresse
1	Gatore	Ali	1990-05-15	Amiens
2	Dure	Laure	1985-09-22	Amiens
3	Erateur	Maud	1995-03-10	Lille

Add an attribute

• Query
• Result

ALTER TABLE personne
ADD joue_echecs BOOLEAN;

You can add a default value.

ALTER TABLE personne
ADD joue_echecs BOOLEAN DEFAULT true;

id_personne	nom	prenom	dnais	adresse	joue_echecs
1	Gatore	Ali	1990-05-15	Amiens	true
2	Dure	Laure	1985-09-22	Amiens	true
3	Erateur	Maud	1995-03-10	Lille	true

Delete a column

• Query
• Result

ALTER TABLE personne
DROP COLUMN joue_echecs;

id_personne	nom	prenom	dnais	adresse
1	Gatore	Ali	1990-05-15	Amiens
2	Dure	Laure	1985-09-22	Amiens
3	Erateur	Maud	1995-03-10	Lille

Rename when displaying

The AS keyword allows you to rename a column when displaying it.

⚠️ It does not change the name of the column.

• Query
• Result

SELECT prenom,
       adresse AS Ville
  FROM personne;

prenom	Ville
Maud	Lille
Ali	Amiens
Laure	Amiens

Joins

Alias

Until now, we only had one table.

We therefore knew that the nom field came from the personne table.

What should we do if we join with a table that also has a column named nom?

SELECT p.nom       -- 2. p.nom: nom attribute of the table aliased as p, i.e. personne
  FROM personne p  -- 1. we declare p as an alias for the personne table

Full join

• Query
• Alternative query
• Person
• Order
• Result

SELECT p.prenom,
       c.produit,
       c.quantite
  FROM personne p
  JOIN commande c ON p.id_personne = c.id_personne
 WHERE prenom = 'Laure';

SELECT p.prenom,
       c.produit,
       c.quantite
  FROM personne p
  JOIN commande c USING(id_personne);

USING

If and only if the 2 columns used to perform the join have the same name.

↪️ You can then use this syntax with USING.

id_personne	nom	prenom	dnais	adresse
1	Gatore	Ali	1990-05-15	Amiens
2	Dure	Laure	1985-09-22	Amiens
3	Erateur	Maud	1995-03-10	Lille

Create and insert data

CREATE TABLE commande (
    id_commande INT PRIMARY KEY,
    produit VARCHAR(50),
    quantite INT,
    prix_unitaire DECIMAL(10, 2),
    id_personne INT,
    FOREIGN KEY (id_personne) REFERENCES personne(id_personne)
);

INSERT INTO commande (id_commande, produit, quantite, prix_unitaire, id_personne) VALUES
(1, 'livre', 1, 10, 2),
(2, 'pain', 3, 2, 3),
(3, 'pomme', 10, 0.5, 2);

id_commande	produit	quantite	prix_unitaire	id_personne
1	livre	1	10	2
2	pain	3	2	3
3	pomme	10	0.5	2

prenom	produit	quantite
Laure	livre	1
Laure	pomme	10
Maud	pain	3

Performing a join is like creating a large merged table.

Types of joins

In the previous join:

• Laure appears twice because she has 2 orders
• Maud appears once
• Ali does not appear

How can we include Ali in the table even though he has no order?

• inner join (INNER JOIN)

  • the value of id_personne must be present in both tables
  • otherwise, if id_personne exists in only one table, nothing is displayed for it

Outer joins

• Query
• Result
• Explanation

SELECT p.prenom,
       c.produit,
       c.quantite
  FROM personne p
  LEFT JOIN commande c USING(id_personne);

• LEFT JOIN means that we keep all the content from the previous table
• and complete it with the content from the following table
• RIGHT JOIN does the opposite

prenom	produit	quantite
Laure	livre	1
Laure	pomme	10
Maud	pain	3
Ali

The ****outer join**** performed using the LEFT JOIN keyword indicates that we display:

• all data from the ****people**** table

• supplemented with data from the ****orders**** table

  • for rows where the relationship is established

Aggregation

GROUP BY

• Used to group results based on one or more columns
• Allows the use of aggregate functions such as COUNT, SUM, AVG…

• Query
• Result

SELECT adresse,
       COUNT(1)
  FROM personne
 GROUP BY adresse;

adresse	count
Amiens	2
Lille	1

HAVING

To filter after a GROUP BY

• Query
• Result
• Warning

SELECT adresse,
       COUNT(1)
  FROM personne
 GROUP BY adresse
HAVING COUNT(1) > 1;

adresse	count
Amiens	2

Warning

• do not confuse it with WHERE
• HAVING is used to filter after a GROUP BY

Sort rows

• Query
• Result

SELECT *
  FROM personne
 ORDER BY dnais DESC

• To sort the results

  • Optionally add DESC for descending order

id_personne	nom	prenom	dnais	adresse
3	Erateur	Maud	1995-03-10	Lille
1	Gatore	Ali	1990-05-15	Amiens
2	Dure	Laure	1985-09-22	Amiens

It is possible to sort by several columns.

Normal forms

First normal form

• Statement
• Examples

A relation is in first normal form (1NF) if

• it has at least one key
• and all its attributes are atomic

Atomic attribute

• Contains only one value for a given tuple
• Therefore, it does not group several values together

id	nom
1	Ali Gator
2	Laure Dure
3	Maud Erateur

This table does not comply with 1NF because its nom attribute is not atomic.

Other normal forms

• 2NF: 1NF + every non-key attribute depends on the key
• 3NF: 2NF + a non-key attribute cannot depend on another non-key attribute

Schemas

A good practice (not applied here) is to organize our tables into different schemas.

In the same way that you organize files into folders, you will find your way around more easily by organizing tables into schemas.

If you do not specify a schema when creating a table, it is placed in the public schema.

Schemas - example

Create a schema for the programming project

CREATE schema projet;

When creating the table, store it in the schema

CREATE table projet.joueuse(
  ...
);

Specify the schema in queries

SELECT *
  FROM projet.joueuse;

SQL without a table

SELECT CURRENT_DATE;
SELECT 1 + 2;
SELECT 1 > 2;
SELECT 'Hello';

Other concepts not covered

• ACID, COMMIT, ROLLBACK
• views, snapshot
• indexes
• privileges: grant / revoke
• WITH (Common Table Expressions)
• UNION, INTERSECT, EXCEPT
• DISTINCT
• EXISTS
• SEQUENCE

Bibliography

• ENSAI 1A Course
• https://www-lisic.univ-littoral.fr/\~hebert/bdd/introduction/
• https://sgbd.developpez.com/tutoriels/cours-complet-bdd-sql/
• https://sql.sh/

Exercise

exercise