Bangoua-IT — BDR — S3 : Contraintes d’intégrité (bibliothèque, débranché)

But de la séance. Savoir reconnaître, formuler et vérifier les contraintes d’intégrité d’une BDR sur le cas « bibliothèque » (Élève, Auteur, Livre, Emprunt), uniquement sur papier (sans SQL), à partir d’instances volontairement bruitées.

Objectifs

Distinguer : intégrité d’entité (PK), intégrité référentielle (FK), intégrité de domaine.
Repérer des violations : PK dupliquée/NULL, FK orpheline, valeurs hors domaine

Bibliothèque

Élève — instance

id_eleve	nom	email	classe
1	Martine Durand	martine.durand@mail.com	T07
2	Hermine Nyaleu	hermine.nyaleu@mail.com	T08
2	Hermine Nyaleu	h.nyaleu@mail.com	T08
	Aïcha Djérouni	aicha.djerouni@mail.com	T07
5	Paul Dupont	paul.dupont@mail.com	T13
6	Paul Dupont	paul.dupont@mail.com	T05

Auteur — instance

id_auteur	nom
1	John Grisham
2	René Descartes
2	R. Descartes
4

Livre — instance

id_livre	titre	id_auteur	genre	editeur	annee_publication	isbn
101	Le réseau	1	roman	JCLattès	2024	978270000001
102	Méditations métaphysiques	2	essai	Flammarion	2011	978208070000
103	Les Misérables	3	roman	Larousse	2007
104	Le réseau	1	roman	JCLattès	2024	978270000001
105	Belle Greene	99	roman	Flammarion	2021	978208070123
106	Jacaranda	5	roman	Grasset	1420	978224680000

Emprunt — instance

id_emprunt	id_eleve	id_livre	date_emprunt	date_retour
1	1	101	2024-09-25	2024-10-05
2	2	102	2024-09-20	2024-09-30
3	2	102	2024-09-20	2024-09-30
4	6	105	2024-10-01	2024-10-10
5	3	103	2024-09-18	2024-09-10
2	5	101	2024-10-02	2024-10-12
7	999	101	2024-10-03	2024-10-12
8	1		2024-10-03	2024-10-12

Activité A : Diagnostiquer les violations

PK (entité) : surlignez les PK dupliquées / NULL dans chaque table.
FK (référentielle) : entourez les FKs orphelines et les FKs NULL non autorisées.
Domaine : repérez les valeurs hors liste (classe), hors bornes (année), formats invalides (email/ISBN), incohérences de dates.
Unicité métier : isolez au moins 2 doublons « métier » (ex. email élève, isbn livre, (id_eleve,id_livre,date_emprunt) emprunt).

Correction : pistes

Élève : id_eleve dupliqué (=2), id_eleve NULL, classe=T13 hors liste ; email dupliqué → devrait être UNIQUE.
Auteur : id_auteur dupliqué (=2) ; nom vide si NOT NULL.
Livre : isbn dupliqué ; id_auteur=99 orphelin ; annee_publication=1420 hors bornes ; isbn NULL si requis.

Check-list des contraintes (par table)

Élève

PK : id_eleve (UNIQUE, NOT NULL).
email : UNIQUE (unicité métier), format basique (CHECK).
classe : domaine fermé {T01…T12} (CHECK/liste).

Auteur

PK : id_auteur.
nom : NOT NULL, éventuellement non vide (CHECK).

Livre

PK : id_livre.
FK : id_auteur → Auteur.id_auteur (NOT NULL).
isbn : UNIQUE si présent (unicité métier) ; sinon contrainte approximative UNIQUE(titre,id_auteur,annee_publication) (à documenter).
annee_publication : borne [1450..année_courante] (CHECK).
genre : liste contrôlée (roman/essai/… si utile).

Emprunt

Variante 1 (réaliste) : PK=id_emprunt + UNIQUE(id_eleve,id_livre,date_emprunt) (unicité métier de la saisie).
Variante 2 (pédago) : PK composée (id_eleve,id_livre,date_emprunt).
FK : id_eleve → Eleve.id_eleve ; id_livre → Livre.id_livre (NOT NULL).
CHECK : date_emprunt ≤ date_retour (si date_retour non NULL).

Activité B — Politique de suppression (10 min)

Proposez une politique pour Livre → Emprunt et justifiez.
Proposez une autre politique pour Élève → Emprunt (cas RGPD/anonymisation) et justifiez.

Exercices d’application

Exercice 1 : Écrire les contraintes d’un schéma (bibliothèque)

À partir du schéma texte, listez PK/FK/UNIQUE/NOT NULL/CHECK/domains :

Eleve(id_eleve, nom, email, classe)
Auteur(id_auteur, nom)
Livre(id_livre, titre, *id_auteur, genre, editeur, annee_publication, isbn)
Emprunt(id_emprunt, *id_eleve, *id_livre, date_emprunt, date_retour)
-- Variante sans id_emprunt : PK(Emprunt) = (id_eleve, id_livre, date_emprunt)

Correction : pistes

PK: Eleve.id_eleve, Auteur.id_auteur, Livre.id_livre, Emprunt.id_emprunt (ou PK composée).
FK: Livre.id_auteur→Auteur.id_auteur ; Emprunt.id_eleve→Eleve.id_eleve ; Emprunt.id_livre→Livre.id_livre.
UNIQUE (métier) : Eleve.email ; Livre.isbn (si présent) ou (titre,id_auteur,annee_publication) ; (id_eleve,id_livre,date_emprunt) sur Emprunt.
NOT NULL : toutes PK, FKs, Auteur.nom, éventuellement Livre.titre, Emprunt.date_emprunt.
Domaines/CHECK : classe ∈ {T01…T12} ; annee_publication ∈ [1450..année_courante] ; format email/isbn ; date_emprunt ≤ date_retour.

Exercice 2 : Nettoyer une instance

Sur les tables plus haut, proposez des corrections pour respecter toutes les contraintes.

Correction — pistes

Élève : fusionner/supprimer doublon id=2 ; affecter un id à la ligne NULL ; corriger classe T13 ; rendre email UNIQUE.
Auteur : corriger PK dupliquée (2) ; imposer nom non vide.
Livre : corriger FK id_auteur=99 ; supprimer/éditer doublon d’isbn ; bornes année ; décider si isbn peut être NULL.
Emprunt : supprimer doublon métier (2,102,2024-09-20) ; corriger id_emprunt dupliqué (=2) ; réparer orphelins ; vérifier date_retour ≥ date_emprunt.

Mini-quiz

Différence entre PK (entité) et FK (référentielle) ? Donnez un exemple dans la bibliothèque.
Donnez un exemple de contrainte de domaine utile pour annee_publication et un exemple de CHECK sur des dates d’emprunt.
Expliquez l’unicité métier et citez 2 colonnes/ensembles concernés dans ce cas.
Quand choisir une PK composée pour Emprunt plutôt qu’un id_emprunt technique ?
Dans quel cas choisir RESTRICT plutôt que CASCADE pour Livre → Emprunt ?

À retenir. Une BDR reste saine si la PK est unique et non NULL, les FK pointent vers des lignes existantes, les valeurs respectent leur domaine, et les règles d’unicité métier évitent les doublons « réels ».

← Séance précédente Séance suivante →