Transcript Conception d`une BD

Conception d’une BD
INT Management
180
Plan du document


Introduction
Modèle Entité/Association




Traduction E/A  relationnel
Rétro-conception
Généralisation/Spécialisation
Théorie des dépendances



Dépendances fonctionnelles
Décomposition des relations
Formes normales
slide 182
slide 187
slide 194
slide 206
slide 211
slide 221
slide 227
slide 239
slide 248
181
Introduction
Conception indépendante d’un modèle
de données
Réalité
Schéma conceptuel
transformation
Schéma
relationnel
Schéma
réseau
….
Fichiers
182
Conception relationnelle
Relation
universelle
Introduction
Dépendances
fonctionnelles
décomposition
Schéma
Relationnel
normalisé
183
Conception centralisée
Introduction
Schéma
conceptuel global
Schéma
local
Utilisateur
Schéma
local
Programme
….
Schéma
local
…
184
Conception décentralisée
Introduction
(à partir d’un existant)
Hétérogène/homogène
Schéma local
Schéma local
Schéma local
intégration
Schéma
global
185
Conception d’une BD
Introduction
Réalité
Besoin de la BD
Conception logique
Schéma conceptuel (haut niveau)
Indépendant du SGBD
Recueil des besoins et analyse
Transformation du modèle
Conception physique
Spécifique à
un SGBD
Schéma conceptuel (spécifique SGBD)
Schéma physique (spécifique SGBD)
186
Modèle Entité/Association



Modèle E/A
Proposé en 1976 (Chen)
Nombreuses extensions depuis
Avantages :




Utilisé dans la plupart des méthodes de
conception (MCD Merise, …)
Simple
Graphique
Facilite le dialogue avec les utilisateurs
187
Concepts
Modèle E/A
Graphisme 1
Ens. entité
Graphisme 2
Nom Type Entité
Attribut
T
A
Attribut
composite
Attribut
multivalué
Clé d’un type
d’entité
Type
d’association
Rôle
Contraintes de
cardinalité
A
Ass
Ass
Rôle
1,n
Rôle
0,n
188
Concepts (2)
Graphisme 1
Modèle E/A
Graphisme 2
Type d’entité
faible
Type
d’association
identifiante
Ass
(1,1)
Attribut calculé
189
Modèle E/A Entreprise
nomf
NoSS
adresse
Modèle E/A
prenom
nom
No_dept
libelle
localisations
Nb_emp
Travailler pour
Est supervisé
Employé
1,1
Supervise
0,n
0,n
superviser
1,1
0,1
1,n
0,n Département
1,1
diriger
0,n
date
Avoir à charge
1,1
gérer
Travailler sur
Nb_heures
Personne-à-charge
Parente
prénom
DateNaissance
1,1
1,n
Projet
Numéro
Description
190
Modèle E/A Entreprise
Modèle E/A
Graphisme 2
supervise
Superviser
0,n
Employe
1,1
NoSS
Adresse
Est supervisé
1,1 Nomf
Prenom
TravaillerPour
0,1
1,n
1,1
Département
No_dept
libelle
Nb_Emp
localisations
Diriger
0,n
1,n
1,n
Date
Avoir a charge
Gérer
TravaillerSur
Nb_heures
(1,1)
PersonneACharge
prénom
DateNaissance
Parenté
1,1
1,n
Projet
Numéro
Description
191
UML Entreprise
UML
192
Entité-Association /
Diagramme de classe UML
Entité-Association
Diagramme de Classe
SI – BD
Génie logiciel – langages
Entité
Classe
Entité faible
Composition ? Clé ?
Association sans attribut
Association / Composition
Association avec attribut
Association + Classe d’association
(Héritage) - Association
Héritage
Attribut / Propriété
Attribut
Clé

Attribut calculé
Méthode
(Attributs multivalués) – Entité + Association
Attributs multivalués

Méthode
Cardinalité
Multiplicité
UML
193
Traduction EA  relationnel




Traduction
La sémantique n’est pas complètement
préservée (il faut ajouter des contraintes
d’intégrité)
Règles sont automatisables (de nombreux outils
existent sur le marché, AMCDesignor par
exemple)
Transformation se fait en 7 étapes
Comparaison des concepts des deux modèles : à
faire par l’étudiant
194
Étape 1 : transformation des entitésTraduction
non faibles




Type d'entité  relation
Attribut atomique  constituant (attribut)
Attributs composites n constituants
Attribut(s) clé(s)  clé candidate
E
C
A
E(C, A)
195
Exemples d’étape 1
Employé
Traduction
Attributs atomiques
Employe(noss, adresse)
Attributs composites
Employe(noss, adresse, nomf, prenom)
Aplatissement de la structure (suite d’attributs atomiques)  perte de la sémantique
Département
Projet
Attributs atomiques
Département(no_dept, libelle)
Attributs multivalués
Etape 6 : local
Attributs calculés
Etape 7 : nb_emp
Attributs atomiques
Projet(numero, description)
196
Étape 2 : transformation des entitésTraduction
faibles





Type d'entité faible  relation
Attribut atomique  constituant (attribut)
Attributs composites n constituants
Attribut(s) clé(s)  partie de clé candidate
Attributs clés de l’entité identifiante  partie
de clé
E
C
A
(1,1)
E2
C2
A2
E2(C, C2, A2)
197
Exemple d’étape 2
Entité
PersonneACharge
Traduction
Attributs atomiques
PersonneACharge(nom, DateNaissance,
parenté)
+
Clé de l’entité
identifiante
PersonneACharge(nom, DateNaissance,
parenté, noss)
Noss :
clé étrangère sur Employé
+
Partie de la clé de PersonneACharge
198
Traduction
Étape 3 : transformation des associations
binaires monovaluées


Clé associée à E1  attribut de E2
Attributs de Ass  attributs de E2
E1
C1
A1
?,?
Ass
A3
?,1
E2
C2
A2
E2(C2, A2, C1, A3)
Clé étrangère,
pas clé dans E2
199
Exemples d’étape 3
TravaillePour
Employe(noss, adresse, nomf, prenom, no_dept)
Dirige
Département(no_dept, libelle, nosschef, dateDebut)
Traduction
Dirige monovaluée dans les 2 sens  on aurait pu créer
Employe(noss, adresse, nomf, prenom, nodept, nodeptDirigé,
dateDebut)
Cardinalités (0,1), association partielle  valeurs nulles
Controle
Projet(numero, description, numdept)
Supervision
Employe(noss, adresse, nomf, prenom, nodept,
noSSSuperviseur)
noSSSuperrviseur clé étrangère de Employé sur elle-même
PersonneACharge
Cf. étape 2
200
Étape 4 : transformation des associations
binaires multivaluées dans les 2 sens



Traduction
Création d'une nouvelle relation Ass
Clé de E1 + Clé de E2  clé de Ass
Attributs de Ass  Attributs de Ass
E1
C1
A1
TravailleDans
?,n
Ass
A3
?,n
E2
C2
A2
Ass(C1, C2, A3)
TravailleDans(noss, numeroProjet, nb_heures)
201
Étape 5 : Transformation des
associations n-aires (n > 2)

Traduction
Idem étape 4 :



Création d'une nouvelle relation Ass
Clé de E1 + Clé de E2 + … clé de En clé de
Ass
Attributs de Ass  Attributs de Ass
E1
C1
A1
Ass
A4
E2
C2
A2
Ass(C1, C2, C3, A4)
E3
C3
A3
202
Traduction
Étape 6 : transformation des attributs
multivalués




Création d'une nouvelle relation R
Attribut multivalué -> constituant
Clé du type d'entité associé -> constituant
Clé de la relation : tout le schéma
A
Département
Localisations dans Département
C
R(A, C)
LocalisationDept(localisation, num_dept)
203
Traduction
Étape 7 : transformation des attributs
calculés


Attribut calculé
Requête associée
Nb_emp dans
Département
Département(no_dept, libelle, nosschef, dateDebut)
SELECT no_dept, COUNT(*)
FROM Employe
GROUP BY no_dept
Couples no département – effectif du département
Associée à une vue relationnelle
204
Récapitulatif de l’exemple
Traduction
Employe(noss, adresse, nomf, prenom, nodept, noSSSuperviseur)
Département(no_dept, libelle, nosschef, dateDebut)
Projet(numero, description, numdept)
PersonneACharge(nom, noss, DateNaissance, parenté)
TravailleDans(noss, numeroProjet, nb_heures)
LocalisationDept(localisation, num_dept)
CREATE VIEW Effectifs(no_dept, nb_emp) AS
SELECT nodept, COUNT(*)
FROM Employe
GROUP BY nodept
CREATE VIEW DepartementComplet AS
SELECT D.*, EF.nb_emp
FROM Département D, Effectifs EF
WHERE D.no_dept=EF.no_dept
205
Rétro-conception

But :


Analyse n’a pas été faite ou a été perdue
Comment ?


Passer d’un schéma relationnel à un schéma EntitéAssociation équivalent
Pourquoi ?


Rétro-conception
Appliquer les étapes de transformation « à l’envers »
Remarque

Ne fournit pas une solution unique (perte d’information sur
le schéma relationnel par rapport au schéma E/A)
206
Principes de rétro-conception


Relation ne possédant pas de clé étrangère :
ensemble d'entité
Relation possédant une clé étrangère à
l'extérieur de sa clé : ensemble d'entité plus
association monovaluée vers l'ensemble
d'entité correspondant à la clé étrangère
207
Principes de rétroconception (2)


Relation dont la clé est intégralement
composée de clés étrangères : association
multivaluée entre les ensembles d'entités
correspondant aux clés étrangères
Relation dont la clé comprend une clé
étrangère et une clé "locale" : ensemble
d'entité faible par rapport à l'ensemble
d'entité correspondant à la clé étrangère
208
Schéma relationnel BD vins
Exemples
Vins(num, cru, annee, degre)
Recoltes(nvin, nprod, quantite)
Producteurs(num, nom, prenom, region)
Buveurs(num, nom, prenom, ville)
Commandes(ncde, date, nb, nvin, qte)
Livraisons(ncde, no_ordre, qteLivree)
209
Rétro-conception de la BD des vins
Rétro-conception
Vins
num
cru
degré
annee
1
?
Buveurs
num
nom
prenom
ville
?
0,n
0,n
0,n
recoltes
concerne
?
Passer
quantite
1
?
0,n
Producteurs
num
nom
prenom
region
1
?
1,1
1,1
Commandes
ncde
qté
date
0,n
Livraisons
Donner_lieu
(1,1)
No_ordre
qteLivrée
date
1
?
210
Généralisation / spécialisation


Introduire dans le modèle E/A un concept
objet "classique"
Permet de définir un ensemble d'entité
relativement à un autre (attention ressemble
à entité faible mais est très différent)
211
Sémantique de la généralisation /
spécialisation



Sous ensemble d'entité hérite des attributs
de son super ensemble d'entité
A priori pas d'héritage multiple
Spécialisation peut être :


totale (toute instance est spécialisée dans au
moins un sous-ensemble) ou partielle
une partition (une instance ne peut être
spécialisée dans plusieurs sous-ensembles) ou
un recouvrement
212
Exemple de généralisation /
spécialisation Ensemble Super ensemble d'entité
d'entité A
A1
A2
A=BCD
Ensemble
d'entité B
B1
A1
A2
B1
T/P
Ensemble
d'entité C
C1
A1
A2
C1
BC=
CD=
Ensemble
d'entité D
D1
D2
A1
A2
D1
D2
213
Exemple de généralisation /
spécialisation
PERSONNE
nom
prénom
adresse
P=ES
ETUDIANT
noétud
cycle
ES
T/R
SALARIE
salaire
S = PR  PU T/P
PRIVE
prime
PR  PU = 
PUBLIC
indice
214
Transformation G / S vers modèle
relationnel (1)

Approche par généralisation


feuille : relation de base dont le schéma est
l'union des attributs de la feuille jusqu'à la racine
non feuille : vue définie comme union des fils
(avec projection sur les attributs communs)
215
Conséquences






Pas de création d'instances sur les non feuilles
Accès aux feuilles peu coûteux
Accès aux non feuilles par union-projection
Une même instance peut être spécialisée dans
plusieurs entités
Pas vraiment de notion "d'identité d'objet" (donc pas
de partage de structure)
Evolution de schéma difficile
216
Exemple de transformation
CREATE TABLE ETUDIANT(nom, prénom, adresse, noétud, cycle)
CREATE TABLE PRIVE(nom, prénom, adresse, salaire, prime)
CREATE TABLE PUBLIC(nom, prénom, adresse, salaire, indice)
CREATE VIEW SALARIE AS
SELECT nom, prénom, adresse, salaire
FROM PRIVE
UNION
SELECT nom, prénom, adresse, salaire
FROM PUBLIC
CREATE VIEW PERSONNE AS
SELECT nom, prénom, adresse
FROM ETUDIANT
UNION
SELECT nom, prénom, adresse
FROM SALARIE
217
Transformation G / S vers modèle
relationnel (2)

Approche par spécialisation



chaque noeud : relation dont le schéma est
constitué des attributs "locaux"
chaque instance : on rajoute un oid (ou surrogate)
héritage : on ajoute l'oid de l'instance mère
comme clé étrangère dans l'instance fille
218
Conséquences






Création d'instance à n'importe quel
niveau dans l'arbre d'héritage
Accès coûteux aux instances "loin" de la
racine (reconstruction par jointures)
Héritage multiple difficile
Notion d'oid est offerte (partage de
structure possible)
Multi-instanciation possible
Evolution de schéma plus facile
219
Exemple de transformation
CREATE TABLE PERSONNE(idfP, nom, prénom, adresse)
CREATE TABLE ETUDIANT(idfE, noétud, cycle, idfP)
CREATE TABLE SALARIE(idfS, salaire, idfP)
CREATE TABLE PUBLIC(idfPU, indice, idfS)
CREATE TABLE PRIVE(idfPR, prime, idfS)
CREATE VIEW PERSONNE AS
SELECT nom, prénom, adresse FROM PERSONNE
CREATE VIEW ETUDIANT AS
SELECT P.nom, P.prénom, P.adresse, E.noétud, E.cycle
FROM  PERSONNE P,  ETUDIANT E
WHERE P.idfP = E.idfP
...
220
Théorie des dépendances
Normalisation des schémas relationnels
221
Objectifs

Mauvaise conception => Redondance des
données




anomalies de maj (valeurs dupliquées)
anomalies d’insertion (valeurs nulles,
incohérences)
anomalies de suppression (pertes
d'information non désirées)
Introduire une notion de "bon" schéma
(sans redondance d'informations)

permet de comparer deux schémas
222
Exemple de mauvaise conception
Relation APPROVISIONNEMENT
PRODUIT QUANTITE COULEUR FOURNISSEUR ADRESSE
parapluie
110
rouge
Labaleine
Paris
Chapeau
50
vert
Lemelon
Lyon
sac à main
65
noir
Toutcuir
Lyon
parasol
15
jaune
Labaleine
Paris
ombrelle
5
rouge
Labaleine
Paris
ceinture
25
vert
Letour
Nantes
sac à main
65
noir
Legrand
Paris
223
Outils proposés

Étudier les propriétés des données




Formes normales


dépendances fonctionnelles
dépendances multi-valuées
dépendances produits, ...
ordre partiel sur les schémas
Algorithmes de décomposition / synthèse
pour obtenir des schémas en 3ème forme
normale
224
Décomposition d’une relation
Relation universelle
nom
nc
ville
nv
dateexp
prénom
DF
nb
datec
qtéexp
qtéc
Normalisation
R1(.....)
R2(....)
......
225
Les dépendances
fonctionnelles
226
Dépendance fonctionnelle (DF)

Propriété définie sur le schéma



Définition



cas particulier de contrainte d'intégrité
définie sur l'intension (donc valide qq soit
l'extension)
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
227
Exemple



BUVEURS(nb, nom, prénom, ville)
COMMANDES(nc, datec, nv, qtéc, nb)
EXPEDITIONS(nc, dateexp, qtéexp)
228
Exemples de DF
NB  NOM
NB  PRENOM
NB  VILLE
NC  DATEC
NC  NB
NC  NV
NC  QTEC
NC, DATEEXP 
QTEEXP
NOM  VILLE ?
NB  NV ?
QTEC  QTEEXP ?
229
Axiomes d’Armstrong

Réflexivité


XY
Augmentation


YX
XY
 XZ  YZ
Transitivité

X  Y et Y  Z
XZ
230
Propriétés déduites

Union


 X  YZ
Pseudo-transitivité


X  Y et X  Z
X  Y et YW  Z
 XW  Z
Décomposition

X  Y et Z  Y
XZ
231
Fermeture transitive



Fermeture transitive d'un ensemble F de DF
est notée F+
F+ = F U DF obtenues via les axiomes
Par exemple



NC  NB et NB  NOM donc NC  NOM
NB  NOM donc NB, NV  NOM, NV
essentiellement transitivité et pseudo-transitivité
232
Graphe de dépendances fonctionnelles


Noeuds = attributs
Arcs = DF
nc
datec
nv
nb
qtéc
nom
prénom
ville
dateexp
qtéexp
233
Graphe de fermeture transitive
nc
datec
nv
nb
qtéc
nom
prénom
ville
dateexp
qtéexp
234
Dépendance fonctionnelle élémentaire

Une dépendance fonctionnelle X  A est
dite élémentaire si




A n’est pas inclus dans X
il n’existe pas X’ inclus dans X tel que X’ A
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
235
Couverture minimale

Définition
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
236
Clé d’une relation

Définition


ensemble minimum d’attributs permettant de
déterminer tous les autres
Soit R(A1, A2, ..., An) un schéma de
relation. Soit F+ l’ensemble des DF
associées à R. X (sous-ensemble
d'attributs de R) est une clé de R ssi :


X  A1, A2, ..., An
il n’existe pas de sous-ensemble Y de X tel
que Y  A1, A2, ..., An
237
Clé d’une relation (2)

Exemple


nc, dateexp est une clé du schéma coopérative
Remarques


une même relation peut avoir plusieurs clés
il existe toujours au moins une clé (au pire on
prend tout le schéma)
238
Décomposition des relations
239
Décomposition d’une relation

Décomposition d’un schéma de relation

La décomposition d’un schéma de relation
R(A1, A2, ..., An) est sa substitution par un
ensemble de schéma de relations R1, R2, ...,
Rp telles que :


schéma(R)  schéma(R1)  schéma(R2)  ...
schéma(Rp)
Critères de bonne décomposition


Décomposition sans perte d'informations
Décomposition préservant les DF
240
Décomposition sans perte
d'informations

La décomposition d’un schéma de relation
R(A1, A2, ..., An) par un ensemble de schéma
de relations R1, R2, ..., Rp est sans perte
d'informations ssi :

R  R1
R2
...
Rp
241
Décomposition préservant les DF


Soit R(A1, A2, ..., An) et DFR (ens. de DF
associé)
la décomposition de R en R1, R2, ..., Rp (avec
DFR1, ...DFRn resp. ens. de DF de R1, ..., Rn)
préserve les DF ssi :
DFR+ = DFR1+ U ... U DFRn+
242
Exemples de décomposition

Commandes(nc, datec, nv, qtéc, nb, nom,
prénom, ville)



C(nc, datec, nv, qtéc, nb) et B(nom, prénom,
ville) : perte d'info
C(nc, datec, nv, qtéc, nb) et B(nb, nom,
prénom, ville) : pas de perte d'info et
préservation DF
C(nc, nb) et B(nc, datec, nv, qtéc, nom,
prénom, ville) : pas de perte d'info et perte de
DF (nb  nom par exemple)
243
Principe de décomposition binaire
R(X,Y,Z) et X  Y 
R(X,Y,Z)=R1(X,Y) R2(X,Z)



on peut toujours décomposer une relation suivant
une DF
on ne peut pas décomposer une relation s'il n'y a
pas de DF
la décomposition selon une DF préserve
l'information
244
Algorithme de décomposition
Commandes(nc, datec, nv, qtéc, nb, nom, prénom, ville)
nb  nom; nb  prénom; nb  ville;
nc  datec; nc  nb; nc  nv; nc  qtéc;
nc, dateexp  qtéexp
Commandes(nc, datec, nv, qtéc, nb, nom, prénom, ville)
nc  datec, nv, qtéc
C1(nc, datec, nv, qtéc)
C(nc, nb, nom, prénom, ville)
nb  nom, prénom, ville
C3(nb, nom, prénom, ville)
C2(nc, nb)
245
Algorithme de décomposition



Garantie que la décomposition soit sans
perte (puiqu'on suit le principe de
décomposition binaire)
Pas de garantie que la décomposition
préserve les DF
Décomposition n'est pas unique (dépend de
l'ordre du choix des DF dans la
décomposition)
246
Autre décomposition
Commandes(nc, datec, nv, qtéc, nb, nom, prénom, ville)
nb  nom, prénom, ville
C1(nb, nom, prénom, ville) C2(nc, datec, nv, qtéc, nb)
247
Formes normales
248
Formes normales




Première forme normale
Deuxième forme normale
Troisième forme normale
...
249
1ère forme normale

Définition


Une relation est en première forme normale si
tous ses attributs sont atomiques (inhérent au
modèle relationnel)
Un attribut atomique n’est pas :


multivalué (liste de valeurs)
composé (structuré en sous-attributs)
250
Deuxième forme normale

Définition

Une relation est en deuxième forme normale ssi :



elle est en première forme normale
tout attribut non clé dépend de la totalité de toutes les
clés
Exemple

C(nc, dateexp, qtéexp, nb) pas en 2FN car nc,
dateexp clé et nc  nb
251
Troisième forme normale


Objectif : élimination des redondances dues
aux dépendances fonctionnelles déduites
par transitivité
Définition

Une relation est en troisième forme normale ssi :


elle est en deuxième forme normale
il n’existe aucune DF entre attributs non clé
252
Comment calculer une forme normale
Soit R(A1, ..., An) et DFR ens. de DF associé
 calculer DFR+
 déterminer la (les) clé (s) de R
 partitionner les attributs en attributs clés (ils
appartiennent à au moins une clé) et attributs
non clés
 appliquer les définitions de forme normale
(depuis la 1ère)
253
Propriétés


Toute relation R admet au moins une
décomposition en 3FN qui préserve
l'information et les DF
Deux approches pour la calculer :


décomposition : on s'arrête dès que les
relations dérivées sont en 3FN (mais pas de
garantie sur les DF)
synthèse à partir de la couverture minimale
254
Algorithme de synthèse en troisième
forme normale


Pré : connaissance du contenu de la relation universelle + DF
Principe de l’algorithme :
 A partir du graphe G des DF, calculer une couverture minimale C
 Editer l’ensemble des attributs isolés dans une même relation (tous
sont clés)
 Recherche le plus grand ensemble X d’attributs qui détermine
d’autres attributs
 Editer la relation R(X, A1, ..., An)
 Supprimer les DF X  A1, .., X An du graphe de couverture
minimale C
 Supprimer les attributs isolés de C
 Reprendre l’opération à partir de l’étape 3 jusqu’à ce que C soit vide
255
Insuffisance de la 3FN
Relation VINS(CRU, PAYS, REGION)
Chenas, France, Beaujolais
Juliénas, France, Beaujolais
Chablis, France, Bourgogne
Chablis, USA, Californie
avec les DF suivantes :
région  pays; cru, pays  région
VINS est en 3FN, pourtant il y a des
redondances (ici on peut régler cela avec
la 3FN Boyce Codd Kent)
256
Conclusion sur la normalisation





Permet d'affiner une conception de
schéma
Peut se coupler avec une démarche de
conception "à la Merise"
Inconvénient majeur : on suppose qu'on
possède une couverture minimale des DF
(si on a oublié une DF tout est faux)
Autres formes normales avec d'autres
types de dépendance
Peut être remis en cause au niveau
physique (dénormalisation)
257