Dossier complet — Bases de données et SQL

L2 · S4 · Données et logique

Dossier pédagogique original Matrice MI. Contenu d’entraînement non officiel; il ne remplace pas le support ou la maquette d’un établissement.

Objectifs d’apprentissage

• Modéliser un besoin de données en relations, clés, dépendances et contraintes cohérentes.

• Écrire et justifier des requêtes SQL combinant filtres, jointures, agrégations et sous-requêtes.

• Normaliser un schéma relationnel et expliquer les anomalies qu’une décomposition évite.

• Mettre en œuvre une transaction sûre en mobilisant les propriétés ACID et la concurrence.

• Diagnostiquer une requête et proposer un index ou une amélioration mesurée sans sacrifier la qualité des données.

Prérequis

• Manipuler variables, tables et ensembles; savoir lire un schéma simple.

• Connaître les comparaisons, les fonctions d’agrégation et le raisonnement logique élémentaire.

• Savoir exécuter des commandes dans un terminal ou un environnement SQL et interpréter un résultat tabulaire.

Cours

1. Modèle relationnel

Modèle relationnel, clés et contraintes

Une relation est un ensemble de tuples décrits par des attributs; une table SQL en est une représentation pratique, mais l’ordre des lignes n’a pas de sens. Le schéma ETUDIANT(id, nom, filiere) fixe les colonnes et leurs domaines. Une clé candidate identifie chaque tuple; la clé primaire en choisit une, tandis qu’une clé étrangère référence une clé d’une autre table. Les contraintes NOT NULL, UNIQUE et CHECK expriment des invariants contrôlables par le SGBD. Éviter de confondre identifiant technique et information métier: un numéro étudiant peut changer de format, alors qu’un identifiant interne reste stable.

Exemple guidé: pour INSCRIPTION(etudiant_id, ue_id, annee), l’inscription est unique par (etudiant_id, ue_id, annee). On choisit donc cette clé composée; etudiant_id référence ETUDIANT(id) et ue_id référence UE(id). Deux inscriptions identiques sont refusées, et une inscription orpheline aussi.

Point de contrôle. Pour chaque table, nommer la clé primaire, les clés étrangères et une valeur qui violerait une contrainte.

Points-clés

• Une relation est définie par ses attributs et ses tuples, indépendamment de l’ordre d’affichage.
• La clé primaire identifie; la clé étrangère référence une clé d’une autre table.
• Les contraintes déclaratives préviennent doublons, valeurs absentes et références orphelines.

2. Interrogations SQL

Interrogations SQL: filtrer, joindre et agréger

SQL est déclaratif: on décrit le résultat, et le SGBD choisit un plan d’exécution. La méthode fiable consiste à partir de FROM, vérifier les correspondances avec JOIN ... ON, filtrer les lignes avec WHERE, puis regrouper avec GROUP BY et filtrer les groupes avec HAVING. NULL n’est pas zéro: une comparaison à NULL utilise IS NULL. Une jointure interne élimine les lignes sans correspondance; LEFT JOIN les conserve côté gauche.

Exemple guidé: compter les inscriptions par UE, y compris les UE sans inscrit:

SELECT u.id, u.nom, COUNT(i.etudiant_id) AS total
FROM UE AS u
LEFT JOIN INSCRIPTION AS i ON i.ue_id = u.id
GROUP BY u.id, u.nom
HAVING COUNT(i.etudiant_id) >= 0;

COUNT(i.etudiant_id) vaut zéro pour une UE vide, contrairement à COUNT(*) qui compterait la ligne fabriquée par la jointure. Le HAVING est démonstratif; un seuil utile serait >= 10.

Point de contrôle. Expliquer pourquoi le filtre sur une colonne de i doit rester dans ON si l’on veut conserver les UE sans inscription.

Points-clés

• WHERE filtre les lignes avant regroupement et HAVING filtre les groupes après agrégation.
• LEFT JOIN conserve les lignes de la table gauche, même sans correspondance.
• Avec NULL, utiliser IS NULL et choisir COUNT(colonne) ou COUNT(*) consciemment.

3. Normalisation et transactions

Dépendances fonctionnelles et normalisation

