Des tables, des lignes, des liens entre elles : une idée vieille de 50 ans qui fait encore tourner le monde.
⏱️ Durée : environ 70 minutes · 🎓 Niveau : intermédiaire · 🏅 Badge : Archiviste des données
🎬 L'histoire
HashBox stocke ses membres, ses cours et leurs inscriptions dans trois fichiers Excel qui s'envoient par e-mail. Résultat : Ada a deux fiches avec deux adresses différentes, Bob est inscrit à un cours qui n'existe plus, et personne ne sait quelle version est la bonne.
« Il te faut une base de données, dit Sam. Un endroit unique, avec des règles, que plusieurs personnes utilisent en même temps sans se marcher dessus. »
Tu découvres le modèle relationnel et tu crées ta première base avec SQLite.
🎯 Ta mission
À la fin, je serai capable de :
- définir table, ligne, colonne, clé primaire et clé étrangère
- expliquer ce qu'est une relation entre tables (1-n, n-n)
- créer une base SQLite et trois tables avec Python
- insérer et lire des données
- expliquer pourquoi une base vaut mieux que des fichiers séparés
🧩 Défi d'échauffement (5 XP)
Tu tiens la liste des élèves d'une école dans un tableur : nom, classe, professeur principal, adresse du professeur. Si le professeur change d'adresse, combien de lignes dois-tu modifier ? Que se passe-t-il si tu en oublies une ? Comment organiserais-tu les informations pour ne les écrire qu'une seule fois ?
💡 Le concept
Qu'est-ce qu'une base de données ?
Une base de données est un ensemble organisé de données, géré par un SGBD (système de gestion de base de données) qui offre :
- le stockage durable ;
- la recherche rapide ;
- l'intégrité (des règles qui empêchent les données absurdes) ;
- la concurrence (plusieurs utilisateurs en même temps) ;
- la fiabilité (résiste aux pannes).
Le modèle relationnel
On range les données dans des tables (ou relations).
| id | nom | ville |
|---|---|---|
| 1 | Ada | Lyon |
| 2 | Bob | Lille |
- une colonne (attribut) : un type d'information avec un type (entier, texte, date…) ;
- une ligne (enregistrement, tuple) : un élément décrit ;
- un schéma : la structure des tables (noms et types des colonnes).
La clé primaire
Une clé primaire (primary key) identifie chaque ligne de façon unique et stable. Souvent un entier auto-incrémenté (id).
La clé étrangère
Une clé étrangère (foreign key) est une colonne qui référence la clé primaire d'une autre table. C'est elle qui crée le lien.
membres inscriptions cours
┌────┬──────┐ ┌───────────┬──────────┐ ┌─────────┬─────────────┐
│ id │ nom │ ◄──────────── │ membre_id │ cours_id │ ────► │ code │ titre │
├────┼──────┤ ├───────────┼──────────┤ ├─────────┼─────────────┤
│ 1 │ Ada │ │ 1 │ HEF-000 │ │ HEF-000 │ Mentalité │
│ 2 │ Bob │ │ 2 │ HEF-010 │ │ HEF-010 │ Algorithmes │
└────┴──────┘ └───────────┴──────────┘ └─────────┴─────────────┘
Les types de relations
| Relation | Exemple | Réalisation |
|---|---|---|
| 1 – n | un membre a plusieurs commentaires | clé étrangère dans la table du « n » |
| n – n | un membre suit plusieurs cours, un cours a plusieurs membres | table de liaison (inscriptions) |
| 1 – 1 | un membre a un profil | clé étrangère unique |
SQL
SQL (Structured Query Language) est le langage standard pour décrire et interroger les données. Il est déclaratif : on dit quoi obtenir, pas comment.
SQLite en Python
SQLite est une base complète intégrée dans un simple fichier, fournie avec Python (module sqlite3). Parfait pour apprendre : mêmes concepts que PostgreSQL ou MySQL.
import sqlite3
bd = sqlite3.connect(":memory:") # base en mémoire (disparaît à la fermeture)
bd.execute("CREATE TABLE membres (id INTEGER PRIMARY KEY, nom TEXT, ville TEXT)")
bd.execute("INSERT INTO membres (nom, ville) VALUES ('Ada', 'Lyon')")
bd.execute("INSERT INTO membres (nom, ville) VALUES ('Bob', 'Lille')")
for ligne in bd.execute("SELECT id, nom, ville FROM membres"):
print(ligne) # (1, 'Ada', 'Lyon') puis (2, 'Bob', 'Lille')
🧠 Astuce : une bonne table décrit un seul type de chose. Les liens entre les choses passent par des clés étrangères, pas par des copies.
🔍 Exemple pas à pas
Problème : créer la base de HashBox (membres, cours, inscriptions) et répondre à « qui est inscrit à quoi ? ».
import sqlite3
bd = sqlite3.connect(":memory:")
bd.execute("PRAGMA foreign_keys = ON") # SQLite : à activer pour vérifier les clés étrangères
bd.executescript('''
CREATE TABLE membres (
id INTEGER PRIMARY KEY,
nom TEXT NOT NULL,
ville TEXT
);
CREATE TABLE cours (
code TEXT PRIMARY KEY,
titre TEXT NOT NULL
);
CREATE TABLE inscriptions (
membre_id INTEGER NOT NULL REFERENCES membres(id),
cours_code TEXT NOT NULL REFERENCES cours(code),
PRIMARY KEY (membre_id, cours_code)
);
''')
bd.executemany("INSERT INTO membres (nom, ville) VALUES (?, ?)", [("Ada", "Lyon"), ("Bob", "Lille"), ("Cléo", "Lyon")])
bd.executemany("INSERT INTO cours VALUES (?, ?)", [("HEF-000", "Mentalité"), ("HEF-010", "Algorithmes")])
bd.executemany("INSERT INTO inscriptions VALUES (?, ?)", [(1, "HEF-000"), (1, "HEF-010"), (2, "HEF-000")])
requete = '''
SELECT membres.nom, cours.titre
FROM inscriptions
JOIN membres ON membres.id = inscriptions.membre_id
JOIN cours ON cours.code = inscriptions.cours_code
ORDER BY membres.nom, cours.titre
'''
for nom, titre in bd.execute(requete):
print(nom, "→", titre)
# Ada → Algorithmes / Ada → Mentalité / Bob → Mentalité
Ce que la base garantit :
try:
bd.execute("INSERT INTO inscriptions VALUES (99, 'HEF-000')") # membre inexistant
except sqlite3.IntegrityError as e:
print("refusé :", e)
La base refuse une inscription pour un membre qui n'existe pas. Dans le tableur, personne ne l'aurait empêchée.
Pourquoi trois tables ? Un membre est écrit une seule fois, un cours une seule fois. Les inscriptions ne stockent que des identifiants. Changer la ville d'Ada se fait en une seule ligne.
✍️ À toi, avec aide (10 XP)
Une bibliothèque a des livres, des lecteurs et des emprunts.
- Dessine (en texte) les tables : colonnes, clés primaires, clés étrangères.
- Crée-les avec SQLite.
- Insère 3 livres, 2 lecteurs, 3 emprunts.
- Écris la requête « qui a emprunté quoi ? ».
💡 Indice 1
Un emprunt relie un lecteur et un livre : table de liaison, avec une date.
💡 Indice 2
La clé primaire d'un emprunt peut être un id propre (un lecteur peut emprunter plusieurs fois le même livre).
💡 Indice 3
La requête a deux jointures, comme dans l'exemple.
✅ Réponse
import sqlite3
bd = sqlite3.connect(":memory:")
bd.execute("PRAGMA foreign_keys = ON")
bd.executescript('''
CREATE TABLE livres (id INTEGER PRIMARY KEY, titre TEXT NOT NULL, auteur TEXT);
CREATE TABLE lecteurs (id INTEGER PRIMARY KEY, nom TEXT NOT NULL);
CREATE TABLE emprunts (
id INTEGER PRIMARY KEY,
livre_id INTEGER NOT NULL REFERENCES livres(id),
lecteur_id INTEGER NOT NULL REFERENCES lecteurs(id),
date_emprunt TEXT NOT NULL
);
''')
bd.executemany("INSERT INTO livres (titre, auteur) VALUES (?, ?)", [("Dune", "Herbert"), ("Fondation", "Asimov"), ("Ubik", "Dick")])
bd.executemany("INSERT INTO lecteurs (nom) VALUES (?)", [("Léa",), ("Omar",)])
bd.executemany("INSERT INTO emprunts (livre_id, lecteur_id, date_emprunt) VALUES (?, ?, ?)",
[(1, 1, "2025-01-05"), (2, 1, "2025-01-10"), (3, 2, "2025-01-12")])
for ligne in bd.execute('''SELECT lecteurs.nom, livres.titre, emprunts.date_emprunt
FROM emprunts JOIN lecteurs ON lecteurs.id = emprunts.lecteur_id
JOIN livres ON livres.id = emprunts.livre_id
ORDER BY emprunts.date_emprunt'''):
print(ligne)
🚀 À toi, en solo (20 XP)
Concevoir le schéma d'une plateforme de cours (sans écrire encore de code) :
- les membres s'inscrivent à des cours ;
- chaque cours est composé de missions ordonnées ;
- chaque membre termine des missions et gagne des points (XP) ;
- chaque membre peut avoir un badge par mission.
Dessine les tables, les clés, les relations (1-n, n-n) et justifie chaque table de liaison. Puis crée le schéma avec SQLite et teste-le avec des données d'exemple : refuse une mission terminée pour un membre inexistant.
✅ Pistes de correction
import sqlite3
bd = sqlite3.connect(":memory:")
bd.execute("PRAGMA foreign_keys = ON")
bd.executescript('''
CREATE TABLE membres (id INTEGER PRIMARY KEY, nom TEXT NOT NULL);
CREATE TABLE cours (code TEXT PRIMARY KEY, titre TEXT NOT NULL);
CREATE TABLE missions (
id INTEGER PRIMARY KEY,
cours_code TEXT NOT NULL REFERENCES cours(code),
numero INTEGER NOT NULL,
titre TEXT NOT NULL,
badge TEXT,
UNIQUE (cours_code, numero)
);
CREATE TABLE inscriptions (
membre_id INTEGER NOT NULL REFERENCES membres(id),
cours_code TEXT NOT NULL REFERENCES cours(code),
PRIMARY KEY (membre_id, cours_code)
);
CREATE TABLE missions_terminees (
membre_id INTEGER NOT NULL REFERENCES membres(id),
mission_id INTEGER NOT NULL REFERENCES missions(id),
xp INTEGER NOT NULL,
terminee_le TEXT NOT NULL,
PRIMARY KEY (membre_id, mission_id)
);
''')
bd.execute("INSERT INTO membres (nom) VALUES ('Ada')")
bd.execute("INSERT INTO cours VALUES ('HEF-000', 'Mentalité')")
bd.execute("INSERT INTO missions (cours_code, numero, titre, badge) VALUES ('HEF-000', 1, 'Cadrer', 'Détective')")
bd.execute("INSERT INTO missions_terminees VALUES (1, 1, 100, '2025-02-01')")
try:
bd.execute("INSERT INTO missions_terminees VALUES (42, 1, 100, '2025-02-01')")
except sqlite3.IntegrityError as e:
print("refusé :", e)
Tables de liaison : inscriptions (membres n-n cours) et missions_terminees (membres n-n missions, avec des attributs propres : xp et date). Les badges sont un attribut de la mission (1-n : un badge par mission).
⚠️ Pièges classiques
| Piège | Pourquoi c'est un problème | Comment l'éviter |
|---|---|---|
| Tout dans une seule table | Duplications et incohérences | Une table = une sorte de chose |
| Oublier d'activer les clés étrangères (SQLite) | Aucune vérification des liens | PRAGMA foreign_keys = ON |
| Clé primaire instable | Un nom qui change casse les liens | Identifiant propre qui ne change jamais |
| Relation n-n sans table de liaison | Colonnes de listes impossibles à interroger | Table de liaison avec deux clés étrangères |
🐉 Mini-boss (40 XP)
Schéma d'une boutique en ligne. Besoins : clients, produits, commandes, lignes de commande (un produit peut apparaître dans plusieurs commandes avec une quantité), adresses de livraison (un client peut en avoir plusieurs).
- Dessine le schéma avec clés primaires/étrangères et cardinalités.
- Crée-le avec SQLite.
- Insère au moins 3 clients, 5 produits, 4 commandes.
- Écris les requêtes : (a) toutes les lignes de la commande 1 avec le nom du produit ; (b) le montant total de chaque commande (prix × quantité) ; (c) les clients qui n'ont jamais commandé.
- Tente 2 insertions invalides et montre que la base les refuse.
✅ Pistes de correction
import sqlite3
bd = sqlite3.connect(":memory:")
bd.execute("PRAGMA foreign_keys = ON")
bd.executescript('''
CREATE TABLE clients (id INTEGER PRIMARY KEY, nom TEXT NOT NULL);
CREATE TABLE adresses (id INTEGER PRIMARY KEY, client_id INTEGER NOT NULL REFERENCES clients(id), rue TEXT NOT NULL, ville TEXT NOT NULL);
CREATE TABLE produits (id INTEGER PRIMARY KEY, nom TEXT NOT NULL, prix_centimes INTEGER NOT NULL);
CREATE TABLE commandes (id INTEGER PRIMARY KEY, client_id INTEGER NOT NULL REFERENCES clients(id), adresse_id INTEGER NOT NULL REFERENCES adresses(id), date TEXT NOT NULL);
CREATE TABLE lignes (commande_id INTEGER NOT NULL REFERENCES commandes(id), produit_id INTEGER NOT NULL REFERENCES produits(id), quantite INTEGER NOT NULL, PRIMARY KEY (commande_id, produit_id));
''')
bd.executemany("INSERT INTO clients (nom) VALUES (?)", [("Ada",), ("Bob",), ("Cléo",)])
bd.executemany("INSERT INTO adresses (client_id, rue, ville) VALUES (?, ?, ?)", [(1, "1 rue A", "Lyon"), (2, "2 rue B", "Lille"), (3, "3 rue C", "Nice")])
bd.executemany("INSERT INTO produits (nom, prix_centimes) VALUES (?, ?)", [("Stylo", 150), ("Cahier", 300), ("Sac", 2500), ("Règle", 200), ("Gomme", 100)])
bd.executemany("INSERT INTO commandes (client_id, adresse_id, date) VALUES (?, ?, ?)", [(1, 1, "2025-01-01"), (, , ), (, , ), (, , )])
bd.executemany(, [(, , ), (, , ), (, , ), (, , ), (, , ), (, , )])
(bd.execute().fetchall())
(bd.execute().fetchall())
(bd.execute().fetchall())
sql (, ):
:
bd.execute(sql)
sqlite3.IntegrityError e:
(, e)
⚡ Quiz éclair (15 XP si 4/5 ou plus)
1. Une clé primaire :
- A. identifie chaque ligne de façon unique
- B. relie deux bases
- C. chiffre les données
2. Une clé étrangère :
- A. est toujours un texte
- B. référence la clé primaire d'une autre table
- C. est facultative et sans effet
3. Pour une relation n-n entre membres et cours, on utilise :
- A. une colonne contenant une liste
- B. un fichier texte
- C. une table de liaison
4. 🔁 Révision HEF-060, mission 09 : Un pare-feu à règles ordonnées applique :
- A. la première règle qui correspond
- B. la dernière règle
- C. toutes les règles
5. 🔁 Révision HEF-060, mission 08 : Une clé publique peut être :
- A. gardée secrète
- B. partagée avec tout le monde
- C. utilisée uniquement pour déchiffrer
✅ Réponses
- A.
- B.
- C.
- A.
- B.
🪞 Je réfléchis (10 XP)
- Quelles informations de ta vie sont dupliquées à plusieurs endroits (adresses, numéros) et poseraient problème en cas de changement ?
- Qu'est-ce qui rend une base de données plus sûre qu'un tableur partagé ?
🎒 À retenir
- Une base de données : stockage, recherche, intégrité, concurrence, fiabilité.
- Table, ligne, colonne, schéma.
- Clé primaire = identité ; clé étrangère = lien.
- Relation n-n = table de liaison.
- SQLite : mêmes concepts, fichier local.
flowchart LR
T(["Le modèle relationnel"])
T --> R1["1. Une base de données : stockage, recherche, intégrité, concurrence,…"]
T --> R2["2. Table, ligne, colonne, schéma."]
T --> R3["3. Clé primaire = identité ; clé étrangère = lien."]
T --> R4["4. Relation n-n = table de liaison."]
T --> R5["5. SQLite : mêmes concepts, fichier local."]
Description du schéma : la mission au centre, reliée aux cinq idées à retenir.
🏅 Récompenses
Total possible : 100 XP · 🏅 Badge Archiviste des données
Mission 02 — Modélisation et normalisation →
HEF-070 — Bases de données

