Exercices corrigés de NSI : bases de données relationnelles et SQL

a) La relation s'appelle eleve. Ses attributs sont id, nom et classe. Son degré, c'est-à-dire son nombre d'attributs, vaut 3. Sa cardinalité, c'est-à-dire son nombre de lignes, vaut 4. Une ligne affirme un fait : « il existe un élève, d'identifiant 1, nommé Dupont, inscrit en 1G3 ». Cette lecture en termes de fait n'est pas une image : c'est elle qui justifie que l'ordre des lignes soit sans importance et qu'un doublon soit dépourvu de sens.

b) Un domaine est l'ensemble des valeurs autorisées pour un attribut, autrement dit son type accompagné de ses contraintes. Ici : id est un entier strictement positif ; nom est une chaîne de caractères non vide, d'au plus une centaine de caractères ; classe est une chaîne appartenant à une liste fermée de codes de classes de l'établissement. Le système refusera alors d'écrire un identifiant négatif, un nom vide, ou une classe inexistante comme « 1G9 ». Sans domaine déclaré, ces trois erreurs entrent dans la base et n'en ressortent jamais.

c) La clé primaire est un attribut, ou un groupe d'attributs, dont la valeur identifie de façon unique et non nulle chaque ligne de la relation. L'attribut nom serait un mauvais choix pour trois raisons. Un, l'unicité n'est pas garantie dans le temps : deux homonymes finiront par arriver, et la base deviendrait alors incapable d'enregistrer le second. Deux, la valeur peut CHANGER, par mariage ou correction d'orthographe, et il faudrait alors mettre à jour toutes les tables qui la référencent. Trois, elle est longue, donc coûteuse à indexer et à recopier. Une clé primaire doit être unique, stable et courte, ce qu'un identifiant artificiel garantit par construction.

d) Non, pas selon la définition mathématique : une relation est un ENSEMBLE de n-uplets, et un ensemble ne contient pas deux fois le même élément. Deux lignes identiques n'apporteraient d'ailleurs aucune information, puisqu'elles affirmeraient exactement le même fait. Dans un système réel, l'absence de doublon n'est garantie que si une clé primaire ou une contrainte d'unicité est déclarée : sans elle, la plupart des moteurs acceptent d'insérer deux lignes identiques, ce qui fausse ensuite tout comptage. Déclarer la clé primaire n'est donc pas une formalité, c'est ce qui rend la table conforme au modèle.

e) Une clé candidate est un ensemble d'attributs qui pourrait servir de clé primaire, c'est-à-dire qui identifie de façon unique chaque ligne et dont aucune partie stricte n'a cette propriété. Une relation peut en avoir plusieurs, et l'on en choisit une comme clé primaire. Oui, une clé primaire peut porter sur plusieurs attributs : dans une table de notes, le couple formé de eleve_id et devoir_id est une clé primaire naturelle si l'on décide qu'un élève ne peut avoir qu'une seule note par devoir. On parle alors de clé COMPOSÉE, et elle exprime une règle de gestion, pas seulement une contrainte technique.

a) Une clé étrangère est un attribut, ou un groupe d'attributs, d'une relation, dont la valeur doit obligatoirement correspondre à une valeur de clé primaire existante dans une autre relation, ou bien être nulle si la conception l'autorise. C'est la table note qui porte la contrainte, par son attribut eleve_id ; elle s'appuie sur la table eleve, par sa clé primaire id. Le sens de la flèche compte : la contrainte pèse sur celui qui référence, jamais sur celui qui est référencé.

b) L'insertion est REFUSÉE. Aucun élève d'identifiant 9 n'existe dans la table eleve, donc la ligne violerait l'intégrité référentielle : elle affirmerait la note d'un élève inexistant. Le système lève une erreur explicite et n'écrit rien. C'est exactement le service qu'on attend d'une base de données : une donnée incohérente est rejetée AU MOMENT de l'écriture, et non découverte six mois plus tard par un calcul de moyenne qui tombe faux.

c) Trois comportements possibles, tous prévus par la norme. Le REFUS : la suppression est rejetée tant que des notes référencent l'élève, ce qui oblige à traiter les notes d'abord. La SUPPRESSION EN CASCADE : les notes de l'élève sont supprimées automatiquement en même temps que lui. La MISE À NUL : eleve_id passe à la valeur nulle dans les notes concernées, qui deviennent orphelines mais subsistent. Dans un carnet de notes, il faut choisir le REFUS : une suppression en cascade ferait disparaître silencieusement des évaluations, et une mise à nul produirait des notes qui n'appartiennent à personne, donc inexploitables. Le refus force à décider explicitement, ce qui est exactement ce qu'on veut sur des données scolaires.

d) Oui, c'est ce qu'on appelle une clé étrangère réflexive. Exemple : dans une table eleve, un attribut tuteur_id qui référence l'identifiant d'un autre élève de la même table, dans un système de tutorat entre pairs. On obtient alors un GRAPHE ORIENTÉ dont les sommets sont les lignes de la table, ou un arbre si l'on ajoute la contrainte qu'aucun cycle n'est permis et qu'un seul élément n'a pas de tuteur. C'est ainsi qu'on représente une hiérarchie, une arborescence de catégories ou un fil de discussion en réponses imbriquées.

