Base de Données 2

1. Base de Données 2 Cours 5 partie 2 Julie Dugdale Julie.dugdale@upmf-grenoble.fr Material/Sources: Daniel Bardou, Julie Dugdale & Vanda Luengo

2. Sommaire • La normalisation • Buts de la normalisation • Formes normales • Première forme normale • Deuxième forme normale • Troisième forme normale • Forme normale de Boyce-Codd • Algorithme de Normalisation • Algorithme de Normalisation – Exemple • Entre Fonctionnel et Opérationnel • Dénormalisation

3. Normalisation - Résumé Définition: Le processus de restructurer le modèle de données logiques pour : • Eliminer les redondances, • Organiser les données efficacement, • Réduire le potentiel d'anomalies pendant les opérations sur les données.

4. Normalisation - Résumé • Chaque nouvelle forme normale marque une étape supplémentaire de progression vers des relations présentant de moins en moins de redondance • Le but.. est d'obtenir une représentation des données présentant un minimum de redondance à l'intérieur de chaque relation et un maximum d'indépendanceentre les différentes relations

5. A, B, C B, C OK Normalisation : 3FN - Résumé Définition : troisième forme normale: • Une relation est en troisième forme normale si : - elle est en deuxième forme normale ; - tout attribut n'appartenant pas à une clé ne dépend pas d'un attribut non clé.- cad toute DF dont la partie droite n’est pas une clé doit avoir une partie gauche qui est une clé :

6. Algorithme de Normalisation

7. Algorithme de Normalisation • L’algorithme de conception de schéma relationnel en 3NF • Théorème: Tout schéma relationnel peut se décomposer en schémas sous 3ième forme normale avec préservation des Dépendances Fonctionnelles • Il existe divers algorithmes de décomposition (nous n’en étudierons qu’un « classique » dû à Bernstein

8. Algorithme de Normalisation • 4 étapes 1. Calculer DF-, une Couverture Irredondante Minimale (une CIM) de DF, en enlevant: • A) les redondances des attributs de partie gauche Est-ce que AB → D a des attributs redondants dans la partie gauche? Est-ce que cela pourrait être remplacé par B → D ou A → D? • B) les dépendances redondantes (transitivité) Si A dépend de B et B dépend de C, alors A dépend aussi de C 2. Regrouper en DFi- les dépendances fonctionnelles de DF- ayant même partie gauche.

9. Algorithme de Normalisation 3. Pour chaque DFi-, construire un schéma relationnel Ri avec l’union des attributs de DFi- 4. Trouver les clés de DF-. Si aucune de ces clés n’est contenue dans au moins un des Ri, alors il faut rajouter un schéma relationnel Rk = {Ki K->K} avec K un clé candidate. Résultat : Toutes les Ri sont 3NF!

10. Algorithme de Normalisation: Exemple • On considère la relation R(A, B, C, D, E) et l’ensemble de dépendances fonctionnelles suivant DF= {A -> BC, CD -> E, B -> D, A -> D, BE -> D} • D'abord mettre DF sous forme canonique: (partie droite atomique)… DF = {A -> B, A -> C, CD -> E, B -> D, A -> D, BE -> D}

11. A B C D E Algorithme de Normalisation: Exemple • Souvent utile de dessiner un graphe des dépendances fonctionnelles de DF: DF = {A -> B, A -> C, CD -> E, B -> D, A -> D, BE -> D}

12. Algorithme de Normalisation: Exemple 1. Calculer un couverture irredondante DF- (une CIM) de DF, en enlevant: • A) les redondances des attributs de partie gauche • B) les dépendances redondantes (transitivité) DF = {A -> B, A -> C, CD -> E, B -> D, A -> D, BE -> D} • A) Elimine BE -> D (car on a déjà B->D) DF = {A -> B, A -> C, CD -> E, B -> D, A -> D, BE -> D} B) Elimine A -> D (car on a déjà A->B et B->D) Donc, DF- = {A -> B, A -> C, CD -> E, B -> D}