Une dépendance fonctionnelle $X \to Y$ signifie qu’une même valeur de $X$ impose une unique valeur de $Y$. Elle aide à repérer les répétitions et les anomalies d’insertion, de modification ou de suppression. La première forme normale exige des valeurs atomiques; la deuxième élimine les dépendances partielles vis-à-vis d’une clé composée; la troisième élimine les dépendances transitives entre attributs non clés. La normalisation ne consiste pas à séparer mécaniquement chaque colonne: il faut préserver les dépendances utiles et une jointure sans perte.

Exemple guidé: RESULTAT(etudiant, ue, nom_etudiant, nom_ue, note) a la clé (etudiant, ue), mais etudiant -> nom_etudiant et ue -> nom_ue. On décompose en ETUDIANT(etudiant, nom_etudiant), UE(ue, nom_ue) et RESULTAT(etudiant, ue, note). La note reste liée au couple, les mises à jour sont uniques et une jointure sur les deux identifiants reconstruit les informations.

Erreur fréquente. Déclarer une clé sans vérifier les règles métier peut masquer des doublons. Point de contrôle. Pour chaque relation, écrire les dépendances puis tester si chaque déterminant est une superclé.

Points-clés

• Une dépendance fonctionnelle relie un déterminant à des valeurs déterminées.
• Les formes normales réduisent les anomalies sans supprimer l’information utile.
• Une décomposition correcte vise la jointure sans perte et le respect des dépendances importantes.

4. Transactions, concurrence et propriétés ACID

Transactions, concurrence et propriétés ACID

Une transaction regroupe des opérations qui doivent être validées ensemble par COMMIT ou annulées par ROLLBACK. L’atomicité évite les états partiels; la cohérence préserve les contraintes; l’isolation limite les effets de transactions concurrentes; la durabilité conserve un commit après panne. Les niveaux d’isolation arbitrent cohérence et concurrence: des lectures non validées peuvent voir des données sales, tandis qu’un niveau plus strict augmente les attentes et les verrous.

Exemple guidé: transférer 30 euros de A vers B. On débite A, vérifie que son solde reste positif, crédite B, puis valide. Si le contrôle échoue, on annule les deux écritures:

BEGIN;
UPDATE compte SET solde = solde - 30 WHERE id = 1 AND solde >= 30;
-- vérifier le nombre de lignes modifiées
UPDATE compte SET solde = solde + 30 WHERE id = 2;
COMMIT;

En production, il faut tester les deux comptes, gérer une erreur et verrouiller ou conditionner les lignes pour éviter une course.

Point de contrôle. Citer un état impossible empêché par chaque lettre d’ACID et distinguer COMMIT de ROLLBACK.

Points-clés

• Une transaction est une unité validée ou annulée dans son ensemble.
• ACID décrit atomicité, cohérence, isolation et durabilité, pas une vitesse d’exécution.
• La concurrence impose de traiter les courses, verrous, erreurs et niveaux d’isolation.

5. Conception SQL, index et qualité des requêtes

Conception SQL, index et qualité des requêtes

Un index est une structure auxiliaire qui accélère certaines recherches au prix d’espace et d’un coût lors des insertions ou mises à jour. Il est pertinent sur une colonne fréquemment filtrée, jointe ou triée, mais un index sur une colonne peu sélective peut être ignoré. La sélectivité mesure approximativement la capacité à réduire le nombre de lignes. Pour diagnostiquer une requête, lire son plan avec EXPLAIN, vérifier les cardinalités et éviter de récupérer des colonnes inutiles. Un index ne corrige ni un mauvais modèle ni un filtre logique erroné.

Exemple guidé: les inscriptions sont souvent cherchées par ue_id puis annee. On crée CREATE INDEX idx_inscription_ue_annee ON INSCRIPTION(ue_id, annee);. L’ordre favorise les prédicats commençant par ue_id; une recherche par seule annee ne bénéficie pas nécessairement de cet index composite. On compare le plan avant et après, sur des données réalistes.

Erreur fréquente. Multiplier les index sans mesurer ralentit les écritures. Point de contrôle. Justifier la colonne, l’ordre et le gain attendu de chaque index proposé.

Points-clés

• Un index accélère certains accès mais augmente l’espace et le coût des écritures.
• L’ordre d’un index composite doit suivre les prédicats et tris réellement utilisés.
• EXPLAIN et des données réalistes permettent de vérifier, plutôt que de deviner, le gain.

