Bases de Données Relationnelles et SQL

Ludovic Deneuville

Objectifs

• Qu’est ce qu’une base de données relationnelle ?
• Comment sont construites les tables ?
• Savoir écrire des requêtes SQL
• Utiliser le logiciel DBeaver

Qu’est-ce qu’une base de données ?

• Collection organisée de données
• Stockée électroniquement
• Structurée pour faciliter l’accès et la gestion
• Conçue pour minimiser la redondance

• peut être utilisée par des applications
• interrogeables, modifiables par les utilisateurs

Base de données relationnelles

• Organise les données dans des Tables
• Tables interconnectées
• Tables structurées

• Table = tableau

SGBD

Un Système de Gestion de Base de Données (SGBD) est un logiciel qui permet de :

• stocker
• organiser
• gérer des données de manière structurée

Exemples de SGBD :

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

• SGBD : interface principale avec les données
• Pour nous PostgreSQL, gratuit, libre, installé VM
• NoSQL : Not Only SQL

Kesako

• PostgreSQL : SGBD
• DBeaver : outil client qui permet de se connecter à un SGBD
• SQL : langage de programmation conçu pour gérer et manipuler des bases de données relationnelles.

On parle de SQL ensuite

Types de données

Il existe de très nombreux types de données que l’on peut stocker, les principaux :

• Chaîne de caractères
• Numérique
• Booléen
• Date, Temps
• Géométrique

Plus de détails sur la documentation PostgreSQL.

• Date, Temps -> plein de fonctions
• Géométrique -> contours géographiques

Types - exemple

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 : séquence
• NOT NULL : obligatoire
• UNIQUE : distinctes, mais n’empêche pas les NULL
• DECIMAL(3, 2) : de -9.99 à 9.99
  • 3 chiffres significatifs dont 2 après la virgule

Code bien aligné, Mots clés en majuscule

Table

Une Table est composée de lignes et de colonnes :

• une ligne représente un enregistrement spécifique
• une colonne représente un attribut particulier de ces enregistrements
• Clé primaire (PK) : colonne ou un ensemble de colonnes qui identifie de manière unique chaque enregistrement dans une table
  • permet d’identifier une ligne sans ambiguïté

PK :

• obligatoire (non null)
• unique

Table - exemple

• MCD
• Données

