Adloun

Probleme – Contrôler une base avec des SELECT

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

Énoncé

Une base peut être incohérente sans que rien ne le signale. On écrit ici les requêtes qui le vérifient — chacune doit rendre zéro ligne sur une base saine.

Corrigé

1. Les orphelins. Une clé étrangère qui ne désigne rien.


-- une note dont l'élève n'existe pas
SELECT n.eleve, n.matiere FROM Note n
LEFT JOIN Eleve e ON e.id = n.eleve
WHERE e.id IS NULL;              -- 0 ligne : sain

-- une note dont la matière n'existe pas
SELECT n.eleve, n.matiere FROM Note n
LEFT JOIN Matiere m ON m.code = n.matiere
WHERE m.code IS NULL;            -- 0 ligne : sain

Le motif est toujours le même : jointure externe depuis la table qui porte la clé étrangère, puis IS NULL sur la clé primaire de l'autre. C'est l'exercice 21.6 employé comme instrument de mesure — et l'on teste e.id IS NULL, la clé, jamais un attribut ordinaire qui pourrait être nul de son propre chef.

2. L'unicité de la clé primaire.


SELECT eleve, matiere, COUNT(*) FROM Note
GROUP BY eleve, matiere HAVING COUNT(*) > 1;    -- 0 ligne : la clé tient

Le motif de la duplication : grouper par ce qui devrait être unique, garder les paquets de plus d'un. Il vaut pour toute contrainte d'unicité, et il est le seul moyen de la vérifier quand la base ne la déclare pas.

3. Les clés candidates. Le même motif, appliqué colonne par colonne, répond à « cet attribut pourrait-il être clé ? ».


SELECT nom, COUNT(*) FROM Eleve GROUP BY nom HAVING COUNT(*) > 1;
-- 0 ligne : nom est UNIQUE dans cette instance

SELECT promo, COUNT(*) FROM Eleve GROUP BY promo HAVING COUNT(*) > 1;
-- 2026 | 2   et   2027 | 2  : promo n'est PAS une clé candidate
AttentionUnique *aujourd'hui* n'est pas *clé*

nom est unique dans cette instance : la requête rend zéro ligne. Cela ne fait pas de nom une clé. Une clé est une contrainte sur toutes les instances possibles, une propriété du schéma ; ici, on n'a mesuré qu'un état. Le premier homonyme inscrit fera tomber la propriété, et toute requête qui groupait par nom fusionnera silencieusement deux élèves.

Aucune requête ne peut établir qu'un attribut est une clé — c'est une décision de modélisation. Une requête peut seulement établir qu'il ne l'est pas. Un test réfute, il ne démontre pas : le chapitre chap:discipline le dit des programmes, et cela vaut mot pour mot des données.

4. Totalité et domaine.


-- l'association 1-1 est-elle totale ?
SELECT e.nom FROM Eleve e
LEFT JOIN Dossier d ON d.eleve = e.id WHERE d.eleve IS NULL;   -- Dupont

-- les notes sont-elles dans [0, 20] ?
SELECT valeur FROM Note WHERE valeur < 0 OR valeur > 20;       -- 0 ligne

La première rend une ligne : l'association n'est pas totale. Ce n'est pas nécessairement un défaut — un élève peut ne pas encore avoir de casier — mais c'est une propriété qu'il faut avoir décidée, et la requête est le seul moyen de savoir laquelle est vraie.

En pratique — Une base se teste comme un programme

Ces requêtes forment un jeu de tests au sens du chapitre chap:discipline. Elles ont trois qualités qu'on demande à un test : elles sont automatisables (chacune doit rendre zéro ligne), localisantes (elles rendent les lignes fautives, pas un booléen), et indépendantes des données (elles ne présupposent aucun contenu).

Un contrôle de plus, à écrire toujours, est le compte de référence :


SELECT COUNT(*) AS lignes, COUNT(DISTINCT eleve) AS eleves,
       COUNT(DISTINCT matiere) AS matieres FROM Note;   -- 5 | 3 | 3

Cinq lignes, trois élèves notés, trois matières notées. Ces trois nombres se comparent à ce qu'on attend, et tout écart est une question à poser — ici, trois élèves notés sur quatre inscrits, ce qui redit que Dupont n'a rien.

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.