Les bases de données : le modèle relationnel et les premières requêtes

Schémas : $\text{auteur}(\text{id\_auteur}:\text{entier},\text{nom}:\text{chaıˆne},\text{naissance}:\text{entier})$, clé id_auteur ; $\text{livre}(\text{id\_livre},\text{titre},\text{id\_auteur},\text{annee},\text{pages})$, clé id_livre ; $\text{usager}(\text{id\_usager},\text{nom},\text{ville})$, clé id_usager ; $\text{emprunt}(\text{id\_usager},\text{id\_livre},\text{jour})$, clé composée $(\text{id\_usager},\text{id\_livre},\text{jour})$. Le couple sans le jour échouerait dès qu'un usager réemprunte le même livre à une autre date — deux lignes porteraient la même valeur de « clé », interdit. Clés étrangères : livre.id_auteur $\to$ auteur (association $1-\ast$ « écrit ») ; emprunt.id_usager $\to$ usager et emprunt.id_livre $\to$ livre — les deux moitiés $1-\ast$ de l'association $\ast -\ast$ « emprunte ». (Lire un schéma avant de le requêter est le réflexe professionnel : les clés disent ce qui identifie, les clés étrangères dessinent le graphe des tables — au chapitre 16, les jointures suivront exactement ces flèches.)

SELECT titre, annee FROM livre WHERE pages > 500;                  -- (a)
SELECT nom FROM usager WHERE NOT ville = 'Paris';                  -- (b)
SELECT titre FROM livre WHERE annee >= 1860 AND annee <= 1880;     -- (c)
SELECT nom FROM auteur WHERE naissance = 1828;                     -- (d)

(a) rend Guerre et paix, Anna Karénine, Michel Strogoff ; (b) Alice, Chloé, David, Farid (ville <> 'Paris' est équivalent) ; (c) les cinq livres de $1865$ à $1877$ ; (d) Verne et Tolstoï — nés la même année, et la base le sait. (Discipline du chapitre 1, transposée : avant d'exécuter, prédire le résultat à la main sur les petites tables — l'écart entre prédiction et exécution est, ici aussi, le meilleur des professeurs.)

SELECT titre, 2025 - annee AS age, pages / 16 AS mm
FROM livre
WHERE annee >= 1801 AND annee <= 1900
ORDER BY mm DESC;

Guerre et paix                   | 156 | 96
Anna Karénine                    | 148 | 54
Michel Strogoff                  | 149 | 32
Orgueil et préjugés              | 212 | 30
Vingt mille lieues sous les mers | 155 | 28
De la Terre à la Lune            | 160 | 20