e) Trois arguments. La REDONDANCE : le nom serait stocké autant de fois qu'il y a de notes, ce qui gaspille de l'espace et surtout multiplie les occasions d'incohérence. L'ANOMALIE DE MISE À JOUR : corriger une faute d'orthographe dans un nom obligerait à modifier toutes les lignes de notes, et en oublier une seule crée deux élèves là où il n'y en a qu'un. La PERTE DE LA CONTRAINTE : un nom recopié n'est plus vérifié par personne, alors qu'une clé étrangère est contrôlée à chaque écriture. On peut ajouter un quatrième argument, purement pratique : la jointure que l'on cherchait à éviter coûte, sur une clé indexée, à peine plus qu'une lecture directe, alors que la dénormalisation coûte pour toujours.

a) La table de gauche répète le couple Dupont et 1G3 sur trois lignes, une par note. L'information « Dupont est en 1G3 » y est donc écrite TROIS fois, alors qu'il s'agit d'un seul fait. Dans la conception de droite, elle n'est écrite qu'une fois, dans la table des élèves, et les trois notes ne portent que l'identifiant.

b) Dans la conception de droite, on modifie UNE ligne de la table des élèves, et c'est fini : les trois notes suivent automatiquement, puisqu'elles ne stockent que l'identifiant. Dans celle de gauche, il faut modifier les trois lignes concernées. Le risque propre à la première conception est l'ANOMALIE DE MISE À JOUR : si l'une des trois lignes est oubliée, la base affirme simultanément que Dupont est en 1G3 et en 1G4. Elle contient alors une contradiction, et aucune requête ne pourra plus trancher, puisque les deux réponses y sont écrites noir sur blanc.

c) Dans la conception de droite, on insère simplement une ligne dans la table des élèves, sans aucune note : c'est parfaitement légitime. Dans celle de gauche, une ligne exige une valeur de note ; il faudrait inventer une note fictive, ou laisser une valeur nulle qui faussera tous les calculs de moyenne. On appelle cela une ANOMALIE D'INSERTION : la conception interdit d'enregistrer un fait, l'inscription de l'élève, parce qu'un autre fait sans rapport, sa première note, n'existe pas encore.

d) Dans la conception de droite, on perd la note et rien d'autre : Nadeau reste inscrit en 1G1. Dans celle de gauche, la suppression de sa seule ligne fait disparaître du même coup l'existence de Nadeau et son appartenance à la 1G1 : on perd un fait qu'on ne voulait pas supprimer. C'est l'ANOMALIE DE SUPPRESSION, la troisième du trio classique.

e) La règle : une table doit décrire UNE SEULE sorte de chose, et chacun de ses attributs doit dépendre de la clé primaire entière et de rien d'autre. Ici, la classe dépend de l'élève et non de la note : elle n'a donc rien à faire dans une table de notes. Formulée autrement, chaque fait doit être enregistré à un seul endroit ; dès qu'une information est recopiée, elle peut se contredire, et une base qui se contredit n'est plus une source de vérité. C'est cette exigence qu'on appelle la normalisation, et les trois anomalies des questions b, c et d en sont la justification complète.

a) La requête : SELECT nom FROM eleve WHERE classe = '1G3'. Le résultat contient deux lignes, Dupont et Ferrand. Noter que le résultat d'une requête est lui-même une relation, ici de degré 1 et de cardinalité 2 : c'est ce qui permet de composer les requêtes entre elles.

b) La requête : SELECT valeur FROM note WHERE valeur > 12 ORDER BY valeur DESC. Les notes strictement supérieures à 12 sont 15, 14 et 17. Triées par ordre décroissant : 17, 15, 14. Sans la clause ORDER BY, l'ordre des lignes n'est pas garanti : il dépend du plan d'exécution choisi par le moteur et peut changer d'une exécution à l'autre, ce qui est une source classique de tests instables.

c) La requête : SELECT DISTINCT eleve_id FROM note. Le résultat est 1, 2, 3, 4, soit quatre lignes, alors que la table en compte sept. Le mot-clé DISTINCT élimine les doublons du résultat : sans lui, l'identifiant 1 apparaîtrait deux fois et les identifiants 3 et 4 également. Il agit APRÈS la projection, ce qui est important : projeter d'abord peut créer des doublons qui n'existaient pas dans la table de départ, exactement comme au chapitre des données en tables.

d) Sur ces données, WHERE valeur > 12 ramène 15, 14 et 17, soit trois lignes ; WHERE valeur >= 12 ramène en plus la note 12, soit quatre lignes. La différence porte donc exactement sur les lignes dont la valeur vaut la borne. Pour la classe : WHERE classe = '1G3' ramène les seuls élèves de 1G3, soit Dupont et Ferrand ; WHERE classe LIKE '1G%' ramène tous ceux dont le code commence par 1G, donc aussi Nadeau en 1G1, soit trois élèves. Le signe pour cent remplace n'importe quelle suite de caractères, y compris vide.

