Les bases de données relationnelles

Sommaire

1. Vocabulaire des bases de données relationnelles
2. Le modèle entité-association
3. Clés primaires et clés étrangères
4. Du modèle E/A au modèle relationnel
5. SQL : manipulation des données (INSERT, UPDATE, DELETE)
6. Interroger une base : SELECT, projection et sélection
7. Opérateurs ensemblistes et produit cartésien
8. Les jointures internes
9. Renommage avec AS
10. Fonctions d'agrégation et GROUP BY
11. Filtrer les agrégats avec HAVING
12. Tri et limitation : ORDER BY, LIMIT, OFFSET
13. Requêtes imbriquées (sous-requêtes)
14. Récapitulatif

1. Vocabulaire des bases de données relationnelles

Une base de données est un ensemble structuré de données mémorisées de façon durable et partagées par plusieurs utilisateurs. Un SGBD (Système de Gestion de Base de Données) est le logiciel qui gère cette base : il en assure le stockage, la cohérence et l'interrogation. Dans ce cours, le SGBD utilisé est SQLite, un SGBD léger qui stocke toute la base dans un seul fichier.

Dans le modèle relationnel, les données sont organisées en tables. Le vocabulaire est le suivant :

Le schéma de la table des étudiants se note : Etudiant(id, nom, prenom, age, id_dep). Voici un extrait du contenu (les enregistrements) :

2. Le modèle entité-association

Avant de créer les tables, on conçoit la base au moyen d'un modèle entité-association (modèle E/A, ou modèle entité-relation). C'est un schéma de conception, indépendant du SGBD, qui décrit ce que représente la base.

Une cardinalité indique, pour une entité, combien d'occurrences de l'autre entité lui sont reliées :

• 1 - 1 : à un objet d'un côté correspond au plus un objet de l'autre (ex. : un pays a une seule capitale).
• 1 - * (un à plusieurs) : un département contient plusieurs étudiants, mais chaque étudiant appartient à un seul département.
• * - * (plusieurs à plusieurs) : un étudiant suit plusieurs modules et un module est suivi par plusieurs étudiants.

3. Clés primaires et clés étrangères

Clé primaire

La clé primaire d'une table est un attribut (ou un groupe d'attributs) dont la valeur identifie de façon unique chaque enregistrement. Deux lignes ne peuvent pas avoir la même valeur de clé primaire, et celle-ci ne peut jamais être NULL. Dans Etudiant, la clé primaire est id.

Clé étrangère

Une clé étrangère est un attribut d'une table qui référence la clé primaire d'une autre table. Elle matérialise le lien entre deux tables et garantit la cohérence référentielle : une valeur de clé étrangère doit correspondre à une ligne existante dans la table référencée.

Dans Etudiant, l'attribut id_dep est une clé étrangère qui référence Departement(id) : il indique à quel département appartient chaque étudiant.

CREATE TABLE Departement (
    id   INTEGER PRIMARY KEY,
    nom  TEXT NOT NULL
);

CREATE TABLE Etudiant (
    id      INTEGER PRIMARY KEY,
    nom     TEXT NOT NULL,
    prenom  TEXT,
    age     INTEGER,
    id_dep  INTEGER,
    FOREIGN KEY (id_dep) REFERENCES Departement(id)
);

4. Du modèle E/A au modèle relationnel

On transforme le schéma E/A en tables selon des règles systématiques.

Règle 1 — Une entité devient une table

Chaque entité devient une table. Ses attributs deviennent des colonnes et son identifiant devient la clé primaire. Ainsi : Étudiant ⇒ Etudiant(id, nom, prenom, age) et Module ⇒ Module(code, intitule, credits).

Règle 2 — Association 1-1 ou 1-* : clé étrangère

Pour une association 1 - *, on ajoute dans la table du côté « plusieurs » une clé étrangère référençant la clé primaire du côté « un ». Un département contient plusieurs étudiants (1-*) : on ajoute donc id_dep dans Etudiant.

Règle 3 — Association *-* : une table de liaison

