Formes Normales

1. Formes Normales Chapitre 19

2. Objectifs • Illustrer les redondances dans le stockage de l’information • Introduire les dépendances fonctionnellescomme outil pour identifier les problèmes de redondance et y apporter des solutions • Introduire la décomposition en formes normales (normalisation) comme solution aux problèmes de redondance de l’information • Introduire la normalisation comme une des étapes du design des base de données

3. Les problèmes de la Redondance • La redondanceest à la base de beaucoup de problèmes associées avec les schémas relationnels: • Stockage redondant, anomalies d’insertion, d’effacement et de changement • Les contraintes d’intégrité, en particulier les dépendances fonctionnelles, peuvent être utilisées pour identifier les problèmes et suggérer des solutions. • Solution : décomposition (remplacement d’une relation par des relations plus petites). • La décomposition devrait être utilisée judicieusement: • Y-a-t-il une raison valable pour décomposer? • Quels problèmes sont causés par la décomposition?

4. Dépendances Fonctionnelles (DFs) • Notation: • R : schéma relationnel; r: instance de R • X, Y: sous ensemble d’attributs de R • Il existe une dépendance fonctionnelle entre X et Y (X  Y)sur une relation R si, pour chaque instance permise r de R, le fait suivant est valide: • t1 r, t2 r, (t1) = (t2) implique (t1) = (t2) • i.e., étant donne deux tuples dans r, si les valeurs X sont les mêmes, alors les valeurs Y doivent aussi être les mêmes. • K est une candidate clé pour une relation R si K  R • Cependant K  R ne requiert pas que K soit minimal! • Lectures possible pour X  Y : • X détermine Y • Y dépend fonctionnellement de X

5. Exemple de Dépendance Fonctionnelle • Considérez la relation suivante: • Hourly_Emps (ssn, name, lot, rating, hrly_wages, hrs_worked) • Notation:Cette relation sera notée en donnant la liste de ses attributs: SNLRWH (En réalité, il s’agit de l’ensemble d’attributs {S,N,L,R,W,H}). • Parfois, nous nous référerons à tous les attributs d’une relation en utilisant le nom de la relation (p.ex. Hourly_Emps au lieu de SNLRWH) • Quelques DFs sur Hourly_Emps: • ssn est la clé: S SNLRWH • rating détermine hrly_wages: R W

6. Wages Exemple (Suite) Hourly_Emps2 • Problèmes causés par R  W: • Anomalie de modification: Peut-on changerW juste dans le 1er tuple de SNLRWH? • Anomalie d’insertion: Toute insertion d’employé dont on ne connait pas le salaire horaire est problématique. • Anomalie d’effacement: Tout effacement de tous les employés ayant un niveau 5 entraine la perte de l’info sur le salaire associé avec le niveau 5. • Solution: introduire 2 tables plus petites

7. Raisonnement avec les DFs • Etant données des DFs, on peut inférer d’autres DFs: • ssn did, did lot implique ssn lot • Une DF f est impliquée parun ensemble de DFs F si f est valide chaque fois que toutes les DFs dans F sont valides. • = fermeture (“closure”) de F: ensemble de toutes les DFs impliquées par F. • Axiomes d’Armstrong (X, Y, Z sont des ensembles d’attributs): • Réflexivité: X Y  Y X • Augmentation: X Y  XZ YZ pour tout Z • Transitivité: If X Y et Y Z  X Z • Ces axiomes sont des règles d’inférence correctes et complètes.

8. Raisonnement avec DFs (Suite) • Règles additionnelles (impliquées par les axiomes): • Union: X Y et X Z  X YZ • Décomposition: X YZ  X Y et X Z • Exemple: Contracts(cid,sid,jid,did,pid,qty,value): • C est la clé: C CSJDPQV • Un projet achète chaque pièce en usant d’un seul contrat: JP C • Un département achète tout au plus 1 pièce d’un fournisseur: SD P • JP C, C CSJDPQV  JP CSJDPQV (Trans.) • SD P  SDJ JP (Augm.) • SDJ JP, JP CSJDPQV  SDJ CSJDPQV (Trans.)