e) Parce qu'en SQL, la valeur nulle ne signifie pas « une valeur particulière » mais « valeur inconnue ». Toute comparaison avec une valeur inconnue donne un résultat inconnu, ni vrai ni faux, et la clause WHERE ne retient que ce qui est VRAI : les lignes à valeur nulle sont donc écartées, y compris par une condition de différence. C'est la logique à trois valeurs de SQL, et elle surprend toujours. La bonne écriture est WHERE valeur <> 12 OR valeur IS NULL, ou plus simplement WHERE valeur IS NULL quand on cherche précisément ces lignes. On retient qu'on ne compare jamais une valeur nulle avec le signe égale : on utilise IS NULL et IS NOT NULL.

a) La requête : SELECT eleve.nom, note.valeur FROM note JOIN eleve ON note.eleve_id = eleve.id. Le résultat, sept lignes : Dupont 12, Dupont 15, Nadeau 9, Ferrand 14, Ferrand 17, Ouellet 8, Ouellet 11. Chaque ligne de note trouve exactement un élève, puisque eleve_id est une clé étrangère non nulle vers une clé primaire : le nombre de lignes du résultat est donc égal au nombre de lignes de note.

b) La requête : SELECT DISTINCT eleve.nom FROM note JOIN eleve ON note.eleve_id = eleve.id WHERE note.valeur > 13. Les notes strictement supérieures à 13 sont 15, appartenant à Dupont, 14 et 17, appartenant toutes deux à Ferrand. Sans DISTINCT, Ferrand apparaîtrait DEUX fois, une par note retenue. Le résultat correct est donc Dupont et Ferrand, deux lignes. Le réflexe à prendre : dès que la question porte sur les élèves et que le filtre porte sur les notes, il faut se demander si un élève peut satisfaire le filtre plusieurs fois.

c) Sans condition de jointure, le moteur associe CHAQUE ligne de note à CHAQUE ligne de eleve, sans aucun rapport entre elles. On obtient $7 \times 4 = 28$ lignes, dont 21 sont fausses : elles attribuent la note d'un élève à un autre. Ce résultat s'appelle le PRODUIT CARTÉSIEN. Il est particulièrement dangereux parce qu'il ne provoque aucune erreur : la requête s'exécute, renvoie des lignes plausibles, et les totaux calculés ensuite sont multipliés par quatre. Le symptôme à connaître est un nombre de lignes anormalement grand, souvent un multiple exact du nombre attendu.

d) La requête : SELECT eleve.nom, note.valeur FROM eleve LEFT JOIN note ON note.eleve_id = eleve.id. Le type de jointure est la jointure EXTERNE gauche : elle conserve toutes les lignes de la table de gauche, ici eleve, même celles qui n'ont aucune correspondance à droite. Pour un élève sans note, la colonne valeur contient la valeur NULLE, et il faut alors se souvenir de la question e de l'exercice 4 : un calcul de moyenne ignorera cette ligne, mais un comptage de lignes, lui, la comptera. C'est exactement la différence entre COUNT(*) et COUNT(valeur).

e) La requête : SELECT eleve.nom, devoir.matiere, note.valeur FROM note JOIN eleve ON note.eleve_id = eleve.id JOIN devoir ON note.devoir_id = devoir.id WHERE devoir.matiere = 'maths'. Le résultat contient trois lignes, les notes du devoir 10 : Dupont maths 12, Nadeau maths 9, Ferrand maths 14. Le principe se généralise : on part de la table centrale, celle qui porte les clés étrangères, et on ajoute une jointure par table à rejoindre, chacune avec sa propre condition. La table note est ici la table d'ASSOCIATION entre élèves et devoirs.

a) La requête : SELECT COUNT(*), MIN(valeur), MAX(valeur), AVG(valeur) FROM note. Les sept notes sont 12, 15, 9, 14, 17, 8 et 11. Le compte vaut 7, le minimum 8, le maximum 17, et la moyenne $86 / 7 \approx 12{,}29$. Attention à COUNT : COUNT(*) compte les LIGNES, COUNT(valeur) compte les valeurs non nulles, et les deux diffèrent dès qu'une valeur nulle existe.

b) La requête : SELECT eleve.nom, AVG(note.valeur) FROM note JOIN eleve ON note.eleve_id = eleve.id GROUP BY eleve.id, eleve.nom. Les moyennes : Dupont, notes 12 et 15, moyenne $27 / 2 = 13{,}50$. Nadeau, note 9 seule, moyenne $9{,}00$. Ferrand, notes 14 et 17, moyenne $31 / 2 = 15{,}50$. Ouellet, notes 8 et 11, moyenne $19 / 2 = 9{,}50$. Il faut regrouper sur l'identifiant et non sur le seul nom : deux homonymes seraient sinon fondus dans un même groupe, ce qui est précisément le défaut que la clé primaire servait à éviter.

c) La requête : la même, complétée par HAVING AVG(note.valeur) >= 12. Le résultat contient Dupont, moyenne 13,50, et Ferrand, moyenne 15,50. La clause WHERE ne convient pas parce qu'elle s'applique AVANT le regroupement, donc à des lignes individuelles, alors que la moyenne n'existe qu'après regroupement. Écrire WHERE AVG(...) provoque une erreur de syntaxe dans tout moteur correct. L'ordre logique d'exécution est immuable : FROM, puis JOIN, puis WHERE, puis GROUP BY, puis HAVING, puis SELECT, puis ORDER BY. WHERE filtre des lignes, HAVING filtre des groupes.

