[Link]

Donsez/cours

Le Modèle Entité-Association (et UML)

et les Bases de Données Relationnelles

Didier DONSEZ
Université Joseph Fourier
PolyTech’Grenoble - LIG/ADELE
[Link]@[Link]
[Link]@[Link]
 25/09/2007
2

Motivations

Modèle Entité/Association (Entity/Relationship)

• point de départ aux MCD de nombreuses méthodes
• Merise, OMT, UML, …
• extension objet (héritage)

Modèle Relationnel
• modèle de base pour les SGBDs relationnels
• >70 % des applications SI
• théorie de la normalisation
• suppression des redondances
D. Donsez, EA et BD, 1998-2007

• mais il faut déterminer toutes les dépendances fonctionnelles

Transformation E/A vers le Relationnel
 25/09/2007
3
Modèle Entité-Association E/A [Chen76]
Entity-Relationship Model E/R

Concepts de Base
• Type d ’Entité (Entity Set)
• Type d ’Association (Relationship Set)
• Attribut (Attribute)
• Clé (Key) Etudiant Suivre Cours

numss nom adresse année code titre heures

Base pour de nombreux autres méthodologies
• Merise, OMT, Booch, …, Diagramme de classes UML (1 & 2)
D. Donsez, EA et BD, 1998-2007

0..n 0..n
Etudiant Cours
numss code
Suivre
nom titre
adresse année heure
 25/09/2007
4

Les Clés

Une entité a une et une seule clé

même si plusieurs clés sont candidates
• les attributs clé sont soulignés

Cours Cours

formation intitulé salle heure formation intitulé salle heure

D. Donsez, EA et BD, 1998-2007
 25/09/2007
5

Choisir une Clé

Naturelle (Natural)
• Attribut seul ou composition d ’attributs
• doit toujours être renseigné (NOT NULL)
• Remarque : Attribut clé représentant une composition
ISBN : numéro intl d ’éditeur + numéro d ’ouvrage
GENCOD, EAN, SKU : numéro intl de fabricant + numéro de produit
• clé d ’une entité faible

Artificielle (Surrogate)
• introduction d ’un attribut artificiel n ’appartenant pas au
D. Donsez, EA et BD, 1998-2007

système décrit.
• performance en comparaison
• compacité du stockage
souvent employé pour les bases décisionnelles
 25/09/2007
6

Cardinalités des Assocations

Multiplicity of Relationships

A B A B A B

Many-to-many Many-to-one One-to-one

N-M N-1 1-1
0,n - 0,n 0,n - 0,1 0,1 - 0,1
D. Donsez, EA et BD, 1998-2007

E/A A R B A R B A R B

0..n 0..n 0..n 0..1 0..1 0..1

UML A B A B A B
 25/09/2007
7

Associativité Many-to-One

Encadrant
Etudiants Enseignants
de Stage

Se lit : « Un étudiant est encadré Se lit : « Un enseignant encadre

que par un enseignant » plusieurs étudiants »
D. Donsez, EA et BD, 1998-2007
 25/09/2007
8

Association n-Aire

• Plusieurs entités peuvent participer à la même associations

Exemple d ’un association ternaire

Etudiants Enseigne TDs

numss nom adresse codcours titre coef

Assistants
D. Donsez, EA et BD, 1998-2007

numemp nom bureau

 25/09/2007
9
Modèle Entité-Association
Entity-Relationship Model

Etudiants Suivre Cours

année

Etudiants Suivre Cours

D. Donsez, EA et BD, 1998-2007

Année

année
 25/09/2007
10

Rôles dans une association

Rôles asymétrique / symétrique

Couple Ami
époux épouse 1 2

Personne Personne
D. Donsez, EA et BD, 1998-2007

époux épouse 1 2

Joseph Marie Pierre Paul

Sanson Dalida Paul Jean
 25/09/2007
11

Les Entités Faibles (Weak Entity)

Parfois, un attribut de la clé d ’une entité (dite faible)

provient d ’une autre entité

Exemple
Logins @ Machines

nom nom

Produit Instance de Modèle fabriquer Marque

