7/23/2019 normalisation (2)
http://slidepdf.com/reader/full/normalisation-2 1/46
Dé endancesfonctionnelles
1
7/23/2019 normalisation (2)
http://slidepdf.com/reader/full/normalisation-2 2/46
DÉPENDANCES
FONCTIONNELLES Un type important de contraintes intervenant au
niveau d’un schéma de relation est la dépendancefonctionnelle
a erm na on es pen ances onc onne esest une partie importante de la conception debase de données selon le modèle relationnel, etdans la normalisation et dé-normalisation de
bases de données.
2
7/23/2019 normalisation (2)
http://slidepdf.com/reader/full/normalisation-2 3/46
NOTION DE DÉPENDANCE FONCTIONNELLE
(DF)
DF
Un attribut A2 dépend fonctionnellement d'unattribut A1 si à unevaleur de A1 correspond au plusune valeur de A2 :
X
X
X
X
X
X
X
X
X
X
X
X
A1
A2
Non
( A
1 =d terminant ou 1 d termine 2)
3
1 2→
numEn
NomEnseignant
codeMatière
matière
…
Un enseignant n’intervient que dans une matière.
Plusieurs enseignants peuvent intervenir dans une même matière.
Les noms d’enseignants et de matières ne sont pas les ‘identifiants’.
On a les DF suivantes :numEn→→→→ NomEnseignant, codeMatière, matière,
codeMatière→→→→
matière
7/23/2019 normalisation (2)
http://slidepdf.com/reader/full/normalisation-2 4/46
NOTION DE DÉPENDANCE
FONCTIONNELLE (DF) Exemple:
A l’ensemble de phrases suivantes:- Une voiture est identifiée par un numérod’immatriculation N_imm
- Une voiture a une couleur
- À une voiture correspond un type- À un type de voiture correspond une puissance
On peut associer l’ensemble de DF suivant:
{N_imm ->Type, N_imm -> Couleur, Type ->Puissance} 4
7/23/2019 normalisation (2)
http://slidepdf.com/reader/full/normalisation-2 5/46
NOTION DE DÉPENDANCE FONCTIONNELLE
(DF)
DF élémentaire (un unique déterminant)
Une DF est élémentaire si ni , nine sont des DF 1 2 3, A A A→ 1 3 A→
2 3 A→
numEn
5
codeMatière
matière
...
a su van e :
numEn, NomEnseignant→→→→codeMatière
n’aurait pas été élémentaire dansl’exemple précédent
En effet numEn suffit pour déduirecodeMatière
Il n’est PAS nécessaire d’avoir numEn ET
enseignant pour déduire codeMatière
7/23/2019 normalisation (2)
http://slidepdf.com/reader/full/normalisation-2 6/46
NOTION DE DÉPENDANCE
FONCTIONNELLE (DF)
X
X
X
X
X
X
X
X
X
X
A1 A2
X
X
X
X
X
A3
1 3 A A→
DF directe
Une DF est directesi n'existe pas de DF
et .1 2
A A→
A A→
N’est pas une DF directe :
numEn→→→→
matiéreparce que numEn→→→→ codeMatière et codeMatière
→→→→matière
X X
6
7/23/2019 normalisation (2)
http://slidepdf.com/reader/full/normalisation-2 7/46
Contraintes
référentielles ou
contraintesd’inclusion7
7/23/2019 normalisation (2)
http://slidepdf.com/reader/full/normalisation-2 8/46
INTÉGRITÉ RÉFÉRENTIELLE
C’est une contrainte concernant deux relations
(tables)
Utilisée pour spécifier une relation entre lesup es ans eux re a ons.
8
7/23/2019 normalisation (2)
http://slidepdf.com/reader/full/normalisation-2 9/46
CONTRAINTES RÉFÉRENTIELLES OU
CONTRAINTES D’INCLUSION
Souvent nous voudrions assurer qu'une valeur
apparaissant dans une relation pour un ensembledonné d'attributs apparaisse aussi pour un autreensemble d'attributs dans une autre relation. ' .
L'intégrité référentielle est habituellementimposée par la combinaison d'une clé primaire ou
clé candidate, et d'une clé étrangère
9
7/23/2019 normalisation (2)
http://slidepdf.com/reader/full/normalisation-2 10/46
CONTRAINTES RÉFÉRENTIELLES OU
CONTRAINTES D’INCLUSION
Une base de données d'employésstocke le département dans lequel
chaque employé travaille.
Le champ "Dept.No" dans le tableauEm lo é est déclaré comme clé
Name Dept.no Qualified
Julie 27 Yes
Employé
étrangère, et il réfère au champ"Dept.No" dans le tableauDépartement où c’est une cléprimaire.
L'intégrité référentielle serait casséepar l'effacement d'un départementdu tableau Département si desemployés listés dans le tableau
Employé sont listés commetravaillant pour ce département.
.. .. ..
Dept.no Building Name
27 H2 Finance
14 A4 Marketing10
Département
7/23/2019 normalisation (2)
http://slidepdf.com/reader/full/normalisation-2 11/46
CONTRAINTES RÉFÉRENTIELLES OU
CONTRAINTES D’INCLUSION
Le SGBD impose l'intégrité référentielle, Soit en effaçant aussi les rangées de la clé étrangère
pour maintenir l'intégrité,
’ ,
lignes de la relation employé avec Dept.no = 27) Soit en renvoyant une erreur et en n'effectuant pas
l'effacement.
11
7/23/2019 normalisation (2)
http://slidepdf.com/reader/full/normalisation-2 12/46
CONTRAINTES RÉFÉRENTIELLES OU
CONTRAINTES D’INCLUSION
Un exemple d'une basede donnée qui n'a pasimposé d'intégritéréférentielle :il y a une valeur clé
artiste_id artiste_nom
1 Prodigy
2 Red Hot ChilliPeppers
Tableau d’artiste
trang re artiste_i dans le tableau de CDqui référence un artistenon-existant
En d'autres mots, il y aune valeur cléétrangère sans valeur
clé primaire.
a o ea
artiste_id CD_id CD-nom
3 1 Bends
4 2 Blue lines
3 3 OKcomputer
12
Tableau de CD
7/23/2019 normalisation (2)
http://slidepdf.com/reader/full/normalisation-2 13/46
CONTRAINTES RÉFÉRENTIELLES OU
CONTRAINTES D’INCLUSION
Ce qu'il s'est passé :
Il y avait un artiste appelé« Massive attack», avec uneartist_id de "4", qui a étéeffacé du tableau Artiste.
artiste_id artiste_nom
1 Prodigy
2 Red Hot ChilliPeppers
Tableau d’artiste
Cependant, l'album « BlueLines » référait à cet artiste.
Avec l'intégrité référentielleimposée, ceci n'aurait pasété possible.
3 RadioHead
Artiste_id CD_id CD-nom
3 1 Bends
4 2 Blue lines
3 3 OKcomputer13
Tableau de CD
7/23/2019 normalisation (2)
http://slidepdf.com/reader/full/normalisation-2 14/46
Normalisation
14
7/23/2019 normalisation (2)
http://slidepdf.com/reader/full/normalisation-2 15/46
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
7/23/2019 normalisation (2)
http://slidepdf.com/reader/full/normalisation-2 16/46
L A NORMALISATION
La conception d’un MCD représente la vision de
la réalité de l’analyste. Le formalisme obtenu,établi avec une méthode, ne garantit pas justesseet optimisation.
La justesse dépend de la compétence et del’expérience de l’analyste.
L’optimisation est obtenue par les mécanismes de
la normalisation.
7/23/2019 normalisation (2)
http://slidepdf.com/reader/full/normalisation-2 17/46
NORMALISATION
C’est vérifier que la structure devant
recevoir des données est organisée pouréviter: ’ des anomalies de conception (sources
inévitables d’incohérence à court ou long
terme).
7/23/2019 normalisation (2)
http://slidepdf.com/reader/full/normalisation-2 18/46
NORMALISATION
Définition:
Le processus de restructurer le modèle de donnéeslogiques pour : Eliminer les redondances,
, Réduire le potentiel d'anomalies pendant les
opérations sur les données.
7/23/2019 normalisation (2)
http://slidepdf.com/reader/full/normalisation-2 19/46
L A NORMALISATION Les besoins de normalisation :
3 problèmes de cohérence lors de mises à jours de laBD:
anomalie d'insertion (si on rajoute une commande il faut quel'article existe)
1,n 1,n
ARTICLErefartdesignartdescriartmarqueart
FournisseurFnonomadresse
Commandesqute
anomalie de suppression (si on enlève un fournisseur il fautenlever toutes les commandes de ce fournisseur)
anomalie de modification (si on modifie le numéro d'unarticle il faut modifier toutes les commandes avec ce numéro
d'article Fournissuer
Fno
Nom
adresse
Article
Refart
Designart
Descriart
marqueart
Commandes
Refart
Fno
qute
7/23/2019 normalisation (2)
http://slidepdf.com/reader/full/normalisation-2 20/46
L A NORMALISATION
La théorie de la normalisation repose sur l'analyse
de dépendances entre attributs qui sont à l'originede phénomènes de redondance. La normalisation consiste en des méthodes de
.
Dans certains situations, pour des raisons
d'efficacité, on dénormalise.
7/23/2019 normalisation (2)
http://slidepdf.com/reader/full/normalisation-2 21/46
NORMALISATION
Les classifications formelles utilisées pour décrire
le niveau de normalisation d'une base de donnéesrelationnelle sont appelées les formes normales(FN)
Il existe huit formes normales. Les quatrepremières sont les plus pratiques et sont àconnaître.
7/23/2019 normalisation (2)
http://slidepdf.com/reader/full/normalisation-2 22/46
NORMALISATION
Chaque nouvelle forme normale marque uneétape supplémentaire de progression vers desrelations présentant de moins en moins deredondance
Le but..est d'obtenir une représentation des donnéesprésentant un minimum de redondance àl'intérieur de chaque relation et un maximum
d'indépendance entre les différentes relations
7/23/2019 normalisation (2)
http://slidepdf.com/reader/full/normalisation-2 23/46
FORMES NORMALES
Première forme normale
Deuxième forme normaleTroisième forme normale
orme norma e e oyce- oQuatrième forme normale
Cinquième forme normale
Forme normale de domaine/clé
Sixième forme normale
7/23/2019 normalisation (2)
http://slidepdf.com/reader/full/normalisation-2 24/46
• Edgar Codd est à l’origine de la définition des
formes normales …1FN, 2FN, 3FN, BCFN.
• Les quatrième et cinquième formes normales
représentation des relations n-n et 1-n entre lesattributs.
• La sixième forme normale (6FN) inclut desconsidérations pertinentes pour les bases dedonnées temporelles.
7/23/2019 normalisation (2)
http://slidepdf.com/reader/full/normalisation-2 25/46
LA NORMALISATION
La théorie de la normalisation est baséesur les "dépendances fonctionnelles"(DF).
C.A.D. Dépendance entre lesinformationsPar exemple, le salaire dépend de la
qualification
7/23/2019 normalisation (2)
http://slidepdf.com/reader/full/normalisation-2 26/46
Les dépendances fonctionnelles traduisent descontraintes sur les données Par exemple, on décide que deux individus différents
peuvent avoir même nom et prénom mais jamais lemême numéro de sécurité sociale.
Ces contraintes sont représentatives d'une perceptionde la réalité et imposent des limites à la base
7/23/2019 normalisation (2)
http://slidepdf.com/reader/full/normalisation-2 27/46
DÉPENDANCE FONCTIONNELLERappel: Définition : dépendance fonctionnelle
On dit qu'un attribut B dépend fonctionnellement d'unattribut A si, étant donné une valeur de A , il luicorrespond une unique valeur de B.
o a on : -->
Exemple :La dépendance fonctionnelle SS NOM signifie qu'à unnuméro est associé un nom seulement.
Remarquons qu'une dépendance fonctionnelle n'est généralement pas symétrique, c'est-à-dire que SS NOMn'interdit pas que deux personnes distinctes (correspondant
à deux SS différents) portent le même nom.
7/23/2019 normalisation (2)
http://slidepdf.com/reader/full/normalisation-2 28/46
NORMALISATION : 1FN1FN (rubrique élémentaire) :
Un MLD est dit en première forme normale, si toutes les entités
sont composées d’attributs élémentaires ou atomiques (horsclefs concaténées).
IP Role
Conséquences :
un attribut représente une donnée élémentaire dumonde réel ; Un attribut ne peut désigner, ni une donnée composée
d'entités de natures différentes, ni une liste de données
de même nature.
. . . c en
192.168.0.2 serveur
192.168.0.4 client et serveur
7/23/2019 normalisation (2)
http://slidepdf.com/reader/full/normalisation-2 29/46
NORMALISATION : 1FNExemples :
l’entité ARTICLE peut être considérée comme
n’étant pas en 1FN à cause de l’attribut descriart quicontient une description (avec plusieurs composants)de l’article.
D’après l’analyse de l’existant, il ne s’agit pas d’unattribut ayant à subir des traitements, donc, il a été décidé de la maintenir en un seul attribut.
Fournissuer
Fno
Nom
adresse
Article
Refart
Designart
Descriart
marqueart
Commandes
Refart
Fno
qute
7/23/2019 normalisation (2)
http://slidepdf.com/reader/full/normalisation-2 30/46
NORMALISATION : 1FN Si on veut avoir les notes d'un étudiant :
N o m N o t e s
E in ste in 8 ,1 2 .5
N o m N o t e
1
N o t e
2
N o t e
3
E ins te in 8 12 .5 N U LL
F re ud 2 .5 0 18
…
N’est pas en 1FN. Pour y remédier, on peut soit créer unattribut par note si le nombre maximal de notes est connuet si le nombre moyen de notes par personne est proche dece maximum,
Soit fabrique autant de tuples que de couples (nom, note)
F re ud 2 .5 , 0 , 1 8
…
Nom Note
Einstein 8Eintein 12.5
Freud 2.5
Freud 0
Freud 18
…
7/23/2019 normalisation (2)
http://slidepdf.com/reader/full/normalisation-2 31/46
NORMALISATION
: 1FN (a) A une relation ‘emboitée’.
(b) En conséquence, ce n'estpas en 1FN.
(c) Pour normaliser la relation, on enlève les attributsemboités, et on les met dans une nouvelle relation
(EMP_PROJ2) avec la clé primaire.
7/23/2019 normalisation (2)
http://slidepdf.com/reader/full/normalisation-2 32/46
NORMALISATION : 2FN2FN (dépendance fonctionnelle
élémentaire) :
Un MLD est dit en deuxième formenormale, si:- il remplit les conditions de la 1FN et si,
- tout attribut n'appartenant pas à une clé nedépend pas d'une partie de cette clé .
A B CA,B -> C
B->C
7/23/2019 normalisation (2)
http://slidepdf.com/reader/full/normalisation-2 33/46
NORMALISATION : 2FN La 2FN est basée sur le concept de dépendance
fonctionnelle totale Définition de la dépendance fonctionnelle totale x y:
si la suppression d'un attribut A de x signifie que ladépendance n'existe plus
7/23/2019 normalisation (2)
http://slidepdf.com/reader/full/normalisation-2 34/46
NORMALISATION : 2FN Exemple:
Considérons une table « Membres du Departement"
dont les attributs sont :ID departement,ID employe,
et supposons qu’un employé travaille dans un ouplusieurs départements.La combinaison de ID departement et de ID employe
identifie de manière unique un enregistrement de la
table.Est-ce que cette table est en 2FN?Comme Date de naissance employe ne dépend que d’un
seul de ces attributs – l’ID employe – la table n’est
pas 2NF.
7/23/2019 normalisation (2)
http://slidepdf.com/reader/full/normalisation-2 35/46
NORMALISATION : 2FN Autre exemple : Considérons la relation PLAGE de schéma suivant :
PLAGE (NOMP, REGION, TYPE, POLLUTION)où la clé est (NOMP, REGION). Supposons que la
pollution est bien dépendante de la plage (identifiéepar , ma s que e ype es , quanà lui, dépendant de la région seule.
Que pouvons nous faire?:La deuxième forme normale nous impose de
distinguer deux relations R1 et R2 de schémasrespectifs : R1 (NOMP, REGION, POLLUTION) ;
R2 (REGION, TYPE).
7/23/2019 normalisation (2)
http://slidepdf.com/reader/full/normalisation-2 36/46
NORMALISATION : 2FN
Etant données les dépendancesfonctionnelles dans (a) nous pouvonsmettre la relation en 2FN
7/23/2019 normalisation (2)
http://slidepdf.com/reader/full/normalisation-2 37/46
NORMALISATION : 3FNDé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
as d'un attribut non clé.
- cad toute DF dont la partie droite n’est pas une clé doitavoir une partie gauche qui est une clé :
A, B, CB, C OK
7/23/2019 normalisation (2)
http://slidepdf.com/reader/full/normalisation-2 38/46
NORMALISATION : 3FN Exemple :Considérons une table "Departements" dont les attributs sont
ID departement,
Nom departement,ID manager, Date embauche manager e supposons que c aque manager peu g rer un ou p us eurs
départements.ID departement est une clé candidate.Bien que Date embauche manager est fonctionnellement dépendantede {ID departement}, elle est aussi fonctionnellement dépendante del’attribut ID manager.
ID departement Date embauche managerID manager Date embauche manager X
Ceci signifie que la table n’est pas 3FN.Departements (ID department, Nom departement, ID manager
Information Manager (ID manager, date embauche manager)
7/23/2019 normalisation (2)
http://slidepdf.com/reader/full/normalisation-2 39/46
NORMALISATION : 3FN Autre exemple: Considérons maintenant la relation PLAGE de
schémaPLAGE (NP, REGION, TYPE, POLLUTION)
où la clé est NP.
Supposons maintenant comme dans l'exempleprécédent que le type est dépendant de la région.La troisième forme normale nous impose de
distinguer deux relations R1 et R2 de schémas
respectifs :R1 (NP, REGION, POLLUTION) ;R2 (REGION, TYPE).
7/23/2019 normalisation (2)
http://slidepdf.com/reader/full/normalisation-2 40/46
NORMALISATION : 3FN Autre Exemple :Enseignant (Nom, Bureau, Batiment, Discipline, telephone)
Avec des contraintes d’intégrité : Un bâtiment héberge desenseignants d'une même discipline; un bureau donné possède unnuméro de d'appel unique.
Avec les DF: Batiment DisciplineBatiment, BureauTelephone
Telephone Bureau, Batiment
Note: la deuxième contrainte définit une relation 1-1, doncune DF reflexive.
7/23/2019 normalisation (2)
http://slidepdf.com/reader/full/normalisation-2 41/46
ENSEIGNANT (NOM, BUREAU, B ATIMENT,
DISCIPLINE, TELEPHONE) La dépendance Batiment->Discipline ne concerne
pas la clé. La relation Enseignant n'est pas 3FN. On sort donc l’attribut Discipline de la relation
Enseignant (dépendant d’un attribut non-clé) et oncréé une nouvelle relation Affectation:
Affectation(Batiment,Discipline)
Les dépendancesBatiment, Bureau->TelephoneTelephone->Bureau, Batiment
ne concernent pas la clé. On créé la nouvelle relation:
Annuaire(Bureau, Batiment, telephone) La relation Enseignant d’origine peut être
renommée Siège, avec les attributs restant:Siege(Nom,Bureau,Batiment)
7/23/2019 normalisation (2)
http://slidepdf.com/reader/full/normalisation-2 42/46
NORMALISATION : BOYCE-CODD
La FNBC était proposée comme une forme plussimple de 3FN, mais elle est finalement plusstricte que la 3FN.
7/23/2019 normalisation (2)
http://slidepdf.com/reader/full/normalisation-2 43/46
NORMALISATION : BCFNDéfinition : Boyce-Codd forme normale:
Une relation est en BCFN si :- elle est en 3rd forme normale ;- aucun attribut ne dépend d'un attribut non clé.
. . .
flèche) un clé candidate ou primaire entière.
A, B, C
7/23/2019 normalisation (2)
http://slidepdf.com/reader/full/normalisation-2 44/46
NORMALISATION : EXEMPLE FNBCConsidérons la relation suivante: Cours (Matiere, Classe,
Professeur)
complétée par les règles de gestion suivantes:un professeur n'enseigne qu'une seule matière,un classe n'a qu'un seul enseignant par matière
desquelles on déduit les DF suivantes:Matière, Classe ProfesseurProfesseurMatière
Cette relation est en 3NF, néanmoins il est impossible d'enregistrer
un professeur sans classe affectée (puisque classe est une partiede la clé primaire de la table Cours), et la disparition d'une classepeut entraîner la disparition de professeur.
Ceci est du au fait qu'une DF n'ait pas comme origine une clé de larelation.
7/23/2019 normalisation (2)
http://slidepdf.com/reader/full/normalisation-2 45/46
NORMALISATION : EXEMPLE FNBCOn pourrait alors décomposer la table Cours en 2 tables:Spécialité (Professeur, Matière)Enseignant (Classe, Professeur)
Mais la première DF serait alors perdue…
N 2IÈME
7/23/2019 normalisation (2)
http://slidepdf.com/reader/full/normalisation-2 46/46
NORMALISATION : 2IÈME EXEMPLE
FNBCConsidérons la relation suivante:
Fournisseur (nom, adresse, produit, prix)
avec les DF suivantes :
DF2: nom, produit prix Alors la clé de la relation Fournisseur est [nom, produit].Cette relation n'est pas en FNBC, car dans la DF1, la
partie gauche "nom" n'est pas une clé entière.
Top Related