d) La requête : SELECT devoir.matiere, COUNT(*), AVG(note.valeur) FROM note JOIN devoir ON note.devoir_id = devoir.id GROUP BY devoir.matiere. Mathématiques, devoir 10 : notes 12, 9 et 14, soit 3 notes et une moyenne de $35 / 3 \approx 11{,}67$. Physique, devoir 11 : notes 15 et 8, soit 2 notes et une moyenne de $23 / 2 = 11{,}50$. NSI, devoir 12 : notes 17 et 11, soit 2 notes et une moyenne de $28 / 2 = 14{,}00$. On vérifie le total des effectifs : $3 + 2 + 2 = 7$, ce qui correspond bien au nombre de notes.

e) La requête : SELECT SUM(note.valeur * devoir.coefficient) / SUM(devoir.coefficient) FROM note JOIN devoir ON note.devoir_id = devoir.id. Le numérateur vaut $12 \times 4 + 15 \times 3 + 9 \times 4 + 14 \times 4 + 17 \times 4 + 8 \times 3 + 11 \times 4 = 48 + 45 + 36 + 56 + 68 + 24 + 44 = 321$. Le dénominateur vaut $4 + 3 + 4 + 4 + 4 + 3 + 4 = 26$. La moyenne pondérée vaut donc $321 / 26 \approx 12{,}35$, contre $12{,}29$ pour la moyenne simple. L'écart est faible mais réel, et il vient de ce que les matières à coefficient 4 portent ici des notes légèrement meilleures. Le point de méthode est essentiel : on ne calcule JAMAIS une moyenne pondérée en moyennant des moyennes, on somme les produits et on divise une seule fois, exactement comme au chapitre des données en tables.

a) La commande : INSERT INTO eleve (id, nom, classe) VALUES (5, 'Tremblay', 'TG2'). Si l'identifiant 5 existe déjà, l'insertion est REFUSÉE et une erreur de violation de contrainte d'unicité est levée : la clé primaire garantit qu'aucune ligne existante ne sera écrasée. C'est un comportement voulu, et c'est ce qui distingue une insertion d'une mise à jour. Certains moteurs proposent une variante qui met à jour la ligne si elle existe, mais elle doit être demandée explicitement, jamais subie.

b) Première commande : UPDATE note SET valeur = 13 WHERE id = 102. Seconde commande : UPDATE note SET valeur = valeur + 1 WHERE devoir_id = 11. Les notes du devoir 11 sont 15, pour Dupont, et 8, pour Ouellet : elles deviennent 16 et 9. Noter l'écriture valeur = valeur + 1, qui lit et écrit le même attribut dans la même commande : c'est parfaitement légal, le moteur évaluant le membre droit sur la valeur d'avant la mise à jour.

c) Sans clause WHERE, la mise à jour porte sur TOUTES les lignes de la table. Les sept notes 12, 15, 9, 14, 17, 8 et 11 deviennent 13, 16, 10, 15, 18, 9 et 12. Aucun message d'erreur n'apparaît parce que la commande est parfaitement valide : l'absence de WHERE signifie « toutes les lignes », ce qui est un usage légitime dans d'autres contextes. Le moteur exécute donc exactement ce qui lui a été demandé, et il annonce même le succès en indiquant sept lignes modifiées. Ce nombre est d'ailleurs le seul signal disponible : voir sept là où l'on attendait deux est ce qui doit alerter.

d) La commande : DELETE FROM note WHERE valeur < 10. Les notes strictement inférieures à 10 sont 9 et 8, donc DEUX lignes sont supprimées. La vérification à faire avant est de remplacer DELETE par SELECT, en gardant exactement la même clause WHERE : SELECT * FROM note WHERE valeur < 10 affiche les lignes qui seront détruites, sans rien détruire. Cette habitude, qui coûte cinq secondes, est la plus rentable de tout le chapitre : elle transforme une opération irréversible en une opération vérifiée.

e) Quatre précautions. Un, SAUVEGARDER la base avant toute opération d'écriture en masse. Deux, ÉCRIRE LA REQUÊTE EN SELECT D'ABORD, avec le même filtre, pour voir ce qui sera touché. Trois, travailler dans une TRANSACTION, afin de pouvoir tout annuler si le nombre de lignes affectées ne correspond pas à l'attendu. Quatre, limiter les DROITS du compte utilisé : un compte de lecture seule pour les requêtes d'analyse rend impossible toute écriture accidentelle. C'est la deuxième qui protège directement de l'erreur de la question c, puisqu'elle aurait affiché sept lignes au lieu de deux avant toute modification ; la troisième aurait permis de réparer après coup.

1) FAUX. Une clé primaire doit être unique, non nulle et STABLE. L'absence actuelle de doublon ne garantit rien pour l'avenir : deux homonymes finiront par arriver et la base refusera alors d'enregistrer le second. De plus un nom change, et toutes les clés étrangères qui le référenceraient devraient être mises à jour. Énoncé correct : une clé primaire doit être un identifiant artificiel, stable et sans signification métier, sauf clé naturelle réellement immuable.

