Décomposition 1NF 2NF 3NF BCNF Décomposition en 3Nf (Algorithme de Bernstein, 1976) Algorithme – Entrée : Schéma R et un ensemble F de DF – Sortie : Schémas {R1, R2, ... , Rn} avec Ri en 3NF pour tout i Etape 1 – Rechercher une couverture minimale G de F Etape 2 – Partitionner G en groupes ayant la même partie gauche Etape 3 – Fusionner les groupes Gi et Gj possédant des parties gauches Xi et Xj équivalentes Etape 4 – Pour chaque groupe, créer un schéma relationnel Ri dont la clé est la partie gauche des DF et les attributs non clés est la partie droite des DF. Etape 5 – Si aucune des clés candidates ne figure dans une des relations Ri alors il est nécessaire de rajouter une relation dont les attributs constituent une clé candidate. 155 Décomposition 1NF 2NF 3NF BCNF Décomposition en 3Nf • Exemple: Soit R(A,B,C,D,E) et les dépendances A B ; A C; C,D E ; B D. Étape 1 Ces DF forment déjà une couverture minimale, il est impossible d’enlever une de ces dépendances. Étape 2 Il y a trois groupes de dépendances avec la même partie gauche : {AB ; AC} {C,DE} et {BD} Étape 3 Donc l’algorithme donne la décomposition en trois relations R1(A,B,C), R2(C,D,E), R3(B,D). Étape 4 Noter que A est une clé candidate de R et elle est dans R1 donc la dernière étape (5) de l’algorithme ne s’applique pas 156 Décomposition 1NF 2NF 3NF BCNF Décomposition en 3Nf: exercice Trouver des décompositions en 3NF: 1) Soit R(A, B, C, D) avec DF= {AB , BC , AD , DC} 2)Soit R(F, A, N, P) avec DF= {FA , F,NP} 3) Soit R(A, B, C, D) avec DF= {A,B,CD} 4)Soit R(A, B, C, D} avec DF={AB} 157 Décomposition 1NF 2NF 3NF BCNF Décomposition en 3Nf: exercice 5) Soit R= ({A, B, C, D, E}, F) avec F l’ensemble des dépendances fonctionnelles. F = {AC ; A,B E ; D B ; A,B C,D ; DA, E} Trouver des décompositions en 3NF 158 Décomposition 1NF 2NF 3NF BCNF INSUFFISANCE DE LA 3NF • Exemple: Poste (Ville, Rue, Code) et DF={V,RC ; CV} Clés: VR, RC Relation en 3 NF Poste Ville Rue Code Paris St Michel 75005 Paris Champollion 75005 • Redondance entre le code et la ville 159 Décomposition 1NF 2NF 3NF BCNF forme normale de Boyce-codd • La forme normale de Boyce-Codd, (BCNF en anglais ou FNBC en français) examine les dépendances de parties de clé entre elles, et les dépendances d'attributs non-clé vers une partie de clé. • Une relation est sous la Forme Normale de Boyce-Codd Si et seulement si Ses clés candidates sont les uniques sources de DFs • Définition: Un schéma relationnel R(A1,….,An) avec un ensemble F de DF est en forme normale BCNF si pour chaque DF XA dans F +, la partie gauche X est une super clé. • Toute relation en BCNF est en 3NF • Théorème. Toute relation admet une décomposition en BCNF sans perte d’information mais parfois au prix de perte de dépendances fonctionnelles. 160 Décomposition 1NF 2NF 3NF BCNF forme normale de Boyce-codd (2) • Exemple: soit la relation Poste (Ville, Rue, Code) DF = {V,R C ; CV} Clés: VR, RC • R est en 3NF mais n’est pas en BCNF (dans CV, C n’est pas une clé) • Poste Ville Rue Code Paris St Michel 75005 Paris Champollion 75005 161 Décomposition 1NF 2NF 3NF BCNF forme normale de Boyce-codd (3) • La décomposition R1(Ville, Code); R2(Rue, Code) évite la redondance Ville, Code, elle est donc sans perte d’information mais elle ne préserve pas la dépendance V,RC R1 R2 Ville Code Code Rue Paris 75005 75005 St Michel 75005 Champollion 162 Décomposition 1NF 2NF 3NF BCNF Algorithme de décomposition de Boyce-codd Une décomposition SPI de R, qu'on appellera D, est construite de manière itérative : Entrée: un schéma R et un ensemble de dépendances fonctionnelles F Sortie: Schémas {R1, R2, ... , Rn} avec Ri en BCNF pour tout i 1) D est initialisée à R 2) Tant qu’il existe une relation R(U) qui n’est pas en BCNF: a) Chercher une dépendance non triviale X Y dans F+, telle que X ne soit pas une super clé de R b) Décomposer R comme indiqué dans le théorème de Heath c’est-à-dire en R1 (X,Y) et R2 (X,Z) 3) D est réinitialisée avec R1 et R2 et on répète cette procédure tant qu’il existe des relations qui ne sont pas en BCNF. 4) S’il existe des relations Ri (Xi) et Rj (Xj) dans la décomposition telle que Xi⊂Xj alors on supprime Ri . 163 Décomposition 1NF 2NF 3NF BCNF Algorithme de décomposition de Boyce-codd (2) Exemple: Soit R(A,B,C,D,E) et DF={AB ; AC ; C,DE ; BD} 1 )Calcul des clés candidates: A est la seule clé et R n’est pas en BCNF car C,DE , BD 2) Calculons les fermetures transitives: A+={A,B,C,D,E} B+={B,D} (C,D)+= {C,D,E} et (BC)+= {B,C,D,E} 3) Première décomposition: Nous pouvons prendre la dépendance BD: a) L’algorithme de décomposition donne deux relations: R1(B,D) et R2(A,B,C,E) A, R R1 B, C, D, B, D R1 est en BCNF avec une seule DF non triviale BD R2 n’est pas BCNF car BCE viole la condition BCNF E AB C E R2 164 Décomposition 1NF 2NF 3NF BCNF Algorithme de décomposition de Boyce-codd (3) b) Décomposition de la relation R2 en deux relations R21(B,C,E) et R22(A,B,C) qui sont en BCNF R2 R21 B, C, E A, B, C, E AB C R22 Dans R21(B,C,E), on a une dépendance non triviale: B,CE Dans R22(A,B,C), on a deux dépendances non triviales: AB et AC Avec ces dépendances R21(B,C,E) et R22(A,B,C) sont BCNF 165 Décomposition 1NF 2NF 3NF BCNF Algorithme de décomposition de Boyce-codd (4) • • • • R(A,B,C,D,E) est finalement décomposée en trois relations: R1(B,D) avec BD R21(B,C,E) avec B,CE et R22(A,B,C) avec AB et AC • Cette décomposition préserve les DF et SPI • Décomposez R en commençant avec la DF: C,DE 166 Décomposition 1NF 2NF 3NF BCNF Algorithme de décomposition de Boyce-codd (5) Deuxième décomposition en commençant par C,DE • En utilisant l’algorithme de décomposition nous obtenons 2 relations: R1(C, D, E) avec la DF C,DE Et R2(A, B, C, D) avec 3DF: AB , AC , BD • R1 est en BCNF avec comme clé {C,D} • Dans R2, A est la seule clé candidate, mais BD viole BCNF. On décompose: R21(B,D) et R22(A,B,C) 167 Décomposition 1NF 2NF 3NF BCNF décomposition de Boyce-codd Exercice: Proposer une décomposition en BCNF 1) Description des pièces employées dans un atelier de montage. tva Piece(numPiece, prix, tva, libelle, categorie) DF1 : numPiece prix, tva, libelle, categorie et DF2 : categorie 2) Liste des auteurs d'une publication avec leur position dans la liste des auteurs. Auteurs(publication, auteur, position) DF1 : auteur, publication position et DF2 : publication, position auteur 3) Commande(numCommande, numClient, nomClient, date, numProduit, nomProduit) DF1 : numCommande numClient, date,numProduit DF2 : numClient nomClient et DF3 : numProduit nomProduit 168 Décomposition 1NF 2NF 3NF BCNF décomposition de Boyce-codd (2) Soit le schéma relationnel VOL (V, P, A, J, H, D, R) Avec; V: num Vol, A: nom Avion P: nom pilote H: heure du départ du vol J: Jour du départ du vol D: ville de départ et R: ville arrivée. et Les DF= { VH, D, R H, D, R V J, VP, A J,H,P D, R, A J, H, AD, R, P } Proposer une décomposition en BCNF 169 Décomposition 1NF 2NF 3NF BCNF décomposition de Boyce-codd (3) • Supposons la contrainte (peu réaliste) suivante : Un pilote ne travaille qu'un jour par semaine (tous les lundi ou tous les mardi ou ...) qui se traduit par la DF : P J • Considérons maintenant le schéma : VOL( V, P, A, J) avec {P J ; J, V P, A} Proposer une décomposition en BCNF 170 Décomposition 1NF 2NF 3NF BCNF Décomposition en 3ème forme normale et en BCNF • Il est démontré que: – Toute relation a au moins une décomposition en 3ème forme normale qui préserve les dépendances fonctionnelles et qui est sans perte – Toute relation a au moins une décomposition en forme normale de Boyce Codd qui est sans perte mais qui peut ne pas préserver les dépendances fonctionnelles. 171 Décomposition 1NF 2NF 3NF BCNF • La notion de dépendance fonctionnelle nous a conduit à décomposer les relations en 3NF et en forme normale de BOYCE CODD. • Ceci est pourtant insuffisant pour éliminer les redondances et les anomalies de mises à jour. 172 Synthèse sur la normalisation • Permet d'affiner une conception de schéma • Si on applique de façon rigoureuse la méthode d'élaboration d'un Modèle Conceptuel de Données (M.C.D.), le Modèle Relationnel résultant sera automatiquement en 3FN. • Inconvénient majeur : on suppose qu'on possède une couverture minimale des DF (si on a oublié une DF tout est faux) • Peut être remise en cause au niveau physique (dénormalisation) 173 Synthèse • Conception de base de données Niveau conceptuel • Représente le contenu de la base en schémas conceptuels Niveau logique • Résulte de la traduction du schéma conceptuel en un schéma propre à un type de BD Niveau physique • Produire à partir d’un schéma logique une structure de données performante spécifique à un SGBD particulier MCD Schémas relationnels+ théorie de la Normalisation 174 Sources [1] Cécile Capponi et Rémi Eyraud. «Cours Base de données » [2] Cours Claude Sabatier «Conception de base de données» [3] Dominique Nanci, Bernard Espinasse: «Ingénierie des systèmes d’information: Merise Deuxième génération» [4] Jacques le Maître «cours base de données» [5] Jean-Luc Hainaut «Bases de données Concepts, utilisation et développement» Editions DUNOD [6] Jacky Akoka et Isabelle –Wattiau «Conception de bases de données relationnelles En pratique» Vuibert Informatiuqe [7] Christian Fischer, Département génie informatique Université de Technologie de Belfort –Montbéliard [8]Bernstein, Philip A. Synthesizing Third Normal Form Relations from Functional Dependencies. ACM Trans. Database Syst, pp. 277—298, 1976 [9] Cours «Introduction aux bases de données» Michel Cartereau 175