Plan
Elaboration dun
Modle Logique de Donnes Relationnel
(MLD-R)
Bernard ESPINASSE
Professeur Aix-Marseille Universit (AMU)
1.
Problmatique du MLD-R
2.
Formalisme graphique de Merise
3.
Drivation dun MLD-R partir dun MCD en Entit-Relation
4.
Cration de tables en langage SQL (cl primaires et
trangres)
5.
Dimensionnement dune BD Relationnelle (multiplicit
moyenne des liens)
Ecole Polytechnique Universitaire de Marseille
Novembre 2012
Problmatique du MLD
Formalisme graphique de Merise
Drivation dun MLD-R partir dun MCD en Entit-Relation
Cration de tables en langage SQL (cl primaires et trangres)
Dimensionnement dune BD Relationnelle
Bernard ESPINASSE Elaboration dun MLD-R
Problmatique du MLD
Bernard ESPINASSE Elaboration dun MLD-R
D marche d laboration dun MLD Re lationne l
Modle Conceptuel de Donnes (MCD) :
- permet de modliser la smantique des informations dune faon
comprhensible par lutilisateur de la future base de donnes
- utilise le formalisme (graphique) Entit-Relation
- ne permet pas dimplmentation informatique de la base de
donnes dans un SGBD donn
! MCD : Modle Conceptuel de Donnes
! MLD-R : Modle Logique de Donnes Relationnel
MCD
NIVEAU CONCEPTUEL
En formalisme Entit-Relation
Modle Logique de Donnes (MLD) :
- permet de modliser la structure selon laquelle les donnes seront
stockes dans la future base de donnes
- est adapt une famille de SGBD : ici les SGBD relationnels (MLD
Relationnels ou MLD-R)
- utilise le formalisme graphique Merise
- permet dimplmenter la base de donnes dans un SGBD donn
En formalisme Merise
Cration des tables de la base
de donnes en langage SQL
NIVEAU PHYSIQUE
Bernard ESPINASSE Elaboration dun MLD-R
MLD (Relationnel)
NIVEAU LOGIQUE
Bernard ESPINASSE Elaboration dun MLD-R
SGBD Relationnel
4
Formalisme graphique Merise pour le MLD-R (1)
Formalisme graphique Merise pour le MLD-R (2)
Table
Lien entre tables : contrainte dintgrit rfrentielle
Table EMPLOYE (reprsentation graphique) :
Table EMPLOYE (reprsentation graphique) :
DEPARTEMENT
EMPLOYE
EMPLOYE
Appartenir
Matricule
Nom_departement
Nom
Age
Adresse
Matricule
Nom
Age
Adresse
Schma de la table EMPLOYE :
EMPLOYE (Matricule, nom, age, adresse)
Schmas des tables :
! Table DEPARTEMENT (Nom_departement, Effectif) :
! Nom_departement : cl primaire
Attributs de la table EMPLOYE :
! Matricule : cl primaire
! Nom, Age, Adresse : autres attributs
! Table EMPLOYE (Matricule, Nom_departement, Nom, Age, Adresse)
! Matricule: cl primaire
! Nom_departement : cl trangre vers table DEPARTEMENT
5
Bernard ESPINASSE Elaboration dun MLD-R
Nom_departement
Effectif
Bernard ESPINASSE Elaboration dun MLD-R
Formalisme graphique Merise du MLD-R (3)
Formalisme graphique Merise du MLD-R (4)
Lien entre tables : cl primaire compose rfrentielle
Lien entre tables : contraintes dintgrit rfrentielle rflexive
TACHE
Nordre
Nprojet
dure
TACHE
Prcder
PROJET
Concerner
Ntache
Ntache prcdente
Dsignation
Dure
Nprojet
budget
Schmas des tables :
Schmas des tables :
! Table PROJET (Nprojet, budget)
! Nprojet : cl primaire
! Table TACHE (ntache, ntache_prcdente, dsignation, dure)
! Ntache : cl primaire
! Table TACHE (Nordre, Nprojet, dure)
! Ntache_prcdente: cl trangre vers table TACHE
! Nordre, nprojet : cl primaire compose
! Nprojet : cl trangre vers table PROJET
Bernard ESPINASSE Elaboration dun MLD-R
Bernard ESPINASSE Elaboration dun MLD-R
Drivation dun MLD-R partir dun MCD en EntitRe lation
MCD -> MLD : drivation des entits
Rgle : toute entit du MCD se drive en une table du MLD
Entit
MCD
NIVEAU CONCEPTUEL
En formalisme Entit-Relation
EMPLOYE
EMPLOYE
Matricule
Nom
Age
Adresse
Matricule
Nom
Age
Adresse
Ensemble de rgles
MLD (Relationnel)
NIVEAU LOGIQUE
Table
En formalisme Merise
la proprit identifiante de lentit devient
la cl primaire de la table
Cration des tables de la base
de donnes en langage SQL
NIVEAU PHYSIQUE
SGBD Relationnel
Bernard ESPINASSE Elaboration dun MLD-R
10
Bernard ESPINASSE Elaboration dun MLD-R
MCD -> MLD : relations (*,N)-(1,1)
MCD -> MLD : relations (*,N)-(0,1)
MCD:
MCD :
EMPLOYE
DEPARTEMENT
appartenir
Matricule
Nom
Age
Adresse
1,N
1,1
PERSONNE
Nom_departement
Effectif
Nom
Prenom
Adresse
MLD :
EMPLOYE
Matricule
Nom_departement
Nom
Age
Adresse
Appartenir
DEPARTEMENT
PERSONNE
Nom_departement
Effectif
MLD :
Nom
Prenom
Adresse
VOITURE
Posseder
Date dacquisition
0,N
P
o
s
s
Posseder e
d
e
r
0,1
Numero
Marque
Type
VOITURE
Numero
Nom
Marque
Type
Date dacquisition
Schmas relationnels :
! PERSONNE (Nom, Prenom, Adresse) ;
! VOITURE (Numro, Nom, Marque, Type, Date_acquisition) ;
Schmas relationnels :
! Table DEPARTEMENT (Nom_departement, Effectif) :
! Table EMPLOYE (Matricule, Nom_departement, Nom, Age, Adresse)
Nom_departement : cl trangre vers table DEPARTEMENT
Bernard ESPINASSE Elaboration dun MLD-R
11
Bernard ESPINASSE Elaboration dun MLD-R
12
MCD -> MLD : relations (0,1)-(1,1)
MCD -> MLD : relations (0,1)-(0,1)
MCD :
MCD :
MAISON
EDIFICE
Nedifice
Type
Est-un
1,1
0,1
ENTREPRISE
Nmaison
Adresse
Nentreprise
Adresse
TIERS
Correspondre
0,1
0,1
Ntiers
Type
MLD (solution 1) :
MLD :
Ntiers
Nentreprise
Type
Correspondre
Nentreprise
Adresse
Nmaison
Nedifice
Adresse
Est-un
Nedifice
Type
TIERS
ENTREPRISE
MAISON
EDIFICE
Schmas relationnels :
Schmas relationnels :
! EDIFICE (Nedifice, Type) ;
! MAISON (Nmaison, Ndifice, Adresse).
! ENTREPRISE (Nentreprise, Adresse) ;
! TIERS (Ntiers, Nentreprise, Adresse)
La cardinalit (0,1) pose le problme daccepter des valeurs nulles sur lattribut migrant
pouvant fixer le sens de migration (par exemple la taille des cls).
13
Bernard ESPINASSE Elaboration dun MLD-R
14
Bernard ESPINASSE Elaboration dun MLD-R
MCD -> MLD : relations (0,1)-(0,1)
MCD -> MLD : relations (0/1,N)-(0/1,N)
MCD :
MCD :
ENTREPRISE
Nentreprise
Adresse
MLD (solution 2) :
ENTREPRISE
Nentreprise
Ntiers
Adresse
Schmas relationnels :
0,1
C
o
r
r
e
s
Correspondrep
o
n
d
r
e
COMMANDE
TIERS
Correspondre
0,1
Ncommande
Date
Statut
Ntiers
Type
ARTICLE
Narticle
0,N Dsignation
Prix
MLD :
TIERS
COMMANDE
Ntiers
Type
Ncommande
Date
Statut
! ENTREPRISE (Nentreprise, Ntiers, Adresse) ;
! TIERS (Ntiers, Adresse)
Idem : la cardinalit (0,1) pose le problme daccepter des valeurs nulles sur lattribut
migrant pouvant fixer le sens de migration (par exemple, la taille des cls).
Bernard ESPINASSE Elaboration dun MLD-R
1,N
Porter
Qte-commande
15
PORTER
Ncommande
Narticle
Qte-commande
ARTICLE
Narticle
Dsignation
Prix
Schmas relationnels :
! COMMANDE (Ncommande, Date, Statut) ;
! PORTER (Narticle, Ncommande, Qte_commande) ;
! ARTICLE (Narticle, Dsignation, Prix).
Bernard ESPINASSE Elaboration dun MLD-R
16
MCD -> MLD : relations ternaires ou plus (1)
MCD
MCD -> MLD : relations ternaires ou plus (2)
MLD
ENTREPRISE
MAISON
Nentreprise
Nom
Adresse
Coordonnes
Date_construction
Surface
O,N
ENTREPRISE
MAISON
Coordonnes
Date_construction
Surface
Nentreprise
Nom
Adresse
Schmas relationnels associs :
! MAISON (Coordonnes, Date_construction, Surface) ;
! TYPE_TRAVAUX (Ntype_travaux, Dsignation) ;
O,N
Realiser
REALISER
Date
Montant
! RALISER (Nentreprise, Coordonnes, Ntype_travaux, Date, Montant) ;
Coordonnes
Nentreprise
Ntype_travaux
Date
Montant
O,N
TYPE_TRAVAUX
! ENTREPRISE (Nentreprise, Nom, Adresse).
Ntype_travaux
Dsignation
TYPE_TRAVAUX
Ntype_travaux
Dsignation
17
Bernard ESPINASSE Elaboration dun MLD-R
MCD -> MLD : Re lations r fle xive s (0,N)-(0,1)
MCD :
TACHE
Ntache
Dsignation
Dure
O,N
prcde
MLD :
18
Bernard ESPINASSE Elaboration dun MLD-R
MCD -> MLD : Re lations r fle xive s (*,N)-(*,N)
Entit-Relation :
O,1
TRAVAUX
Ntravaux
Dsignation
Dure
O,N
suit
Correspondre
ensemble
Solution 1
TACHE
Ntache
Dsignation
Dure
PRECEDER
Ntache
Ntache_suivante
TRAVAUX
Ntravaux
Dsignation
Dure
TACHE
Ntache
Ntache prcdente
Dsignation
Dure
Schmas relationnels :
Dcomposer
Schmas relationnels :
ensemble
lment
DECOMPOSER
Ntravaux
Ntravaux ensemble
! TRAVAUX (ntravaux, dsignation, dure) ;
! DCOMPOSER (ntravaux, ntravaux_ensemble).
Solution 1 :
! TACHE (Ntche, Dsignation, Dure) ;
! PRCDER (Ntche, Ntche_suivante)
Solution 2 : TACHE (Ntche, Ntche_prcdente, Dsignation, Dure)
Bernard ESPINASSE Elaboration dun MLD-R
lment
Relationnel driv :
Solution 2
Prcder
O,N
19
Bernard ESPINASSE Elaboration dun MLD-R
20
R gle de d rivation : Ide ntifiant re latif
Exe mple de passage E-R au Re lationne l
MCD :
Entit-Relation :
PROJET
Nprojet
Nom_projet
TACHE
Comporter
1,1
(R)
1,N
COMMANDE
CLIENT
nclient
nom
age
adresse
type
CA annuel
taux remise
Nordre
Dsignation
1,n
PASSER
1,1
ncommande
date
statut
LIGNE_CMD
1,n
ARTICLE
qt!_cmd!e
qt!_livr!e
prix_unitaire
0,n
0,1
FOURNISSEUR
0,n
FOURNIR
narticle
d!signation
qt! stock
poids
prix d'achat
prix de vente
nfournisseur
nom
adresse
MLD :
PROJET
Relationnel driv :
TACHE
Comporter
Nordre
Nprojet
Dsignation
Nprojet
Nom_projet
COMMANDE
CLIENT
cli_num
cli_nom
cli_age
cli_adresse
cli_type
cli_ca
cli_tremise
relationnels :
Schmas
! PROJET (Nprojet, Nom_projet)
! TRANCHE (Nordre, Nprojet, Dsignation)
PASSER
cmd_num
cmd_cli
cmd_date
cmd_statut
FOURNISSEUR
four_num
four_nom
four_adresse
21
Bernard ESPINASSE Elaboration dun MLD-R
LIGNE_CMD
lcd_cmd
lcd_art
lcd_qt
lcd_liv
lcd_pu
FOURNIR
ARTICLE
art_num
art_four
art_nom
art_stock
art_poids
art_pa
art_pv
Bernard ESPINASSE Elaboration dun MLD-R
E x e m p l e d e p assage E-R au relationnel
Introduction au langage SQL
Modle relationnel driv :
Origine : SQL (Structured Query Language) est un langage de
requtes standard pour les SGBD relationnels
COMMANDE
cmd_num
cmd_cli
CLIENT
cli_num
cli_nom
cli_age
PASSER
cmd_date
cmd_statut
cli_adresse
cli_type
cli_ca
cli_tremise
FOURNISSEUR
four_num
four_nom
four_adresse
LIGNE_CMD
lcd_cmd
lcd_art
lcd_qt
lcd_liv
lcd_pu
FOURNIR
3 niveaux de normes :
ARTICLE
art_num
art_four
art_nom
art_stock
art_poids
SQL86 (standard ANSI en 86 puis ISO en 87) : la base puis SQL89 ou
SQL1 : lintgrit:
art_pa
art_pv
SQL91 ou SQL2
SQL3 (98) : SQL devient un langage de programmation et volue
vers lobjet
Schmas relationnels :
! CLIENT (cli_num, cli_nom, cli_age, cli_adresse, cli_type, cli_ca, cli_tremise)
! ARTICLE (art_num, art_nom, art_four, art_stock, art_poids, art_pa, art_pv)
! COMMANDE (cmd_num, cmd_cli, cmd_date, cmd_statut)
! LIGNE_CMD (lcd_cmd, lcd_art, lcd_qte, lcd_liv, lcd_pu)
! FOURNISSEUR (four_num, four_nom, four_adresse)
Bernard ESPINASSE Elaboration dun MLD-R
22
Dans ce chapitre nous ne considrerons que la cration BASIQUE de
tables par la commande CREATE TABLE de SQL2,
23
Bernard ESPINASSE Elaboration dun MLD-R
24
Cration dune table en langage SQL (1)
Cration dune table (2) : cl primaire
Soit le MLD suivant :
CLIENT
cli_num
cli_nom
cli_age
cli_adresse
cli_type
cli_ca
cli_tremise
Table CLIENT (cli_num, cli_nom, cli_age, cli_adresse, cli_type, cli_ca, cli_tremise)
COMMANDE
cmd_num
cmd_cli
cmd_date
cmd_statut
Schmas relationnels :
Table CLIENT (cli_num, cli_nom, cli_age, cli_adresse, cli_type, cli_ca, cli_tremise)
- cli_num = cl primaire
Table COMMANDE (cmd_num, cmd_cli, cmd_date, cmd_statut)
NOT NULL : on naccepte pas que lattribut puisse avoir une valeur nulle (valeur
inconnue)
PRIMARY KEY (cli_num): lattribut cli_num est cl primaire de la table Client.
Remarque : un attribut dclar cl primaire doit tre dfini avec l'option NOT NULL
- cmd_num = cl primaire
- cmd_cli, = cl trangre
Bernard ESPINASSE Elaboration dun MLD-R
CREATE TABLE Client
(cli_num CHAR(8) NOT NULL,
cli_nom
CHAR(25) NOT NULL,
cli_age
INTEGER NOT NULL,
cli_adresse VARCHAR(80),
cli_type VARCHAR(16),
cli_ca INTEGER,
cli_tremise INTEGER NOT NULL,
PRIMARY KEY (cli_num)) ;
25
Bernard ESPINASSE Elaboration dun MLD-R
26
Cration dune table (3) : cl trangre
Cration dune table (4) : cl trangre
Table COMMANDE (cmd_num, cmd_cli, cmd_date, cmd_statut)
Table ARTICLE (art_num, art_nom, art_four, art_stock, art_poids, art_pa, art_pv) ;
Cration de la table COMMANDE :
CREATE TABLE Commande
(cmd_num CHAR(8) NOT NULL,
cmd_cli
CHAR(8) NOT NULL,
cmd_date DATE NOT NULL,
cmd_statut
VARCHAR(16),
PRIMARY KEY (cmd_num),
FOREIGN KEY (cmd_cli) REFERENCES client);
Cration de la table ARTICLE :
CREATE TABLE Article
(art_num CHAR(8) NOT NULL,
art_nom
VARCHAR(25) NOT NULL,
art_four CHAR(8) NOT NULL,
art_stock INTEGER NOT NULL,
art_poids NUMERIC (8,1),
art_pa INTEGER NOT NULL,
art_pv INTEGER NOT NULL,
PRIMARY KEY (art_num),
FOREIGN KEY (art_four) REFERENCES Fournisseur);
PRIMARY KEY (cmd_num): lattribut cmd_num est cl primaire de la table
Commande.
FOREIGN KEY (cmd_cli) REFERENCES client: lattribut cmd_cli est une cl
trangre qui rfre la table Client
Remarques :
1- la table client doit dj exister
2- l'attribut cmd_cli de la table commande est du mme type que celui de la cl
primaire de la table client.
Bernard ESPINASSE Elaboration dun MLD-R
27
PRIMARY KEY (art_num): lattribut art_num est cl primaire de la table article.
FOREIGN KEY (art_four) REFERENCES Fournisseur: lattribut art_cli est une cl
trangre qui rfre la table Fournisseur.
Bernard ESPINASSE Elaboration dun MLD-R
28
Cration dune table (5) : cl primaire compose
Insertion denregistrement dans une table en SQL
Table LIGNE_CMD (lcd_cmd, lcd_art, lcd_qte, lcd_liv, lcd_pu)
Table CLIENT (cli_num, cli_nom, cli_age, cli_adresse, cli_type, cli_ca, cli_tremise)
CREATE TABLE Ligne_cmd
(lcd_art CHAR(8) NOT NULL,
lcd_cmd
INTEGER NOT NULL,
lcd_qte
INTEGER NOT NULL,
lcd_liv
INTEGER,
lcd_pu INTEGER NOT NULL,
FOREIGN KEY (lcd_cmd) REFERENCES Commande,
FOREIGN KEY (lcd_art) REFERENCES Article,
PRIMARY KEY (lcd_cmd, lcd_art)) ; <- Cl primaire
compose
FOREIGN KEY (lcd_cmd) REFERENCES commande: lattribut lcd_cmd est une cl
trangre qui rfre la table Commande.
FOREIGN KEY (lcd_art) REFERENCES commande: lattribut lcd_art est une cl
trangre qui rfre la table Article.
CREATE TABLE Client
(cli_num CHAR(8) NOT NULL,
cli_nom
CHAR(25) NOT NULL,
cli_age
INTEGER NOT NULL,
cli_adresse VARCHAR(80),
cli_type VARCHAR(16),
cli_ca INTEGER,
cli_tremise INTEGER NOT NULL,
PRIMARY KEY (cli_num)) ;
Insertion dun nouveau enregistrement dans la table Client :
INSERT INTO client VALUES (C2345, Tranvouez, 29,
Marseille, particulier, 3680, 25) ;
PRIMARY KEY (lcd_cmd, lcd_art): la cl primaire de la table Ligne_cmd est
compose des attributs lcd_cmd et lcd_art qui sont ici cls trangres.
Bernard ESPINASSE Elaboration dun MLD-R
29
Suppression denregistrement dans une table
Table CLIENT (cli_num, cli_nom, cli_age, cli_adresse, cli_type, cli_ca, cli_tremise) ;
CREATE TABLE Client
(cli_num CHAR(8) NOT NULL,
cli_nom
CHAR(25) NOT NULL,
cli_age
INTEGER NOT NULL,
cli_adresse VARCHAR(80),
cli_type VARCHAR(16),
cli_ca INTEGER,
cli_tremise INTEGER NOT NULL,
PRIMARY KEY (cli_num)) ;
30
Bernard ESPINASSE Elaboration dun MLD-R
Dime nsionne me nt dune BD Re lationne lle :
Multiplicits moyennes des liens relationnels
personne
possder
a1
r
a2
0,10
1,1
voiture
b1
b2
supposons une distribution triangulaire:
mode
0
mini
3 moyenne
10
maxi
selon la distribution, ici une loi triangulaire d'o moy = (mini+2(mod)+max)/4
d'o cardinalit moyenne = (0+2(3)+10)/4 = 4
si 10000 personnes, la relation "possder" aura 40000 tuples
voiture
personne
4
a1
b1
a2
b2
a1
10000
r
Suppression dun enregistrement dans la table client :
DELETE FROM client WHERE (cli_nom = Tranvouez);
40000
Bernard ESPINASSE Elaboration dun MLD-R
31
Bernard ESPINASSE Elaboration dun MLD-R
32
Propagation des multiplicits moyenne
personne
a1
0,3
a2
conduire
r
0,5
voiture
b1
b2
card. mode 1
card. mode 1
card moy = 5/4 = 1,25
card moy = 7/4 = 1,75
d'o:
nb personne x 1,25 = nb voiture x 1,75 soit:
voiture
b1
b2
personne
a1
a2
1000
conduire
1,25
a1
b1 r
r
1,75
715
1250 = n x 1,75
=> n= 1250/1,75
n = 715
1000 x 1,25 = 1250
Bernard ESPINASSE Elaboration dun MLD-R
33
