Dépendances fonctionnelles et normalisation d'un schéma relationnel
À retenir
Cadre programme : DCG, programme 2025 (arrêté du 4 août 2025, première session 2027), UE 8 « Systèmes d'information de gestion », partie 2 « Gérer des données du système d'information », sous-partie 2.1 « Structurer et manipuler des données via les bases de données », rubrique 2.1.1 « Structurer une base de données ».
Pourquoi c'est central à l'examen : le sujet fournit une table « à plat » ou un schéma et demande s'il est normalisé, en le justifiant, puis de le corriger ou de l'adapter à une nouvelle règle de gestion. La justification repose sur les dépendances fonctionnelles : sans elles, la réponse reste une impression.
01Pourquoi normaliser : les anomalies d'une table à plat
Un service commercial note ses bons de commande dans une seule table. Chaque ligne reprend tout : le client, la commande, le produit.
À retenir
BON (NumBon, DateBon, NumClient, NomClient, VilleClient, RefProduit, Designation, PrixHT, Quantite)
La clé primaire est le couple (NumBon, RefProduit).
| NumBon | DateBon | NumClient | NomClient | VilleClient | RefProduit | Designation | PrixHT | Quantite |
|---|---|---|---|---|---|---|---|---|
| 101 | 2026-01-12 | 1 | Boulangerie Lemoine | Lyon | P01 | Ramette papier A4 | 4.50 | 20 |
| 101 | 2026-01-12 | 1 | Boulangerie Lemoine | Lyon | P02 | Classeur à levier | 3.00 | 10 |
| 102 | 2026-01-20 | 3 | Garage Perrin | Lille | P03 | Clavier sans fil | 25.00 | 2 |
| 103 | 2026-02-03 | 1 | Boulangerie Lemoine | Lyon | P01 | Ramette papier A4 | 4.50 | 10 |
| 103 | 2026-02-03 | 1 | Boulangerie Lemoine | Lyon | P04 | Souris optique | 12.00 | 5 |
| 104 | 2026-02-10 | 4 | Mairie de Tours | Tours | P05 | Chaise de bureau | 90.00 | 4 |
Cette table fonctionne tant que personne n'y touche, mais elle cumule trois anomalies.
1.1 Anomalie de mise à jour (incohérence)
Le client 1 apparaît sur 4 lignes, avec sa ville recopiée à chaque fois. Un employé corrige la ville sur une seule ligne.
UPDATE BON
SET VilleClient = 'Villeurbanne'
WHERE NumBon = 101 AND RefProduit = 'P01';
On interroge ensuite les villes connues pour le client 1.
SELECT DISTINCT NumClient, VilleClient
FROM BON
WHERE NumClient = 1
ORDER BY VilleClient;
| NumClient | VilleClient |
|---|---|
| 1 | Lyon |
| 1 | Villeurbanne |
La base affirme désormais que le même client habite à deux endroits : elle est devenue incohérente. Cette incohérence vient de la redondance : la même information est stockée plusieurs fois.
1.2 Anomalie d'insertion
On veut enregistrer un nouveau client avant sa première commande, ou un nouveau produit avant sa première vente. C'est impossible : la clé (NumBon, RefProduit) doit être renseignée, et les autres colonnes aussi.
INSERT INTO BON (NumClient, NomClient, VilleClient)
VALUES (5, 'Atelier Duval', 'Lyon');
Le SGBDR refuse la ligne : la table oblige à inventer un bon et un produit fictifs pour enregistrer un simple client.
1.3 Anomalie de suppression (perte d'information)
La mairie de Tours n'a passé qu'une commande, la 104. On la supprime parce qu'elle a été annulée.
DELETE FROM BON
WHERE NumBon = 104;
SELECT COUNT(*) AS NbLignesMairie
FROM BON
WHERE NumClient = 4;
| NbLignesMairie |
|---|
| 0 |
En supprimant la commande, on a aussi perdu le client (nom, ville) et le produit P05 (désignation, prix) : une information a disparu sans que personne l'ait voulu.
À retenir
La normalisation est la démarche qui supprime ces trois anomalies en décomposant la table en plusieurs relations, chacune ne décrivant qu'un seul sujet.
02La dépendance fonctionnelle
2.1 Définition
Il y a dépendance fonctionnelle de X vers Y, notée X → Y, lorsque la valeur de X détermine une seule valeur de Y. On lit « X détermine Y » ou « Y dépend fonctionnellement de X ». X et Y peuvent être un ou plusieurs attributs.
Exemples sur BON :
- NumClient → NomClient : un numéro de client désigne un seul nom ;
- RefProduit → Designation, PrixHT : une référence désigne une désignation et un prix ;
- NumBon → DateBon, NumClient : un numéro de bon désigne une seule date et un seul client ;
- (NumBon, RefProduit) → Quantite : un couple bon et produit désigne une seule quantité.
Un contre-exemple : NomClient → NumClient est faux si deux clients peuvent porter le même nom. Une dépendance fonctionnelle est une règle de gestion, pas une propriété des données observées : elle ne se prouve pas en regardant quelques lignes. Les données peuvent seulement montrer qu'elle n'est pas respectée : après la mise à jour du paragraphe 1.1, la table affiche deux villes pour le client 1. La règle NumClient → VilleClient reste vraie ; ce sont les données qui la violent, et cette incohérence est le symptôme de la redondance.
2.2 Dépendance fonctionnelle élémentaire
Une dépendance X → Y est élémentaire quand aucune partie de X ne suffit à déterminer Y. Si X est un attribut unique, elle est élémentaire d'office.
- (NumBon, RefProduit) → Quantite est élémentaire : ni NumBon seul, ni RefProduit seul ne donne la quantité.
- (NumBon, RefProduit) → Designation est vraie mais non élémentaire : RefProduit seul détermine déjà Designation. On dit que Designation dépend d'une partie de la clé.
2.3 Dépendance fonctionnelle directe
Une dépendance X → Z est directe quand elle ne s'obtient pas par transitivité, c'est-à-dire qu'il n'existe pas d'attribut intermédiaire Y tel que X → Y et Y → Z.
- NumBon → NumClient et NumClient → NomClient. Alors NumBon → NomClient est vraie, mais non directe : elle passe par NumClient.
- NumClient → NomClient est directe.
2.4 Récapitulatif pour BON
| Dépendance | Élémentaire ? | Directe ? | Commentaire |
|---|---|---|---|
| NumBon → DateBon | oui | oui | Informations du bon |
| NumBon → NumClient | oui | oui | Informations du bon |
| NumClient → NomClient, VilleClient | oui | oui | Informations du client |
| NumBon → NomClient | oui | non | Transitive via NumClient |
| RefProduit → Designation, PrixHT | oui | oui | Informations du produit |
| (NumBon, RefProduit) → Designation | non | sans objet | Dépend d'une partie de la clé (le caractère direct ne s'examine que pour les dépendances élémentaires) |
| (NumBon, RefProduit) → Quantite | oui | oui | Dépend de toute la clé |
Dans un schéma bien construit, ce sont les dépendances élémentaires et directes qui structurent les relations.
2.5 Lien avec la clé primaire
La clé primaire d'une relation est un ensemble minimal d'attributs qui détermine tous les autres. Dans BON, (NumBon, RefProduit) détermine tout : le bon donne la date et le client (et, à travers lui, le nom et la ville), le produit donne la désignation et le prix, et le couple donne la quantité.
03La normalisation
3.1 Le critère à connaître
Une relation est normalisée lorsque ses attributs sont à la fois :
- atomiques : une seule valeur par case, sans liste ni groupe répété ;
- dépendants de toute la clé : aucune dépendance d'un attribut non clé vers une partie seulement de la clé ;
- dépendants uniquement de la clé : aucune dépendance d'un attribut non clé vers un autre attribut non clé.
On retient la formule : « la clé, toute la clé, rien que la clé ». Ces trois conditions correspondent aux trois premières formes normales. Le programme limite l'étude à la 3e forme normale et n'exige pas de les distinguer : il demande de dire si le schéma est normalisé ou non et de le justifier.
3.2 Méthode de justification
- Lister les dépendances fonctionnelles à partir des règles de gestion de l'énoncé (pas des seules données).
- Identifier la clé primaire.
- Chercher les écarts : un attribut non clé qui dépend d'une partie de la clé ? d'un autre attribut non clé ? une case contenant plusieurs valeurs ?
- Conclure : « non normalisé, car ... » en citant la dépendance fautive et l'anomalie qui en résulte. Ou « normalisé, car tous les attributs non clés dépendent de la clé entière et d'elle seule ».
3.3 Méthode de décomposition
Pour chaque dépendance fautive X → Y :
- créer une nouvelle relation (X, Y) dont la clé primaire est X ;
- retirer Y de la relation d'origine ;
- conserver X dans la relation d'origine, où il devient clé étrangère vers la nouvelle relation ;
- recommencer jusqu'à ce que plus aucune dépendance fautive ne subsiste.
Appliquée à BON, la décomposition donne :
À retenir
CLIENT (NumClient, NomClient, VilleClient)
PRODUIT (RefProduit, Designation, PrixHT)
COMMANDE (NumBon, DateBon, #NumClient)
LIGNE (#NumBon, #RefProduit, Quantite)
| NumClient | NomClient | VilleClient |
|---|---|---|
| 1 | Boulangerie Lemoine | Lyon |
| 3 | Garage Perrin | Lille |
| 4 | Mairie de Tours | Tours |
| RefProduit | Designation | PrixHT |
|---|---|---|
| P01 | Ramette papier A4 | 4.50 |
| P02 | Classeur à levier | 3.00 |
| P03 | Clavier sans fil | 25.00 |
| P04 | Souris optique | 12.00 |
| P05 | Chaise de bureau | 90.00 |
| NumBon | DateBon | NumClient |
|---|---|---|
| 101 | 2026-01-12 | 1 |
| 102 | 2026-01-20 | 3 |
| 103 | 2026-02-03 | 1 |
| 104 | 2026-02-10 | 4 |
| NumBon | RefProduit | Quantite |
|---|---|---|
| 101 | P01 | 20 |
| 101 | P02 | 10 |
| 102 | P03 | 2 |
| 103 | P01 | 10 |
| 103 | P04 | 5 |
| 104 | P05 | 4 |
La décomposition ne perd aucune information : en rapprochant les quatre relations par leurs clés (jointures du cours 16), on reconstitue exactement la table BON d'origine.
Les trois anomalies ont disparu : la ville d'un client se corrige une seule fois, on peut créer un client ou un produit sans commande, et la suppression d'une commande laisse intacts le client et le produit.
3.4 Ne pas confondre normalisé et morcelé
La normalisation sépare des sujets (clients, produits, commandes), pas des attributs pris au hasard. Elle a un coût : retrouver une information demande des jointures. Le bon niveau est celui où chaque fait n'est stocké qu'une seule fois.
3.5 Les données calculées
Un attribut que l'on peut recalculer à partir d'autres (le montant d'une ligne = Quantite × PrixHT, un âge à partir de la date de naissance) est une redondance : si l'une des sources change, la valeur stockée devient fausse. On ne le stocke pas, on le calcule dans la requête (cours 15).
Exception à justifier par une règle de gestion : le prix figé au moment de la commande. Si le prix facturé doit rester celui du jour de la commande alors que le prix du catalogue évolue, il faut stocker un prix unitaire dans la ligne de commande. Ce n'est plus une redondance : il dépend alors de la clé entière de la ligne (cas 2 ci-dessous).