13. A B C D E Algorithme de Normalisation: Exemple • Utile: graphe des dépendances fonctionnelles de DF: • A) Elimine BE -> D (car on a déjà B->D) • B) Elimine A -> D (car A->B et B->D) Donc, DF- = {A -> B, A -> C, CD -> E, B -> D}

14. Algorithme de Normalisation: Exemple 2. Regrouper les dépendances fonctionnelles de DF- ayant même partie gauche, en DFi- DF- = {A -> B, A -> C, CD -> E, B -> D} DF1- = {A -> B, A -> C} DF2- = {CD -> E} DF3- = {B -> D}

15. Algorithme de Normalisation: Exemple 3. Pour chaque DFi-, construire un schéma relationnel Ri avec l’union des attributs de DFi- DF1- = {A -> B, A -> C} DF2- = {CD -> E} DF3- = {B -> D} • R1 = {A, B, C} • R2 = {C, D, E} • R3 = {B, D}

16. Algorithme de Normalisation: Exemple 4. Trouver les clés de DF-. Si aucune de ces clés n’est contenue dans au moins un des Ri, alors il faut rajouter un schéma relationnel Rk = {Ki K->K} avec K un clé candidate. Clés = seule candidate = A A appartient à R1 => OK R1 = {A, B, C} R2 = {C, D, E} R3 = {B, D} Résultat: Toutes les Ri sont 3NF!

17. Processus global de conception de base de données • On a supposé que le schéma R est donné • R pourrait avoir été généré pendant la conversion du diagramme E-A en un ensemble de tableaux. • R pourrait avoir été une seule relation contenant tous les attributs ayant un intérêt. • La normalisation découpe R en des relations plus petites. • R pourrait avoir été le résultat d'une conception ad hoc de relations, qu'on teste/convertit alors vers une forme normale.

18. Modèle ER et normalisation • Quand un diagramme E-A est conçu avec soin, identifiant toutes les entités correctement, les tableaux générés à partir du diagramme E-A ne devraient pas nécessiter de plus de normalisation. • Cependant, dans une conception réelle (imparfaite), il peut y avoir des dépendances fonctionnelles d'attributs non-clé d'une entité vers d'autres attributs de l'entité • Exemple : une entité employé avec les attributs numéro_département et adresse_département, et une dépendance fonctionnelle numéro_département  adresse_département • Une bonne conception aurait fait du département une entité

19. Entre Fonctionnel et Opérationnel • Quelques conseils  • Il ne faut jamais oublier qu’il y aura des utilisateurs de la base. Il faut par conséquent être fonctionnel et opérationnel. • Fonctionnel : une base avec beaucoup de tables est difficile à gérer. Il faut optimiser les tables dans leur structure (recherche de clefs étrangères). • Opérationnel : pour les traitements, les relations de type CIF (contrainte d'intégrité référentielle) sont à privilégier.

20. Dénormalisation

21. Dénormalisation • On appelle dénormalisation l'introduction volontaire de redondance répondant principalement à des conditions d'efficacité. • Exemple; Soit le schéma relationnel suivant: Article(NumArt, desigArt, prixArt) Commande(NumCom,DateCom, NumCli) Ligne_Commande(NumCom,NumArt,Qtite) Client(NumCli,NomCli,AdrCli)

22. Dénormalisation • Supposons que le marketing (pour l’émission de mailing) soumet très fréquemment à la base des requêtes de la forme: • Quels sont les adresses des clients ayant passé commande de tel(s) article(s) -> On doit à chaque fois vérifier dans 4 tables pour donner la réponse * Article(NumeroArticle2, designationArticle1, prixArticle) * Commande(NumeroCommande5,DateCommande, NumeroClient6) * Ligne_Commande(NumeroCommande4,NumeroArticle3,Qtite) * Client(NumeroClient7,NomClient,AdresseClient8)

23. Dénormalisation Si on met l'attribut designationArt dans Commande on a besoin de deux tables ..Redondance mais efficacité • Article(NumeroArticle, designationArticle, prixArticle) • * Commande(NumeroCommande,DateCommande, NumeroClient2, • designationArticle1) • Ligne_Commande(NumeroCommande,NumeroArticle,Qtite) • * Client(NumeroClient3,NomClient,AdresseClient4)