Normalisation relationnelle

1. Normalisation relationnelle Witold Litwin http://ceria.dauphine.fr/witold.html

2. Modélisation du monde réel • Une base est un modèle d'une entreprise (ANSI-SPARC) • Innombrable méthodes d'aide à la conception (intégré) d'une base: • empiriques • semi-empiriques (E-R) • formelles • normalisation relationnelle • OO

3. Base S-P (Exemple canon) SP S P

4. Pourquoi ce schéma pour S-P ? • Autres schémas sont possibles • Une table par fournisseur IBM (S#, STATUT, CITY, P#, PNAME; QTY) HP (S#; STATUT, CONTACT, CITY, P#, PNAME, QTY) …. • Une seule table (universelle) S-P (S#, SNAME,STATUS,CITY,P#,QTY,PNAME,COLOR,WEIGHT,PCITY) • Autres intermédiaires SP’ (S#, SNAME, STATUS, CITY, P#, QTY ), P (P#…) • Alors pourquoi le schéma S,SP,P ?

5. Normalisation • Processus de création formelle d'une base relationnelle (Codd) • Un remplacement de relations avec des anomalies par une ou plusieurs relations "meilleures" • Traditionnellement, le remplacement appelé aussi décomposition sans perte est basé en général sur la notion de • dépendances fonctionnelles • dépendances multivaluées • Pour 1-O NF, le remplacement est un regroupement au lieu de la décomposition

6. Normalisation Base Attributs (rel. universelle) Couverture de Dépendances (FDs, MDs, IDs...)

7. Normalisation Base Cuverture minimale (FDs, MDs, IDs...) Base en 1-O NF table en 1 NF

8. Normalisation Base Cuverture minimale (FDs, MDs, IDs...)

9. Normalisation Base Cuverture minimale (FDs, MDs, IDs...) etc jusqu'à n 7 fois (5NF)

10. Normalisation • Une de plus belles pièces de la théorie des BDs • Mais, d'un intérêt discutable en pratique • Notamment, car on proscrit les valeurs nulles • L'idée première: séparation de torchons et de serviettes mélangées dans une même relation R • en deux ou plus relations- projections, séparant les torchons de serviettes et telles que • si besoin, la jointure de ces projections donne de nouveau R • Très à la mode il y a 20 ans • Peu de nouveautés depuis sauf: • 1-O NF (1991) • Théorème de Date & Fagin sur l'équivalence de 3 NF et n NF n > 3 (1993)

11. Formes normales connues • 1-O NF (Ketabchi, Krishnamourthy, Litwin, 1991) • 1 NF (Codd, 1971) • 2 NF (Codd, 1971) • 3 NF (Codd, 1971) • BCNF (Boyce, Codd, 1971) • 4 NF (Fagin, 1977) •  5 NF (Fagin, 1979) • 1-O NF est une forme d'une base, toute autre est une forme d'une relation • Une relation en n > 1 NF ou en BCNF est toujours en n-1 NF ou BCNF.Relation en 5 NF est en 4 NF et en BCNF et en 3NF… • Une relation à clé atomique et en 3 NF est en BCNF et en 4 et 5 NF.

12. Dépendances Fonctionnelles • Un attribut B d'une table R est fonctionnellement dépendant sur A de R ; A -> B ; ssi, pour tout tuple t de toute extension de R: t [a1] = t [a2 ]t [b1] = t [b2 ] • A et B peuvent être composites • DFs sont des propriétés sémantiques (du schéma de R), donc s'appliquent à toute extension (légale) de R • Une DF d'une extension de R n'est pas nécessairement une DF de R • Pour tout B non-clé et tout A clé, on a: A -> B `

13. SP S • S: S# -> SNAME ; S# -> STATUS ; S# -> CITY STATUS -> CITY ?S: S# -> {SNAME, CITY} ; {S#, CITY} -> {STATUS, CITY} • Combien y a-t-il de DFs dans S ? • Et en général dans une R n-aire avec une clé atomique ? ? SP : S# -> P# ; P# -> QTY P

14. Règles d'inférence de DFs • Si F est un ensemble de DFs sur R, (couverture fonctionnelle de R), une DF X -> Y est inférée (déduite) de F si X -> Y est valide pour tout tuple d'une extension légale de R (donc respectant F). • On note F ÞX -> Y • F: {S# -> SNAME ; S# -> STATUS ; S# -> CITY} Þ S# -> {SNAME, CITY} ; {S#, CITY} -> {STATUS, CITY} • Règles d'inférence des DFs (Armstrong ,1974) • X, Y, Z sont des attributs de R (i) XY ÞX -> Y * Réflexivité (ii) XYÞXZ -> YZ * Augmentation (iii) XY, YZ ÞX -> Z * Transitivité

15. Règles d'inférence de DFs • Il est prouvé que les règles d'Armstrong sont fondées (sound) et complètes • Toute DF inférée en utilisant (i) à (iii) est une DF inférée • Toute DF qui peut être inférée de F, peut être inférée en utilisant seulement (i) à (iii) • Fermeture F+ de F: l'ensemble des DFs qui peuvent être inférées de F F+ F

16. Règles additionnelles • XYZ ÞX -> Y * Projection • XY, XZ ÞX -> YZ * Union (addition) • XY, WYZ ÞWX -> Z * Pseudo-transitivité • XYZ ÞX -> Y, X -> Z * Décomposition • XX* Auto-détermination • X -> Y, V -> Z Þ XV -> YZ * Composition • XY ÞXW -> Y * Augmentation à gauche • .... • Prouver ces règles

17. 1 NF • Relation R est en 1 NF si toute valeur d'attribut est atomique • cette normallisation crée des redondances ! O NF 1 NF P1 P2 P3 P4 S1 S1 S1 S1 P1 P2 P3 P4 S1 Norm. S2 S2 S2 P1 P2 P3 P1 P2 P3 S2

18. 2 NFDéfinitions préliminaires • Attribut clé • Tout attribut faisant partie d’une clé • Clé primaire ou candidate • Un attribut clé n’est peut-être pas la clé lui-même • (S#, P#) c’est la clé de SP • S# est un attribut clé, ainsi que P# • Attribut non-clé • Tout attribut non inclus dans une clé • QTY est un attribut non-clé

19. 2 NF • R est en 2 NF si R est en 1 NF et tout attribut A non-clé est pleinement (fonctionnellement) dépendant de la clé primaire S (S#, STATUS, CITY) est en 2 NF SP’ (S#, STATUS, CITY, P#, QTY ) n'est pas en 2 NF STATUS S# QTY P# CITY

20. Base S-P (Exemple canon) SP S P

21. Pourquoi 2NF ? Considère que la base S-P contient la table P et, au lieu de S et SP, la table SP’ déjà mentionnée: SP’ (S#, STATUS, CITY, P#, QTY ) = S JOIN SP Pour simplifier, on oublie SNAME dans S STATUS S# QTY P# CITY

22. STATUS S# QTY CITY P#

23. 1 NF • Anomalies • d'insertion • pas de nouveau fournisseur sans aucune pièce • de suppression • suppression de la dernière pièce supprime toute info sur le fournisseur • de mise à jour • il faut mettre à jour des valeurs inutilement dupliquées • consistance peut être détruite • S'agit-t-il vraiment d'anomalies ?? • Rédondances !! • les données d'un fournisseur sont dupliquées plusieurs fois

24. Table SP’ Dernière info sur S3

25. Solution • Décomposition sans perte • On remplace une relation R par ses projections Ri i=1,2.. telles que la jointure naturelle R' des Ri est égale à R, c. à d. pour toute extension de R : • il n'y a pas de tuples de R qui manquent dans R' • il n'y a pas de tuples en plus dans R' par rapport à R R1 R R' R2

26. Décomposition sans perte • En général, il existe une décomposition sans perte en deux projections R1 et R2 • mais il y a en des cas où la seule décomposion sans perte est en i > 2 projections • on verra l'ex. en discutant la 5 NF • Souvent R1 ou R2 est décomposable à son tour • en conduisant aussi à une décomposition sans perte de R en i > 2 relations

27. Décomposition sans perte • Une décomposion de R muni de la couvert. fonct. F en R1 et R2 est sans perte ssi: • (R1R2) -> R1 \ R2 est dans F+ • ou (R1R2) -> R2 \ R1 est dans F+ • à vérifier sur notre exemple

28. Règle pratique • Toute relation R avec • A, B, C attr. atomiques ou composés • une DF A -> B peut être décomposée sans perte enR1 (A, B) et R2 (A, C) • Théorème de Heath (1971) • Conduit à la « règle de patates »

29. Normalisation en 2-NF • Décomposition correspondante de SP’ A = S# ; B = (STATUS, CITY) • S’ est la « patate blanche», SP la rouge • SP (S#, P#, QTY) • S’ (S#, STATUS, CITY) STATUS S# QTY CITY P#

30. Normalisation en 2-NF • Décomposition correspondante de SP’ A = S# ; B = (STATUS, CITY) • Après la séparation de patates • Avec la contrainte d'intégrité référentielle générée ainsi • SP (S#, P#, QTY) • S’ (S#, STATUS, CITY) STATUS S# S# QTY P# CITY

31. SP (S#, P#, QTY) S# QTY P# • S’ (S#, STATUS, CITY) STATUS S# CITY • Vérifie que cette décomposition est sans perte ! • Sans la DF suppl.,on retrouverait notre schéma initial de S-P

32. Décomposition sans perte • On ne perd pas d'information • On gagne la possibilité d'en introduire davantage dans la base • Fournisseurs sans fourniture en cours • Pièces sans fournitures • Fournitures sans fournisseur en cours ? • Une possibilité très importante comme on l'a vu • Une autre vue de l'anomalie d'insertion

33. Décomposition préservant les DF • L'union des couvertures fonctionnelles des projections de R est équivalente à la couverture fonctionnelles de R • La couverture : un ensemble de DFs complet au sens des axiomes d'Armstrong • Une décomposition sans perte • peut préserver les DFs • décompositions en 3NF • peut ne pas préserver les DFs • décompositions en BCNF

34. Conception de la base S-P • Relation universelle initiale SP1 (S#, SNAME, SCITY, STATUS, P#, PNAME, COLOR, WEIGHT, PCITY, QTY) • Les DFs ? • 1ère décomposition sans perte P (P#, PNAME, COLOR, WEIGHT, PCITY) SP2 (S#, SNAME, SCITY, STATUS, P#, QTY) • On retrouve SP’ augmenté par SNAME • Est-ce que c'est la seule 1ère décomposition sans perte possible ? • 2ème décomposition sans perte, de SP2 seul SP (S#, P#, QTY) S (S#, SNAME, SCITY, STATUS) • Avec P, on retrouve notre schéma habituel • Est-ce que les DFs de SP1 sont préservées ?

35. Exercices • Etud (E#, Nom, Cours, Note)F = {E# -> Nom ; {E# , Cours} -> Note}} • Décompositions sans perte ? • EN (E#, Nom), EC (E#, Cours, Note) • ENC (E#, Nom, Cours), CN (Cours, Note) • Et les DFs, sont-elles préservées ?

36. Exemples courants • Pers (SS#, Nom, Ville, CP, Voit#, Type) Pers (SS#, ...) et PV (SS#, Voit#,..) • Empl (E#, Nom, Dep, Loc, Tache, Def, Perf) • à décomposer en combien d'étapes ? • Ami1 (SS#, Nom, Tel, A-SS#, A-Nom, A-Tel) • Ami2 (SS#, Nom, Tel, A-SS#) • Dessine les graphes de ref. pour Ami1 et Ami2 ? • Equipe (E#, Nom, EqNom) • La modélisation naturelle souvent évite ces cas "naturellement"

37. 2NF et clé candidates • Une relation peut être en 2NF par rapport à la clé primaire (P#), sans être en 2NF par rapport à une clé candidate (Nom, Tel) Pers (P#, Nom, Tel, Salaire, Prefix-0?) • Une définition + stricte de 2NF est alors possible R est en 2NF, si R is en NF et tout A non-clé est pleinement dépendant de toute clé • Divergences existent sur l'utilité de cette définition • la décomposion en 3NF n'en dépend pas

38. 3-NF • Problème avec 2-NF • DFs transitives conduisent à des anomalies • Considère la table SP’ déjà mentionnée: • SP’ (S#, STATUS, CITY, P#, QTY ) • Avec une DF supplémentaire STATUS S# QTY Contrainte additionnelle P# CITY

39. 3-NF R est en 3 NF si: • R est en 2 NF • Tout attribut non-clé est non- transitivement dépendant de la clé primaire • La table SP' sans DF suppl. est elle en 3-NF • Et avec

40. Décomposition 2 NF -> 3 NF • Plusieurs décompositions sans perte sont souvent possibles • En projections indépendantes • De préférence • En projections dépendantes

41. Décomposition 2 NF -> 3 NFen projections indépendantes • On isole les attributs non-clé en DFs dans des projections telles que • la décomposition est sans perte • aucune MAJ d'une projection ne peut violer une DF originelle

42. Décomposition 2 NF -> 3 NFen projections indépendantes • CS (CITY, STATUS) • S1 (S#, STATUS, CITY) STATUS STATUS S# CITY S# CITY • SC (S#, CITY) CITY

43. Décomposition 2 NF -> 3 NFen projections indépendantes • Les MAJ de SC ou de CS peuvent être faites sans violer une FD de S1 • Ce n'est pas le cas de toute autre décomposition dite dès lors en projections dépendantes • bien qu'une telle décomposition peut être aussi sans perte • Comme on verra

44. CITY STATUS • CS (STATUS, CITY) • S1 CS Join SC • SC (S#, CITY) S# CITY

45. CITY STATUS • CS (STATUS, CITY) MAJ de CS CS Join SC • SC (S#, CITY) S# CITY

46. CITY STATUS • CS (STATUS, CITY) MAJ de CS CS Join SC • SC (S#, CITY) S# CITY

47. Décomposition en projections dépendantes S# STATUS • SS (S#, STATUS) • S1 • SC (S#, CITY) SC Join SS S# CITY

48. S# STATUS • SS (S#, STATUS) MAJ de SS CITY -|-> STATUS • SC (S#, CITY) SC Join SS S# CITY

49. Conception de la base S-P • Relation universelle initiale SP1 (S#, SNAME, SCITY, STATUS, P#, PNAME, COLOR, WEIGHT, PCITY, QTY) • Les DFs ? • 1ère décomposition sans perte P (P#, PNAME, COLOR, WEIGHT, PCITY) SP2 (S#, SNAME, SCITY, STATUS, P#, QTY) • On retrouve SP’ augmenté par SNAME • Est-ce que c'est la seule 1ère décomposition sans perte possible ? • 2ème décomposition sans perte, de SP2 seul SP (S#, P#, QTY) S (S#, SNAME, SCITY, STATUS) • 3ème décomposition sans perte, de S seul S (S#, SNAME, SCITY) CS (SCITY, STATUS)

50. Décomposition 2 NF -> 3 NF • Un autre exemple très courant • CV (CP, Ville) Ville • Pers (SS#, Ville, CP) Ville SS# CP SS# • PC (SS#, CP) CP CP