D. Donsez, EA et BD, 1998-2007

nserie code nom

Remarque
• Certains attributs clés sont composés et forme la clé d’une entité faible
• ISBN, GENCOD, EAN, SKU, IBAN, …
 25/09/2007
13

Transformation E/A vers Relationnel

But
• Implantation du modèle E/A sur un SGBD réel

Méthode
• transformation
des associations n-aires en associations binaires
• 1 entité = 1 relation
• 1 association = dépendant de sa cardinalité

Cas de navigation restriente
D. Donsez, EA et BD, 1998-2007

appartient >
Employé Service
nemp 1..n 1 nom
nom adr
sal
 25/09/2007
14

Cas général : Association Many-to-Many

0..n 0..n
A R B A B
ka kb
cc B
ka aa kb bb aa bb
cc

Many-to-Many A ka aa R ka kb cc B kb bb
N-M ... ... ... ... ... ... ...

create table A ( create table R ( create table B (

ka …, aa …, ka …, kb …, cc …, kb …, bb …,
primary key(ka) primary key(ka,kb), primary key(kb)
D. Donsez, EA et BD, 1998-2007

) foreign key (ka) references A, )

foreign key (kb) references B
)
 25/09/2007
15

Cas d ’une Association Many-to-One

0..n 0..1
A R B A B
ka kb
Coût des jointures ! cc B
ka aa kb bb aa bb
cc

A ka aa R ka kb cc B kb bb
... ... ... ... ... ... ...
Many-to-One
N-1 A ka aa kb cc B kb bb
... ... ... ... ... ...
D. Donsez, EA et BD, 1998-2007

create table A ( create table B (

ka …, kb …, aa…, cc …, kb …, bb …,
primary key(ka), primary key(kb)
foreign key (kb) references B )
check(cc IS NULL
OR kb IS NOT NULL)
)
 25/09/2007
16

Cas d ’une Association One-to-One

0..1 0..1
A R B A B
ka kb
Coût des jointures ! cc B
ka aa kb bb aa bb
cc
A ka aa R ka kb cc B kb bb
One-to-One ... ... ... ... ... ... ...
1-1
A ka aa kb cc B kb bb
... ... ... ... ... ...
D. Donsez, EA et BD, 1998-2007

A ka aa B kb bb ka cc
... ... ... ... ... ...
 25/09/2007
17

Cas d ’une Association avec des Rôles

rôle 2 A ka aa R krole1 krole2 cc

... ... ... ... ...
A R
rôle1 create table A (
ka …, aa …, create table R (
ka aa cc primary key(ka) krole1 …, krole2 …, cc …,
) primary key(krole1, krole2),
foreign key (krole1) references A,
foreign key (krole2) references A
)

Rôle symétrique et One-To-One

D. Donsez, EA et BD, 1998-2007

• quel est le danger de cette modélisation ?

A ka aa krole2 cc Rôle Symétrique

et One-to-One
... ... ... ...
 25/09/2007
18

Cas de l ’entité faible (Weak Entity)

0..n 0..1
A B
A R B
ka kb
B
aa bb
ka aa cc kb bb cc

A ka aa kb cc B kb bb
... ... ... ... ... ...

create table A ( create table B (

ka …, kb …, kb …, bb …,
aa…, cc …, primary key(kb)
D. Donsez, EA et BD, 1998-2007

primary key(ka,kb), )
- - différent du cas N-to-1
foreign key (kb) references B
)

Cas de l’aggrégation en UML

 25/09/2007
19

Cas de navigation restreinte

La navigation est unidirectionnelle

Exemple
Héberge
Hotel Client
nom nom
adr adr
D. Donsez, EA et BD, 1998-2007
 25/09/2007
20

Exercices
de transformation E/A vers Relationnel

Exercice 1
• transformez les cas de l ’entité faible avec 3 tables A,
B, R

Exercice 2
• transformez le schéma suivant
• attention à l ’ordre des noms
Domaines Domaines
D. Donsez, EA et BD, 1998-2007

Machines @ @
Secondaires Primaires

