Cours conception de base de données

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 : {AB ; AC}
{C,DE} et {BD}
É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= {AB , BC , AD
, DC}
2)Soit R(F, A, N, P) avec DF= {FA , F,NP}
3) Soit R(A, B, C, D) avec DF= {A,B,CD}
4)Soit R(A, B, C, D} avec DF={AB}
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 = {AC ; A,B E ; D B ; A,B C,D ; DA, 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,RC ; CV}
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 XA 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 ; CV}
Clés: VR, RC
• R est en 3NF mais n’est pas en BCNF (dans CV, 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,RC
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={AB ; AC ; C,DE ; BD}
1 )Calcul des clés candidates: A est la seule clé et R n’est pas en BCNF car C,DE , BD
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 BD:
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 BD
R2 n’est pas BCNF car BCE 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,CE
Dans R22(A,B,C), on a deux dépendances non triviales: AB et AC
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 BD
R21(B,C,E) avec B,CE
et R22(A,B,C) avec AB et AC
• Cette décomposition préserve les DF et SPI
• Décomposez R en commençant avec la DF:
C,DE
166
Décomposition
1NF
2NF
3NF
BCNF
Algorithme de décomposition de Boyce-codd (5)
Deuxième décomposition en commençant par C,DE
• En utilisant l’algorithme de décomposition nous
obtenons 2 relations:
R1(C, D, E) avec la DF C,DE
Et R2(A, B, C, D) avec 3DF: AB , AC , BD
• R1 est en BCNF avec comme clé {C,D}
• Dans R2, A est la seule clé candidate, mais BD 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= { VH, D, R
H, D, R  V
J, VP, A
J,H,P D, R, A
J, H, AD, 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