Une association * - * ne peut pas se traduire par une simple clé étrangère. On la décompose en deux associations 1 - * en créant une table de liaison (table d'association). Sa clé primaire est composée des deux clés étrangères vers les entités liées ; les attributs de l'association y sont ajoutés.

CREATE TABLE Module (
    code     TEXT PRIMARY KEY,
    intitule TEXT NOT NULL,
    credits  INTEGER
);

CREATE TABLE Inscription (
    id_etudiant  INTEGER,
    code_module  TEXT,
    note         REAL,
    PRIMARY KEY (id_etudiant, code_module),
    FOREIGN KEY (id_etudiant) REFERENCES Etudiant(id),
    FOREIGN KEY (code_module) REFERENCES Module(code)
);

5. SQL : manipulation des données

Le SQL (Structured Query Language) est le langage standard d'interrogation et de manipulation des bases relationnelles. Une instruction SQL s'appelle une requête. Cette section présente les trois requêtes qui modifient les données.

INSERT INTO — insérer des lignes

INSERT INTO Etudiant (id, nom, prenom, age, id_dep)
VALUES (1, 'Alami', 'Sara', 19, 1);

-- plusieurs lignes d'un coup :
INSERT INTO Departement (id, nom) VALUES
    (1, 'Mathematiques'),
    (2, 'Informatique');

UPDATE — modifier des lignes

UPDATE Etudiant
SET age = 22
WHERE id = 3;

DELETE FROM — supprimer des lignes

DELETE FROM Etudiant
WHERE age < 18;

6. Interroger une base : SELECT, projection et sélection

La requête SELECT extrait des données sans les modifier. Sa forme de base :

SELECT  <colonnes>        -- projection
FROM    <table>
WHERE   <condition>;      -- sélection

Projection (choix des colonnes)

La projection garde certaines colonnes. En algèbre relationnelle on la note π (pi). SELECT nom, prenom FROM Etudiant; correspond à π_{nom, prenom}(Etudiant). Le symbole * sélectionne toutes les colonnes.

Sélection (choix des lignes)

La sélection garde les lignes vérifiant une condition. En algèbre relationnelle on la note σ (sigma). SELECT * FROM Etudiant WHERE age ≥ 20; correspond à σ_{age ≥ 20}(Etudiant).

DISTINCT — éliminer les doublons

Le mot-clé DISTINCT supprime les lignes en double dans le résultat. Pour obtenir la liste des âges sans répétition :

SELECT DISTINCT age FROM Etudiant;

Opérateurs de condition

7. Opérateurs ensemblistes et produit cartésien

On peut combiner les résultats de deux requêtes SELECT qui ont le même nombre de colonnes, de types compatibles, par les opérateurs ensemblistes :

SELECT nom FROM Etudiant WHERE id_dep = 1
EXCEPT
SELECT nom FROM Etudiant WHERE age > 20;

Produit cartésien

Le produit cartésien de deux tables associe chaque ligne de la première à chaque ligne de la seconde. Si A a m lignes et B a n lignes, le résultat en a m × n. On l'obtient en listant deux tables dans le FROM :

SELECT * FROM Etudiant, Departement;   -- toutes les combinaisons

8. Les jointures internes

Une jointure interne combine les lignes de deux tables en ne gardant que les couples qui vérifient une condition de liaison, typiquement l'égalité d'une clé étrangère et d'une clé primaire. C'est un produit cartésien immédiatement filtré.

SELECT Etudiant.nom, Departement.nom
FROM   Etudiant
JOIN   Departement ON Etudiant.id_dep = Departement.id;

On relie ainsi chaque étudiant au nom de son département. Pour une requête croisant trois tables (les notes par module), on enchaîne les jointures :

SELECT Etudiant.nom, Module.intitule, Inscription.note
FROM   Inscription
JOIN   Etudiant ON Inscription.id_etudiant = Etudiant.id
JOIN   Module   ON Inscription.code_module = Module.code;

9. Renommage avec AS

Le mot-clé AS donne un alias (nom temporaire) à une colonne ou à une table, le temps de la requête. Cela rend les résultats plus lisibles et raccourcit les jointures.

SELECT  E.nom AS etudiant,
        D.nom AS departement
FROM    Etudiant AS E
JOIN    Departement AS D ON E.id_dep = D.id;

Ici la colonne résultat s'appelle etudiant au lieu de nom, et l'on écrit E et D à la place des noms complets. Le renommage de table est indispensable pour l'auto-jointure (joindre une table avec elle-même).

10. Fonctions d'agrégation et GROUP BY

Une fonction d'agrégation calcule une valeur unique à partir d'un ensemble de lignes :

Sans GROUP BY, l'agrégat porte sur toute la table (un seul groupe) :

SELECT COUNT(*), AVG(age) FROM Etudiant;   -- 1 ligne résultat

La clause GROUP BY partitionne les lignes selon une (ou plusieurs) colonnes, puis applique l'agrégat à chaque groupe. Pour la note moyenne par module :

SELECT   code_module, AVG(note) AS moyenne
FROM     Inscription
GROUP BY code_module;

11. Filtrer les agrégats avec HAVING

La clause HAVING filtre les groupes après agrégation, comme WHERE filtre les lignes avant. Pour ne garder que les modules dont la moyenne dépasse 12 :

SELECT   code_module, AVG(note) AS moyenne
FROM     Inscription
GROUP BY code_module
HAVING   AVG(note) > 12;

12. Tri et limitation : ORDER BY, LIMIT, OFFSET

ORDER BY trie les lignes du résultat selon une ou plusieurs colonnes : ASC (croissant, par défaut) ou DESC (décroissant). LIMIT borne le nombre de lignes affichées, et OFFSET en saute un certain nombre au début.

SELECT nom, age FROM Etudiant
ORDER BY age DESC, nom ASC
LIMIT 5 OFFSET 2;    -- saute 2 lignes, en affiche 5

13. Requêtes imbriquées (sous-requêtes)

Une sous-requête est une requête SELECT placée à l'intérieur d'une autre, entre parenthèses. Elle peut apparaître dans une clause WHERE, FROM ou HAVING. Elle sert à comparer avec une valeur ou un ensemble calculés dynamiquement.

Les étudiants plus âgés que la moyenne :

SELECT nom, age FROM Etudiant
WHERE age > (SELECT AVG(age) FROM Etudiant);

Avec IN / NOT IN, on compare à un ensemble de valeurs renvoyé par la sous-requête. Les étudiants inscrits à au moins un module :

SELECT nom FROM Etudiant
WHERE id IN (SELECT id_etudiant FROM Inscription);

Avec EXISTS / NOT EXISTS, on teste si la sous-requête renvoie au moins une ligne. Les départements qui ont au moins un étudiant :

SELECT nom FROM Departement D
WHERE EXISTS (SELECT 1 FROM Etudiant E WHERE E.id_dep = D.id);

14. Récapitulatif

Informatique CPGE MP/PSI · Les bases de données relationnelles