Bases de données relationnelles et SQL

On donne la relation livre(isbn, titre, annee, id_auteur). On affiche trois n-uplets : (978-2070, 'Candide', 1759, 7), (978-2253, 'Germinal', 1885, 12), (978-2070, 'Zadig', 1747, 7). Nommez la relation, ses attributs, proposez un domaine pour chacun, et indiquez combien de n-uplets sont présents. Y a-t-il un problème ?

1. 01Identifier relation et attributs

   La relation s'appelle livre. Ses attributs (colonnes) sont isbn, titre, annee et id_auteur. C'est l'intension (le schéma) ; les trois lignes affichées sont l'extension (les données).

2. 02Proposer un domaine pour chaque attribut

   isbn : chaîne de caractères (le tiret et les chiffres en font un identifiant texte, pas un nombre). titre : chaîne de caractères. annee : entier. id_auteur : entier (il référencera plus tard un auteur).

   $$\mathtt{livre}(\underline{\mathtt{isbn}},\ \mathtt{titre},\ \mathtt{annee},\ \mathtt{id\_auteur})$$
3. 03Compter les n-uplets et repérer l'anomalie

   Trois lignes sont affichées, mais deux portent le même isbn 978-2070 pour deux titres différents (« Candide » et « Zadig »). Si isbn est censé identifier de façon unique un livre, ces deux n-uplets violent l'unicité attendue de cet identifiant : isbn ne peut pas servir de clé primaire telle quelle.

Résultat : Relation livre, quatre attributs (isbn : texte ; titre : texte ; annee : entier ; id_auteur : entier), trois n-uplets affichés. Anomalie : l'isbn 978-2070 apparaît deux fois pour deux livres distincts — il ne peut donc pas faire office de clé primaire, qui doit être unique.

On a auteur(id, nom) et livre(isbn, titre, id_auteur), id_auteur étant clé étrangère vers auteur.id. auteur contient les id 7 et 12. On tente : (1) insérer livre('978-1', 'Essai', 99) ; (2) supprimer l'auteur 7 alors que le livre '978-1' (s'il existait) ou tout autre livre le référence. Pour chaque cas, dites la contrainte concernée et le verdict du SGBD.

1. 01Cas (1) — insertion d'un livre

   On insère un livre avec id_auteur = 99. Or aucun auteur n'a l'id 99 (seuls 7 et 12 existent). La clé étrangère pointerait vers une clé primaire inexistante : c'est une violation de l'intégrité référentielle.

   $$99 \notin \{\,a.\mathtt{id}\ :\ a \in \mathtt{auteur}\,\} = \{7,\,12\}$$
2. 02Verdict du cas (1)

   Le SGBD refuse l'insertion. Pour qu'elle réussisse, il faudrait d'abord insérer l'auteur 99 dans auteur, puis insérer le livre.

3. 03Cas (2) — suppression d'un auteur référencé

   On veut supprimer l'auteur 7. Si au moins un livre porte id_auteur = 7, supprimer l'auteur laisserait ces livres avec une clé étrangère pointant vers un n-uplet disparu : ce serait à nouveau une violation de l'intégrité référentielle.

4. 04Verdict du cas (2)

   Tant que des livres référencent l'auteur 7, le SGBD refuse la suppression (sauf politique ON DELETE particulière). Il faut d'abord traiter les livres concernés (les supprimer ou réaffecter leur id_auteur).

Résultat : (1) Insertion refusée : id_auteur = 99 n'existe pas dans auteur (intégrité référentielle). (2) Suppression de l'auteur 7 refusée tant qu'un livre le référence (intégrité référentielle). Dans les deux cas, c'est le SGBD qui fait respecter automatiquement la contrainte.

Pour une billetterie en ligne, identifiez le service du SGBD mobilisé dans chacune de ces situations : (a) deux clients tentent au même instant d'acheter le dernier siège ; (b) après une coupure de courant, on doit retrouver toutes les ventes validées ; (c) une recherche par numéro de commande doit répondre instantanément sur des millions de lignes ; (d) seul un administrateur peut consulter les coordonnées bancaires.

1. 01(a) Dernier siège, deux clients simultanés

   Deux écritures concurrentes sur la même donnée : c'est la gestion des accès concurrents. Via une transaction, le SGBD sérialise les opérations pour qu'un seul des deux clients obtienne le siège, sans double vente.

2. 02(b) Retrouver les ventes après coupure

   Les données doivent survivre à l'arrêt brutal de la machine : c'est la persistance (écriture durable sur disque), renforcée par la sécurisation (journalisation/reprise après panne) qui garantit qu'une vente validée n'est pas perdue.