9. Raisonnement avec les DFs (Suite) • Calculer la fermeture d’un ensemble DFs peut couter cher. (La taille de la fermeture est exponentielle) • Si par contre, nous voulons simplement vérifier si une DF donnée X Y est dans la fermeture d’un ensemble de DFs F, nous pouvons le faire de manière efficiente comme suit: • Calculer la fermeture des attributs de X (dénotée par ) par rapport à F: • Ensemble de tous les attributs Z tels que X Z est dans • Il existe un algorithme linéaire pour faire ce calcul (Voir page 614) • Vérifier si Y est dans • F = {S E, P NL, SP H } impliquent SP EP? • i.e, SP EP est-elle dans la fermeture de ? Ce qui veut dire: EP est-elle dans la fermeture de SP ?

10. Formes Normales • Lorsque nous avons obtenu un schéma relationnel, l’on doit décider si ce schéma est un bon design ou pas. Si ce n’est pas un bon design, une décomposition en des schémas plus petits s’impose. • Une telle décomposition permet d’évacuer des anomalies éventuelles et obtenir un bon design. • Certaines formes normales (BCNF, 3NF etc.) sont connues comme éliminant ou diminuant certaines anomalies spécifiques. • Ainsi donc, l’on décomposera le schéma relationnel en une forme normale selon le type d’anomalie que l’on veut éviter.

11. Forme Normale de Boyce-Codd (BCNF) • Une relation R avec les DFs F est en BCNF si pour tous les DFs X A dans • A X (DF trivial), ou • X contient une clé pour R. • En d’autres termes, R est en BCNF si les seules DFs non-triviales qui sont valides sur R sont les contraintes de clé primaire. • Supposez que X  A soit valide sur XYA • Si l’instance ci-contre est en BCNF, les 2 tuples doivent être identiques (puisque X est la clé). Ainsi, y1 = y2 et le deuxième tuple aura a comme valeur de A.

12. Troisième Forme Normale (3NF) • Une relation R avec les DFs F est en 3NF si pour tous les DFs X A dans • A X (DF trivial), ou • X contient une clé pour R, ou • A fait partie d’une clé. • La contrainte de minimalité de la clé est cruciale dans la 3ème condition ci-dessus • Si R est en BCNF, elle est aussi en 3NF.

13. Troisième Forme Normale (Suite) • Si 3NF est violée par X A, cela peut être due à l’une des raisons suivantes: • X est un sous-ensemble d’une clé K (Dépendance partielle). • Nous devrons stocker des valeurs (X, A) redondantes . • Ex.: Reserves SBDC, S  C. • X n’est pas un sous-ensemble propre d’une clé (Dépendance transitive). • Il y a une chaine de DFs K X A . • Nous ne pouvons pas associer une valeur de X avec une valeur de K sans en même temps associer une valeur de A avec X. • Ex.: Hourly_Emps SNLRWH, S  SNLRWH , R  W. • Une relation en 3NF peut toujours avoir des problèmes. • P.ex., Reserves SBDC, S C, C S est en 3NF (car BDC est aussi une clé pour Reserves), mais la même paire (S,C) est quand même stockée pour toute réservation faite par le navigateur S. • Ainsi donc 3NF est un compromis par rapport a BCNF.

14. Décomposition d’un Schéma Relationnel • Supposez que la relation R contient les attributs A1 ... An. Unedécompositionde R consiste en un remplacement de R par 2 ou plusieurs relations telles que: • Chaque nouvelle relation contient un sous-ensemble des attributs de R (et ne contient aucun attributs n’apparaissant pas dans R). • Chaque attribut de R apparait comme un attribut de l’une des nouvelles relations. • Intuitivement, décomposer R signifie stocker des instances des relations produites par la décomposition en lieu et place de l’instance de R. • Exemple: SNLRWH ===> SNLRH et RW.