nom nom nom

 25/09/2007
21

Exercices
de transformation E/A vers Relationnel

Exercice 3
• transformez le schéma suivant
#client
Client Préfère
nom

Date ∈
date
D. Donsez, EA et BD, 1998-2007

Produit ∈
R Modèle ∈
R Marque

#serie sav #mod design cc #mar nom

 25/09/2007
23

Le Cas des Sous Entités (i)

Employé Cadre Empl Cadre

nemp
nom prime
nemp nom sal prime sal

1 - Separation
Emp nemp nom sal Cadre nemp prime
100 Dupond 5000 200 10000
200 Durant 20000

[Link] est une clé étrangère sur Emp

2 - Push down
D. Donsez, EA et BD, 1998-2007

Emp nemp nom sal Cadre nemp nom sal prime

100 Dupond 5000 200 Durant 20000 10000

CI : {[Link]}∩
∩ {[Link]} = ∅
 25/09/2007
25

Le Cas des Sous Entités (ii)

Employé Cadre Empl Cadre

nemp
nom prime
nemp nom sal prime sal

3 - Push Up

Emp nemp nom sal prime

100 Dupond 5000 NULL
200 Durant 20000 10000
D. Donsez, EA et BD, 1998-2007
 25/09/2007
27

Le Cas des Sous Entités (iii)

Inconvénients

1 • Jointure pour reconstituer Cadre

2 • Union pour reconstituer Employé

3 • Pas de distinction entre un Employée

et un Cadre avec une prime NULL
D. Donsez, EA et BD, 1998-2007

• en général, pas d ’abstraction dans les Traitements

 25/09/2007
28

Amélioration du Cas 3 - Push Up

Employé Cadre Empl Cadre

nemp
nom prime
nemp nom sal prime sal

4
Emp typemp nemp nom sal prime Emp typemp
EMP 100 Dupond 5000 NULL EMP
CAD 200 Durant 20000 10000 CAD
[Link] est une clé étrangère
CI : [Link] =EMP ⇒ prime=NULL
5
Emp typemp
D. Donsez, EA et BD, 1998-2007

nemp nom sal prime

EMP 100 Dupond 5000 NULL
CAD 200 Durant 20000 10000

Domaine typemp, CD typeemp IN (EMP,CAD)

CI : [Link] =EMP ⇒ prime=NULL
 25/09/2007
30

Entité Abstraite

