Rappels sur le relationnel

Plan
Rappels sur les bases de données
1. Niveaux de description d’une base de données
• Niveau « Réel perçu » et « Niveau Interne ou Logique »
• Niveau « Conceptuel » et « Externe »
relationnelles
2. Rappel sur le modèle Entité-Relation (E-R)
• Historique, concepts, graphisme
• Cardinalités (ou multiplicités)
• Dépendances fonctionnelles
Bernard ESPINASSE
Professeur à l'Université d'Aix-Marseille
3. Dérivation d’un MLD-R à partir d’un MCD en Entité-Relation
Septembre 2015
Problématique du MLD
Formalisme graphique de Merise
Dérivation d’un MLD-R à partir d’un MCD en Entité-Relation
Création de tables en langage SQL (clé primaires et étrangères)
• Rappel sur le modèle Entité-Relation (E-R)
•
•
•
•
• Dérivation d’un MLD-R à partir d’un MCD en Entité-Relation
4. Dimensionnement d’une BD Relationnelle
• Dimensionnement d’une BD Relationnelle
• Multiplicités moyennes des liens relationnels et propagation des multiplicités moyenne
• Calculs cumulés des volumes des tables
• Niveaux de description d’une base de données
• Sous schéma de données et valorisation de l'activité des traitements
sur la BD
5. Sous-schéma de données, valorisation de l'activité des traitements sur
la BD
• Transformation de sous-schémas conceptuelsen sous-schémas logiques
• Valorisation des primitives logiques
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
1
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
2
Nive aux « Ré e l pe rçu » e t « Nive au Inte rne ou
logique » (1)
Niveau « Réel perçu » :
• Représentation du réel que l'on se construit selon les finalités, le
phénomène observé
1. Nive aux de de scription d’une
base de donné e s
• Exprimé dans le langage naturel + vocabulaire du domaine étudié.
Niveau « interne ou logique » :
• Niveau « Réel perçu » et « Niveau Interne ou Logique »
• Niveau « Conceptuel » et « Externe »
• Définit dans le système informatique la réalisation de la structure de
données selon le SGBD choisi (notamment relationnel) et d’objectifs
d’optimisation
• Exprimé dans le formalisme informatique lié à l'outil informatique :
Modèle Interne (ou logique) de Données (MLD)
Modèle Logique de Données Relationnel (MLDR)
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
3
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
4
Nive aux « Ré e l pe rçu » e t « Nive au Inte rne ou
logique » (2)
Niveaux « Conceptuel » et « Externe » (1)
• Emergence des niveaux Conceptuel et Externe :
Réel
Réel
Réel perçu
Réel perçu
difficile !
modèle
interne
administrateur de
la base de données
modèle
conceptuel
SGBD
administrateur de
la base de données
base de données
programmes
modèles
modèles
modèles
externes
modèles
externes
modèles
externes
externes
externes
modèle
interne
Grande difficulté à décrire le « réel perçu » dans un « modèle
interne » informatique :
-> émergence d'un niveau intermédiaire (rapport ANSI/Sparc
1975) : le niveau conceptuel
programmeur d'application
administrateurs de
fonctions
SGBD
base de données
programmes
Remarque : définition des rôles dans l'organisation
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
5
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
6
Niveaux « Conceptuel » et « Externe » (2)
• Description en termes d'objets, propriétés et relations du réel perçu,
permettant :
! l’unicité et stabilité la structure de mémorisation des
informations
! la rencontre gens du domaine/informaticiens
• Exprimé dans un formalisme plutôt naturel (pour gens du domaine)
tout en étant rigoureux (pour les informaticiens):
Modèle Conceptuel de Données (MCD): exprimé en formalisme EntitéRelation (E-R)
Niveau externe :
• Description en terme d'objets, propriétés et relations d'une utilisation
particulière d'information du domaine :
Modèle Externe (MED) : exprimé dans le même formalisme qu'au niveau
conceptuel
On parlera souvent de « Sous-Schémas conceptuels externes »
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
7
2. Rappe l sur le modè le Entité Re lation (E-R)
• Historique, concepts, graphisme
• Cardinalités (ou multiplicités)
• Dépendances fonctionnelles
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
8
Historique
Graphisme
• pour l'élaboration des modèles de données (MCD):
3 concepts de base :
• PROPRIETE :
• ENTITE (individu):
• RELATION (association):
• nécessité d'un formalisme graphique
• approche ENTITE-RELATION (P.Chen 75)
• formalisme INDIVIDUEL (D.Nanci, D.Pascot, H.Tardieu 75)
• norme ISO: ENTITY-RELATIONSHIP
description
structure
structure
Une représentation graphique :
entité
• 3 concepts de base :
• PROPRIETE
• ENTITE
• RELATION (association)
relation
PERSONNE
nom
prénom
age
LOGEMENT
habiter
1,n
cardinalités
9
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
Propriété
0,n
adresse
type
surface
propriété
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
10
Entité
Propriété = particule élémentaire d'information
nom
TYPE
Entité = représentation d'une famille d'objets distinguables perçus comme
stables et homogènes
valeur
OCCURENCE
couleur ...............................
rouge
bleu
vert
âge ......................................
22
23
24
56
PERSONNE
nom
prénom
adresse
rue
code postal
ville
attention !!!! : dans un modèle de données, on ne représente pas les
valeurs mais les TYPES des valeurs
Propriété composée : adresse : rue, code postal, ville
PERSONNE
nom
prénom
adresse
rue
code postal
ville
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
11
Règles :
1) pour toute occurrence d'entité, il y a au plus une valeur pour chacune de ses
propriétés (stabilité)
2) au moins une propriété, identifiante, permet de distinguer les occurrences d'entité
entre elles (distinguabilité)
3) toute propriété doit avoir un sens pour toute occurrence d'entité même si elle est
inconnue à un certain moment (homogénéité)
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
12
Entité
• Types/occurrences :
Re lation
Relation = classe d'information définies par rapport à un ensemble d'entités
Pensées
Guerre
et
Paix
Guerre
et
Paix
8
2
3
5
1
2
Conseil : les nommer par un verbe à l’infinitif (ACHETER)
Règles :
1) les relations n’ont pas d'existence propre :
la relation "ACHETER" n'existe que si des occurrences d'entités "PERSONNE" et
"VOITURE" existent
2) elles peuvent avoir des propriétés propres:
date achat
3) elles sont identifiées par les identifiants de leur collection :
! collection: {entités participant à la relation}
! dimension: nb d'entité(s) de la collection
Combien y a-t-il de livres?
• Homogénéité :
4553
DUVAL
Alice
DUPONT
23
personne
matricule
nom
prénom
nom de j.fille
âge
....
6787
BERGMAN
Jean
?
30
Attention, distinguer:
propriétés non significatives, valeur inconnue, valeur nulle,...
13
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
14
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
V a rVi éatréi édt é
e sd reesl ar e
t iloant iso n s
V a• Partage
r iVéd'une
r se lracollection
at réi d'une
éd
te
é sdmême
ecollection
etliaot:n
i osn:s
• Partage
même
R e lR
ae
t iloant i o n
Exemple :
Exemple :
• Partage
même
collection
: possèder
• Partage
d'uned'une
même
collection
:possèder
ETUDIANT
nom
prénom
date naissance
1,n
posséder
année
mention
0,n
DIPLOME
0,n
titre
niveau
possèder
0,n
PERSONNE
PERSONNE
nom
PERSONNE
nom
1,n
prénom prénom
nom
1,1
1,1
1,n
1,n
1,nhabiter
prénom
LOGEMENT
LOGEMENT
adresse
LOGEMENT
adresse
1,1
0,n
1,n
habiter
adresse
1,n
habiter
• Relation
sursur
une
même
entité
: : :
• Relation
sur
une
même
entité
• Relation
une
même
entité
! collection: {ETUDIANT, DIPLOME}
! collection:
! dimension:
2 {ETUDIANT, DIPLOME}
!
dimension:
! identifiants: (nom)2 x (titre)
! identifiants: (nom) x (titre)
• Relation sur une même entité :
0,n
mère
mère
0,n mère
0,n
SOCIETE SOCIETE
SOCIETE
détenir détenir
détenir
N° SIRENN°
N° SIREN
SIREN
raison sociale
raison sociale
%%
capital
raison
sociale % capital
capital
capital
capital capital
Un graphe des occurrences possible :
Un graphe des occurrences possible :
0,n
filiale0,n0,n filiale
filiale
• Relation
de dimension
quelconque
• Relation
dede
dimension
quelconque
• Relation
de dimension
quelconque
: : ::
• Relation
dimension
quelconque
JOURNEE
JOURNEEJOURNEE
date
date
date
ENGIN
CHANTIER
ENGIN n°
ENGIN
engin
n° engin n°désignation
engin
désignationdésignation
0,n
0,n
ACTIVITE
ACTIVITE
type activité
libellé
type activité
type activité
d'oeuvre
libellé unitélibellé
unité d'oeuvre
unité d'oeuvre
0,n
0,n
0,n
0,n
ACTIVITE
CHANTIER
n°CHANTIER
chantier
désignation
n° chantier
n° chantier
désignationdésignation
0,n
travail réalisé
0,n
quantité
0,n
0,n
travail réalisé
travail réalisé
quantité
quantité
Bernard ESPINASSE - Modèle Entité-Relation : la base
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
Bernard ESPINASSE - Modèle Entité-Relation : la base
15
9
Bernard
ESPINASSE
- Modèle
Entité-Relation
la base
Bernard
ESPINASSE
- Modèle
Entité-Relation
: la base
Bernard
ESPINASSE
– Rappels
sur les
Bases: de
données
relationnelles
0,n
0,n
10
1610
10
Cardinalité (ou multiplicité) d’une entité dans
une relation (1)
Cardinalité ou multiplicité
Cardinalité d’une entité dans une relation (1)
: caractérise le rôle d'une entité dans une relation
• permet d'enrichir le modèle (niveau des types) en connaissances du niveau des
occurrences
x1 et x2 = cardinalités minimales
et
y1 et y2 = cardinalités maximales
! 0, n : une occurrence de CLIENT est en relation par la relation PASSER avec une
ou plusieurs occurrences de COMMANDE
Cardinalités fréquemment utilisées :
Participation
Optionnelle
Obligatoire
Unique
0,1
1,1
Multiple
0,n
1,n
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
! 1, 1 : une occurrence de COMMANDE est en relation par la relation PASSER
avec une et une seule occurrence de CLIENT
17
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
18
Cardinalité d’une entité dans une relation (3)
Cardinalité d’une entité dans une relation (2)
Cardinalités sur relation n-aire
! 1, n : une occurrence de COMMANDE est en relation par la relation
CONCERNER avec une ou plusieurs occurrences de PRODUIT
! 0, n : une occurrence de PRODUIT est en relation par la relation CONCERNER
avec aucune ou plusieurs occurrences de COMMANDE
un graphe des occurrences possible :
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
! 1 ) (0, n) : une occurrence de PROFESSEUR est en relation par la relation
ENSEIGNER avec aucun ou plusieurs couples d’occurrences (SALLE, PERIODE)
! 2 ) (0, n) : une occurrence de PERIODE est en relation par la relation
ENSEIGNER avec aucun ou plusieurs couples d’occurrences (PERIODE, SALLE)
! 3 ) (0, n) : une occurrence de SALLE est en relation par la relation ENSEIGNER
avec aucun ou plusieurs couples d’occurrences (PROFESSEUR, PERIODE)
19
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
20
Dépendances fonctionnelles
D
ess ffoonnccttiioonnnneelllleess
D ééppContraintes
eennddaanncce
d'intégrité
fonctionnelle
Modèles génériques de cardinalités individuelles
Modèles génériques de cardinalités individuelles
Contraintes d'intégrité fonctionnelle
mariage conventionnel
polyandrie
mariage
homme
femme
mariage
homme
0,1
0,1
1234 PX 13
1234 PX 13
femme
mariage
homme
0,1
0,n
soit:
soit:
soit:
femme
a
pére
0,n
voiture -------------->
--------------> personne
personne
voiture
voiture --------------> personne
appartient
appartient
appartient
0,n
pére / fils
Représentation::
Représentation
Représentation :
enfant
voiture
voiture
1,1
Bernard
Entité-Relation
: ladonnées
base relationnelles
BernardESPINASSE
ESPINASSE- –Modèle
Rappels
sur les Bases de
21 15
Jean LAFRANCE
Jean LAFRANCE
une voiture n'appartient qu'à une personne
une voiture
voiture n'appartient
n'appartient qu'à
qu'à une
une personne
personne
une
mariage de groupe
mariage
0,n
appartient
appartient
femme
0,n
0,1
polygamie
homme
Contraintes d'intégrité
fonctionnelle
Relation binaire fonctionnelle
:
Relation
Relationbinaire
binairefonctionnelle
fonctionnelle: :
...,1
...,1
appartenir
appartenir
personne
...,... personne
...,...
flèche
flèche
Bernard ESPINASSE - Modèle Entité-Relation : la base
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
Bernard ESPINASSE - Modèle Entité-Relation : la base
22 16
16
Dépendances fonctionnelles sur relations n-aire
Soit la contrainte à exprimer :
« pour toute période d'emploi du temps, (le mercredi de 9 h à 12 h), un professeur n'a
cours que dans une seule salle »
soit: PERODE x PROFESSEUR -----ENSEIGNER------> SALLE
3. Dé rivation d’un MLD-R à partir
d’un MCD e n Entité -Re lation
Limitation du formalisme Entité-Relation de base => besoin d’extension
•
•
•
•
Problématique du MLD
Formalisme graphique de Merise
Dérivation d’un MLD-R à partir d’un MCD en Entité-Relation
Création de tables en langage SQL (clé primaires et étrangères)
Types de dépendances fonctionnelles :
• simples (à un émetteur: a --> b)
• composées (à n émetteurs: a x b --> c) englobant pas la totalité de la collection
• composées (à n émetteurs: a x b --> c) englobant la totalité de la collection
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
23
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
24
Problématique du MLD
Dé marche d’é laboration d’un MLD Re lationne l
Modèle Conceptuel de Données (MCD) :
- permet de modéliser la sémantique des informations d’une façon
compréhensible par l’utilisateur de la future base de données
- utilise le formalisme (graphique) Entité-Relation
- ne permet pas d’implémentation informatique de la base de
données dans un SGBD donné
! MCD : Modèle Conceptuel de Données
! MLD-R : Modèle Logique de Données Relationnel
MCD
NIVEAU CONCEPTUEL
En formalisme Entité-Relation
Modèle Logique de Données (MLD) :
- permet de modéliser la structure selon laquelle les données
seront stockées dans la future base de données
- est adapté à une famille de SGBD : ici les SGBD relationnels (MLD
Relationnels ou MLD-R)
- utilise le formalisme graphique Merise
- permet d’implémenter la base de données dans un SGBD donné
En formalisme « Merise »
Création des tables de la base
de données en langage SQL
NIVEAU PHYSIQUE
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
25
MLD (Relationnel)
NIVEAU LOGIQUE
!
SGBD Relationnel
26
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
Formalisme graphique “Merise” pour le MLD-R (1)
Formalisme graphique “Merise” pour le MLDR (2)
Table
Lien entre tables : contrainte d’intégrité référentielle
Table EMPLOYE (représentation graphique) :
Table EMPLOYE (représentation graphique) :
EMPLOYE
EMPLOYE
Matricule
Nom_departement
Nom
Age
Adresse
Matricule
Nom
Age
Adresse
Schéma de la table EMPLOYE :
EMPLOYE (Matricule, nom, age, adresse)
Nom_departement
Effectif
Schémas des tables :
! Table DEPARTEMENT (Nom_departement, Effectif) :
! Nom_departement : clé primaire
Attributs de la table EMPLOYE :
! Matricule : clé primaire
! Nom, Age, Adresse : autres attributs
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
DEPARTEMENT
Appartenir
! Table EMPLOYE (Matricule, Nom_departement, Nom, Age, Adresse)
! Matricule: clé primaire
! Nom_departement : clé étrangère vers table DEPARTEMENT
27
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
28
Formalisme graphique “Merise” du MLD-R (3)
Formalisme graphique “Merise” du MLD-R (4)
Lien entre tables : clé primaire composée référentielle
Lien entre tables : contraintes d’intégrité référentielle réflexive
TACHE
N°ordre
N°projet
durée
TACHE
Précéder
PROJET
Concerner
N°tache
N°tache précédente
Désignation
Durée
N°projet
budget
Schémas des tables :
Schémas des tables :
! Table PROJET (N°projet, budget)
! N°projet : clé primaire
! Table TACHE (n°tache, n°tache_précédente, désignation, durée)
! N°tache : clé primaire
! Table TACHE (N°ordre, N°projet, durée)
! N°tache_précédente: clé étrangère vers table TACHE
! N°ordre, n°projet : clé primaire composée
! N°projet : clé étrangère vers table PROJET
29
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
MCD -> MLD : dérivation des entités
Dérivation d’un MLD-R à partir d’un MCD en
Entité -Re lation
Règle : toute entité du MCD se dérive en une table du MLD
Entité
MCD
NIVEAU CONCEPTUEL
En formalisme Entité-Relation
EMPLOYE
Matricule
Nom
Age
Adresse
Ensemble de règles
MLD (Relationnel)
NIVEAU LOGIQUE
30
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
Table
EMPLOYE
Matricule
Nom
Age
Adresse
En formalisme « Merise »
la propriété identifiante de l’entité devient
la clé primaire de la table
Création des tables de la base
de données en langage SQL
NIVEAU PHYSIQUE
!
SGBD Relationnel
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
31
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
32
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
Date d’acquisition
0,N
MLD :
EMPLOYE
Matricule
Nom_departement
Nom
Age
Adresse
DEPARTEMENT
Appartenir
PERSONNE
Nom_departement
Effectif
MLD :
VOITURE
Posseder
Nom
Prenom
Adresse
P
o
s
s
Posseder e
d
e
r
0,1
Numero
Marque
Type
VOITURE
Numero
Nom
Marque
Type
Date d’acquisition
Schémas relationnels :
! PERSONNE (Nom, Prenom, Adresse) ;
! VOITURE (Numéro, Nom, Marque, Type, Date_acquisition) ;
Schémas relationnels :
! Table DEPARTEMENT (Nom_departement, Effectif) :
! Table EMPLOYE (Matricule, Nom_departement, Nom, Age, Adresse)
Nom_departement : clé étrangère vers table DEPARTEMENT
33
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
MCD -> MLD : relations (0,1)-(1,1)
MCD -> MLD : relations (0,1)-(0,1)
MCD :
MCD :
MAISON
EDIFICE
N°edifice
Type
Est-un
1,1
0,1
ENTREPRISE
N°maison
Adresse
34
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
N°entreprise
Adresse
TIERS
Correspondre
0,1
0,1
N°tiers
Type
MLD (solution 1) :
MLD :
N°edifice
Type
Est-un
TIERS
ENTREPRISE
MAISON
EDIFICE
N°entreprise
Adresse
N°maison
N°edifice
Adresse
Correspondre
N°tiers
N°entreprise
Type
Schémas relationnels :
Schémas relationnels :
! EDIFICE (N°edifice, Type) ;
! MAISON (N°maison, N°édifice, Adresse).
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
! ENTREPRISE (N°entreprise, Adresse) ;
! TIERS (N°tiers, N°entreprise, Adresse)
La cardinalité (0,1) pose le problème d’accepter des valeurs nulles sur l’attribut migrant
pouvant fixer le sens de migration (par exemple la taille des clés).
35
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
36
MCD -> MLD : relations (0,1)-(0,1)
MCD -> MLD : relations (0/1,N)-(0/1,N)
MCD :
MCD :
ENTREPRISE
N°entreprise
Adresse
0,1
C
o
r
r
e
s
Correspondre
p
o
n
d
r
e
MLD (solution 2) :
ENTREPRISE
N°entreprise
N°tiers
Adresse
Schémas relationnels :
COMMANDE
TIERS
Correspondre
N°commande
Date
Statut
N°tiers
Type
0,1
Porter
Qte-commandée
1,N
ARTICLE
N°article
0,N Désignation
Prix
MLD :
COMMANDE
TIERS
N°commande
Date
Statut
N°tiers
Type
PORTER
N°commande
N°article
Qte-commandée
ARTICLE
N°article
Désignation
Prix
Schémas relationnels :
! COMMANDE (N°commande, Date, Statut) ;
! PORTER (N°article, N°commande, Qte_commandée) ;
! ARTICLE (N°article, Désignation, Prix).
! ENTREPRISE (N°entreprise, N°tiers, Adresse) ;
! TIERS (N°tiers, Adresse)
Idem : la cardinalité (0,1) pose le problème d’accepter des valeurs nulles sur l’attribut
migrant pouvant fixer le sens de migration (par exemple, la taille des clés).
37
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
MCD -> MLD : relations ternaires ou plus (1)
MCD
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
38
MCD -> MLD : relations ternaires ou plus (2)
MLD
ENTREPRISE
MAISON
N°entreprise
Nom
Adresse
Coordonnées
Date_construction
Surface
O,N
ENTREPRISE
MAISON
Coordonnées
Date_construction
Surface
N°entreprise
Nom
Adresse
Date
Montant
O,N
TYPE_TRAVAUX
! MAISON (Coordonnées, Date_construction, Surface) ;
! TYPE_TRAVAUX (N°type_travaux, Désignation) ;
O,N
Realiser
Schémas relationnels associés :
REALISER
! RÉALISER (N°entreprise, Coordonnées, N°type_travaux, Date, Montant)
;
Coordonnées
N°entreprise
N°type_travaux
Date
Montant
! ENTREPRISE (N°entreprise, Nom, Adresse).
N°type_travaux
Désignation
TYPE_TRAVAUX
N°type_travaux
Désignation
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
39
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
40
MCD -> MLD : Re lations ré fle xive s (0,N)-(0,1)
MCD :
TACHE
N°tache
Désignation
Durée
Entité-Relation :
O,1
Correspondre
Solution 1
TACHE
N°tache
Désignation
Durée
TRAVAUX
N°travaux
Désignation
Durée
O,N
suit
O,N
précède
MLD :
MCD -> MLD : Re lations ré fle xive s (*,N)-(*,N)
Solution 2
Précéder
PRECEDER
N°tache
N°tache_suivante
ensemble
Relationnel dérivé :
TRAVAUX
N°travaux
Désignation
Durée
TACHE
N°tache
N°tache précédente
Désignation
Durée
Schémas relationnels :
élément
Décomposer
ensemble
élément
Schémas relationnels :
DECOMPOSER
N°travaux
N°travaux ensemble
! TRAVAUX (n°travaux, désignation, durée) ;
! DÉCOMPOSER (n°travaux, n°travaux_ensemble).
Solution 1 :
! TACHE (N°tâche, Désignation, Durée) ;
! PRÉCÉDER (N°tâche, N°tâche_suivante)
Solution 2 : TACHE (N°tâche, N°tâche_précédente, Désignation, Durée)
41
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
O,N
42
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
Rè gle de dé rivation : Ide ntifiant re latif
Exe mple de passage E-R au Re lationne l
MCD :
Entité-Relation :
PROJET
N°projet
Nom_projet
TACHE
Comporter
1,1
(R)
1,N
COMMANDE
CLIENT
n°client
nom
age
adresse
type
N°ordre
Désignation
1,n
PASSER
n°commande
date
statut
LIGNE_CMD
1,n
ARTICLE
qtО_cmdОe
qtО_livrОe
prix_unitaire
0,n
0,1
CA annuel
taux remise
FOURNISSEUR
MLD :
0,n
FOURNIR
n°article
dОsignation
qtО stock
poids
prix d'achat
prix de vente
n°fournisseur
nom
adresse
PROJET
Comporter
TACHE
Relationnel dérivé :
N°ordre
N°projet
Désignation
N°projet
Nom_projet
Schémas
1,1
COMMANDE
CLIENT
cli_num
cli_nom
cli_age
cli_adresse
cli_type
cli_ca
cli_tremise
relationnels :
! PROJET (N°projet, Nom_projet)
! TRANCHE (N°ordre, N°projet, Désignation)
PASSER
cmd_num
cmd_cli
cmd_date
cmd_statut
FOURNISSEUR
four_num
four_nom
four_adresse
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
43
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
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
44
E x e m p l e d e p assage E-R au relationnel
Introduction au langage SQL
• Modèle relationnel dérivé :
• Origine : SQL (Structured Query Language) est un langage de
requêtes standard pour les SGBD relationnels
COMMANDE
CLIENT
cli_num
cli_nom
cli_age
cli_adresse
cli_type
cli_ca
cli_tremise
PASSER
cmd_num
cmd_cli
cmd_date
cmd_statut
FOURNISSEUR
LIGNE_CMD
lcd_cmd
lcd_art
lcd_qté
lcd_liv
lcd_pu
FOURNIR
four_num
four_nom
four_adresse
• 3 niveaux de normes :
ARTICLE
art_num
art_four
art_nom
art_stock
art_poids
art_pa
art_pv
• SQL86 (standard ANSI en 86 puis ISO en 87) : la base puis SQL89
ou SQL1 : l’intégrité:
• SQL91 ou SQL2
• SQL3 (98) : SQL devient un langage de programmation et évolue
vers l’objet
• Schémas 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)
Dans ce chapitre nous ne considèrerons que la création BASIQUE
de tables par la commande CREATE TABLE de SQL2,
45
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
Création d’une table en langage SQL (1)
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
46
Création d’une 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
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)) ;
Schémas relationnels :
Table CLIENT (cli_num, cli_nom, cli_age, cli_adresse, cli_type, cli_ca,
cli_tremise)
NOT NULL : on n’accepte pas que l’attribut puisse avoir une valeur nulle (valeur
inconnue)
PRIMARY KEY (cli_num): l’attribut cli_num est clé primaire de la table Client.
- cli_num = clé primaire
Table COMMANDE (cmd_num, cmd_cli, cmd_date, cmd_statut)
Remarque : un attribut déclaré clé primaire doit être défini avec l'option NOT NULL
- cmd_num = clé primaire
- cmd_cli, = clé étrangère
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
47
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
48
Création d’une table (3) : clé étrangère
Création d’une table (4) : clé étrangère
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) ;
Création 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);
PRIMARY KEY (cmd_num): l’attribut cmd_num est clé primaire de la table
Commande.
FOREIGN KEY (cmd_cli) REFERENCES client: l’attribut cmd_cli est une clé
étrangère qui réfère à la table Client
Remarques :
1- la table client doit déjà exister
2- l'attribut cmd_cli de la table commande est du même type que celui de la clé
primaire de la table client.
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
49
• Création 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 (art_num): l’attribut art_num est clé primaire de la table article.
FOREIGN KEY (art_four) REFERENCES Fournisseur: l’attribut art_cli est une clé
étrangère qui réfère à la table Fournisseur.
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
50
Création d’une table (5) : clé primaire composée
Insertion d’enregistrement 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
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)) ;
composée
FOREIGN KEY (lcd_cmd) REFERENCES commande: l’attribut lcd_cmd est une
clé étrangère qui réfère à la table Commande.
FOREIGN KEY (lcd_art) REFERENCES commande: l’attribut lcd_art est une clé
étrangère qui réfère à la table Article.
Insertion d’un 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
composée des attributs lcd_cmd et lcd_art qui sont ici clés étrangères.
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
51
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
52
Suppression d’enregistrement 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)) ;
Suppression d’un enregistrement dans la table client :
• Niveau « Réel perçu » et « Niveau Interne ou Logique »
• Niveau « Conceptuel » et « Externe »
DELETE FROM client WHERE (cli_nom = ‘Tranvouez’);
53
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
Multiplicités moyennes des liens relationnels
personne
possèder
a1
r
a2
0,10
1,1
personne
a1
0,3
a2
r
0,5
voiture
b1
b2
card. mode 1
card moy = 5/4 = 1,25
card moy = 7/4 = 1,75
d'où:
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 "posséder" aura 40000 tuples
voiture
personne
4
a1
b1
a2
b2
a1
10000
r
40000
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
conduire
card. mode 1
mode
3 moyenne
54
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
Propagation des multiplicités moyenne
voiture
b1
b2
supposons une distribution triangulaire:
0
mini
4. Dime nsionne me nt d’une BD
Re lationne lle
voiture
b1
b2
personne
a1
a2
1000
1,25
conduire
a1
b1 r
r
1,75
715
1250 = n x 1,75
=> n= 1250/1,75
n = 715
nb
person
ne x
1,25 =
nb
voiture
x 1,75
soit:
1000 x 1,25 = 1250
55
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
56
Calculs cumulés des volumes des tables
• Il s’agit de faire sur un tableau, par exemple sous Excel, le
cumul du volume estimé de toutes les tables composant la
base de données
5. Sous sché ma de donné e s e t
valorisation de l'activité des
traite me nts sur la BD
• Ce tableau peut être paramétré
• Il permet d’estimer l’évolution du volume de la base de
données selon différents scénarii de croissance
• …
• Transformation de sous-schémas conceptuels (SSC) en sous-schémas
logiques (SSL)
• Valorisation des primitives logiques
57
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
Valorisation de l'activité des traitements sur la BD (1)
CLIENT
N°client
nom_client
adresse_client
passer
1,1
0,n
concerner
DEPOT
N°dépot
adresse_dépot
Sous-Schéma Logique
(SSL) associé
0,n
1,1
COMMANDE
N°commande
date_commande
DEPOT
N°dépot
adresse_dépot
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
2. Fréquence d'une tâche associée à un modèle externe
• fréquence du message associé au modèle externe exprimée par rapport à la période de
référence (généralement le mois moyen)
• basée sur la fréquence de survenance des événements initiateurs des procédures où
figure cette tâche
1,n
composer
qté_commandée
3. Transformation des actions en primitives
0,n
- #T : Accès à un tuple d'une table relationnelle par accés sur clé primaire, connaissant
une valeur de cette clé
- ?T : Accès à un tuple d'une table relationnelle par accès sur un autre attribut que la clé
primaire, ou sélection/restriction de tuples d'une table selon une qualification
- T+ : Ajout d'un tuple à une table relationnelle
- T- : Suppression d'un tuple à une table relationnelle. Cette primitive est notée T- nom de
la table.
- Tm : Modification d'un tuple d'une table relationnelle, par la modification de valeurs d'un
ou plusieurs attributs de ce tuple
- TXT': Jointure entre deux tables relationnelles T et T', par la modification de valeurs d'un
ou plusieurs attributs de ce tuple. Cette primitive est notée TXT' noms des tables
concernées .
ARTICLE
N°article
libellé_article
COMMANDE
CLIENT
N°client
nom_client
adresse_client
58
Valorisation de l'activité des traitements sur la BD (2)
1. Transformation de sous-schémas conceptuels (SSC) en sous-schémas
logiques (SSL)
Sous-Schéma
Conceptuel (SSC)
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
N°commande
N°client
N°dépot
date_commande
COMPOSER
N°commande
N°article
qté_commandée
ARTICLE
N°article
libellé_article
59
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
60
Valorisation de l'activité des traitements sur la BD (3)
Valorisation de l'activité des traitements sur la BD (4)
Illustration des primitives relationnelles :
• Actions associées à la prise en compte d'une nouvelle occurrence de
COMMANDE:
Sous-Schéma Conceptuel
(SSC)
COMMANDE
CLIENT
n°commande
n°client
date
n°client
nom client
LEC
LEC
n°client
COMMANDE
nom client
0001!!!!!!!!DUPONT
0002
MARTIN
0003!!!! DURAND
...
#CLIENT (N°0003)
CRE
passer
1,1
0,n
concerner
CLIENT X COMMANDE
CLIENT
CLIENT
N°client
nom_client
adresse_client
n°commande
n°client
100
101
102
0001
0001
0001
12-02
19-03
22-04
103
104
0002
0002
12-02
20-04
105
....
0003
12-02
DEPOT
N°dépot
adresse_dépot
date
1,n
m = 4,2
composer
qté_commandée
CRE
CRE
CRE
0,n
0,n
ARTICLE
N°article
libellé_article
LEC
m = cardinalité moyenne = 4,2
(en moyenne, une commande porte sur de 4,2 articles)
Sous-Schéma Logique
(SSL) associé
N°commande
N°client
N°dépot
date_commande
CLIENT
N°client
nom_client
adresse_client
4,2
DEPOT
N°dépot
adresse_dépot
61
T+
COMMANDE
#T
#T
?COMMANDE (date=12-02)
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
1,1
COMMANDE
N°commande
date_commande
COMPOSER
T+
N°commande
N°article
qté_commandée
ARTICLE
N°article
libellé_article
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
#T
62
Valorisation de l'activité des traitements sur la BD (5)
Valorisation de l'activité des traitements sur la BD (4)
4. Récapitulatif de l'activité valorisée par primitive
5. Poids relatif des primitives en accès logiques
• pour chaque modèle externe : traduction des actions, en primitives
• effectuer ce calcul d'abord pour l'activité unitaire de la tâche, puis de la multiplier par la
fréquence de la tâche sur la période de référence.
• cumul par primitive de l'activité supportée par le modèle (total par ligne). Du fait de
l'hétérogénéité des primitives en termes d'équivalent-accès, seul le cumul par primitive a
une signification :.
Définir des équivalents-accès-logiques = poids relatifs entre les différentes primitives.
primitives
#T CLIENT
#T DEPOT
T+ COMMANDE
T# ARTICLE
T+ COMPOSER
activité unitaire
1
1
1
1x4,2
1x4,2
activité mensuelle
10 000
10 000
10 000
42 000
42 000
→ repérer les primitives à forte activité, et retrouver les modèles externes responsables
• en pratique:
en l'absence d'outil logiciel prenant en charge cette valorisation fortement algorithmique,
pratiquer ces calculs que sur un échantillon d'une vingtaine de modèles externes
remarquables par leur fréquence, d'aspect critique (comme dans d'autres domaines la loi
des 80-20 se constate également ici).
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
63
Conventions pour la valorisation des primitives logiques
1- unité d'accés logique = #T, toutes les autres seront exprimées en fonction de #T.
2- le nombre de tuples occupées par une table T sera noté NbT(T)
3- le nombre de pages occupées par une table T sera noté NbP(T)
avec :
• NbP(T) = NbT(T)/nombre moyen de tuples dans la page
• le nombre moyen de tuples dans la page dépendra :
- de la taille de la page, noté P
- de la taille du tuple, noté T
- du taux de remplissage de la page
• ces valeurs seront liées au SGBD et à l'organisation de la table adoptés.
• on pourra faire des calculs en première approximation avec un taux de
remplissage de 80% et des pages de 4K.
Exemple :
NbT(T) = 10000; P = 4Koctets; taux de remplissage de 80%; T = 200 octets;
Nombre moyen de tuples par page = 4000x0,8/200 = 16
NbP(T) = 10000/16 = 625 pages.
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
64
Valorisation des primitives logiques (1)
Valorisation des primitives logiques (2)
• valeurs moyennes pour SGBDR actuellement commercialisés, une réflexion nécéssaire
sur la pertinence de ces valeurs dans le contexte de la BD
#T =1
: sélection selon clé primaire = unité d'accés logique
?T= NbP(T) : sélection selon un attribut non clé primaire sans index secondaire :
balayage séquentiel
?T= Sx(NbP(T)+NP(I)):
si le critère de sélection comprend plusieurs termes dont un
faisant intervenir un index secondaire I (avec S = facteur de sélectivité du
critère, cad la probabilité qu'un tuple satisfasse le critère; NbP(T) = nb
pages de la table T; NbP(I) = nb pages de l'index I)
T+ = T- = 4: le tuple est atteint par 1 accès à l'index + 1 accès à la page contenant le
tuple cherché + 1 mise à jour d'une page + 1 mise à jour de l'index.
Tm = 3:
le tuple est atteint par 1 accès à l'index + 1 accès à la page contenant le
tuple cherché + 1 écriture sur page.
Globalement on pourrait retenir :
primitive
équivalent accès
#T
1
Tm
3
T+=T-
4
?T
NbP(T)
?T si critère/index
Sx(NbP(T)+NP(I))
TXT'
S(NbP(T) + (N(T)xNbP(T'))
TXT' = S(NbP(T) + (N(T)xNbP(T'))) : jointure, avec NbP(T) = nombre page de la table
T, S = facteur de sélectivité du critère sur T, N(T) = nombre de tuples de
T satisfaisant le critère, NbAP(T') = nombre page de la table T'
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
65
Bernard ESPINASSE – Rappels sur les Bases de données relationnelles
66