Exercices corrigés

Exercice 1 · Fondamental

On considère ETUDIANT(id, nom) et UE(id, nom), puis INSCRIPTION(etudiant_id, ue_id, annee, note). Proposer la clé primaire d’INSCRIPTION et les deux clés étrangères. Dire si (7, 3, 2025) peut être inséré deux fois.

Correction détaillée

1. Une inscription est identifiée par un étudiant, une UE et une année: la clé primaire est (etudiant_id, ue_id, annee). 2. etudiant_id référence ETUDIANT(id), et ue_id référence UE(id). 3. La note peut recevoir un CHECK (note BETWEEN 0 AND 20) si elle est obligatoire. 4. La seconde insertion avec les mêmes trois valeurs viole la clé primaire: elle est refusée, même si la note diffère. Une nouvelle année ou une autre UE constitue en revanche une autre inscription.

Exercice 2 · Fondamental

Écrire une requête SQL qui retourne, pour chaque UE, son nom et le nombre d’inscriptions ayant une note supérieure ou égale à 10. Les UE sans réussite doivent apparaître avec 0. Utiliser UE(id, nom) et INSCRIPTION(ue_id, note).

Correction détaillée

1. Partir de UE pour conserver toutes les UE. 2. Faire un LEFT JOIN, avec le critère de réussite dans ON afin de ne pas supprimer les UE sans réussite. 3. Compter une colonne de l’inscription, puis grouper par identifiant et nom. Requête: SELECT u.id, u.nom, COUNT(i.ue_id) AS reussites FROM UE u LEFT JOIN INSCRIPTION i ON i.ue_id = u.id AND i.note >= 10 GROUP BY u.id, u.nom; Pour une UE sans correspondance, i.ue_id est NULL et COUNT(i.ue_id) vaut 0.

Exercice 3 · Intermédiaire

Avec ETUDIANT(id, nom), UE(id, nom) et INSCRIPTION(etudiant_id, ue_id, note), trouver les étudiants dont la moyenne est supérieure à la moyenne générale des notes. Afficher l’identifiant et le nom, une seule fois par étudiant.

Correction détaillée

1. Calculer la moyenne générale dans une sous-requête scalaire: SELECT AVG(note) FROM INSCRIPTION. 2. Grouper les inscriptions par étudiant et calculer AVG(i.note). 3. Comparer cette moyenne au résultat scalaire dans HAVING, car le filtre porte sur une agrégation. 4. Joindre ETUDIANT pour afficher le nom. Requête: SELECT e.id, e.nom FROM ETUDIANT e JOIN INSCRIPTION i ON i.etudiant_id = e.id GROUP BY e.id, e.nom HAVING AVG(i.note) > (SELECT AVG(note) FROM INSCRIPTION); Les étudiants sans inscription sont absents, ce qui est cohérent puisque leur moyenne est indéfinie.

Exercice 4 · Intermédiaire

La table RESULTAT(etudiant_id, ue_id, nom_etudiant, nom_ue, enseignant, note) utilise (etudiant_id, ue_id) comme clé. Les dépendances sont etudiant_id -> nom_etudiant; ue_id -> nom_ue, enseignant; (etudiant_id, ue_id) -> note. Proposer une décomposition en troisième forme normale et expliquer la jointure de reconstruction.

Correction détaillée

1. Les deux premières dépendances sont partielles par rapport à la clé composée, donc la table mélange des faits étudiant, UE et résultat. 2. Créer ETUDIANT(etudiant_id clé primaire, nom_etudiant). 3. Créer UE(ue_id clé primaire, nom_ue, enseignant). 4. Créer RESULTAT(etudiant_id, ue_id, note), avec clé primaire composée et deux clés étrangères. 5. Pour reconstruire les données, joindre RESULTAT à ETUDIANT sur etudiant_id puis à UE sur ue_id. Chaque déterminant est alors une clé de sa table, et la note reste attachée au couple; les mises à jour de nom ou d’enseignant sont effectuées une seule fois.

Exercice 5 · Avancé

Deux transactions peuvent inscrire simultanément un étudiant à une UE, alors que la règle métier impose au plus 30 places et une seule inscription par étudiant et UE. Proposer une stratégie SQL robuste et expliquer les protections contre doublon et dépassement de capacité.

