Le mod le relationnel
Mehdi HAJJI
Conception BD II2
Introduction
} Le mod le relationnel a t formalis par CODD en
1970. Quelques exemples de r alisation en sont :
} DB2(IBM), INFORMIX, INGRES, ORACLE.
} Dans ce mod le, les donn es sont stock es dans des
tables, sans pr juger de la fa on dont les informations
sont stock es dans la machine.
} Un ensemble de donn es sera donc mod lis par un
ensemble de tables.
} Le succ s du mod le relationnel aupr s des chercheurs,
concepteurs et utilisateurs est d la puissance et la
simplicit de ses concepts.
Le mod le relationnel
-M. HAJJI-
2
Introduction
} Les objectifs du mod le relationnel :
} proposer des sch mas de donn es faciles utiliser,
} am liorer l'ind pendance logique et physique,
} mettre la disposition des utilisateurs des langages de haut
niveau pouvant ventuellement tre utilis s par des non
informaticiens,
} optimiser les acc s la base de donn es,
} am liorer l'int grit et la confidentialit ,
} fournir une approche m thodologique dans la construction des
sch mas.
Le mod le relationnel
-M. HAJJI-
3
Introduction
} De fa on informelle, on peut d finir le mod le relationnel
de la mani re suivante :
} Les donn es sont organis es sous forme de tables deux
dimensions, encore appel es relations et chaque ligne n-uplet
ou tuple,
} les donn es sont manipul es par des op rateurs de l'alg bre
relationnelle,
} l' tat coh rent de la base est d fini par un ensemble de
contraintes d'int grit .
} Au mod le relationnel est associ e la th orie de la
normalisation des relations qui permet de se d barrasser
des incoh rences au moment de la conception d'une base
de donn es.
Le mod le relationnel
-M. HAJJI-
4
D finitions
Domaine
} ensemble de valeurs caract ris par un nom
Relation
} sous-ensemble du produit cart sien d'une liste de
domaines caract ris par un nom unique
} repr sent e sous forme de table deux dimensions
} colonne = un domaine du produit cart sien
} un m me domaine peut appara tre plusieurs fois
} ensemble de nuplets sans doublon
Le mod le relationnel
-M. HAJJI-
5
D finitions
Attribut
} une colonne dans une relation
} caract ris par un nom et dont les valeurs appartiennent un
domaine
} les valeurs sont atomiques
Degr
} Le degr d'une relation est son nombre d'attributs.
Occurrence
} Une occurrence est un l ment de l'ensemble figur par une
relation.
Cardinalit
} La cardinalit d'une relation est son nombre d'occurrences.
Le mod le relationnel
-M. HAJJI-
6
D finitions
Tuple (ou nuplet)
} Liste de n valeurs (v1, ..., vn) o chaque valeur vi est la
valeur dun attibut Ai de domaine Di
Sch ma dune relation
} Nom de la relation, suivi de la liste des attributs avec
leurs domaines.
Le mod le relationnel
-M. HAJJI-
7
D finitions
Cl candidate
} D finition: Une cl candidate d'une relation est un ensemble
minimal des attributs de la relation dont les valeurs identifient
coup s r une occurrence.
} La valeur d'une cl candidate est donc distincte pour toutes
les occurrences.
} La notion de cl candidate est essentielle dans le mod le
relationnel.
} Toute relation a au moins une cl candidate et peut en avoir
plusieurs. Cela a pour cons quence qu'il ne peut jamais y avoir
deux occurrences identiques au sein d'une relation : ces deux
occurrences repr senteraient en fait le m me objet.
Le mod le relationnel
-M. HAJJI-
8
D finitions
Cl candidate
} Les cl s candidates d'une relation n'ont pas forc ment le
m me nombre d'attributs.
} Une cl candidate peut tre form e d'un attribut
arbitraire, utilis cette seule fin.
} Le contexte du domaine mod lis est essentiel pour
d terminer les cl s candidates d'une relation. Le contenu
de la relation peut tre un indice, mais il est parfois
trompeur.
Le mod le relationnel
-M. HAJJI-
9
D finitions
Cl primaire
} D finition: La cl primaire d'une relation est une de ses
cl s candidates.
} La notion de cl primaire est moins importante que celle
de cl candidate dans le mod le relationnel.
} La cl primaire peut tre choisie arbitrairement mais le
contexte aide souvent d terminer laquelle des cl s
candidates doit tre consid r e comme cl primaire.
} Pour signaler la cl primaire, ses attributs sont
g n ralement soulign s.
Le mod le relationnel
-M. HAJJI-
10
D finitions
Cl trang re
} D finition: Une cl trang re d'une relation est form e
d'un ou plusieurs de ses attributs qui constituent une cl
candidate dans une autre relation.
Le mod le relationnel
-M. HAJJI-
11
Les anomalies de mise jour
Le mod le relationnel
-M. HAJJI-
13
Les anomalies de mise jour
} Redondance : chaque fois quun film (titre) appara t, les
valeurs pour le genre, length et rating apparaissent aussi.
} M lange de la s mantique des attributs : attributs
concernant la vid o avec des attributs dun film.
} Anomalie de mise jour
} Que se passe t-il si la longueur (length) du film 90987 est mise
jour et passe de 105 107
} Incoh rence ou besoin de modification de plusieurs n-uplet!
Le mod le relationnel
-M. HAJJI-
14
Les anomalies de mise jour
} Anomalie dinsertion
} Que se passe t-il si avec linsertion du n-uplet :
{102, 1/1/99, Elisabeth, drama, 110, PG13}
} Incoh rence ou interdiction dinsertion!
} Anomalie de suppression
} Que se passe t-il si avec la suppression de la vid o num ro
123?
} Perte des informations sur le film Annie Hall
Le mod le relationnel
-M. HAJJI-
15
Les anomalies de mise jour
} Exemple
} Soit le sch ma de relation
} FOURNISSEUR (Nom_Fournisseur, Adresse, Produit, Prix).
} Une relation (table) correspondant ce sch ma pourra
ventuellement contenir plusieurs produits pour un m me
fournisseur. Dans ce cas, l'adresse du fournisseur sera
dupliqu e dans chaque n-uplet (redondance).
} Si on souhaite modifier l'adresse d'un fournisseur, il faudra
rechercher et mettre jour tous les n-uplets correspondant
ce fournisseur.
} Si on ins re un nouveau produit pour un fournisseur d j
r f renc , il faudra v rifier que l'adresse est identique.
} Si on veut supprimer un fournisseur, il faudra retrouver et
supprimer tous les nuplets correspondant ce fournisseur
(pour diff rents produits) dans la table.
Le mod le relationnel
-M. HAJJI-
16
Notion de d pendances fonctionnelles
D finition
} Une d pendance fonctionnelle, not e DF, indique que la
valeur d'un ou plusieurs attributs est associ e au plus
une valeur d'un ou plusieurs autres attributs.
} B d pend fonctionnellement de A si, tant donn une
valeur de A, il lui correspond une unique valeur de B (quel
que soit l'extension)
} A et B sont des ensembles d'attributs
} Notation A : B
Le mod le relationnel
-M. HAJJI-
17
Notion de d pendances fonctionnelles
} Exemple
} Anniversaire(num ro : Entier, amie : Cha ne, ville : Cha ne,
cadeau : Cha ne) :
num ro
amie
ville
cadeau
1
2
4
12
14
Publicité
Sylvie Dupont
Sylvie Dupont
Nice
Nice
Corinne Durand
Menton
Juliette Dubois
Nice
Corinne Durand
Menton
Fleurs
Collier
Fleurs
Livre
Livre
} On peut d terminer les DF suivantes pour cette relation :
} num ro Anniversaire amie, ville, cadeau
} amie Anniversaire ville
Le mod le relationnel
-M. HAJJI-
18
Notion de d pendances fonctionnelles
} Exemple
} BUVEURS(nb, nom, pr nom, ville)
} COMMANDES(nc, datec, nv, qt c, nb)
} EXPEDITIONS(nc, dateexp, qt exp)
} NOM VILLE ?
} NB NV ?
} QTEC QTEEXP ?
} NB NOM
} NB PRENOM
} NB VILLE
} NC DATEC
} NC NB
} NC NV
} NC QTEC
} NC, DATEEXP QTEEXP
Le mod le relationnel
-M. HAJJI-
19
Notion de d pendances fonctionnelles
} Soit le sch ma de relation PERSONNE (No_SS, Nom,
Adresse, Age, Profession).
} Les d pendances fonctionnelles qui s'appliquent sur ce
sch ma de relation sont les suivantes :
} No_SS Nom, No_SS Adresse, No_SS Age,
No_SS Profession.
} On pourra aussi crire :
} No_SS Nom Adresse Age Profession.
} L'attribut No_SS d termine tous les attributs du sch ma
de relation. Il s'agit d'une propri t de la cl d'un sch ma
de relation.
Le mod le relationnel
-M. HAJJI-
20
Notion de d pendances fonctionnelles
Types de d pendances fonctionnelles
} Soient X, Y et Z des ensembles d'attributs non vides d'une
relation R avec X R Y.
} Cette DF est alors dite :
} triviale
si et seulement si Y X
} l mentaire
si et seulement si Z tel que Z X on n'a pas Z R Y
} canonique
si et seulement si Y n'a qu'un seul attribut
} directe
si et seulement si X R Y est l mentaire et Z tel que Z ` X
' Z ` Y on n'a pas X R Z ' Z R Y
Le mod le relationnel
-M. HAJJI-
21
Notion de d pendances fonctionnelles
Propri t s de d pendance fonctionnelle
} Soient W, X, Y et Z des ensembles d'attributs non vides
d'une relation R.
} Propri t s (Axiomes dArmstrong):
} R flexivit
(X W) (W R X)
} Augmentation
(W R X) (W, Y R X, Y)
} Transitivit
(W R X ' X R Y) (W R Y)
Le mod le relationnel
-M. HAJJI-
22
Notion de d pendances fonctionnelles
Propri t s de d pendance fonctionnelle
} Propri t s (d duites):
} Union
(W R X ' W R Y) (W R X, Y)
} Pseudo-transitivit
(W R X ' X, Y R Z) (W, Y R Z)
} D composition
(W R X ' Y X) (W R Y)
Le mod le relationnel
-M. HAJJI-
23
Notion de d pendances fonctionnelles
Graphe de d pendances fonctionnelles
} NSuds = Attributs
} Arcs = DF
nc
nb
datec
nv
qtec
nom
pr nom ville
dateexp
qt exp
Le mod le relationnel
-M. HAJJI-
24
Notion de d pendances fonctionnelles
Fermeture transitive
} Fermeture transitive d'un ensemble F de DF est not e F+
} F+ = F U DF
} Par exemple
} NC NB et NB NOM donc NC NOM
} NB NOM donc NB, NV NOM, NV
} essentiellement transitivit et pseudo-transitivit
Le mod le relationnel
-M. HAJJI-
25
Notion de d pendances fonctionnelles
Graphe de fermeture transitive
nc
nb
datec
nv
qtec
nom
pr nom ville
dateexp
qt exp
Le mod le relationnel
-M. HAJJI-
26
Notion de d pendances fonctionnelles
D pendance fonctionnelle l mentaire
} Une d pendance fonctionnelle X A est dite
l mentaire si
} A nest pas inclus dans X
} il nexiste pas X inclus dans X tel que X A
} D pendance fonctionnelle l mentaire = le plus petit
nombre dattributs en d terminant un autre.
} Permet de simplifier la fermeture transitive (sinon on
peut toujours cr er de nouvelles DF par augmentation)
} Exemple :
} NB NOM
} NB, NV NOM non DFE
Le mod le relationnel
-M. HAJJI-
27
Notion de d pendances fonctionnelles
D pendance fonctionnelle l mentaire
} Lensemble des DF forme un graphe, mais sans aucun
int r t car comportant trop darcs.
} Lensemble des DFE est mod lis par un graphe dit
graphe des d pendances fonctionnelles l mentaires .
Le mod le relationnel
-M. HAJJI-
28
Notion de d pendances fonctionnelles
Couverture minimale
} Sous ensemble minimum de DF l mentaires permettant
de g n rer toutes les autres
} Exemple
} (nb nom; nb pr nom; nb ville;
nc datec; nc nb; nc nv; nc qt c;
nc, dateexp qt exp)
} Th or me
} Tout ensemble de DF admet une couverture minimale, en
g n ral non unique
Le mod le relationnel
-M. HAJJI-
29
Notion de d pendances fonctionnelles
Couverture minimale
} Calcul de la couverture minimale
} D composer chaque DF pour avoir un seul attribut droite
} Supprimer les attributs en surnombre gauche
} Supprimer les DF redondantes
} Cela suffit. (On peut inverser les tapes 2 et 3 mais il faut alors
it rer).
Le mod le relationnel
-M. HAJJI-
30
Notion de d pendances fonctionnelles
Couverture minimale
} Soit la relation R(I, J, K, L) et les d pendances
fonctionnelles :
} JK L
} J I
} IK L
} Graphe de d pendance fonctionnelle
Le mod le relationnel
-M. HAJJI-
31
Notion de d pendances fonctionnelles
Couverture minimale
} Rendre canoniques & l mentaires les DFs qui ne le sont
pas
} J I permet de dire que dans la DF IK L lattribut I peut
tre remplac par J et donc cela donne : JK L
} Donc si JK L se d duit il reste les DFs suivantes :
} IK L
} J I
Le mod le relationnel
-M. HAJJI-
32
Notion de d pendances fonctionnelles
Publicité
Couverture minimale
} Repr senter les nouvelles Dfs sous forme d'un graphe
dont les nSuds sont les attributs impliqu s dans les Dfs
et les arcs les Dfs elles-m mes
} Construction de l'ensemble des DFs compos es d'un seul
attribut source de DF
} Lister les DFs non encore int gr es (qui n'apparaissent pas
dans le graphe repr sentant l'ensemble des DFs en 1) : IK L
} Placer les DFs avec comme source un sous-ensemble
d'attributs d j source de DF : aucunes
} Int grer les DFs o l'un des attributs non affect s appara t
comme source :
Le mod le relationnel
-M. HAJJI-
33
Notion de d pendances fonctionnelles
Couverture minimale
J
I
K
.
} Exemple1
L
} L'ensemble F = {A B, A C, B C, C B} admet les deux
couvertures minimales :
} CM1 = {A C, B C, C B} et CM2 = {A B, B C, C B}
} Exemple2
} L'ensemble F = {AB C, B A, A D, D C} admet la
couverture minimale :
} F1 = {B A, A D, D C}
AB C
B A
BB C B C
Le mod le relationnel
-M. HAJJI-
34
Notion de d pendances fonctionnelles
Couverture minimale
} Exemple
} AB C
} C A
} BC D
} ACD B
} D EG
} BE C
} CG BD
} CE AG
} Couverture minimale?
Le mod le relationnel
-M. HAJJI-
35
Les formes normales
} Objectif : d tecter et tudier les d pendances lint rieur des
tables pour en liminer les informations redondantes et les
anomalies qui en r sultent.
} Un attribut dans une table est redondant lorsque ses valeurs
peuvent tre limin es de cette table sans perte dinformation.
} La normalisation est utile:
} pour limiter les redondances de donn es,
} pour limiter les pertes de donn es,
} pour limiter les incoh rences au sein des donn es et
} pour am liorer les performances des traitements.
Le mod le relationnel
-M. HAJJI-
39
Les formes normales
} FOURNISSEUR (NomFournisseur, AdresseFournisseur, Produits,
Prix)
NomFournisseur
AdresseFournisseur
10, Rue des Gras - Clermont
Produit
Chaise
Table
86, Rue de la R publique - Moulins
Bureau
26, Rue des D mes - Vichy
39, Rue des Buttes - Moulins
Lit
Lampe
Table de chevet
Lebras
Dupont
Lajoie
Dupont
Prix
20
35
60
50
18
25
L Il ny a pas de cl primaire : on ne sait pas si les deux Dupont sont
diff rents ou pas (si cest le m me Dupont, il y a une des deux
adresses qui est fausse.
L Ladresse nest pas d compos e. Si on veut par exemple rechercher
tous les fournisseurs qui habitent la m me ville, a ne va pas tre
possible
Le mod le relationnel
-M. HAJJI-
40
Les formes normales
L Une relation (table) correspondant ce sch ma pourra
ventuellement contenir plusieurs produits pour un m me
fournisseur.
Dans ce cas, il faudra faire face un certain nombre de
probl mes :
} l'adresse du fournisseur sera dupliqu e dans chaque n-uplet
(redondance),
} si on souhaite modifier l'adresse d'un fournisseur, il faudra
rechercher et mettre jour tous les n-uplets correspondant ce
fournisseur,
} si on ins re un nouveau produit pour un fournisseur d j r f renc ,
il faudra v rifier que l'adresse est identique,
} si on veut supprimer un fournisseur, il faudra retrouver et supprimer
tous les n-uplets correspondant ce fournisseur (pour diff rents
produits) dans la table.
Le mod le relationnel
-M. HAJJI-
41
Processus de Normalisation
} La normalisation limine les redondances, ce qui
permet :
} une diminution de la taille de la base de donn e sur le disque
} une diminution des risques dincoh rence
} d viter une mise jour multiple des m mes donn es
Le mod le relationnel
-M. HAJJI-
42
Processus de Normalisation
La premi re forme normale
} Une relation est normalis e en premi re forme normale si :
1)
2)
3)
elle poss de une cl identifiant de mani re unique et stable
chaque ligne
chaque attribut est monovalu (ne peut avoir quune seule
valeur par ligne)
aucun attribut nest d composable en plusieurs attributs
significatifs
} La premi re forme normale est not e 1NF (1FN en
fran ais).
} Si besoin est, on d compose les attributs ou la relation
pour respecter la 1NF.
Le mod le relationnel
-M. HAJJI-
43
Processus de Normalisation
La premi re forme normale
} Un employ peut avoir plusieurs enfants et plusieurs dipl mes.
En outre, ces attributs sont d composables : dipl me est
d composable en Nature et Ann e, et Enfants est
d composable en Pr nom et Ann e de Naissance.
Le mod le relationnel
-M. HAJJI-
44
Processus de Normalisation
La premi re forme normale
} Par exemple,
} Pers1(nom, pr nom, rueEtVille, pr nomEnfants) n'est pas en
1NF
} alors que Pers2(nom, pr nom, nombreEnfants) est en 1NF.
} La 1NF ne r sout pas tout car aucune DF n'est prise en
compte.
} Par exemple, Commande(codeClient, codeArticle, client,
article) est en 1NF.
} Il est cependant possible d'y trouver les occurrences 20, 5,
Dupont, Table , 21, 5, Dupont, Table et 22, 5, Durand, Chaise ,
ce qui est incoh rent.
Le mod le relationnel
-M. HAJJI-
45
Processus de Normalisation
La premi re forme normale
} Exercice
type
commandants
num ro
avion
Bernard
100
110
200
221
222
Airbus A320 A1247
Airbus A320
Gilbert
Boeing 747
B1248
Boeing 737
B323
Airbus A330 A100
Boeing 747
Jos phine
Gilbert
Marianne
Boeing 747
B222
Boeing 737
Gilbert
Vol
Avion
} (num ro, constructeur, nom avion, num ro vol)
} (constructeur, nom avion, commandant)
Le mod le relationnel
-M. HAJJI-
46
Processus de Normalisation
La premi re forme normale
} Exercice
} Personne1(num roS cu, nom, pr nom, adresse, pr nomEnfants,
Publicité
geEnfants)
} Personne2(nom, pr nom, natureDipl mes,
lieuExamenDipl mes, dateExamenDipl mes,
pr nomEt geEnfants)
} Personne1(num roS cu, nom, pr nom, num ro, rue,
codePostal, ville, pr nomEnfants, ageEnfant)
} Personne2(nom, pr nom, natureDipl mes,
lieuExamenDipl mes, dateExamenDipl mes, pr nomEnfants,
ageEnfant)
Le mod le relationnel
-M. HAJJI-
47
Les formes normales
La deuxi me forme normale
} Une relation R est en deuxi me forme normale si et seulement si :
1)
elle est en 1FN
et tout attribut non cl est totalement d pendant de toute la cl .
2)
Autrement dit, aucun des attributs ne d pend que dune partie de la cl .
} La 2FN n'est v rifier que pour les relations ayant une cl compos e. Une
relation en 1FN n'ayant qu'un seul attribut cl est toujours en 2FN
} La deuxi me forme normale est not e 2NF (2FN en fran ais).
} Un relation peut tre en 2NF par rapport une de ses cl s candidates et
ne pas l' tre par rapport une autre.
} Pour rechercher une 2NF, il est au pr alable n cessaire de d terminer
toutes les DF et de choisir une cl candidate. Il est recommand de trouver
toutes les cl s candidates afin de ne pas en laisser passer une plus
int ressante qu'une autre.
} Si besoin est, on d compose les attributs ou la relation pour respecter la
2NF.
} Une relation avec une cl candidate choisie r duite un seul attribut est,
par d finition, forc ment en 2NF.
Le mod le relationnel
-M. HAJJI-
48
Les formes normales
La deuxi me forme normale
} Cette relation est en premi re forme normale (existence dune
cl valide et aucun attribut nest d composable)
} MAIS elle nest pas en 2 forme normale car on a
D signationProd ne d pend pas de toute la cl mais
seulement de R f renceProd:
} R f renceProd D signationProd
} pour conna tre lattribut d signationProd, on na pas besoin de
conna tre le num ro de commande.
Le mod le relationnel
-M. HAJJI-
49
Les formes normales
La deuxi me forme normale
} D terminez toutes les DF et cl s candidates des relations
suivantes puis passez les en 2NF. (La d termination des DF
d pend notablement du contexte. En l'absence de celui-ci, il
convient toujours de faire des hypoth ses raisonnables,
justifi es explicitement.)
} Personne1(num roS cu, nom, pr nom, adresse, pr nomEnfants,
geEnfants)
} Personne2(nom, pr nom, natureDipl mes, lieuExamenDipl mes,
dateExamenDipl mes, initialeNom, pr nomEt geEnfants)
} Commande1(codeClient, codeArticle, client, article)
} Commande2(numCommande, numProduit, libell Produit,
quantit Command e)
} Enseignement1(nom tudiant, ge, cours, jourCours) Chaque cours n'a
lieu qu'une fois par semaine.
} Enseignement2(cours, joursCours, nomProfesseur, salaireProfesseur)
Chaque cours n'a qu'un enseignant et n'a lieu qu'une fois par semaine.
Le mod le relationnel
-M. HAJJI-
50
Les formes normales
La deuxi me forme normale
} Personne1(num roS cu, nom, pr nom, adresse,
pr nomEnfants, geEnfants)
} (num roS cu) et (nom,pr nom)
} Personne2(nom, pr nom, natureDipl mes,
lieuExamenDipl mes, dateExamenDipl mes, initialeNom,
pr nomEt geEnfants)
} (nom, pr nom)
} Commande1(codeClient, codeArticle, client, article)
} La table doit tre scind e en plusieurs tables
} (codeClient, codeArticle)
} (codeClient, client)
} (codeArticle, article) av
Le mod le relationnel
-M. HAJJI-
51
Les formes normales
La deuxi me forme normale
} Commande2(numCommande, numProduit, libell Produit,
quantit Command e)
} La table doit tre scind e
} (numProduit, libell Produit) avec numProduit la cl primaire
} (numCommande, numProduit, quantit Command e)
} Enseignement1(nom tudiant, ge, cours, jourCours)
Chaque cours n'a lieu qu'une fois par semaine.
} (nomEtudiant, age, cours, jourCours)
Le mod le relationnel
-M. HAJJI-
52
Les formes normales
La deuxi me forme normale
} Enseignement2(cours, joursCours, nomProfesseur,
salaireProfesseur) Chaque cours n'a qu'un enseignant et
n'a lieu qu'une fois par semaine.
} La table doit tre scind e
} (cours, jourCours, nomProfesseur)
} (nomProfesseur, salaireProfesseur)
Le mod le relationnel
-M. HAJJI-
53
Les formes normales
La troisi me forme normale
} Une relation est en 3 forme normale si et seulement si :
1)
2)
elle est en 2FN
et tout attribut doit d pendre directement de la cl , c'est- -dire
quaucun attribut ne doit d pendre de la cl par transitivit .
Autrement dit, aucun attribut ne doit d pendre dun autre attribut non cl .
} La troisi me forme normale est not e 3NF (3FN en fran ais).
} Un relation peut tre en 3NF par rapport une de ses cl s
candidates et ne pas l' tre par rapport une autre.
} Si besoin est, on d compose les attributs ou la relation pour
respecter la 3NF.
} Une relation en 2NF avec au plus un attribut qui n'appartient
pas la cl candidate choisie est, par d finition, forc ment en
3NF.
Le mod le relationnel
-M. HAJJI-
54
Les formes normales
La troisi me forme normale
} Par exemple,
} Commande(num roCommande, codeClient, client,
article) avec les DF
} num roCommande Commande codeClient, client, article
et
} codeClient Commande client
n'est pas en 3NF alors que Pers(nom, pr nom, ge,
nombreEnfants) avec la DF
} nom, pr nom Pers ge, nombreEnfants
est en 3NF.
Le mod le relationnel
-M. HAJJI-
55
Les formes normales
La troisi me forme normale
} Par exemple,
Le mod le relationnel
-M. HAJJI-
56
Les formes normales
La troisi me forme normale
} Exercice: Passez les relations suivantes en 3NF.
} Universit ( tudiant, mati re, enseignant, note) Admettez
} tudiant, mati re Universit enseignant, note et
} enseignant Universit mati re
} Personne1(num roS cu, nom, pr nom, adresse, pr nomEnfants,
geEnfants)
} Personne2(nom, pr nom, natureDipl mes, lieuExamenDipl mes,
dateExamenDipl mes, initialeNom, pr nomEt geEnfants)
} Commande1(codeClient, codeArticle, client, article)
} Commande2(numCommande, numProduit, libell Produit,
quantit Command e)
} Enseignement1(nom tudiant, ge, cours, jourCours) Chaque cours
n'a lieu qu'une fois par semaine.
} Enseignement2(cours, joursCours, nomProfesseur, salaireProfesseur)
Chaque cours n'a qu'un enseignant et n'a lieu qu'une fois par
semaine.
Le mod le relationnel
-M. HAJJI-
57
Les formes normales
La troisi me forme normale
} Universit ( tudiant, mati re, enseignant, note)
} La table doit tre scind e
} (enseignant, mati re)
} ( tudiant, enseignant, note)
} Personne1(num roS cu, nom, pr nom, adresse,
pr nomEnfants, geEnfants)
} La table doit tre scind e
} (num roS cu, nom, pr nom)
} (nom, pr nom, num ro de rue, rue, code postal, ville)
} (nom, pr nom, pr nomEnfants)
} (nom, pr nomEnfants, geEnfants)
Le mod le relationnel
-M. HAJJI-
58
Les formes normales
Le troisi me forme normale
} Personne2(nom, pr nom, natureDipl mes,
lieuExamenDipl mes, dateExamenDipl mes, initialeNom,
pr nomEt geEnfants)
} La table doit tre scind e
} (nom, pr nom, initialeNom)
} (nom, pr nom, pr nomEnfants)
} (nom, pr nomEnfants, geEnfants)
} (natureDipl mes, lieuExamenDipl mes, dateExamenDipl mes)
} (nom, pr nom, natureDipl mes, lieuExamenDipl mes)
} Commande1(codeClient, codeArticle, client, article)
} La table doit tre scind e
} (codeClient, codeArticle)
} (codeClient, client)
} (codeArticle, article)
La transformation en 2NF permet
de passer directement la 3NF
Le mod le relationnel
-M. HAJJI-
59
Les formes normales
Le troisi me forme normale
} Commande2(numCommande, numProduit, libell Produit,
Publicité
quantit Command e)
} La table doit tre scind e
} (numProduit, libell Produit)
} (numCommande, numProduit, quantit Command e)
} Enseignement1(nom tudiant, ge, cours, jourCours)
} La table doit tre scind e
} (cours, jourCours)
} (nom tudiant, ge, cours)
} Enseignement2(cours, joursCours, nomProfesseur, salaireProfesseur)
} La table doit tre scind e
} (cours, joursCours, nomProfesseur)
} (nomProfesseur, salaireProfesseur)
Le mod le relationnel
-M. HAJJI-
60
Les formes normales
Forme normale de Boyce-Codd
} Une relation est en forme normale de Boyce-Codd si et
seulement si :
1) Elle est en 3NF
2)
ses cl s candidates sont les uniques sources de DF.
} La forme normale de Boyce-Codd est not e BCNF
(FNBC en fran ais).
} Si une relation est en BCNF, elle l'est par d finition pour
toutes ses cl s candidates.
} Si besoin est, on d compose les attributs ou la relation
pour respecter la BCNF.
Le mod le relationnel
-M. HAJJI-
61
Les formes normales
Forme normale de Boyce-Codd
} Par exemple, Universit ( tudiant, mati re, enseignant,
note) avec les DF
} tudiant, mati re Universit enseignant, note et
} enseignant Universit mati re
n'est pas en BCNF alors que Pers(nom, pr nom, ge,
nombreEnfants) avec la DF
} nom, pr nom Pers ge, nombreEnfants
est en BCNF.
Le mod le relationnel
-M. HAJJI-
62
Les formes normales
Forme normale de Boyce-Codd
} Par exemple
Le mod le relationnel
-M. HAJJI-
63
Les formes normales
Forme normale de Boyce-Codd
} Toutes les DF sont prises en compte par la BCNF.
} On consid re g n ralement qu'une base de donn es de
bonne qualit ne contient que des relations qui
respectent la BCNF.
} Cependant, la BCNF ne r sout pas tout.
} Il existe d'autres types de d pendance dont la prise en
compte peut encore am liorer la qualit des relations.
} Notamment, les d pendances multivalu es ont permis de
d finir la 4NF
} Et les d pendances de jointure ont donn naissance la 5NF.
} Leur usage reste cependant plus marginal que celui de la BCNF.
Le mod le relationnel
-M. HAJJI-
64
De lentit -association au relationnel
Passage dun mod le deux structures (entit s +
associations)
un mod le une structure (relations)
} R gle 1 : entit s
} Pour chaque entit du sch ma E/A
1. On cr e une relation de m me nom que lentit
2. Chaque propri t de lentit , y compris lidentifiant, devient un
attribut de la relation (une colonne)
Les attributs de lidentifiant constituent la cl de la relation
3.
Le mod le relationnel
-M. HAJJI-
65
De lentit -association au relationnel
Film (idFilm, titre, ann e, genre, r sum )
Artiste (idArtiste, nom, pr nom, ann eNaissance)
Internaute (email, nom, pr nom, r gion)
Pays (code, nom, langue)
Le mod le relationnel
-M. HAJJI-
66
De lentit -association au relationnel
} R gle 2 : associations de un plusieurs
} Soit une association de un plusieurs entre A et B.
1. On cr e les relations RA et RB correspondant respectivement aux
entit s A et B
Lidentifiant de B devient un attribut de RA.
2.
Film (idFilm, titre, ann e, genre, r sum )
Artiste (idArtiste, nom, pr nom, ann eNaissance)
Internaute (email, nom, pr nom, r gion)
Pays (code, nom, langue)
Film (idFilm, titre, ann e, genre, r sum , idMES)
Artiste (idArtiste, nom, pr nom, ann eNaissance)
Internaute (email, nom, pr nom, r gion)
Pays (code, nom, langue)
Le mod le relationnel
-M. HAJJI-
67
De lentit -association au relationnel
} R gle 3 : associations avec type entit faible
} Une entit faible est toujours identifi e par rapport une autre
entit (e.g. salle de cin ma).
} Il sagit dune association un plusieurs.
M me r gle de passage : on utilise un m canisme de cl trang re
pour r f rencer lentit forte dans lentit faible. La cl trang re est
une partie de lidentifiant de lentit faible.
Cin ma (nomCin ma, num ro, rue, ville)
Salle (nomCinema, no, capacit )
Le mod le relationnel
-M. HAJJI-
68
De lentit -association au relationnel
} R gle 4 : associations binaires de plusieurs plusieurs
} Soit une association binaire de n-m entre A et B.
1. On cr e les relations RA et RB correspondant respectivement aux entit s A
et B
2. On cr e une relation RA-B pour lassociation
3.
La cl de RA et la cl de RB deviennent des attributs de RA-B
La cl de cette relation est la concat nation des cl s des relations RA et
RB
Les propri t s de lassociation deviennent des attributs de RA-B.
4.
5.
Film (idFilm, titre, ann e, genre, r sum , iDMES, codePays)
Artiste (idArtiste, nom, pr nom, ann eNaissance)
Internaute (email, nom, pr nom, r gion)
Role (ideFilm, idActeur, nomR le)
Notation (email, idFilm, note)
Le mod le relationnel
-M. HAJJI-
69
De lentit -association au relationnel
Choix des identifiants
} Il est pr f rable, en g n ral, de choisir un identifiant
neutre qui ne soit pas une propri t de lentit
} Chaque valeur de lidentifiant doit caract riser de mani re
unique une occurrence
} Ex : Titre pour la relation Film ou nom pour la relation Acteur ne
sont pas de bons choix
} Si on utilise un ensemble de propri t s comme identifiant, la
r f rence une occurrence est tr s lourde
} Ex : si on prenait pour cl de Cin ma lidentifiant (nom, rue, ville)
} Lidentifiant sert de r f rence externe et ne doit jamais tre
modifiable (il faudrait r percuter les mises jour)
} Ex : Titre pour la relation Film ou nom pour la relation Acteur ne
sont pas de bons choix
Le mod le relationnel
-M. HAJJI-
70
Exercice1
} Un organisme d partemental souhaite mettre en place une base de
donn es pour le suivi des films projet s dans les salles de cin ma du
d partement. Pour simplifier, on consid re qu'une salle de cin ma ne
projette qu'un seul film une heure donn e. Toutefois, un m me film
peut tre projet simultan ment dans plusieurs salles. Pour des
raisons d'organisation et d'espace, une salle de cin ma ne projette
chaque film qu'une seule fois par jour et toujours la m me heure.
On repr sentera les films actuellement l'affiche. On ne souhaite
pas archiver l'historique des projections des films par salle.
L'organisme d partemental effectue r guli rement des sondages sur
un groupe de spectateurs fid les pour recueillir leur impression sur
tous les films qu'ils ont vus. Pour simplifier, on consid re que chaque
spectateur met une appr ciation qui peut tre r sum e par bien,
quelconque, nul. On ne s'int resse pas l'information sur la salle
dans laquelle il a regard ce film.
Le mod le relationnel
-M. HAJJI-
71
Exercice1
} On dispose pour chaque salle des donn es suivantes :
nom, adresse et liste des films projet s avec l'heure de
leur projection dans la salle. Les informations stock es
sont celles de la semaine en cours.
Chaque spectateur est identifi par un num ro. On
conna t d'autre part son nom, son pr nom, son adresse, sa
date de naissance et sa cat gorie professionnelle. Pour
chaque film, on souhaite stocker son visa d'exploitation,
son titre, le nom du r alisateur et son ann e de sortie.
Enfin, on enregistre, pour chaque spectateur interrog , la
liste des films visionn s et son impression sur chacun des
films.
Le mod le relationnel
-M. HAJJI-
72
Exercice1
} Mod le entit association
} On consid re que 2 salles ne peuvent porter le m me nom. De
plus, les cardinalit s minimales sont gales 0, ce qui permet
une grande souplesse pour enregistrer des salles sans films, des
films non projet s, des spectateurs nouveaux, des films non
encore visionn s etc..
Mod le relationnel ?
Le mod le relationnel
-M. HAJJI-
73
Exercice1
} Mod le relationnel
} SALLE_CINEMA(nom_salle, adresse) (3NF)
} FILM(visa, titre, r alisateur, ann e_sortie) (2NF si on consid re qu'un
titre est unique)
} SPECTATEUR(num ro, nom, pr nom, adresse, date_naissance,
cat_prof)
} Les types d'association :
...