Modèle Physique des Données (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	Hongrie
3	Hou	Yifan	1994-02-27	Chine
4	Kosteniuk	Alexandra	1984-04-23	Suisse
5	Ju	Wenjun	1991-01-31	Chine

Associations entre tables

Clé étrangère

Une clé étrangère (FK) :

• permet de faire le lien entre 2 tables
• c’est une colonne d’une table A
• elle correspond à la clé primaire d’une table B

Types d’associations

• 1..1 : Une Personne a un Passeport et un Passeport appartient à une seule Personne
  • clé étrangère dans l’une des 2 tables
• 1..* : Une Joueuse joue pour une seule équipe Equipe. Une Equipe est composée de plusieurs Joueuses
  • clé étrangère dans la table Joueuse
• *..* : Un Etudiant suit plusieurs Cours et un Cours et suivi par plusieurs Etudiants
  • table d’association entre Etudiant et Cours

Lien entre 2 tables via une clé étrangère

• Diagramme
• Table joueuse
• Table pays

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 : Appartient

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	Suisse
CN	Chine
FR	France
HU	Hongrie

On supposera ici :

• Une joueuse a un pays
• Un pays a plusieurs joueuses

Lien entre 2 tables via une table d’association

• Diagramme
• Table joueuse
• Table participation
• Table tournoi

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

• Une joueuse peut participer a plusieurs tournois
• Un tournoi accueille plusieurs joueuses

SQL

• SQL : Structured Query Language
• Inventé en 1970 par Edgar F. Codd
• Langage de programmation
• Utilisé pour gérer et manipuler des bases de données relationnelles

• très très très utilisé
• tous les langages qui traitent de la data peuvent encapsuler du SQL

Opérations CRUD

SQL permet d’effectuer les opérations du CRUD :

• SELECT : récupérer des données depuis une table
• INSERT : insérer de nouvelles données dans une table
• UPDATE : mettre à jour des données existantes
• DELETE : supprimer des données d’une table

CRUD

Create, Read, Update, Delete

Actions sur une table

Créer une Table

• Requête
• Résultat

-- Création de la table personne (cette ligne est un commentaire)

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

La table est créée mais vide.

id_personne	nom	prenom	date_naissance	adresse

Bonne pratique

Une bonne pratique est de stocker les tables dans un schéma pour bien organiser votre base de données.

CREATE SCHEMA ran;

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

Insérer des données

• Requête
• Résultat

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

Supprimer une table

DROP TABLE personne;

Si ensuite, vous essayez :

SELECT *
  FROM personne;

ERREUR: la relation « personne » n’existe pas

Actions sur les lignes

Sélectionner tout

Pour afficher tout le contenu d’une table.

SELECT *
  FROM personne;

• Une requête se termine par un ;
• Possible de limiter le nombre de lignes affichées
  • LIMIT 5

Filter les lignes

• Requête
• Résultat
• 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

Pour une meilleure lisibilité, alignez votre code !

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

La clause LIKE est utilisée pour rechercher un texte spécifique dans une colonne de texte

• % représente zéro, un ou plusieurs caractères
• _ représente un seul caractère

Mettre à jour des lignes

• Requête
• Résultat

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

Si vous refaites un SELECT * FROM personne;

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

• simples quotes en SQL
• pas de double

Supprimer des lignes

• Requête
• Résultat

DELETE FROM personne
 WHERE prenom = 'Ali';

Pour ensuite faire revivre Ali :

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 sur les colonnes

Sélectionner des colonnes

• Requête
• Résultat

SELECT nom,
       prenom
  FROM personne;

nom	prenom
Gatore	Ali
Dure	Laure
Erateur	Maud

Renommer une colonne

• Requête
• Résultat

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

Ajouter un attribut

• Requête
• Résultat

ALTER TABLE personne
ADD joue_echecs BOOLEAN;

Vous pouvez ajouter une valeur par défaut.

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

Supprimer une colonne

• Requête
• Résultat

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

Renommer à l’affichage

Le mot clé AS permet de renommer une colonne à l’affichage.

⚠️ Il ne change pas le nom de la colonne.

• Requête
• Résultat

SELECT prenom,
       adresse AS Ville
  FROM personne;

prenom	Ville
Maud	Lille
Ali	Amiens
Laure	Amiens

Jointures

Alias

Jusqu’à maintenant, nous n’avions qu’une seule table.

Nous savions donc que le champ nom venait de la table personne.

Comment faire si nous faisons une jointure avec une table qui a également une colonne nommée nom ?

SELECT p.nom       -- 2. p.nom : attribut nom de la table d'alias p i.e. personne
  FROM personne p  -- 1. nous déclarons p comme alias de la table personne

Jointure totale

• Requête
• Requête bis
• Personne
• Commande
• Résultat

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

Si et seulement si les 2 colonnes qui permettent de faire la jointure ont le même nom.

↪️ Alors vous pouvez utiliser cette syntaxe avec 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

Créer et insérer les données

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

Faire une jointure c’est comme créer un gros tableau fusionné

Types de jointures

Dans la jointure précédente :

• Laure apparait 2 fois car elle a 2 commandes
• Maud apparait une fois
• Ali n’apparait pas

Comment faire pour inclure Ali dans le tableau même sans commande ?

• jointure interne (INNER JOIN)
  • la valeur de id_personne doit être présente dans les 2 tables
  • sinon id_personne est dans une seule, alors rien n’est affiché la concernant

Jointures externes

• Requête
• Résultat
• Explication

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

• LEFT JOIN signifie que l’on garde tout le contenu provenant de la table précédente
• et que l’on complète avec le contenu de la table qui suit
• RIGHT JOIN pour faire l’inverse

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

La jointure externe effectuée avec le mot clé LEFT JOIN indique que nous affichons :

• toutes les données provenant de la table personnes
• complétées par les données de la table commandes
  • pour les lignes où le lien est établi

Agrégation

GROUP BY

• Utilisé pour regrouper les résultats en fonction d’une ou plusieurs colonnes
• Permet l’utilisation de fonctions d’agrégation telles COUNT, SUM, AVG…

• Requête
• Résultat

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

adresse	count
Amiens	2
Lille	1

HAVING

Pour filter après un GROUP BY

• Requête
• Résultat
• Attention

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

adresse	count
Amiens	2

Warning

• ne pas confondre avec WHERE
• HAVING sert à filter après un GROUP BY

Ordonner les lignes

• Requête
• Résultat

SELECT *
  FROM personne
 ORDER BY dnais DESC

• Pour ordonner les résultats
  • Ajouter éventuellement DESC pour décroissant

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

Possible d’ordonner selon plusieurs colonnes

Formes normales

1ere forme normale

• Énoncé
• Exemples

Une relation est de première forme normale (1NF) si

• elle possède au moins une clé
• et si tous ses attributs sont atomiques

Attribut atomique

• Ne contient qu’une seule valeur pour un tuple donné
• Donc ne regroupe pas un ensemble de plusieurs valeurs

id	nom
1	Ali Gator
2	Laure Dure
3	Maud Erateur

Cette table ne respecte pas la 1NF car son attribut nom n’est pas atomique.

Autres formes normales

• 2NF : 1NF + tout attribut non clé dépend de la clé
• 3NF : 2NF + un attribut non clé ne peut pas dépendre d’un autre attribut non clé

Les schémas

Une bonne pratique (non appliquée ici) est de classer nos tables dans différents schémas.

De la même manière que vous rangez des fichiers dans des dossiers, vous vous y retrouverez plus facilement en rangeant les tables dans des schémas.

Si vous ne déclarez pas de shéma lors de la création d’une table, elle va dans le schéma public.

Schémas - exemple

Créer un schéma pour le projet info

CREATE schema projet;

À la création, stocker la table dans le schéma

CREATE table projet.joueuse(
  ...
);

Préciser le schéma lors des requêtes

SELECT *
  FROM projet.joueuse;

SQL sans table

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

Autres notions non abordées

• ACID, COMMIT, ROLLBACK
• vues, snapshot
• index
• droits : grant / revoke
• WITH (Common Table Expressions)
• UNION, INTERSECT, EXCEPT
• DISTINCT
• EXISTS
• SEQUENCE

Bibliographie

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

Exercice

exercice