Module 1 : Les base de données relationnelles
Section 2 : Le Schéma
Logique d'une Base de
Données
Riadh ZAAFRANI
Octobre 2020
1ère année MP2L
1
Objectifs du cours
Ce cours va vous permettre de :
Connaître
les
concepts
relatifs
aux
schémas relationnels.
Maîtriser
les règles de transformation
d'un schéma conceptuel entité-relation en
un schéma relationnel.
2
1
Plan
Conception logique relationnelle
Démarche de transformation d’un
schéma EER en un schéma
relationnel
Exemple de base de données
relationnelle
3
Conception logique relationnelle
Conçu par E.F. Codd au début des années 70,
le modèle relationnel allie la simplicité de
représentation des données au moyen de
tables à une définition rigoureuse des concepts
fondée sur
la théorie mathématique des
relations.
Le modèle relationnel s’appuie sur 3 concepts
la relation ou
le domaine,
fondamentaux :
table, et l’attribut.
4
2
Conception logique relationnelle
On appelle domaine un ensemble de
valeurs défini en extension ou en intension.
Par exemple,
l’ensemble des grades du
salarié peut être défini en extension par
employé, agent de maîtrise, ou cadre.
Le salaire d’un salarié peut être défini en
intension par la plage des valeurs réelles
comprises entre 250 et 1500 DT.
5
Conception logique relationnelle
Un domaine peut être simple ou composé.
Il est dit simple si tous ses éléments sont atomiques
le cas des 2
ou encore indécomposables. C’est
exemples précédents.
Un domaine est dit composé si ses éléments peuvent
être décomposés. Par exemple,
les dates sont
composées d’un mois, un jour et une année.
Cependant, on peut
considérer qu’un
domaine n’est pas décomposable. Ainsi, la date peut
aussi être considérée comme un domaine simple.
toujours
6
3
Conception logique relationnelle
On appelle relation ou table un sous-ensemble du
produit cartésien d’une liste de domaines, non
nécessairement
tous distincts. En d’autres termes,
une relation est un tableau à deux dimensions.
Chaque colonne, appelée aussi attribut, contient un
sous-ensemble des valeurs d’un domaine. Un attribut
est donc un nom de colonne correspondant à une
relation et qui prend ses valeurs dans un domaine.
Deux attributs ne peuvent porter le même nom, même
s’ils partagent le même domaine.
7
Conception logique relationnelle
Chaque ligne représente un n_uplet ou t_uple,
formé d’un
plus fréquemment appelé tuple,
ensemble de n valeurs prises dans les n domaines
considérés.
Le degré de la relation est le nombre de colonnes
ou de domaines considéré.
On appelle schéma d’une relation le nom de la
relation suivi de la liste des attributs et la définition
de leurs domaines.
8
4
Conception logique relationnelle
Par exemple, la table Salarié est caractérisée par
le schéma relationnel suivant :
SALARIÉ (matricule : entier,
Nom : chaîne de caractères,
Grade : {employé, agent de maîtrise, cadre},
Salaire : [250,1500])
Parfois les domaines sont omis pour ne donner
que : SALARIÉ (matricule, nom, Grade, Salaire)
On appelle schéma relationnel l’ensemble des
9
relations d’une base de données.
Conception logique relationnelle
A l’ensemble des tables d’un schéma
relationnel, on associe généralement un
ensemble de contraintes d’intégrité.
Une
est
d’intégrité
contrainte
une
expression logique qui doit être vraie, à tout
moment, dans une base de données. Par
Publicité
exemple, deux employés ne peuvent avoir
la même matricule.
10
5
Conception logique relationnelle
Spécifier les contraintes d’intégrité permet d’en
assurer
le
SGBD et ainsi de contrôler la cohérence des
données.
la vérification systématique par
Il
existe
plusieurs
types de contraintes
d’intégrité (définition de domaine, contrainte de
clé, la contrainte obligatoire qui précise qu’un
valeur,
une
toujours
attribut
contrainte d’intégrité référentielle, etc.).
avoir
doit
11
Conception logique relationnelle
On peut ainsi définir de façon complète le
schéma d’une relation comme le nom de
la relation suivi de la liste des attributs, de la
de
définition
l’ensemble
d’intégrité
associées à cette table.
contraintes
domaines
leurs
des
de
et
12
6
Plan
Conception logique relationnelle
Démarche de transformation d’un
schéma EER en un schéma
relationnel
Exemple de base de données
relationnelle
13
Démarche de transformation d’un
schéma EER en un schéma relationnel
Règle 1. Toute entité est traduite en une
table relationnelle dont les caractéristiques
sont les suivantes :
le nom de la table est le nom de l’entité ;
la clé de la table est
l’identifiant de
l’entité ;
les autres attributs de l’entité forment
les
autres colonnes de la table.
14
7
Démarche de transformation d’un
schéma EER en un schéma relationnel
Règle 2. Toute relation binaire M-N est traduite en une
les
les caractéristiques sont
table relationnelle dont
suivantes :
le nom de la table est le nom de la relation ;
la clé de la table est formée par la concaténation des
identifiants des entités participant à la relation ;
les attributs spécifiques de la relation forment les autres
colonnes de la table.
Une contrainte d’intégrité référentielle est générée entre
chaque colonne clé de la nouvelle table et la table d’origine
de cette clé.
15
Démarche de transformation d’un
schéma EER en un schéma relationnel
Règle 3. Toute relation binaire 1-N est traduite :
1) soit par un report de clé : l’identifiant de
l’entité participant à la relation côté N est ajouté
comme colonne supplémentaire à la table
représentant
l’autre entité. Cette colonne est
appelée clé étrangère.
Le cas échéant,
les attributs spécifiques à la
relation sont eux aussi ajoutés à la même table ;
16
8
Démarche de transformation d’un
schéma EER en un schéma relationnel
2) soit par une table dont les caractéristiques
sont les suivantes :
le nom de la table est le nom de la relation ;
la
clé
de
la
table
est
l’identifiant
de
l’entité participant à la relation côté 1;
la clé de la table côté N et les attributs spécifiques
de la relation forment les autres colonnes de la
table.
Démarche de transformation d’un
schéma EER en un schéma relationnel
Personne
Service
0-1
travaille dans
un
1-N
Salle
0-1
Projection
Publicité
Heure
0-N
DemandePrêt
1-1
Effectue
1-N
Film
Client
17
18
9
Démarche de transformation d’un
schéma EER en un schéma relationnel
De plus, une contrainte d’intégrité référentielle est
générée.
Le choix du mode de traduction (report de clé ou
dicté par des
table spécifique) peut être
considérations liées aux traitements futurs sur la
base.
Même si
l’on ne dispose pas nécessairement
d’une spécification détaillée de ces traitements, on
peut partiellement les anticiper par l’analyse plus
approfondie de cette relation.
19
Démarche de transformation d’un
schéma EER en un schéma relationnel
Un élément d’appréciation de ces traitements est
la cardinalité minimale de l’entité côté 1.
Si cette cardinalité minimale est égale à 1, il est
le report de clé, dont
préférable d’opter pour
l’avantage est de minimiser le nombre de tables.
A noter que l’effet de cette minimisation n’est pas
tant d’économiser l’espace disque que d’améliorer
les performances de la base en réduisant
le
nombre de jointures entre tables.
20
10
Démarche de transformation d’un
schéma EER en un schéma relationnel
Si la cardinalité minimale est égale à 0, on examinera
la proportion d’entités participant effectivement à la
relation.
Si cette proportion est importante, on peut en déduire
que la relation l’est aussi et opter encore pour le report
de clé.
En revanche, si cette proportion est
relativement
faible, il est préférable d’opter pour la table spécifique,
dans la mesure où l’on peut penser que cette faible
proportion exprime une importance relative dans
l’application.
21
Démarche de transformation d’un
schéma EER en un schéma relationnel
Règle 4. Toute relation binaire 1-1 est traduite, au
choix, par l’une des 3 solutions suivantes :
fusion des tables des entités qu’elle relie (choix 1) ;
report de clé d’une table dans l’autre (choix 2) ;
création d’une table spécifique reliant les clés des
2 entités (choix 3).
DemandePrêt
Prêt
0-1
Donne_lieu
1-1
22
11
Démarche de transformation d’un
schéma EER en un schéma relationnel
Comme dans la règle précédente, le choix du mode de
traduction dépend des cardinalités minimales. Trois cas
sont alors possibles :
les deux cardinalités minimales sont égales à 1 : la
relation forme une bijection entre les 2 ensembles d’entités.
Alors, la meilleure traduction est la fusion (choix 1) ;
l’une des deux est égale à 0, l’autre est 1 : l’une des 2
entités a toujours une entité correspondante dans l’autre
ensemble. Alors, la meilleure traduction est le report de clé
de l’entité dont la participation minimale est 0 dans la table
représentant l’autre entité ;
23
Démarche de transformation d’un
schéma EER en un schéma relationnel
les 2 cardinalités minimales sont égales à 0.
Il est
recommandé d’examiner la proportion d’entités participant
effectivement à la relation.
Si cette proportion est
importante pour les 2 entités, on
optera pour la fusion (choix 1).
En revanche, si cette proportion est importante pour l’une
des entités, mais secondaire pour l’autre, il est préférable
d’opter pour un report de clé (choix 2).
Enfin, si cette proportion est faible pour les deux entités, la
table spécifique est la traduction la plus appropriée (choix 3).
24
12
Démarche de transformation d’un
schéma EER en un schéma relationnel
Règle 5. Toute relation ternaire ou plus est
traduite par une table spécifique.
Sauf cas particulier, la clé de cette table est
des
la
constituée
identifiants des entités de cette table.
concaténation
par
Pour
chaque identifiant, une contrainte
d’intégrité référentielle est générée.
25
Démarche de transformation d’un
schéma EER en un schéma relationnel
Règle 6. Toute généralisation de E de n entités E1, E2,…
En peut être traduite au choix par l’une des trois solutions
suivantes :
1)
Publicité
la création d’une seule table représentant
l’entité
générique E et intégrant tous les attributs des entités
spécifiques. Les relations éventuelles impliquant ces entités
sont alors considérés comme impliquant l’entité E avec une
cardinalité minimale nulle. La spécialisation est traduite par
l’introduction d’un attribut supplémentaire dont
l’ensemble
des valeurs possibles sera l’ensemble des noms des entités
spécifiques ;
26
13
Démarche de transformation d’un
schéma EER en un schéma relationnel
2)
les
la création de n tables représentant
entités E1, E2, …En qui héritent de l’ensemble des
attributs et des relations de l’entité générique E ;
3) la création conjointe des tables E, E1, E2,…
En. On peut considérer que l’entité générique et ses
spécifiques sont reliées par des relations 1-1. On
est alors ramené à la règle 4 pour les différentes
traductions de ces relations.
27
Démarche de transformation d’un
schéma EER en un schéma relationnel
Le choix entre ces trois solutions est dicté par la nature des
traitements les plus courants.
Si ces derniers portent essentiellement sur
attributs
indépendamment
spécifiques
générique, on préférera alors la solution 2.
des
les attributs
de
l’entité
Si,
au
contraire,
opèrent
conjointement sur tous les attributs, qu’ils soient au niveau
générique ou au niveau spécifique, la solution 1 est préférable.
traitements
plupart
des
la
La solution 3 est la seule qui préserve toute la sémantique.
Cependant, elle est, la plupart du temps, trop complexe pour
être recommandée.
28
14
Démarche de transformation d’un
schéma EER en un schéma relationnel
Règle
7. Toute
réflexive
considérée comme une relation binaire.
relation
est
Sa traduction dépend donc du type de cette
relation binaire (1-1, 1-N ou M-N) et obéit à
l’une des règles 2,3 ou 4.
29
Démarche de transformation d’un
schéma EER en un schéma relationnel
Si, à la fin du processus de traduction de
l’ensemble du schéma ER, on obtient des
tables ne comportant qu’une seule colonne,
on ne représentera pas ces tables qui ne sont
pas porteuses d’information spécifique.
30
15
Plan
Conception logique relationnelle
Démarche de transformation d’un
schéma EER en un schéma
relationnel
Exemple de base de données
relationnelle
31
Exemple de base de données relationnelle
A titre d'exemple, considérons le cas d'un MCD
rudimentaire, utilisant 2 entités Fournisseurs et
Produits et une association Commandes
Fournisseurs
numFrs
nom
adresse
ville
1-n
Commandes
1-n
numCde, qté
Produits
numProd
design
prix
poids
couleur
MCD Fournisseur-Produit-Commande
32
16
Exemple de base de données relationnelle
L'association commandes est un lien maillé porteur
d'une propriété.
Il y a donc création d'une table
commande avec report des clés des entités liées, ce qui
nous donne bien un MLD, utilisant 3 tables représentant
les commandes de produits à des fournisseurs.
Le schéma de cette base est donc :
produits(numProd, design, prix, poids, couleur)
fournisseurs(numFrs, nom, adresse, ville)
commandes(numCde, #numFrs, #numProd, qté)
33
Exemple de base de données relationnelle
Produits
numProd
design
prix
poids
Publicité
couleur
102
103
101
105
104
107
106
108
fauteuil
bureau
fauteuil
armoire
bureau
caisson
caisson
classeur
1500
3500
2000
2500
4000
1000
1000
1500
9
30
7
35
40
12
12
20
rouge
vert
gris
rouge
gris
jaune
gris
bleu
34
17
Exemple de base de données relationnelle
Fournisseurs
numFrs
10
15
17
12
14
13
11
19
16
nom
Ouerghi
Jaïbi
Ben mahmoud
Lahmar
Kouki
Jaïbi
Kouki
Ghorbel
Ouerghi
ville
Tunis
Tunis
Tunis
Sfax
Beja
Sfax
Nefza
Jendouba
Jendouba
35
Exemple de base de données relationnelle
commandes
numCde numFrs numProd qte
10
1001
2
1003
1
1005
1
1007
12
1011
5
1013
3
1017
10
1019
8
1023
15
1029
103
103
102
108
107
107
105
103
102
108
17
15
17
15
19
13
19
14
10
17
36
18