2) FAUX, elles ne s'appliquent pas au même objet. WHERE filtre des LIGNES, avant tout regroupement, et ne peut donc pas porter sur un agrégat. HAVING filtre des GROUPES, après regroupement, et porte précisément sur des agrégats. Énoncé correct : WHERE s'exécute avant GROUP BY et HAVING après ; écrire WHERE AVG(...) est une erreur de syntaxe, et écrire HAVING sur une colonne non agrégée est au mieux inutile.

3) FAUX, et c'est bien le problème. Le moteur produit le PRODUIT CARTÉSIEN, c'est-à-dire toutes les associations possibles : $7 \times 4 = 28$ lignes ici, dont 21 sont fausses. Aucune erreur n'est signalée. Énoncé correct : une jointure sans condition est légale et renvoie le produit cartésien ; le symptôme est un nombre de lignes anormalement grand, souvent un multiple exact du nombre attendu.

4) FAUX pour les lignes dont la valeur est NULLE. En SQL, la valeur nulle signifie « inconnue », et toute comparaison avec elle donne un résultat inconnu, que la clause WHERE écarte. Énoncé correct : WHERE valeur <> 12 ramène les lignes dont la valeur est connue et différente de 12 ; pour inclure les valeurs nulles il faut écrire WHERE valeur <> 12 OR valeur IS NULL.

5) FAUX sur les deux points. Sur le fond, cette dénormalisation crée les trois anomalies de l'exercice 3 : mise à jour, insertion et suppression, et elle supprime la contrainte d'intégrité qui vérifiait le lien. Sur la performance, une jointure sur une clé primaire indexée coûte à peine plus qu'une lecture directe, alors que le nom recopié alourdit chaque ligne et donc chaque lecture. Énoncé correct : on ne dénormalise que sur mesure démontrée, jamais par principe, et jamais sur des données susceptibles de changer.

a) T1 lit 100, T2 lit 100, T1 écrit $100 - 30 = 70$, T2 écrit $100 - 50 = 50$, puis les deux valident. Le solde final vaut 50, puisque l'écriture de T2 recouvre celle de T1. Il devrait valoir $100 - 30 - 50 = 20$. La mise à jour perdue est celle de T1 : T2 avait lu le solde AVANT que T1 ne l'écrive, donc son calcul reposait sur une valeur devenue périmée, et son écriture a effacé le travail de T1 sans même s'en apercevoir. Ce défaut porte un nom, la mise à jour perdue, et il ne se produit que si les deux transactions se chevauchent.

b) Une transaction est un groupe d'opérations traité comme une SEULE opération indivisible du point de vue de la base. Les quatre propriétés garanties sont l'atomicité, la cohérence, l'isolation et la durabilité. ATOMICITÉ : la transaction est exécutée entièrement ou pas du tout, jamais à moitié. COHÉRENCE : elle fait passer la base d'un état vérifiant toutes les contraintes à un autre état les vérifiant également. ISOLATION : son déroulement ne doit pas être perturbé par les transactions concurrentes, et le résultat doit être le même que si elles s'étaient exécutées l'une après l'autre. DURABILITÉ : une fois validée, elle survit à toute panne, y compris une coupure de courant immédiate.

c) C'est l'ISOLATION qui est violée : le résultat obtenu, 50, n'est celui d'aucune exécution où T1 et T2 se suivraient. En les exécutant l'une puis l'autre, dans un ordre ou dans l'autre, on obtiendrait toujours 20. Le mécanisme qui l'empêche est le VERROUILLAGE : lorsqu'une transaction lit une donnée qu'elle compte modifier, elle pose un verrou dessus, et toute autre transaction qui tente d'y accéder est mise en attente jusqu'à la validation de la première. T2 aurait alors attendu, puis lu 70 au lieu de 100, et écrit 20. Une variante répandue, plus rapide, laisse les deux avancer mais vérifie à la validation que la donnée n'a pas changé depuis la lecture, et rejoue la transaction perdante.

d) C'est l'ATOMICITÉ qui protège la base, secondée par la durabilité. La transaction n'ayant pas été validée au moment de la panne, elle est ANNULÉE dans son intégralité : au redémarrage, le système relit son journal, constate qu'une transaction est restée incomplète, et défait ses écritures partielles. On retrouve donc les deux comptes exactement dans leur état d'avant le virement : les 200 n'ont ni disparu ni été dupliqués. Sans cette garantie, une panne au mauvais moment ferait s'évaporer de l'argent, ce qui est la raison historique pour laquelle les bases de données existent.

e) La situation : T1 détient un verrou sur la ressource A et demande B, tandis que T2 détient un verrou sur B et demande A. Aucune des deux ne peut avancer, et aucune ne relâchera son verrou : elles attendront indéfiniment. Cela s'appelle un INTERBLOCAGE. Deux façons de le traiter. La DÉTECTION : le système construit le graphe des attentes, y cherche un cycle, et lorsqu'il en trouve un, choisit une victime dont il annule la transaction pour libérer ses verrous ; la transaction annulée est ensuite rejouée. La PRÉVENTION : on impose à toutes les transactions de demander leurs verrous dans un ordre total fixé à l'avance, par exemple par ordre croissant d'identifiant de ressource ; aucun cycle ne peut alors se former, mais la contrainte est lourde à respecter dans un programme réel.

