Fiche à étudier — Relier deux entités (1-N)

Cette fiche rassemble ce qu'il faut savoir par cœur pour relier deux entités : découper les données, poser une association 1-N, la traduire en clé étrangère et interroger les deux tables ensemble. Elle ne contient pas d'exercice et peut s'imprimer seule. Les exemples portent sur une école : des élèves, des classes, et chaque élève appartient à une classe.

Le vocabulaire

Rappel des mots des autres niveaux : une entité est une chose dont on veut garder la trace ; une occurrence en est un exemplaire concret ; une contrainte est une règle que la base vérifie à chaque écriture (NOT NULL, UNIQUE, clé étrangère).

Les conventions de notation

MLD de l'école (clé primaire soulignée) :

• classe (id, nom, annee, local)
• eleve (id, nom, prenom, date_naissance, commune, email, absences, #classe_id → classe.id)

Du texte à la base, étape par étape

1. Repérer les entités et les associations. Chercher les phrases sujet – verbe – complément dont le sujet et le complément sont deux entités. « Chaque élève appartient à une classe » donne l'association ELEVE —appartient— CLASSE. Sans verbe, c'est souvent un attribut.
2. Poser les cardinalités. De chaque côté, se demander le minimum et le maximum, et écrire la phrase qui commence par « Un … ». Le minimum est une décision de gestion. Résultat : ELEVE (0,1) —appartient— (0,n) CLASSE.
3. Écrire le MLD. Une table par entité. Pour chaque association 1-N, ajouter une clé étrangère dans la table de l'entité qui porte le maximum 1. Vérifier : autant de clés étrangères que d'associations 1-N.
4. Activer le pragma, avant tout le reste. DB Browser : onglet Éditer les pragmas, case Foreign Keys, Enregistrer. En SQL : PRAGMA foreign_keys = ON;
5. Créer la clé étrangère. CREATE TABLE pour la nouvelle table, ALTER TABLE … ADD COLUMN … REFERENCES … pour la colonne, puis UPDATE pour remplir les liens.
6. Faire les deux tests. Rattacher une ligne à une ligne inexistante, puis supprimer une ligne encore désignée. Les deux doivent afficher FOREIGN KEY constraint failed et ne rien modifier.

La syntaxe

Déclarer une clé étrangère dans une nouvelle table ou dans une table existante :

CREATE TABLE eleve (
    id        INTEGER PRIMARY KEY,
    nom       TEXT NOT NULL,
    classe_id INTEGER REFERENCES classe(id)
);

ALTER TABLE eleve ADD COLUMN classe_id INTEGER REFERENCES classe(id);

Activer, contrôler et vérifier les clés étrangères :

PRAGMA foreign_keys = ON;     -- activer (pour la session en cours)
PRAGMA foreign_keys;          -- 1 = activé, 0 = désactivé
PRAGMA foreign_key_check;     -- liste les lignes dont la clé étrangère est invalide

Joindre deux tables, avec des alias :

SELECT e.prenom, e.nom, c.nom AS classe
FROM eleve e
JOIN classe c ON c.id = e.classe_id;

Garder aussi les lignes sans correspondance, ou les isoler :

SELECT e.prenom, e.nom, c.nom AS classe
FROM eleve e
LEFT JOIN classe c ON c.id = e.classe_id;

SELECT e.prenom, e.nom
FROM eleve e
LEFT JOIN classe c ON c.id = e.classe_id
WHERE c.id IS NULL;

Compter par groupe, groupes vides compris :

SELECT c.nom AS classe, COUNT(e.id) AS nb_eleves
FROM classe c
LEFT JOIN eleve e ON e.classe_id = c.id
GROUP BY c.nom;

Depuis Python :

conn = sqlite3.connect("mon-ecole-classes.db")
conn.execute("PRAGMA foreign_keys = ON")
conn.row_factory = sqlite3.Row

Les pièges à connaître

Lire une cardinalité à l'envers

Le (0,1) écrit à côté de ELEVE parle d'un élève et compte ses classes. Il ne veut pas dire « zéro ou un élève ». Parade : toujours formuler la phrase « Un élève … ».

Placer la clé étrangère du mauvais côté

La clé étrangère va du côté du maximum 1, jamais du côté du n. Une colonne eleve_id dans classe obligerait à écrire plusieurs élèves dans une seule case, ou à recopier la classe : retour à la redondance.

Oublier le pragma

Sans PRAGMA foreign_keys = ON, SQLite accepte n'importe quelle valeur dans une clé étrangère et laisse supprimer des lignes encore désignées. Les lignes orphelines créées pendant ce temps restent dans la base quand on réactive le pragma. Il faut l'activer dans chaque logiciel et chaque programme, avant toute écriture.

Le produit cartésien

FROM eleve, classe sans condition donne toutes les combinaisons : 12 × 3 = 36 lignes, sans message d'erreur. Symptôme : un résultat beaucoup trop gros, où chaque ligne se répète. Une condition ON fausse (ON c.id = e.id) donne aussi des lignes fausses sans erreur.

Le LEFT JOIN annulé par un WHERE

Une condition du WHERE sur la table de droite (WHERE c.nom <> '4TTR') élimine les lignes où cette table vaut NULL, c'est-à-dire celles que le LEFT JOIN devait garder. Parade : ajouter OR c.id IS NULL.

✅ Teste-toi

1. Qu'est-ce que la redondance ? Donne un exemple.
2. Cite les trois anomalies provoquées par la redondance, avec un exemple pour chacune.
3. Pourquoi une faute de frappe comme 4TRR ne provoque-t-elle aucun message d'erreur dans un tableau unique ?
4. Dans la phrase « Un client passe des commandes », quelles sont les entités et quel est le nom de l'association ?
5. Que signifient les deux chiffres d'une cardinalité ? Cite les quatre cardinalités possibles.
6. Lis à voix haute les deux cardinalités de ELEVE (0,1) —appartient— (0,n) CLASSE.
7. Pourquoi dit-on que le minimum est une décision de gestion ?
8. Qu'est-ce qu'une association 1-N ?
9. Énonce la règle de placement de la clé étrangère. Où va la clé étrangère de VEHICULE (1,1) —appartient— (0,n) CLIENT ?
10. Que signifie #classe_id → classe.id dans un MLD ?
11. Pourquoi faut-il activer PRAGMA foreign_keys dans chaque programme ?
12. Quels sont les deux tests à faire après avoir posé une clé étrangère, et quel message doivent-ils afficher ?
13. Qu'est-ce qu'une ligne orpheline ?
14. Combien de lignes renvoie SELECT * FROM eleve, classe; avec 12 élèves et 3 classes ? Comment s'appelle ce résultat ?
15. Tu veux la liste de tous les élèves avec leur classe, y compris ceux qui n'en ont pas. JOIN ou LEFT JOIN ? Pourquoi ?