Correction détaillée

1. Déclarer UNIQUE(etudiant_id, ue_id) pour rendre le doublon impossible, même en cas de course. 2. Dans une transaction, verrouiller la ligne UE avec SELECT capacite, COUNT(*) ... FOR UPDATE, ou maintenir un compteur contrôlé sur la ligne verrouillée. 3. Si le nombre courant est inférieur à 30, insérer; sinon ROLLBACK avec un message de quota. 4. COMMIT libère le verrou. 5. Traiter une violation de contrainte unique comme un refus métier, pas comme un succès. Le verrou sérialise les décisions de capacité; l’unicité protège l’invariant individuel. Une isolation adaptée et des tests de concurrence vérifient qu’aucun scénario ne produit 31 inscriptions.

Quiz d’autoévaluation

Question 1

Quelle contrainte garantit qu’une valeur identifie au plus une ligne?

✓ PRIMARY KEY • CHECK • DEFAULT • VIEW

Explication : PRIMARY KEY impose l’unicité et l’absence de NULL pour l’identifiant choisi; CHECK ne vérifie qu’une condition.

Question 2

Quelle clause filtre les groupes après GROUP BY?

• WHERE ✓ HAVING • ORDER BY • ON

Explication : HAVING s’applique aux résultats agrégés d’un groupe; WHERE filtre les lignes avant le regroupement.

Question 3

Quel JOIN conserve toutes les lignes de la table placée à gauche?

• INNER JOIN • RIGHT JOIN ✓ LEFT JOIN • CROSS JOIN

Explication : LEFT JOIN conserve chaque ligne gauche et met NULL à droite lorsqu’aucune correspondance n’existe.

Question 4

Quelle écriture teste correctement l’absence de valeur?

• colonne = NULL ✓ colonne IS NULL • colonne == NULL • NULL(colonne)

Explication : NULL représente une absence inconnue et se teste avec IS NULL, jamais avec une égalité classique.

Question 5

Quel problème la troisième forme normale cherche-t-elle notamment à réduire?

✓ Les dépendances transitives • Les commentaires SQL • La casse des mots-clés • La taille de l’écran

Explication : La troisième forme normale sépare les dépendances transitives entre attributs non clés, ce qui réduit les anomalies.

Question 6

Que fait ROLLBACK?

• Valide définitivement la transaction ✓ Annule les modifications non validées • Crée un index • Trie les lignes

Explication : ROLLBACK ramène la transaction à son état antérieur au début ou au dernier point de sauvegarde selon le SGBD.

Question 7

Quel outil aide à observer le plan d’exécution d’une requête?

✓ EXPLAIN • DESCRIBE HTML • COMMIT • GRANT

Explication : EXPLAIN décrit le plan choisi, les accès et parfois les cardinalités estimées; il aide à diagnostiquer la performance.

Question 8

Pourquoi compter COUNT(i.id) dans un LEFT JOIN plutôt que COUNT(*) pour compter les correspondances?

✓ COUNT(i.id) ignore les lignes sans correspondance • COUNT() interdit GROUP BY • COUNT(i.id) trie les résultats • COUNT() ne fonctionne jamais

Explication : La colonne de droite vaut NULL sans correspondance, donc COUNT(i.id) vaut zéro, tandis que COUNT(*) compte la ligne gauche conservée.

Glossaire

• Clé primaire — Attribut ou ensemble d’attributs qui identifie de manière unique chaque tuple d’une relation et n’accepte pas NULL.
• Clé étrangère — Attribut qui référence une clé d’une autre table afin de maintenir une intégrité référentielle.
• Dépendance fonctionnelle — Contrainte X → Y indiquant qu’une valeur de X détermine une unique valeur de Y dans la relation.
• Normalisation — Démarche de décomposition d’un schéma pour limiter redondances et anomalies tout en préservant l’information.
• Jointure — Opération qui combine des lignes de plusieurs tables selon une condition de correspondance.
• Transaction — Suite indivisible d’opérations validée par COMMIT ou annulée par ROLLBACK.
• Isolation — Propriété ACID qui contrôle ce qu’une transaction concurrente peut observer avant validation.
• Index — Structure auxiliaire permettant d’accélérer certains accès au prix d’espace et de travail lors des écritures.