3. 03(c) Recherche instantanée sur des millions de lignes

   Répondre vite sur un gros volume relève de l'efficacité : le SGBD utilise un index sur le numéro de commande pour ne pas parcourir toute la table.

4. 04(d) Accès réservé à l'administrateur

   Restreindre l'accès à certaines données selon l'utilisateur relève de la sécurisation (gestion des droits d'accès).

Résultat : (a) accès concurrents ; (b) persistance (et sécurisation) ; (c) efficacité (indexation) ; (d) sécurisation (droits d'accès). Ces quatre situations illustrent les quatre services attendus d'un SGBD relationnel.

Avec auteur(id, nom) et livre(isbn, titre, annee, id_auteur), écrivez : (1) les titres des livres parus après 1850, triés par année croissante ; (2) le titre de chaque livre avec le nom de son auteur ; (3) le nombre de livres par auteur, du plus prolifique au moins prolifique.

1. 01(1) Sélection + projection + tri (une table)

   Projection sur titre (et annee pour le tri), sélection des lignes telles que annee > 1850, tri croissant par annee.

   $$\texttt{SELECT titre, annee FROM livre WHERE annee > 1850 ORDER BY annee ASC;}$$
2. 02(2) Jointure de deux tables

   On relie chaque livre à son auteur par l'égalité clé étrangère = clé primaire, puis on projette le titre et le nom.

   $$\texttt{SELECT l.titre, a.nom FROM livre AS l JOIN auteur AS a ON l.id\_auteur = a.id;}$$
3. 03(3) Jointure + agrégation + regroupement + tri

   On joint livre et auteur, on regroupe par auteur, on compte les livres de chaque groupe avec COUNT(*), puis on trie ce comptage par ordre décroissant.

   $$\texttt{SELECT a.nom, COUNT(*) AS nb FROM auteur AS a JOIN livre AS l ON l.id\_auteur = a.id GROUP BY a.id, a.nom ORDER BY nb DESC;}$$
4. 04Vérifier le résultat de (3) sur le jeu d'exemple

   Avec Candide (id_auteur 7) et Zadig (id_auteur 7) pour Voltaire, et Germinal (id_auteur 12) pour Zola : Voltaire totalise 2 livres, Zola 1. La requête renvoie donc (Voltaire, 2) puis (Zola, 1) grâce au tri décroissant.

Résultat : (1) renvoie les titres d'après-1850 triés par année ; (2) apparie chaque titre à son auteur par la jointure ; (3) renvoie (Voltaire, 2) puis (Zola, 1). La clé du sujet est de joindre sur l.id_auteur = a.id, puis de regrouper par auteur avant d'agréger.

Avec auteur(id, nom) et livre(isbn, titre, annee, id_auteur) : (1) ajoutez l'auteur (20, 'Hugo') ; (2) ajoutez ('978-9', 'Les Misérables', 1862, 20) ; (3) corrigez son année en 1869 ; (4) supprimez tous les livres parus avant 1800. Justifiez l'ordre (1) puis (2).

1. 01(1) Insérer l'auteur

   On crée d'abord l'auteur, car le livre y fera référence par sa clé étrangère.

   $$\texttt{INSERT INTO auteur (id, nom) VALUES (20, 'Hugo');}$$
2. 02(2) Insérer le livre référençant cet auteur

   Maintenant que l'auteur 20 existe, la clé étrangère id_auteur = 20 satisfait l'intégrité référentielle.

   $$\texttt{INSERT INTO livre (isbn, titre, annee, id\_auteur) VALUES ('978-9', 'Les Misérables', 1862, 20);}$$
3. 03(3) Corriger l'année du livre

   On cible le seul livre concerné par sa clé primaire isbn, et on remplace l'année.

   $$\texttt{UPDATE livre SET annee = 1869 WHERE isbn = '978-9';}$$
4. 04(4) Supprimer les livres antérieurs à 1800

   La condition WHERE annee < 1800 limite la suppression aux seules lignes voulues ; le livre de 1869 n'est pas touché.

   $$\texttt{DELETE FROM livre WHERE annee < 1800;}$$
5. 05Justifier l'ordre (1) puis (2)

   Si l'on insérait le livre avant l'auteur, sa clé étrangère id_auteur = 20 pointerait vers un auteur inexistant : l'intégrité référentielle serait violée et le SGBD refuserait l'insertion. On crée donc toujours la ligne référencée avant la ligne qui la référence.

Résultat : Les quatre requêtes s'écrivent INSERT / INSERT / UPDATE / DELETE avec, à chaque mise à jour ciblée, une clause WHERE sur la clé primaire ou sur la condition voulue. L'ordre (1) avant (2) est imposé par l'intégrité référentielle : l'auteur doit exister avant le livre qui le référence.