Concevoir, remplir, interroger, indexer et protéger une base avec SQLite.
Scénario
HashBox veut suivre ses formations : membres, cours, missions, inscriptions, missions terminées et paiements. Tu dois concevoir la base, l'alimenter avec des données de test, répondre à des questions métier, accélérer les requêtes lentes et garantir la cohérence pendant des accès simultanés.
Objectif
Produire un schéma normalisé et contraint, un jeu de données de 20 000 lignes, dix requêtes validées, un jeu d'index mesuré, et un test de concurrence qui prouve l'absence de mises à jour perdues.
Environnement
Python 3.10 ou plus, module sqlite3. Aucun autre outil. Un fichier de base temporaire pour les tests de concurrence.
État de départ
Un dossier vide avec schema.sql, donnees.py, requetes.py, tests.py à créer. Contraintes à respecter : 3FN, clés étrangères actives, aucune donnée invalide acceptée.
Tâches
- Écris
schema.sql: tables, clés primaires et étrangères, contraintes (NOT NULL, UNIQUE, CHECK), ON DELETE choisis et justifiés. - Écris
donnees.pyqui remplit la base avec des données aléatoires reproductibles (graine fixe) : 500 membres, 6 cours, 60 missions, 20 000 missions terminées. - Écris dix requêtes (jointures, agrégations, sous-requêtes, CTE) dans
requetes.pyet vérifie chaque résultat avec un calcul Python indépendant. - Écris des tests qui prouvent que la base refuse 8 données invalides.
- Repère les trois requêtes les plus lentes, lis leur plan, crée les index nécessaires, et mesure avant/après.
- Mesure le coût des index sur l'insertion de 20 000 lignes.
- Écris une fonction de transfert d'XP entre deux membres, atomique et testée avec une panne simulée.
- Lance 6 threads qui incrémentent un compteur : montre la mise à jour perdue puis la correction.
- Rédige un rapport d'une page : choix de schéma, index conservés, anomalies évitées.
Observations attendues
- Les requêtes donnent les mêmes résultats que les calculs Python.
- Chaque donnée invalide est refusée par la base, pas seulement par le code.
- Les index font passer les trois requêtes lentes de SCAN à SEARCH, avec un gain mesuré.
- Le transfert interrompu ne laisse aucune trace ; le compteur concurrent atteint la bonne valeur après correction.
Validation
python tests.py exécute tout sans erreur et affiche un tableau de mesures. Un collègue peut recréer la base à l'identique avec la graine fixe.
Indices
💡 Indice 1
Active PRAGMA foreign_keys = ON à chaque connexion.
💡 Indice 2
Pour comparer SQL et Python, construis un dictionnaire à partir de SELECT * puis calcule en Python.
💡 Indice 3
Pour la concurrence, utilise une connexion par thread, isolation_level=None et BEGIN IMMEDIATE.
Preuves à rendre
-
schema.sql,donnees.py,requetes.py,tests.py - Tableau des plans et temps avant/après index
- Rapport d'une page
Réflexion
- Quelle contrainte t'a évité le plus de bugs ?
- Quel index as-tu supprimé après mesure, et pourquoi ?
HEF-070 — Bases de données
Atelier Python

