Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

Théorie de la normalisation relationnelle (dépendance fonctionnelle, forme norm

Théorie de la normalisation relationnelle (dépendance fonctionnelle, forme normale, clé) Stéphane Crozat stph.scenari-community.org/bdd nor1.pdf 29 janvier 2018 Paternité - Partage des Conditions Initiales à l'Identique : http://creativecommons.org /licenses/by-sa/4.0/fr/ Table des matières I - Cours 4 1. Redondance et normalisation .......................................................................................................................... 4 1.1. Exercice : Introduction à la redondance ....................................................................................................................................... 4 1.2. Les problèmes soulevés par une mauvaise modélisation ............................................................................................................... 5 1.3. Principes de la normalisation ....................................................................................................................................................... 6 2. Dépendances fonctionnelles ............................................................................................................................ 6 2.1. Exercice : A1, dans l'eau ! ............................................................................................................................................................ 7 2.2. Dépendance fonctionnelle ............................................................................................................................................................ 7 2.3. Axiomes d'Armstrong ................................................................................................................................................................... 8 2.4. Autres propriétés déduites des axiomes d'Armstrong ..................................................................................................................... 9 2.5. DF élémentaire .......................................................................................................................................................................... 10 2.6. Fermeture transitive des DFE ..................................................................................................................................................... 10 2.7. Couverture minimale des DFE ................................................................................................................................................... 10 2.8. Graphe des DFE ........................................................................................................................................................................ 11 2.9. Définition formelle d'une clé ..................................................................................................................................................... 12 2.10. Exercice : A1, touché ! ............................................................................................................................................................. 13 3. Formes normales ........................................................................................................................................... 13 3.1. Les formes normales .................................................................................................................................................................. 13 3.2. Principe de la décomposition ...................................................................................................................................................... 14 3.3. Première forme normale ............................................................................................................................................................ 14 3.4. Deuxième forme normale .......................................................................................................................................................... 15 3.5. Troisième forme normale ........................................................................................................................................................... 16 3.6. Forme normale de Boyce-Codd ................................................................................................................................................. 17 3.7. Synthèse ..................................................................................................................................................................................... 18 3.8. Exercice : A1, coulé ! ................................................................................................................................................................. 19 4. Bibliographie commentée sur la normalisation ............................................................................................. 19 II - Exercices 20 1. Exercice : De quoi dépend un cours ? ........................................................................................................... 20 2. Exercice : Cuisines et dépendances .............................................................................................................. 20 3. Test : Normalisation ...................................................................................................................................... 22 III - Devoir 25 1. Exercice : Abécédaire ................................................................................................................................... 25 Questions de synthèse 27 Solutions des exercices 29 Glossaire 37 Abréviations 38 Bibliographie 39 Cours 4 La théorie de la normalisation relationnelle est très importante pour la conception de , dans la mesure où BD* elle donne le cadre théorique pour la gestion de la redondance, et dans la mesure où une bonne maîtrise de la redondance est un aspect majeur de cette conception. 1. Redondance et normalisation Objectifs Comprendre la problématique de la redondance. 1.1. Exercice : Introduction à la redondance Soit la relation R suivante, définie en extension : Relation R Question 1 Question 2 Cours I Proposez des clés pour cette relation. Justifiez brièvement. [ ] solution n°1 * [ ] p.29 Cette relation contient-elle des redondances ? Si oui lesquelles ? Justifiez brièvement. [ ] solution n°2 * [ ] p.29 Les problèmes soulevés par une mauvaise modélisation 5 Question 3 1.2. Les problèmes soulevés par une mauvaise modélisation Il y a toujours plusieurs façons de modéliser conceptuellement un problème, certaines sont bonnes et d'autres mauvaises. C'est l'expertise de l'ingénieur en charge de la modélisation, à travers son expérience accumulée et sa capacité à traduire le problème posé, qui permet d'obtenir de bons modèles conceptuels. S'il est difficile de définir un bon modèle conceptuel, on peut en revanche poser qu'un bon modèle logique relationnel est un modèle où la redondance est contrôlée. On peut alors poser qu'un bon modèle conceptuel est un modèle conceptuel qui conduit à un bon modèle relationnel, après application des règles de passage E-A ou UML vers relationnel. Mais on ne sait pas pour autant le critiquer avant ce passage, autrement qu'à travers l’œil d'un expert. A défaut de disposer d'outils systématiques pour obtenir de bons modèles conceptuels, on cherche donc à critiquer les modèles relationnels obtenus. La théorie de la normalisation est une théorie qui permet de critiquer, puis d'optimiser, des modèles relationnels, de façon à en contrôler la redondance. Imaginons que nous souhaitions représenter des personnes, identifiées par leur numéro de sécurité sociale, caractérisées par leur nom, leur prénom, ainsi que les véhicule qu'elles ont acheté, pour un certain prix et à une certaine date, sachant qu'un véhicule est caractérisé par son numéro d'immatriculation, un type, une marque et une puissance. On peut aboutir à la représentation relationnelle suivante : 1 Personne(NSS, Nom, Prénom, Immat, Marque, Type, Puiss, Date, Prix) Posons que cette relation soit remplie par les données suivantes : Relation redondante On peut alors se rendre compte que des redondances sont présentes, si l'on connaît NSS on connaît Nom et Prénom, si on connaît Immat, on connaît Marque, Type et Puiss. Si la relation contient des redondances, proposez une solution contenant exactement la même information, mais sans redondance. [ ] solution n°3 * [ ] p.29 Attention Exemple : Un mauvais modèle relationnel Personne(NSS, Nom, Prénom, Immat, Marque, Type, Puiss, Date, Prix) Principes de la normalisation 6 - - Relation redondante On sait que ces redondances conduiront à des problèmes de contrôle de la cohérence de l'information (erreur dans la saisie d'un numéro de sécurité sociale), de mise à jour (changement de nom à reporter dans de multiples tuples), de perte d'information lors de la suppression de données (disparition des informations concernant un type de véhicule) et de difficulté à représenter certaines informations (un type de véhicule sans propriétaire). On conseillera de lire le chapitre 2 de (pages 42 à 49) qui propose une très SQL2 SQL3, applications à Oracle* bonne démonstration par l'exemple des problèmes posés par une mauvaise modélisation relationnelle. 1.3. Principes de la normalisation La théorie de la normalisation est une théorie destinée à concevoir un bon schéma d’une base de données sans redondance d’information et sans risques d'anomalie de mise à jour. Elle a été introduite dès l'origine dans le modèle relationnel. La théorie de la normalisation est fondée sur deux concepts principaux : Les dépendances fonctionnelles Elles traduisent des contraintes sur les données. Les formes normales Elles définissent des relations bien conçues. La mise en œuvre de la normalisation est fondée sur la décomposition progressive des relations jusqu'à obtenir des relations normalisées. Afin de mener une bonne conception on cherchera à obtenir un modèle relationnel en pour lequel BCNF* l'absence de redondance est simple à démontrer (car les sont simples à vérifier). DF* 2. Dépendances fonctionnelles Objectifs Savoir repérer et exprimer des dépendances fonctionnelles. Définir une clé par les dépendances fonctionnelles. Complément Fondamental Méthode Exercice : A1, dans l'eau ! 7 2.1. Exercice : A1, dans l'eau ! 2.2. Dépendance fonctionnelle Soient R(A1, A2, ... , An) un schéma de relation, X et Y des sous-ensembles de A1, A2, ... , An. On dit que X détermine Y, ou que Y dépend fonctionnellement de X, si et seulement s'il existe une fonction qui à partir de toute valeur de X détermine une valeur unique de Y. Plus formellement on pose que X détermine Y pour une relation R si et seulement si quelle que soit l'instance r de R, alors pour tous tuples t1 et t2 de r on a : Projection (t1,X) = Projection (t2,X) ⇒ Projection (t1,Y) = Projection (t2,Y) Si X détermine Y, on note : X→Y - Considérons le schéma de la relation suivante : r (A, B, C, D, E) Cette relation est définie en extension par les tuples suivants : A B C D E a1 b2 c2 d3 e2 a1 b2 c2 d1 e4 a2 b3 c2 d1 e5 a2 b4 c5 d1 e5 Parmi les dépendances fonctionnelles suivantes, lesquelles s'appliquent à r ?  E→D  D→E  C→A  E→B  E→A  B→C  B→D  B→A [ ] solution n°4 * [ ] p.30 Définition : Dépendance fonctionnelle Syntaxe Axiomes d'Armstrong 8 - - - - - - Soit la relation R suivante : 1 Personne(NSS, Nom, Prénom, Marque, Type, Puiss, Date, Prix) On peut poser les exemples de suivants : DF* NSS→Nom NSS→Prénom Type→Marque Type→Puiss (NSS, Type, Date)→Prix etc. Une DF est définie sur l'intension du schéma et non son extension. Une DF traduit une certaine perception de la réalité. Ainsi la DF (NSS, Type, Date)→Prix signifie que personne n'achète deux voitures du même type à la même date. La seule manière de déterminer une DF est donc de regarder soigneusement ce que signifient les attributs et de trouver les contraintes qui les lient dans le monde réel. Les DF font partie du schéma d'une BD, en conséquence, elles doivent être déclarées par les administrateurs de la BD et être contrôlées par le SGBD. De plus l'identification des DF est la base indispensable pour déterminer dans quelle forme normale est une relation et comment en diminuer la redondance. 2.3. Axiomes d'Armstrong Les obéissent à des propriétés mathématiques particulières, dites axiomes d'Armstrong. DF* Tout groupe d'attributs se détermine lui même et détermine chacun de ses attributs (ou sous groupe de ses attributs). Soient X et Y des attributs : XY→XY et XY→X et XY→Y Si un attribut X détermine un attribut Y, alors tout groupe composé de X enrichi avec d'autres attributs détermine un groupe composé de Y et enrichi des mêmes autres attributs. Soient X, Y et Z des attributs : Exemple Remarque : Comment trouver les DF ? Remarque : Pourquoi trouver les DF ? Introduction Définition : Réflexivité Définition : Augmentation Personne(NSS, Nom, Prénom, Marque, Type, Puiss, Date, Prix) Autres propriétés déduites des axiomes d'Armstrong 9 X→Y ⇒ XZ→YZ Si un attribut X détermine un attribut Y et que cet attribut Y détermine un autre attribut Z, alors X détermine Z. Soient X, Y et Z des uploads/Philosophie/ normalisation1-pdf 3 .pdf