15. Exemple de Décomposition • N’utiliser les décompositions qu’en cas de nécessité. • SNLRWH avec les DFs S SNLRWH et R W • La 2ème DF cause une violation de la 3NF; les valeurs de W sont associées avec R de manière répétée. Solution: créer une nouvelle relation RW pour stocker ces associations et enlever W du schéma principal: • i.e., décomposer SNLRWH en SNLRH et RW • L’information originale à stocker consiste en des tuples de SNLRWH. • Si nous ne stockons maintenant que des projections des tuples de SNLRWH sur SNLRH et RW, quelles nouveaux problèmes apparaissent ?

16. Problèmes Causés par les Décompositions • Trois problèmes potentiels sont à considérer: • (1) Certaines requêtes deviennent trop chères. • P.ex.: Combien d’argent le navigateur Joe a-t-il gagné? (salary = W*H) • (2) Etant donné des instances des relations décomposées, nous pourrions ne pas être en mesure de reconstruire les instances correspondantes originales de la relation! • L’exemple SNLRWH n’a pas ce problème. • (3) Vérifier certaines DFs pourrait requérir de faire un join des instances des relations décomposées. • L’exemple SNLRWH n’a pas ce problème. • Compromis:Considérer ces problèmes en rapport avec la redondance.

17. Décomposition à Join sans Perte • Une décomposition de R en X et Y est dite à join sans perte (“lossless-join “) par rapport à un ensemble F de DFs si pour toute instance r qui satisfait F: • (r) (r) = r • Il est toujours vrai que r (r) (r) • En général, l’autre direction n’est pas toujours satisfaite! Si elle l’est, alors la décomposition est dite à join sans perte. • Il est essentiel que toutes les décompositions utilisées pour traiter la redondance aient cette propriété! (L’on évite ainsi le problème (2) au transparent précédent.)

18. Décomposition à Join sans Perte (Suite) • La décomposition de R en X et Y est à join sans perte par rapport à F ssila fermeture de F contient: • X Y X, ou • X Y Y • I.e. l’intersection contient une clé de l’une des 2 relations X et Y. • Ex.: SNLRWH; R  W ===> SNLRH; RW; R  W • Si U V est valide sur R et l’intersection de U et V est vide, la décomposition de R en UV et R - V est à join sans perte.

19. Décomposition Préservant les Dépendances • Ex.: CSJDPQV, C est une clé, JP C et SD P. • Décomposition en BCNF: CSJDQV et SDP • Problème: JP C requiert un join! • Décomposition préservant les dépendances (Intuition): • Si R est décomposée en X, Y et Z, et nous devons nous assurer que les DFs qui sont valident sur X, Y et Z séparément fassent en sorte que toutes les DFs qui étaient valides sur R le soient toujours. (L’on évite ainsi le Problème (3).) • Projection des DFs de F sur X: Si R est décomposée en X, ..., la projection de F sur X (dénotée FX ) est l’ensemble des DFs U V dans F+telles que tous les attributs deU et V sont dans X.

20. Décomposition Préservant les Dépendances (Suite) • La décomposition de R en X et Y préserve les dépendances si (FX union FY ) + = F + • i.e., si nous ne considérons que les dépendances dans la fermeture de F + qui peuvent être vérifiées dans X sans considérer Y et dans Y sans considérer X, ces dernières implique toutes les dépendances dans F +. • Ci-haut, il est important de considérer F +, pas F: • ABC, A B, B C, C A, décomposée en AB et BC. • Préserve-t-on les dépendances ici? C A est-elle préservée ????? • La préservation de la dépendance n’implique pas la propriété de join sans perte: Voir exemple ci-haut. • Et vice-versa!