a) La relation livre : isbn, clé primaire ; titre ; auteur ; annee. La relation exemplaire : id, clé primaire ; isbn, clé étrangère vers livre ; etat ; date_achat. La relation eleve : id, clé primaire ; nom ; prenom ; classe. La relation emprunt : id, clé primaire ; exemplaire_id, clé étrangère vers exemplaire ; eleve_id, clé étrangère vers eleve ; date_sortie ; date_retour, qui peut être nulle. La table emprunt porte donc deux clés étrangères : c'est une table d'ASSOCIATION entre deux entités, comme la table note l'était entre élèves et devoirs.

b) La table exemplaire existe parce qu'un livre et un exemplaire ne sont pas la même chose : le livre est une oeuvre, décrite par son titre et son auteur ; l'exemplaire est un objet physique, qui a un état, une date d'achat, et qui peut être perdu ou abîmé indépendamment des autres. Si emprunt pointait directement sur livre, la base ne pourrait plus dire QUEL exemplaire est sorti : elle serait incapable de gérer deux emprunts simultanés du même titre, ni de savoir lequel des trois exemplaires n'est jamais revenu, ni de retirer un exemplaire du circuit sans toucher aux autres. On perdrait aussi la possibilité de compter les exemplaires disponibles, qui est pourtant la question la plus fréquente d'un usager.

c) La requête : SELECT livre.titre, eleve.nom FROM emprunt JOIN exemplaire ON emprunt.exemplaire_id = exemplaire.id JOIN livre ON exemplaire.isbn = livre.isbn JOIN eleve ON emprunt.eleve_id = eleve.id WHERE emprunt.date_retour IS NULL. Il faut trois jointures parce que le titre est à deux tables de distance de l'emprunt, en passant par l'exemplaire. Et il faut écrire IS NULL, jamais un signe égale suivi de NULL, pour la raison vue à l'exercice 4.

d) La requête : SELECT livre.titre, COUNT(*) AS n FROM emprunt JOIN exemplaire ON emprunt.exemplaire_id = exemplaire.id JOIN livre ON exemplaire.isbn = livre.isbn GROUP BY livre.isbn, livre.titre ORDER BY n DESC LIMIT 5. Les clauses s'exécutent dans cet ordre : FROM et les jointures rassemblent les lignes ; GROUP BY les regroupe par livre ; COUNT compte chaque groupe ; ORDER BY trie les groupes par effectif décroissant ; LIMIT ne garde que les cinq premiers. Le regroupement porte sur l'ISBN et non sur le seul titre, car deux livres différents peuvent porter le même titre.

e) Sans index sur exemplaire.id, chaque ligne d'emprunt oblige à parcourir toute la table exemplaire pour trouver sa correspondance : cela fait $52\ 000 \times 6\ 800 = 353\ 600\ 000$ lignes examinées, plus $52\ 000 \times 4\ 200 = 218\ 400\ 000$ pour la seconde jointure. Avec un index, chaque recherche coûte de l'ordre de $\log_{2}(6\ 800) \approx 12{,}7$ comparaisons, donc environ $52\ 000 \times 13 = 676\ 000$ opérations, soit environ $523$ fois moins. En pratique, la clé primaire étant automatiquement indexée par tout moteur, la version efficace est celle que l'on obtient par défaut ; le piège survient quand la jointure porte sur une colonne NON indexée, ce qui est précisément le cas des clés étrangères, que la plupart des moteurs n'indexent PAS automatiquement. La conclusion pratique est donc simple : indexer toutes les clés étrangères, systématiquement.

a) La sous-requête calcule la moyenne, $12{,}29$, puis la requête principale garde les notes qui la dépassent : 15, 14 et 17, soit 3 lignes. On ne peut pas écrire WHERE valeur > AVG(valeur), parce que WHERE examine les lignes une par une, avant tout calcul d'agrégat : la moyenne n'existe pas encore à ce moment. La sous-requête la calcule à part, et son résultat devient une simple constante pour la requête principale.

b) La sous-requête renvoie les identifiants des élèves notés au devoir 12, soit 3 et 4. La requête renvoie donc Ferrand et Ouellet. Avec une jointure : SELECT DISTINCT eleve.nom FROM eleve JOIN note ON note.eleve_id = eleve.id WHERE note.devoir_id = 12. Le DISTINCT est indispensable dans la version avec jointure si un élève peut avoir plusieurs notes au même devoir ; la version avec IN, elle, ne produit jamais de doublon, puisqu'elle ne fait que tester l'appartenance de chaque élève à un ensemble.

c) SELECT nom FROM eleve WHERE id NOT IN (SELECT eleve_id FROM note WHERE devoir_id = 10). Les élèves notés au devoir 10 sont 1, 2 et 3 : le résultat est Ouellet, seul. Une jointure ne permet pas d'écrire aussi simplement cette requête, parce qu'elle associe des lignes qui EXISTENT, alors qu'on cherche ici une absence.

