Bangoua-IT — BDR — S4 : Qualité de schéma & normalisation (1FN→3FN, cas bibliothèque)

But de la séance. Savoir diagnostiquer un schéma qui sent le roussi (doublons, colonnes composites, dépendances cachées) et décomposer proprement jusqu’en 3FN sur le cas « Élève / Auteur / Livre / Emprunt ».

Objectifs

Identifier les anomalies d’insertion, de mise à jour, de suppression.
Mettre une table en 1FN (valeurs atomiques, pas de listes/colonnes répétées).
Passer en 2FN (pas de dépendance partielle à une clé composée).
Atteindre la 3FN (pas de dépendance transitive entre attributs non-clés).

Méthode

1FN : éclater les listes / valeurs multiples dans une cellule.
2FN : si PK composée, sortir les attributs qui ne dépendent que d’une partie de la PK.
3FN : isoler ce qui dépend d’autres attributs non-clés (transitif).

1. Anomalies typiques (bibliothèque)

Insertion : impossible d’ajouter un Livre si le schéma impose de fournir un Emprunt en même temps.
Mise à jour : l’auteur_nom est recopié dans 200 lignes de Livre → une correction oubliée crée une incohérence.
Suppression : en supprimant le dernier Emprunt d’un livre dans une table mal pensée, on perd le livre lui-même.

2. Vers la 1FN : valeurs atomiques

EmpruntBrut — non 1FN

id_eleve	nom_eleve	livres_ids	dates_emprunt
1	Martine Durand	101, 106	2024-09-25, 2024-09-25
2	Hermine Nyaleu	102	2024-09-20

Problème

Listes dans les cellules (livres_ids, dates_emprunt) : violation 1FN.
Redondance du nom de l’élève.

Correction (1FN)

Créer une table d’association où une ligne = un emprunt.

Eleve(id_eleve, nom, email, classe)
Livre(id_livre, titre, id_auteur*, genre, editeur, annee_publication)
Auteur(id_auteur, nom)
Emprunt(id_emprunt, id_eleve*, id_livre*, date_emprunt, date_retour)

3. Vers la 2FN : éviter les dépendances partielles

La 2FN concerne les tables dont la clé primaire est composée. Aucun attribut non clé ne doit dépendre d’une partie seulement de la clé.

EmpruntLarge — candidate 2FN

id_eleve	id_livre	date_emprunt	eleve_nom	eleve_email	livre_titre	auteur_nom
1	101	2024-09-25	Martine Durand	martine.durand@mail.com	Le réseau	John Grisham
2	102	2024-09-20	Hermine Nyaleu	hermine.nyaleu@mail.com	Méditations métaphysiques	René Descartes
2	101	2024-09-21	Hermine Nyaleu	hermine.nyaleu@mail.com	Le réseau	John Grisham

Dépendances (informelles)

PK(EmpruntLarge) = (id_eleve, id_livre, date_emprunt)
eleve_nom, eleve_email dépendent seulement de id_eleve → dépendance partielle.
livre_titre, auteur_nom dépendent de id_livre → dépendance partielle.

Décomposition (→ 2FN)

Emprunt(id_emprunt, id_eleve*, id_livre*, date_emprunt, date_retour) -- ou PK composée (id_eleve,id_livre,date_emprunt)
Eleve(id_eleve, nom, email, classe)
Livre(id_livre, titre, id_auteur*, genre, editeur, annee_publication)

4. Vers la 3FN : éviter les dépendances transitives

En 3FN, aucun attribut non clé ne doit dépendre d’un autre attribut non clé.

LivreMélangé — candidate 3FN

id_livre	titre	id_auteur	auteur_nom	editeur	editeur_adresse	annee_publication
101	Le réseau	1	John Grisham	JCLattès	Paris	2024
102	Méditations métaphysiques	2	René Descartes	Flammarion	Paris	2011

Dépendances

PK(LivreMélangé) = id_livre.
auteur_nom dépend de id_auteur, et id_auteur dépend de id_livre → transitive (id_livre → id_auteur → auteur_nom).
editeur_adresse dépend de editeur → autre dépendance transitive.

Décomposition (→ 3FN)

Livre(id_livre, titre, *id_auteur, *id_editeur, annee_publication)
Auteur(id_auteur, nom)
Editeur(id_editeur, nom, adresse)  -- optionnel si tu veux modéliser l'éditeur

Dans notre fil rouge minimal, on conserve Auteur et on peut laisser editeur en texte si inutile de le normaliser davantage.

Dépendances fonctionnelles (DF)

Notation : X → Y “les valeurs de X déterminent celles de Y”. Ex. id_livre → titre. 2FN : si PK composée = (A,B,…) aucun attribut non-clé ne doit dépendre seulement de A ou B. 3FN : aucun attribut non-clé ne doit dépendre d’un autre non-clé (chaîne X→Y→Z où Y n’est pas clé).

Unicité métier

Règles d’unicité qui ont du sens métier : Eleve.email UNIQUE ; Livre.isbn UNIQUE (si présent), à défaut UNIQUE(titre,id_auteur,annee_publication) ; et, pour Emprunt, UNIQUE(id_eleve,id_livre,date_emprunt).

5. Activités

Activité A : Mettre en 1FN

À partir de EmpruntBrut, proposez des tables 1FN. Donnez PK & FKs.

Correction — pistes

Eleve(id_eleve, nom, email, classe)
Auteur(id_auteur, nom)
Livre(id_livre, titre, id_auteur*, genre, editeur, annee_publication)
Emprunt(id_emprunt, id_eleve*, *id_livre, date_emprunt, date_retour)

Activité B : Passer en 2FN

Sur EmpruntLarge, repérez les attributs dépendant d’une partie de la PK. Décomposez.

Correction — pistes

Emprunt(id_emprunt, id_eleve*, id_livre*, date_emprunt, date_retour)
Eleve(id_eleve, nom, email, classe)
Livre(id_livre, titre, id_auteur*, genre, editeur, annee_publication)

Activité C : Passer en 3FN

Sur LivreMélangé, isolez Auteur (et éventuellement Éditeur). Donnez le schéma final.

Correction — pistes

Livre(id_livre, titre, id_auteur*, annee_publication)
Auteur(id_auteur, nom)
-- Option : Editeur(id_editeur, nom, adresse) et Livre(id_editeur*)

6. Schéma fil rouge normalisé (cœur en 3FN)

Notation texte

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

Lecture

Les infos élève et livre ne sont plus recopiées dans Emprunt.
Auteur est isolé → plus d’auteur_nom dupliqué dans Livre.
Possibilité d’extension 3FN : modéliser Éditeur si besoin.

7. Quiz

Donnez un exemple de table non 1FN dans le contexte “bibliothèque” et comment la corriger.
Expliquez, en une phrase, la différence entre 2FN et 3FN.
Dans EmpruntLarge, citez un attribut qui dépend partiellement de la clé composée.
Dans LivreMélangé, pourquoi auteur_nom viole 3FN ?
Donnez un exemple d’unicité métier utile dans ce cas.

À retenir. 1FN : pas de listes dans les cellules • 2FN : aucun attribut non-clé ne dépend d’une partie de la PK composée • 3FN : aucun non-clé ne dépend d’un autre non-clé. Résultat : moins de redondance → moins d’incohérences.

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