Employé : entité abstraite (notion dans Java, C#, C++, UML, ...)
• pas de tuple (instance) Employé
Cadre prime

Employé

nemp nom sal Ouvrier equipe

Emp typemp nemp nom sal prime equipe

OUV 100 Dupond 5000 NULL Nuit
CAD 200 Durant 20000 10000 NULL
D. Donsez, EA et BD, 1998-2007

Domaine typemp
CD typeemp IN (OUV,CAD)
CI : [Link] !=CAD ⇒ prime=NULL
CI : [Link] !=OUV ⇒ equipe=NULL
 25/09/2007
32
Transformation E/A vers Relationnel
L ’évolution de Schéma

Inconvénients
• Ajout d ’une sous entité Dirigeant

Employé Cadre Dirigeant

nemp nom sal prime

• Impacts Département
sur le schéma
sur les contraintes d ’intégrité
D. Donsez, EA et BD, 1998-2007

sur les contraintes de domaines

sur les traitements

Solution : l ’Objet-Relationnel ou l ’Objet Pur
 25/09/2007
33

Transformation E/A
vers l ’Objet-Relationnel

But
• Implantation du modèle E/A
sur un SGBD Objet-Relationnel

Fonctionnalités disponibles
• pour les Sous-Entités
• Héritage de Type et Héritage de Table
• pour les Associations
• table indépendante ou collection imbriquée (tableau dimensionnable,
table imbriquée)
D. Donsez, EA et BD, 1998-2007

• clé étrangère ou référence d ’objet

• nombreuses solutions [Soutou99] pp84,93,103-105

 25/09/2007
34

Héritage

Héritage de Table
Mettre en correspondance les Sous-Entités avec des Sous
Tables

Héritage de Type
• Même problème que pour le relationnel pur
• Utilisation des sous types dans le Push-Down
D. Donsez, EA et BD, 1998-2007
 25/09/2007
35
Cas général :
Association Many-to-Many (i)
0..n 0..n
A R B A B
ka kb
cc B
ka aa kb bb aa bb
cc

Many-to-Many A ka aa R ra rb cc B kb bb
N-M ... ... ... ... ...

create type tA as object ( create table R ( create type tB as object (

ka …, aa …, ra REF(tA), rb REF(tB), kb …, bb …,
); cc …, );
D. Donsez, EA et BD, 1998-2007

create table A of tA ( primary key(ra,rb), create table B of tB (

primary key(ka) SCOPE FOR ra IS A, primary key(kb)
); SCOPE FOR rb IS B );
);
 25/09/2007
36
Cas général :
Association Many-to-Many (ii)
0..n 0..n
Attention à la mise A R B A B
à jour des listes de refs ka kb
=> risque d ’incohérence ka aa kb bb aa bb

Many-to-Many A ka aa {rbs} B kb bb {ras}

N-M ... ... ... ...

create type tB as object; create or replace type tB

create type tA as object( as object (
ka …, aa …, kb …, bb …,
D. Donsez, EA et BD, 1998-2007

rbs varray(10) of REF(tB), ras varray(10) of REF(tA),

); );
create table A of tA ( create table B of tB (
primary key(ka), primary key(kb)
); );
 25/09/2007
37
Cas général :
Association Many-to-Many (ii)
En UML: sens unidirectionnel de navigation (B vers A)

A B
ka kb
aa bb
Many-to-Many A ka aa B kb bb {ras}
N-M ... ... ... ...

create type tA as object( create type tB as object (

ka …, aa … kb …, bb …,
); ras varray(10) of REF(tA)
D. Donsez, EA et BD, 1998-2007

create table A of tA ( );
primary key(ka), create table B of tB (
); primary key(kb)
);
 25/09/2007
38
Cas général :
Association One-To-One

A R B

ka aa kb bb

One-To-One A ka aa rb B kb bb ra
1,1 ... ... ... ...

create type tB as object; create or replace type tB as object (

create type tA as object ( kb …, bb …, ra REF(tA)
ka …, aa …, rb REF(tB) );
D. Donsez, EA et BD, 1998-2007

); create table B of tB (
create table A of tA ( primary key(kb),
primary key(ka), SCOPE FOR ra IS A
SCOPE FOR rb IS B );
);
 25/09/2007
39

Bibliographie

Roger Mounyol, "Merise par l'Exemple", Ed Ellispes, 1991, ISBN 2-7298-9114-5

Chris Date, "Introduction aux Bases de Données", 6ème édition, Ed Intl Thomson Publ.
ISBN 2-84180-964-1, 970 pp

Jeffrey D. Ullman, Jennifer Widom, "A First Course in Database Systems", 1ère édition,
Ed. Prentice Hall Engineering, Science & Math, Avril 1997, ISBN 0-13-861337-0, 470 pp.

PPS Chen. The Entity-Relationship model toward a unified view of data. ACM
Transactions on Database Systems, March 1976.

Michael Blaha, William Premerlani. Object-Oriented Modeling and Design for Database
Applications, Prentice Hall, Englewood Cliffs, New Jersey, 1998.
• [Link]

Christian Soutou, "Objet-Relationnel sous Oracle8, Modélisation avec UML", Ed Eyrolles,
1999, ISBN 2-212-09063-3
• décrit bien les alternatives de conception des associations avec l ’objet-relationnel et avec le relationnel

Christian Soutou, "De UML à SQL : Conception de bases de données", Ed Eyrolles,
D. Donsez, EA et BD, 1998-2007

2002, ISBN 2-212-11098-7

• La mise à jour du précedent

Using UML to Design Database Applications
• [Link]
• [Link]
 25/09/2007
40

Annexe : Zoo de méthodes

D. Donsez, EA et BD, 1998-2007

dépendances d’après HDR Bruel