Probleme – Trois cardinalités, une base

Exercice · niveau 3 (difficile) · informatique (MP2I/MPI), chapitre 21 — Bases de données relationnelles et SQL

Énoncé

On enrichit la base de trois tables :

Dossier <div class="overflow-x-auto my-6"><table style="border-collapse:collapse;width:100%" class="text-xs"><tr style="border-top:1.5px solid #2E2C26;border-bottom:1.5px solid #2E2C26"><th style="text-align:right;padding:8px 12px;font-weight:700;font-size:11px;letter-spacing:.04em;text-transform:uppercase;color:#6B6557"><strong>eleve</strong></th><th style="text-align:right;padding:8px 12px;font-weight:700;font-size:11px;letter-spacing:.04em;text-transform:uppercase;color:#6B6557">casier</th></tr><tr style=""><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">1</td><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">204</td></tr><tr style=""><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">2</td><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">118</td></tr><tr style="border-bottom:1.5px solid #2E2C26"><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">3</td><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">331</td></tr></table></div> Professeur <div class="overflow-x-auto my-6"><table style="border-collapse:collapse;width:100%" class="text-xs"><tr style="border-top:1.5px solid #2E2C26;border-bottom:1.5px solid #2E2C26"><th style="text-align:right;padding:8px 12px;font-weight:700;font-size:11px;letter-spacing:.04em;text-transform:uppercase;color:#6B6557"><strong>id</strong></th><th style="text-align:left;padding:8px 12px;font-weight:700;font-size:11px;letter-spacing:.04em;text-transform:uppercase;color:#6B6557">nom</th></tr><tr style=""><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">100</td><td style="text-align:left;padding:8px 12px;color:#34332D;vertical-align:top">Achour</td></tr><tr style=""><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">101</td><td style="text-align:left;padding:8px 12px;color:#34332D;vertical-align:top">Bernard</td></tr><tr style="border-bottom:1.5px solid #2E2C26"><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">102</td><td style="text-align:left;padding:8px 12px;color:#34332D;vertical-align:top">Chan</td></tr></table></div> Enseigne <div class="overflow-x-auto my-6"><table style="border-collapse:collapse;width:100%" class="text-xs"><tr style="border-top:1.5px solid #2E2C26;border-bottom:1.5px solid #2E2C26"><th style="text-align:right;padding:8px 12px;font-weight:700;font-size:11px;letter-spacing:.04em;text-transform:uppercase;color:#6B6557"><strong>prof</strong></th><th style="text-align:right;padding:8px 12px;font-weight:700;font-size:11px;letter-spacing:.04em;text-transform:uppercase;color:#6B6557"><strong>matiere</strong></th><th style="text-align:right;padding:8px 12px;font-weight:700;font-size:11px;letter-spacing:.04em;text-transform:uppercase;color:#6B6557"><strong>promo</strong></th></tr><tr style=""><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">100</td><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">10</td><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">2026</td></tr><tr style=""><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">100</td><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">10</td><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">2027</td></tr><tr style=""><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">101</td><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">11</td><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">2026</td></tr><tr style=""><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">101</td><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">11</td><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">2027</td></tr><tr style=""><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">101</td><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">12</td><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">2026</td></tr><tr style="border-bottom:1.5px solid #2E2C26"><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">102</td><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">10</td><td style="text-align:right;padding:8px 12px;color:#34332D;vertical-align:top">2026</td></tr></table></div>

Identifier les trois cardinalités présentes et dire où sont les clés étrangères.
Écrire, pour chacune, une requête qui la parcourt.
Écrire « quel professeur enseigne à quel élève ». Combien de jointures ?
Que se passe-t-il si l'on oublie une condition de jointure ?

Corrigé

1. Les trois cardinalités.

Association	type	où est la clé étrangère	remarque
Eleve --- Dossier	$1 - 1$	\textsf{Dossier.eleve}	d'un seul côté
Promotion --- Eleve	$1 - *$	\textsf{Eleve.promo}	côté « plusieurs »
Professeur --- Matiere	$* - *$	\textsf{Enseigne}	table intermédiaire