d) SELECT eleve.nom FROM eleve JOIN note ON note.eleve_id = eleve.id WHERE note.devoir_id = 10 AND note.valeur = (SELECT MAX(valeur) FROM note WHERE devoir_id = 10). Les notes du devoir 10 sont 12, 9 et 14 : le maximum vaut 14 et le résultat est Ferrand. En cas d'égalité au maximum, la requête renverrait tous les élèves concernés, ce qui est le comportement correct, alors qu'un tri suivi de LIMIT 1 en choisirait un arbitrairement.

e) Si le devoir 10 porte une ligne à eleve_id nul, la sous-requête renvoie 1, 2, 3 et NULL. La condition id NOT IN (1, 2, 3, NULL) signifie id <> 1 AND id <> 2 AND id <> 3 AND id <> NULL ; or la dernière comparaison donne toujours un résultat INCONNU, donc la conjonction n'est jamais vraie, et aucune ligne n'est retenue. C'est la logique à trois valeurs de l'exercice 4, dans sa forme la plus traîtresse. Correction : exclure les nulls dans la sous-requête, en ajoutant AND eleve_id IS NOT NULL, ou employer NOT EXISTS, qui n'a pas ce défaut.

a) PRIMARY KEY impose que l'attribut soit unique et non nul : il identifie la ligne. NOT NULL interdit la valeur nulle. REFERENCES déclare une clé étrangère : la valeur doit exister comme clé primaire dans la table désignée. CHECK impose une condition sur la valeur, ici une note entre 0 et 20 et un coefficient strictement positif. Le script contient 2 contraintes REFERENCES, toutes deux dans la table note.

b) La table note est créée en PREMIER alors qu'elle référence eleve et devoir, qui n'existent pas encore : le moteur ne peut pas vérifier des références vers des tables inconnues. Il faut créer d'abord les tables référencées, eleve et devoir, dans un ordre quelconque entre elles, puis la table note qui s'appuie sur elles. La règle générale : on crée une table après toutes celles qu'elle référence.

c) (1) REFUSÉE par CHECK, puisque 21 dépasse 20. (2) ACCEPTÉE, et c'est le piège : la condition CHECK appliquée à une valeur nulle donne un résultat inconnu, et une contrainte CHECK ne rejette que ce qui est FAUX ; comme valeur n'est pas déclarée NOT NULL, la ligne entre. (3) REFUSÉE par CHECK, puisque le coefficient 0 n'est pas strictement positif. (4) REFUSÉE par NOT NULL sur l'attribut classe. Trois commandes sur quatre sont donc refusées ; pour refuser aussi la deuxième, il faudrait ajouter NOT NULL à valeur.

d) Il faut supprimer d'abord note, puis eleve et devoir, dans l'ordre inverse de la création. Supprimer eleve en premier laisserait des lignes de note référencer une table disparue, ce que l'intégrité référentielle interdit : le moteur refuse la suppression tant qu'une clé étrangère pointe vers la table.

e) Une contrainte d'unicité sur le couple : UNIQUE (eleve_id, devoir_id). Elle n'empêche pas un élève d'avoir plusieurs notes ni un devoir d'en avoir plusieurs, mais elle interdit que le même COUPLE apparaisse deux fois. On aurait aussi pu faire de ce couple la clé primaire de note, à la place de id, comme dans la clé composée de l'exercice 1.

a) Ferrand a les notes 14 et 17 : la fonction renvoie [(14,), (17,)], une liste de deux tuples à un élément. La virgule dans (14,) est ce qui distingue un tuple à un élément d'un simple nombre entre parenthèses.

b) La concaténation produit : SELECT valeur FROM note JOIN eleve ON note.eleve_id = eleve.id WHERE eleve.nom = 'x' OR '1'='1'. La condition se lit eleve.nom = 'x' OU '1' = '1' ; la seconde partie est toujours vraie, donc la condition est vraie pour toutes les lignes, et la requête renvoie les 7 notes de la base, celles de tous les élèves.

c) C'est une INJECTION SQL. Le principe de la faille : le texte saisi par l'utilisateur est collé dans la requête avant son analyse, si bien qu'une apostrophe bien placée ferme la chaîne et transforme la suite de la saisie en code SQL exécuté par le moteur. Selon les droits du compte, la même technique permet de lire d'autres tables, de modifier des notes ou de supprimer des données.

d) La requête renvoie [], la liste vide : aucun élève ne s'appelle littéralement x' OR '1'='1. Avec le point d'interrogation, la requête est analysée par le moteur AVANT de recevoir la valeur, et la valeur est ensuite transmise comme une donnée, jamais relue comme du code : les apostrophes qu'elle contient ne sont plus que des caractères du nom cherché. C'est la seule protection fiable ; filtrer les apostrophes à la main laisse toujours passer une variante.

e) Il manque connexion.commit(). Le module ouvre implicitement une transaction avant une écriture, et tant qu'elle n'est pas validée, ses modifications ne sont visibles que de la connexion qui les a faites ; si le programme se termine sans valider, elles sont annulées. C'est la DURABILITÉ, liée à l'atomicité, de l'exercice 9 : seule une transaction validée est garantie et partagée.