TP · TP — Inscriptions universitaires sous contraintes

Une faculté veut suivre étudiants, unités d’enseignement, inscriptions et notes. Le mini-cas doit accepter des requêtes de bilan tout en refusant les doublons, les notes hors intervalle et les inscriptions dans une UE pleine.

Objectifs

• Transformer un besoin métier en schéma relationnel avec clés et contraintes.

• Écrire des requêtes SQL avec jointures, agrégations et sous-requêtes.

• Normaliser les données jusqu’à une décomposition justifiée.

• Tester une transaction d’inscription et documenter ses invariants.

Étapes

1. Lister les entités et règles, puis dessiner le schéma relationnel et les dépendances fonctionnelles.

2. Créer ETUDIANT, UE et INSCRIPTION avec clés, clés étrangères, CHECK de note et unicité étudiant-UE.

3. Charger au moins 8 étudiants, 4 UE et 20 inscriptions, dont une UE sans inscription.

4. Écrire cinq requêtes: liste, taux de réussite, moyenne par UE, étudiants au-dessus de la moyenne et UE sans inscrit.

5. Simuler deux inscriptions concurrentes sur une UE limitée, observer l’échec attendu et rédiger une conclusion.

Livrables attendus

• Un fichier schema.sql rejouable et un fichier data.sql cohérent.

• Un fichier requetes.sql commenté avec résultats attendus sur le jeu de test.

• Une courte note présentant le modèle, les invariants, un plan EXPLAIN et les limites.

Critères d’évaluation

• Modèle et normalisation justifiés: 30 %.

• Exactitude des contraintes et des données de test: 25 %.

• Requêtes, résultats et gestion des NULL: 25 %.

• Transaction, reproductibilité et qualité de la note: 20 %.

Piste de résolution

Créer ETUDIANT(id, nom, filiere), UE(id, nom, capacite) et INSCRIPTION(etudiant_id, ue_id, annee, note) avec clé composée, références, CHECK(note BETWEEN 0 AND 20) et éventuellement CHECK(capacite > 0). Utiliser LEFT JOIN pour les UE sans inscription, COUNT(colonne) pour compter les correspondances et HAVING pour les seuils. Pour une place, verrouiller la ligne UE dans une transaction, recompter les inscrits, insérer si le quota est disponible puis COMMIT; sinon ROLLBACK. Vérifier les résultats sur des cas limites et joindre le plan EXPLAIN plutôt qu’une affirmation de performance.

Sujets originaux d’entraînement (non officiels)

Sujet original de préparation — Bases de données et SQL (session normale)

Sujet original non officiel, proposé pour la préparation; ce n’est pas une épreuve d’établissement et il ne reproduit aucune annale ni épreuve d’années passées.

Exercice 1

Concevoir le schéma relationnel d’un service de tutorat: ETUDIANT, TUTEUR, SEANCE et PRESENCE. Donner les clés, les références et une contrainte empêchant deux présences pour la même séance et le même étudiant.

Correction : Choisir des identifiants stables. SEANCE(id, tuteur_id, date_heure) référence TUTEUR; PRESENCE(seance_id, etudiant_id, statut) possède la clé composée (seance_id, etudiant_id) et deux clés étrangères. Ajouter CHECK(statut IN ('present','absent')). Cette clé interdit la double saisie pour une séance et un étudiant tout en permettant des présences dans plusieurs séances.

Exercice 2

Écrire une requête donnant, pour chaque tuteur, le nombre de séances réalisées et la durée moyenne, y compris un tuteur sans séance.

Correction : Partir de TUTEUR et effectuer LEFT JOIN SEANCE ON s.tuteur_id=t.id. Sélectionner t.id, t.nom, COUNT(s.id), AVG(s.duree), puis GROUP BY t.id, t.nom. COUNT(s.id) et AVG(s.duree) ignorent les NULL; le tuteur sans séance obtient 0 et NULL. COALESCE(AVG(s.duree), 0) est possible si l’affichage exige zéro, mais il faut documenter ce choix.

Exercice 3

Expliquer une décomposition en troisième forme normale de RESULTAT(etudiant, ue, nom_etudiant, nom_ue, note) et donner la requête de reconstruction.