21. Normalisation: Décomposition en BCNF • Considérer la relation R avec les DFs F. Si X Y viole BCNF, décomposer R en R - Y et XY. • Répéter l’application de cette idée jusqu’a l’obtention d’une collection de relations qui sont en BCNF; • Garantie de décomposition à join sans perte et de terminaison. • Ex.: CSJDPQV, cle C, JP C, SD P, J S • Pour traiter SD P, décomposer en SDP, CSJDQV. • Pour traiter J S, décomposer CSJDQV en JS et CJDQV • En général, plusieurs dépendances peuvent causer des violations de BCNF. L’ordre de traitement conduit à des résultats différents.

22. BCNF et Préservation des Dépendances • En général, une décomposition en BCNF qui préserve les dépendances n’est pas toujours possible. • Ex.: SBD, SB D, D B • SBD n’est pas en BCNF car D n’est pas une clé; Une décomposition éventuelle ne préserverait pas SB  D. • De même, la décomposition de CSJDQV en SDP, JS et CJDQV ne préserve pas les dépendances (Avec les DFs JP C, SD P and J S). Cependant, elle est à join sans perte. • Dans ce cas, ajouter JPC à la collection des relations nous donnera une décomposition qui préserve la dépendance.

23. Normalisation: Décomposition en 3NF • En principe, l’algorithme pour la décomposition en BCNF vu plus haut pourrait être utilisé pour obtenir une décomposition en 3NF garantissant la propriété de join sans perte (On stoppera souvent plutôt de toute évidence). • Cette approche ne garantie cependant pas la préservation de la dépendance. • Solution: Utiliser la couverture minimale de F au lieu de F.

24. Couverture Minimale d’un Ensemble de DFs • G est uneCouverture Minimalede l’ensemble F de DFs ssi • Fermeture de F = Fermeture de G. • Toute DF de G est de la forme XA (A est un attribut). • Tout effacement d’une DF de G change la fermeture de G. • Intuitivement, chaque DF dans G est nécessaire et est ``aussi petit que possible’’ pour obtenir la même fermeture que F. • Ex.: A B, ABCD E, EF GH, ACDF EG a la couverture minimal suivante: • A B, ACD E, EF G et EF H • Couverture minimale join sans perte et préservation des dépendances.

25. Algorithme de transformation en 3NF • Algorithme: • Input: une relation R, un ensemble F de DFs • Transformer F en uneCouverture MinimaleG en utilisant l’algorithme en page 626 du livre R&G. • Utiliser l’algorithme donné au transparent 21 avec G et R comme input pour obtenir une décomposition à join sans perte de R en une collection de relations R1, …, Rn qui sont toutes en 3NF. • Pour chaque FD XA qui n’est pas préservée, créer une relation XA et l‘ajouter a la collection des relations R1, …, Rn.

26. Exemple de transformation en 3NF • Relation: CSJDPQV; DFs: JPC, SDP, JS • (0) L’ensemble des DFs est déjà une couverture minimale. • (1) Puisque SDP viole 3NF, décomposer CSJDPQV en CSJDQV et SDP en utilisant SDP. • (2) Puisque CSJDQV n’est pas en 3NF et que cela est due à JS, décomposer CSJDQV en CJDQV et JS en utilisant JS. • (3) Remplacer CSJDPQV par SDP, JS et CJDQV qui sont toutes en 3NF et sont toutes à join sans perte. • (4) SDP, JS et CJDQV ne préservent cependant pas la DF JPC. On ajoute JPC a notre collection obtenue a l’étape (3) ci-dessus. • (5) Le résultat final est: SDP, JS, CJDQV, JPC.

27. Résumé • Une relation qui est BCNF est exempte de toute redondance détectable via les DFs. D’où, il est désirable de transformer toutes les relations en BCNF. • Si une relation n’est pas en BCNF, nous pouvons essayer de la décomposer en une collection de relations en BCNF. • On doit s’assurer que toutes les DFs sont préservées. Si une décomposition en BCNF qui est à join sans perte et qui préserve les dépendances n’est pas possible (ou n’est pas souhaitable à cause des requêtes typiques existantes), l’on devrait considérer une décomposition en 3NF. • Les décompositions devraient être faites en ayant les exigences de performance (des requêtes) a l’esprit.