Trois leçons en une requête : les colonnes calculées se trient comme les autres (le ORDER BY peut citer l'alias mm) ; la division pages / 16 entre entiers rend ici un entier (le programme passe outre ces subtilités — retenir seulement qu'un quotient affiché sans décimales doit éveiller l'attention) ; et la borne double s'écrit en deux comparaisons jointes par AND. (Remarquer Orgueil et préjugés : plus vieux mais moins épais que Michel Strogoff — le tri porte sur mm, pas sur l'âge, et les deux colonnes calculées vivent indépendamment.)

SELECT titre, annee FROM livre ORDER BY annee DESC LIMIT 2;        -- (a)
SELECT titre, pages FROM livre
ORDER BY pages DESC LIMIT 1 OFFSET 2;                              -- (b)

(a) Le Blé en herbe ($1923$) puis Les Vrilles de la vigne ($1908$). (b) OFFSET 2 saute les deux plus longs (Guerre et paix, Anna Karénine) : reste Michel Strogoff ($512$). (c) Sans ORDER BY, le moteur rend deux lignes à sa convenance — typiquement les deux premières insérées, qui sont les livres de Verne de $1870$ et $1865$ : ni récents, ni faux, juste non spécifiés ; la requête est syntaxiquement correcte et sémantiquement vide — la pire combinaison (chapitre 1 : l'erreur silencieuse). (d) Avec des ex æquo, le troisième n'est pas défini : selon l'humeur du moteur, l'un ou l'autre sort. Remède : compléter le tri — ORDER BY pages DESC, id_livre — pour le rendre total ; le résultat devient reproductible (et la pagination web cohérente). (Tout classement présenté à un humain doit être total : c'est la stabilité des tris du chapitre 9, revue côté requête.)

SELECT DISTINCT jour FROM emprunt ORDER BY jour;                   -- (a) 10 emprunts, 10 jours
SELECT DISTINCT id_usager, jour FROM emprunt ORDER BY jour;        -- (b)
SELECT DISTINCT id_usager FROM emprunt;                            -- (c) 5 lignes

(a) Dix emprunts à dix dates toutes différentes : le DISTINCT ne supprime rien ici, mais la requête est la bonne — elle resterait juste si deux emprunts partageaient un jour. (b) Le couple est dédoublonné en bloc : Chloé-01/10 et Chloé-02/10 sont deux couples distincts, tous deux conservés. (c) Cinq : le nombre d'usagers distincts ayant emprunté au moins une fois — tous sauf Farid ; c'est déjà un embryon de statistique (le chapitre 16 fera mieux avec COUNT). (d) id_livre est la clé primaire de livre : ses valeurs sont uniques par définition — le DISTINCT est sans effet, et l'écrire révèle qu'on n'a pas lu le schéma. (Règle d'hygiène : chaque DISTINCT doit pouvoir se justifier — « quelles lignes identiques peuvent apparaître, et pourquoi ? » ; un DISTINCT réflexe masque souvent une requête mal posée, et il coûte un dédoublonnage entier.)

SELECT id_livre FROM livre WHERE id_auteur = 1                     -- (a)
UNION
SELECT id_livre FROM livre WHERE annee < 1820;

SELECT id_livre FROM emprunt                                       -- (b)
INTERSECT
SELECT id_livre FROM livre WHERE id_auteur = 3;

SELECT id_livre FROM livre                                         -- (c)
EXCEPT
SELECT id_livre FROM emprunt;

(a) $\{1,2,7\}\cup \{3\}=\{1,2,3,7\}$ — qu'un OR dans un seul WHERE aurait aussi donné : les ensemblistes deviennent indispensables quand les deux moitiés viennent de tables différentes, comme en (b) et (c). (b) emprunts $\{1,4,3,2,6,5,7\}$ $\cap$ Tolstoï $\{4,5\}$ $=\{4,5\}$ : les deux ont circulé. (c) $\{1,\dots ,8\}\setminus \{1,\dots ,7\}=\{8\}$ : Les Vrilles de la vigne dort sur l'étagère. (Les trois opérateurs dédoublonnent leur résultat — sémantique d'ensemble, fidèle à la théorie ; et (c) est le frère de l'EXCEPT du cours sur les usagers : « jamais » se dit toujours par différence.)

Entités : film, salle, spectateur — et seance, entité née d'une association : une séance lie un film à une salle (deux associations $1-\ast$ : un film a plusieurs séances, une salle accueille plusieurs séances). La réservation est une association $\ast -\ast$ entre spectateur et seance $\to$ table d'association. Schéma :

Histoires de contrôle : « le même film passe deux fois le même jour dans deux salles » — deux lignes de seance, licite ✓ ; « un spectateur réserve deux fois la même séance » — interdit par la clé composée de reservation : il doit modifier nb_places, pas dupliquer la ligne — choix de conception qu'on aurait pu faire autrement (ajouter la date de réservation à la clé). (Noter le passage de seance du statut d'association au statut d'entité à part entière, avec son identifiant : dès qu'un lien porte ses propres attributs et que d'autres tables doivent le référencer, on le promeut — la modélisation est un art de décisions explicites, pas un algorithme.)

(a) $8\times 4=32$ lignes : chaque livre accolé à chaque auteur — Guerre et paix apparaît avec Verne, Austen, Tolstoï et Colette. Trois lignes sur quatre sont des accouplements absurdes. (b) Les lignes sensées sont celles où la clé étrangère retrouve sa clé primaire :

SELECT titre, nom
FROM livre, auteur
WHERE livre.id_auteur = auteur.id_auteur;

Vingt mille lieues sous les mers | Verne
De la Terre à la Lune            | Verne
Orgueil et préjugés              | Austen
Guerre et paix                   | Tolstoï
Anna Karénine                    | Tolstoï
Le Blé en herbe                  | Colette
Michel Strogoff                  | Verne
Les Vrilles de la vigne          | Colette

(Les noms d'attributs ambigus se préfixent par leur table : livre.id_auteur.) (c) Huit lignes — une par livre, exactement : chaque livre a un auteur (association $1-\ast$), donc passe le filtre une fois et une seule. Cette construction — produit cartésien $+$ égalité de clés — recolle ce que la modélisation avait séparé : c'est la jointure, l'opération centrale du chapitre 16, qui lui offre sa syntaxe dédiée (JOIN … ON). (La voir naître du produit cartésien immunise contre sa magie apparente : une jointure mal conditionnée — clé oubliée dans le WHERE — redonne le produit cartésien, et ses $32$, ou ses $10^{12}$, lignes : le bogue SQL le plus célèbre.)

Redondance : Verne et sa date de naissance sont recopiés sur ses trois livres — trois fois la même information, et l'espace gaspillé est le moindre des maux. Anomalie de mise à jour : corriger la naissance de Verne ($1828$) exige de modifier trois lignes ; en oublier une, et la base se contredit elle-même — « quelle est la naissance de Verne ? » a deux réponses, et aucun moyen interne de trancher (c'est le bogue de cohérence du chapitre 10, version données : un état que rien ne devait permettre). Anomalie d'insertion : enregistrer un nouvel auteur dont la médiathèque n'a pas encore de livre est impossible — il n'a pas de ligne où exister. Anomalie de suppression : retirer Le Blé en herbe et Les Vrilles de la vigne fait disparaître Colette de la base — supprimer un livre a détruit un auteur. Le schéma du cours répond d'un coup aux quatre : l'information « Verne, $1828$ » vit en un seul exemplaire dans auteur (une mise à jour, un point de vérité), les auteurs existent indépendamment de leurs livres, et la clé étrangère id_auteur relie sans recopier. (C'est le principe le plus fécond de la conception de bases — chaque fait en un seul endroit — et le lecteur l'a déjà rencontré sous d'autres habits : ne pas dupliquer un invariant dans deux variables (chapitre 10), ne pas stocker deux fois une arête… La théorie qui systématise ces idées, la « normalisation », attendra ; l'instinct, lui, est déjà là.)

(a) L'ordre lexicographique compare les chaînes caractère par caractère depuis la gauche (chapitre 2). Au format AAAA-MM-JJ, les champs sont rangés du plus significatif (l'année) au moins significatif (le jour), chacun à largeur fixe grâce aux zéros de tête : deux dates diffèrent d'abord là où elles diffèrent chronologiquement, et la comparaison de caractères y tranche comme la comparaison des nombres (les chiffres sont ordonnés dans la table des caractères). C'est l'argument de la numération positionnelle (chapitre 11), appliqué aux chaînes. (b) Au format français : '02/01/2026' < '15/06/2025' lexicographiquement (le 0 bat le 1) alors que janvier $2026$ est après juin $2025$ — le champ le moins significatif compare en premier. Sans zéros de tête : '2025-9-12' > '2025-10-02' car '9' > '1' — la largeur variable casse l'alignement positionnel. (c)

SELECT id_usager, id_livre, jour FROM emprunt
WHERE jour >= '2025-09-01' AND jour <= '2025-09-30'
ORDER BY jour DESC;

Cinq emprunts, du $20$ au $3$ septembre. La comparaison de chaînes jour >= '2025-09-01' est ici une comparaison de dates parce que le format s'y prête — toute la valeur du choix de conception fait en amont. (Ce format est une norme internationale, et la leçon dépasse SQL : choisir la représentation qui rend les opérations futures triviales — on a trié des dates sans aucune notion de date, comme le chapitre 2 trouvait les anagrammes sans aucune notion d'anagramme, par la seule clé canonique.)