Correction : Avec la clé (etudiant, ue), etudiant détermine nom_etudiant et ue détermine nom_ue: ce sont des dépendances partielles. Décomposer ETUDIANT(etudiant, nom_etudiant), UE(ue, nom_ue) et RESULTAT(etudiant, ue, note). Reconstruire par SELECT ... FROM RESULTAT r JOIN ETUDIANT e ON e.etudiant=r.etudiant JOIN UE u ON u.ue=r.ue. Les noms ne sont plus répétés et la note reste portée par le couple.

Sujet original de préparation — Bases de données et SQL (rattrapage)

Sujet original non officiel, proposé pour la préparation; ce n’est pas une épreuve d’établissement et il ne reproduit aucune annale ni épreuve d’années passées.

Exercice 1

Dans COMMANDE(id, client_id, date_commande) et LIGNE(commande_id, produit_id, quantite, prix_unitaire), calculer le total de chaque commande, puis ne garder que les commandes dépassant 100 euros.

Correction : Calculer SUM(l.quantitel.prix_unitaire), joindre LIGNE à COMMANDE, grouper par commande et client, puis filtrer avec HAVING SUM(l.quantitel.prix_unitaire) > 100. Requête: SELECT c.id, c.client_id, SUM(l.quantitel.prix_unitaire) AS total FROM COMMANDE c JOIN LIGNE l ON l.commande_id=c.id GROUP BY c.id, c.client_id HAVING SUM(l.quantitel.prix_unitaire) > 100; Le filtre ne peut pas être WHERE car le total est agrégé.

Exercice 2

Une table JOURNAL(id, utilisateur_id, action, cree_le) est volumineuse. Proposer un index pour retrouver les actions d’un utilisateur dans une période et indiquer comment vérifier son utilité.

Correction : Créer un index composite sur (utilisateur_id, cree_le), car la requête filtre d’abord l’utilisateur puis une plage de dates: CREATE INDEX idx_journal_user_date ON JOURNAL(utilisateur_id, cree_le). Tester avec EXPLAIN sur une requête sélectionnant les colonnes nécessaires, comparer le plan et le temps sur un volume représentatif. L’index ajoute un coût aux insertions; on conserve la décision seulement si le gain mesuré compense ce coût.

Exercice 3

Décrire une transaction sûre pour réserver une place dans ATELIER(id, capacite) lorsque RESERVATION(atelier_id, etudiant_id) ne doit jamais dépasser la capacité et ne doit pas contenir de doublon.

Correction : Déclarer UNIQUE(atelier_id, etudiant_id). Dans BEGIN, sélectionner la capacité de l’atelier FOR UPDATE afin de sérialiser les réservations de cet atelier, compter les réservations et comparer au quota. Si le compte est inférieur à la capacité, insérer puis COMMIT; sinon ROLLBACK. Intercepter une violation d’unicité comme une réservation déjà présente. Le verrou protège le quota entre le comptage et l’insertion, et la contrainte protège l’invariant individuel.

Parcours de révision sur quatre semaines

Semaine 1 · Schéma relationnel, clés et contraintes

Reprendre le chapitre 1, dessiner trois schémas et résoudre les exercices 1 et 2 en testant des violations de contraintes. Avant de terminer, reformule les notions sans tes notes, vérifie les résultats dans le cours et note une difficulté précise à reprendre la semaine suivante.

Semaine 2 · SQL déclaratif et agrégations

Écrire chaque requête du chapitre 2 sans modèle, puis vérifier JOIN, NULL, GROUP BY, HAVING et les quiz 2, 3, 4 et 8. Avant de terminer, reformule les notions sans tes notes, vérifie les résultats dans le cours et note une difficulté précise à reprendre la semaine suivante.

Semaine 3 · Dépendances, normalisation et transactions

Décomposer deux relations, expliquer les anomalies, puis simuler COMMIT et ROLLBACK; résoudre les exercices 3, 4 et 5. Avant de terminer, reformule les notions sans tes notes, vérifie les résultats dans le cours et note une difficulté précise à reprendre la semaine suivante.

Semaine 4 · Intégration, performance et préparation

Réaliser le TP en conditions limitées, lire deux plans EXPLAIN, refaire un sujet original et corriger chaque réponse avec le barème. Avant de terminer, reformule les notions sans tes notes, vérifie les résultats dans le cours et note une difficulté précise à reprendre la semaine suivante.