Trois remarques que la figure rend visibles.

D'abord, Dossier et Enseigne se ressemblent : toutes deux portent une clé étrangère vers une autre table. Ce qui les sépare est que Dossier.eleve est la clé primaire de sa table — donc un élève a au plus un dossier — alors que Enseigne.prof n'est qu'une partie de la clé. La cardinalité se lit dans la clé primaire, pas dans la clé étrangère.

Ensuite, l'association $1 - 1$ n'est pas forcément totale : Dupont n'a pas de dossier. Mesuré, la jointure externe le montre :


SELECT e.nom, d.casier FROM Eleve e LEFT JOIN Dossier d ON d.eleve = e.id;
-- Alaoui 204 | Benali 118 | Cohen 331 | Dupont NULL

Enfin, Enseigne porte trois attributs en clé : le même professeur enseigne la même matière à deux promotions. La table intermédiaire n'est pas seulement un couple, elle est un triplet — et c'est la preuve que le programme a raison de dire qu'elle « porte souvent une information propre ».

2. Une requête par cardinalité.


-- 1-*
SELECT promo, COUNT(*) AS effectif FROM Eleve GROUP BY promo;
-- 2026 : 2   |   2027 : 2

-- *-*, parcouru dans les deux sens à la fois
SELECT p.nom AS professeur, m.intitule AS matiere, g.promo
FROM Enseigne g JOIN Professeur p ON p.id = g.prof
                JOIN Matiere m ON m.code = g.matiere;
-- 6 lignes

-- *-*, agrégé d'un côté
SELECT m.intitule, COUNT(DISTINCT g.prof) AS nb_profs
FROM Matiere m LEFT JOIN Enseigne g ON g.matiere = m.code
GROUP BY m.code, m.intitule;
-- Informatique 2 | Mathématiques 1 | Physique 1

Le DISTINCT de la dernière n'est pas décoratif : Bernard enseigne les mathématiques à deux promotions, et sans lui on compterait deux professeurs de mathématiques.

3. Quel professeur enseigne à quel élève. Il n'existe aucune clé étrangère entre Eleve et Professeur : le lien passe par la matière et par la promotion. Quatre tables, donc trois jointures.


SELECT DISTINCT e.nom, p.nom AS professeur
FROM Eleve e
JOIN Note n ON n.eleve = e.id
JOIN Enseigne g ON g.matiere = n.matiere AND g.promo = e.promo
JOIN Professeur p ON p.id = g.prof
ORDER BY e.nom, p.nom;

Sept lignes mesurées : Alaoui avec Achour, Bernard et Chan ; Benali avec les mêmes trois ; Cohen avec Bernard seul.

Le ON à deux conditions est le cœur du problème. Une jointure sur la seule matière rapprocherait Cohen (promotion $2027$ ) du professeur Chan, qui n'enseigne qu'en $2026$ . La condition de recollement doit reproduire toute la clé de l'association — ici $(matiere, promo)$ — et pas une partie. C'est l'exact analogue de la clé primaire composée de Note : une clé partielle donne une jointure partielle, et le résultat est faux sans être vide.

4. L'oubli. SELECT COUNT(*) FROM Eleve, Professeur rend $12$ : $4 \times 3$ , le produit cartésien. Aucune erreur, aucun avertissement — seulement un résultat quatre fois trop grand, où chaque élève est associé à tous les professeurs. Sur une base réelle, c'est un résultat des millions de fois trop grand, et un serveur immobilisé.

En pratique — Compter les `ON`

Une règle qui ne coûte rien : $n$ tables dans le FROM demandent $n - 1$ conditions de jointure. Quatre tables, trois ON. Si le compte n'y est pas, il y a un produit cartésien quelque part. C'est un contrôle syntaxique, vérifiable d'un coup d'œil, et il attrape le défaut le plus coûteux de SQL.

Les autres exercices de ce chapitre Le cours du chapitre

Un blocage sur cet exercice ? Le tuteur d'Adloun guide par questions, sans donner la réponse.