a) Le couple (adherent_id, activite_id) : un adhérent peut suivre plusieurs activités et une activité accueillir plusieurs adhérents, mais le même couple ne doit apparaître qu'une fois. C'est une clé composée, formée de deux clés étrangères, typique d'une table d'association. La table contient 8 lignes.

b) SELECT adherent.nom FROM adherent JOIN inscription ON inscription.adherent_id = adherent.id JOIN activite ON inscription.activite_id = activite.id WHERE activite.nom = 'escalade'. Les adhérents 1, 2 et 4 sont inscrits à l'activité 1 : le résultat est Lemieux, Garnier et Diallo.

c) SELECT activite.nom, COUNT(inscription.adherent_id) FROM activite LEFT JOIN inscription ON inscription.activite_id = activite.id GROUP BY activite.id, activite.nom. Résultat : escalade 3, natation 3, judo 2, échecs 0. La jointure externe conserve les échecs, avec une colonne adherent_id nulle ; COUNT(inscription.adherent_id) ne compte que les valeurs non nulles, d'où 0. COUNT(*) compterait la LIGNE, et donnerait 1 aux échecs, un inscrit fantôme. Une jointure interne, elle, ferait disparaître les échecs du résultat.

d) SELECT adherent.nom, activite.jour FROM inscription JOIN adherent ON inscription.adherent_id = adherent.id JOIN activite ON inscription.activite_id = activite.id GROUP BY adherent.id, adherent.nom, activite.jour HAVING COUNT(*) > 1. Le mardi porte l'escalade et le judo ; seul l'adhérent 4 est inscrit aux deux. Le résultat est Diallo, mardi. On regroupe par adhérent ET par jour : un regroupement par adhérent seul signalerait aussi Lemieux et Garnier, inscrits à deux activités mais pas le même jour.

e) Les inscrits à l'escalade sont 1, 2 et 4, ceux de la natation 1, 2 et 5 : l'ensemble des adhérents concernés est {1, 2, 4, 5}, d'âges 16, 17, 16 et 18, d'où une moyenne de $\frac{67}{4} = 16{,}75$ ans. Requête correcte : SELECT AVG(age) FROM adherent WHERE id IN (SELECT adherent_id FROM inscription WHERE activite_id IN (1, 2)). Une moyenne calculée sur les lignes de la jointure compterait Lemieux et Garnier DEUX fois, une par activité : $\frac{16 + 17 + 16 + 16 + 17 + 18}{6} = \frac{100}{6} \approx 16{,}67$ ans. Le résultat paraît plausible, ce qui rend l'erreur invisible : c'est la même duplication qu'à l'exercice 5, cette fois cachée dans un agrégat.

a) La valeur nulle signifie que Bernard n'a PAS de responsable : c'est la racine de l'organisation. L'arbre : Bernard a pour subordonnés Caron et Fortin ; Caron a pour subordonnés Dubois et Éthier ; Fortin a pour subordonné Gagnon. Sa hauteur vaut 2 : le chemin le plus long, de Bernard à Dubois par exemple, compte deux arêtes. La clé étrangère réflexive évoquée à l'exercice 2 sert précisément à ranger une telle hiérarchie dans une seule table.

b) SELECT p.nom, r.nom FROM personnel AS p JOIN personnel AS r ON p.responsable_id = r.id. La table apparaît deux fois, une fois dans le rôle du membre et une fois dans le rôle du responsable : sans les alias p et r, l'expression personnel.nom serait ambiguë, et le moteur ne saurait pas de quelle copie on parle. On obtient 5 lignes, Bernard étant absent puisque sa clé étrangère nulle ne correspond à aucune ligne. Avec LEFT JOIN, on obtient 6 lignes, Bernard apparaissant avec un responsable nul.

c) SELECT r.nom, COUNT(*) FROM personnel AS p JOIN personnel AS r ON p.responsable_id = r.id GROUP BY r.id, r.nom. Résultat : Bernard 2, Caron 2, Fortin 1. Les membres sans subordonné n'apparaissent pas ; pour les faire figurer avec 0, il faudrait partir de r en jointure externe et compter p.id, comme au club de sport.

d) SELECT p.nom FROM personnel AS p JOIN personnel AS r ON p.responsable_id = r.id WHERE r.responsable_id IS NOT NULL. Les responsables qui ont eux-mêmes un responsable sont Caron et Fortin : le résultat est Dubois, Éthier et Gagnon, soit 3 membres, ceux du niveau 2 de l'arbre.

e) Bernard a 5 subordonnés, tous les autres membres. Une jointure remonte d'un niveau, deux jointures de deux niveaux : une requête écrite avec un nombre fixé de jointures ne voit qu'une profondeur fixée, alors que la hauteur de l'arbre peut changer à chaque réorganisation. Il faut un parcours RÉPÉTÉ jusqu'à ce qu'aucun nouveau membre n'apparaisse : soit une boucle dans le programme Python, qui interroge la base niveau par niveau comme un parcours en largeur, soit une requête récursive, que la plupart des moteurs proposent sous la forme WITH RECURSIVE.