Post on 03-Apr-2015
1
Modèle Relationnel de Données
Witold Litwin
2
Base de données relationnelle
Fichier = table ou relationDonnée = ligne ou attribut atomique Opérations = transformations de tables en une table
Opérationrelationnelle
3
Base de données relationnelle
Une collection d'objets :Relations réelles (tables de base)Contraintes d'intégrité (surtout référentielle)
intra-relationnelles monoattribut et multiattribut
inter-relationnelles (et multiattribut)
Déclencheurs (ang. triggers)notamment pour maintenir l'intégrité
Autres (procédures stockées…) Schéma conceptuel = Définition de la
collection
4
Empl (E#, Nom, Prénom, Né, Rue, CodePost, Ville, Dep#) ;E# Counter ; Nom Text ; Né Date ; Dep# Int...:Syst-date - Né < 65 * Contrainte de validationDep# Not Null ; * Contrainte d'existence
Taches (T#, Description) ;Planning (E#, T#, Date-fin, Avancement) ;Dep (Dep#, Name) ;Trigger on Empl
On Insert Check-Ref-Int (Dep, Empl.Dep#) ; Autres Déclencheurs utiles ?
Ce schéma est possible sous MsAccess, bien que exprimé différemment
Schéma de BD Entrepriseclé
5
Schémas Externes
Schéma (vue) externe = Collection de vues relationnels (tables virtuelles dérivées de relations réelles)
Un usager ne voit pas de différence entre une vue relationnelle et une table réelle En principe !
Une vue relationnelle n'est pas une vue externe au sens ANSI-SPARC
Celle-ci serait une base virtuelle
6
P# PNAME COLOR WEIGHT CITYP1 Nut Red 12 London
P2 Bolt Green 17 Paris
P3 Screw Blue 14 Rome
P4 Screw Red 12 London
P5 Cam Blue 19 Paris
P6 Cog Red 19 London
P
7
P# PNAME COLOR WEIGHT CITYP1 Nut Red 12 London
P2 Bolt Green 17 Paris
P3 Screw Blue 14 Rome
P4 Screw Red 12 London
P5 Cam Blue 19 Paris
P6 Cog Red 19 London
P
P# PNAME COLORP1 Nut Red
P2 Bolt Green
P3 Screw Blue
P4 Screw Red
P5 Cam Blue
P6 Cog Red
P1
Create View P1 asselect P#, PNAME, COLORfrom P;
8
P# PNAME COLOR WEIGHT CITYP1 Nut Red 12 London
P2 Bolt Green 17 Paris
P3 Screw Blue 14 Rome
P4 Screw Red 12 London
P5 Cam Blue 19 Paris
P6 Cog Red 19 London
P
P# PNAME COLORP1 Nut Red
P2 Bolt Green
P3 Screw Blue
P4 Screw Red
P5 Cam Blue
P6 Cog Red
P1
Create View P1 asselect P#, PNAME, COLORfrom P;
P# PNAME COLORP1 Nut Red
P4 Screw Red
P6 Cog Red
P2
Create View P2 asselect P#, PNAME, COLORfrom P where CITY = 'London';
9
P# PNAME COLOR WEIGHT CITYP1 Nut BLUE 12 London
P2 Bolt Green 17 Paris
P3 Screw Blue 14 Rome
P4 Screw Red 12 London
P5 Cam Blue 19 Paris
P6 Cog Red 19 London
P
10
P# PNAME COLOR WEIGHT CITYP1 Nut BLUE 12 London
P2 Bolt Green 17 Paris
P3 Screw Blue 14 Rome
P4 Screw Red 12 London
P5 Cam Blue 19 Paris
P6 Cog Red 19 London
P
P# PNAME COLORP1 Nut BLUE
P2 Bolt Green
P3 Screw Blue
P4 Screw Red
P5 Cam Blue
P6 Cog Red
P1
P# PNAME COLORP1 Nut BLUE
P4 Screw Red
P6 Cog Red
P2
11
Base relationnelleTables réelles
12
Base relationnelleTables réelles et vues
13
Relations
Di ; i = 1,2..n des ensembles dits domaines Une relation R est un sous-ensemble de
produit cartésien: Di R Di,1 x Di,2 ... x ... Di,k k n
Dans une BD relationnelle, on n’a que des relations finies
Les Di,j sont les attributs de R ; les rôles de domaines (Codd)
14
Les noms R et Di,j constituent le schéma de la relation
Ce schéma et l'ensemble des éléments possibles de R constituent une intention de R.
Les éléments de R y présent à un moment donnée constituent une extension de R.
Une mise à jour modifie une extension et change l'état de la base
Schéma d'une relation
15
S# SNAME STATUS CITYS1 Smith 20 London
S2 Jones 10 Paris
S3 Blake 30 Paris
S4 Clark 20 London
S5 Adams 30 Athens
P# PNAME COLOR WEIGHT CITYP1 Nut Red 12 London
P2 Bolt Green 17 Paris
P3 Screw Blue 14 Rome
P4 Screw Red 12 London
P5 Cam Blue 19 Paris
P6 Cog Red 19 London
S# P# QTYS1 P1 300
S1 P2 200
S1 P3 400
S1 P4 200
S1 P5 100
S1 P6 100
S2 P1 300
S2 P2 400
S3 P2 200
S4 P2 200
S4 P4 300
S4 P5 400
Un état de la base S-P
S
P
SP
Intention de S
Une extension
de S
16
Deux relations R et R' sont égales si elles diffèrent seulement par ordre :
d'attributs (colonnes) de tuples (lignes) Il n'y a pas de tuples égaux dans une
relation
Egalité de relations
17
S# SNAME STATUS CITYS1 Smith 20 London
S2 Jones 10 Paris
S3 Blake 30 Paris
S4 Clark 20 London
S5 Adams 30 Athens
Une même relation S
SNAME S# STATUS CITYSmith S1 20 London
Jones S2 10 Paris
Blake S3 30 Paris
Clark S4 20 London
Adams S5 30 Athens
S# SNAME STATUS CITYS3 Blake 30 Paris
S4 Clark 20 London
S1 Smith 20 London
S2 Jones 10 Paris
S5 Adams 30 Athens
CITY SNAME STATUS S#Paris Blake 30 S3
London Clark 20 S4
London Smith 20 S1
Paris Jones 10 S2
Athens Adams 30 S5
18
Une mise à jour est correcte si la nouvelle extension est dans l'intention de R
C'est le rôle des contraintes d'intégrité de ne permettre que les mises à jour correctes
Un changement de schéma de R est une restructuration
MAJ / Restructuration
19
Emp (E#, Nom, Prénom, Age, Rue, CodePost, Ville, Dep#) ;
Age < 65 * Contrainte de validation Dep# Not Null ; * Contrainte d'existence
Update Emp Set Age = 35 Where E# = '123' ;Update Emp Set Age = 75 Where E# = '456' ;Alter Emp Add Tel Integer ;
Alter Emp Drop Ville ;Create Table CP - V
CodePost Int Ville Text Primary key (CodePost, Ville) ;
C'est une décomposition d'une relation
SQL : MAJ / Restructuration ?
20
Une relation est un fichier qui supporte les opérations relationnelles
Une opération relationnelle transforme des relations arguments dans une relation résultat : une relation temporaire n'appartenant pas
au schéma de la base. une relation de la base (mise à jour) une vue
Opérations relationnelles
21
OpOpérationsérations relationnelle relationnelless
Sélection :
Projection
Restriction
Jointure
Division
Agrégation
Opération suppl.
Mise à jour
Création d ’une vue
Voir aussi le cours sur l’algèbre
relationnelle
22
Opérations relationnelles (SQL)
Voit (Im#, Pref, Mod, Couleur) Amende (A#, I#, Nom, Addr, Payé)
Select * From Voit ;
Select Mod From Voit Where Couleur = 'rose' ;
Select Nom, Addr From Amende, Voit Where Payé Is Null and Mod = 'Ferrari' and I# = Im# ;
Update Amende Set Payé = '10-01-96' where A# = '123' ;
Create View En-instance AsSelect * From Amende, Voit WherePayé Is Null and Amende.I# = Voit.Im# ;
23
Une relation réelle est définie à partir de ses attributs
Une relation virtuelle (vue) est dérivée (héritée) par une opération relationnelle à partir de relations réelles ou de vues
Relations
24
En général, un domaine et donc un attribut peut être un ensemble XML
Dans les SGBD actuels, ils ne sont considérés pour les opérations relationnelles que comme des éléments (valeurs) atomiques
De telles relations sont dites normales
Relations
25
P1P2P3P4
S1
S2P1P2P3
P1P2P3P4
P1P2P3
S1S1S1S1
S2S2S2
Norm.
O NF 1 NF
26
Normalization en 1-NF
Contrainte très importante ! Etud (E#, Tel, Hobbies, Dipl, Enfants, Voit) Etudiant Dupont:
3 tel, 5 hobbies, 3 diplômes, 3 enfants, 2 voitures
Un tuple d’ue relation en 0-NF suffit Il faut 3*5*3*3*2 = 270 tuples pour une relation en
1-NF ! Solution : normalisation en i-NF ; i > 1
voir le cours sur la normalisation relationnelle
27
Opérations relationnelles sont définies par les expressions :
d'algèbre relationnelle de calcul de tuple (de prédicat) (QUEL, ALPHA) de calcul de domaine (QBE) les trois formalismes sont équivalents (Codd)les trois formalismes sont équivalents (Codd) Un langage de base de données peut mélanger les
types d'expression ci-dessus (SQL) Calcul de tuple et algèbre
Relations
28
Dans toute relation R il existe une combinaison C d'attributs dite clé telle que dans tout tuple t d'intention de R, la valeur C(t)
identifie t, il n'y a pas de sous-combinaison de C avec cette
propriété Démontrez cette assertion !
Exemples: N° SS, N° Étudiant, Nom de pays, (Nom, Prénom, Tel), Oid,...
C atomique consiste d’un attribut C composite en contient plusieurs
Clés
29
Le choix de C est dicté par l'intention de R Soit R = Pers (Nom, Prénom, SS#, Tel)
Dans une famille Pers (Nom, Prénom, SS#, Tel)
A la SS Pers (Nom, Prénom, SS#, Tel)
A l'état civil Pers (Nom, Prénom, SS#, Tel)
Les valeurs d'un attribut d'une extension peuvent à un moment donné être toutes différentes sans qu'il s'agisse d'une clé !
Clés
30
La clé C définie comme auparavant peut-être appelée aussi clé minimale
Tout ensemble C' d'attributs de relation R incluant C est alors appelée clé
Alternativement, si C est appelé clé, alors tout C' est appelé super-clé
Dans notre base S-P, S# est une clé (minimale) de S, donc (S#, SNAME) est une super-clé de S.
Et les attributs (SNAME, STATUS) ne sont même pas une super-clé
Relations
31
R peut avoir plusieurs clés. Dans ce cas: Une clé est arbitrairement choisie est dite
primaire Les autres deviennent clés candidates
La clé C d'une relation R peut être des attributs F d'une autre relation R'
F deviennent une clé étrangère dans R’ F n'est pas en général une clé de R'
Relations
32
Voit (Châssis#, Moteur#, Plaque#, Mod, Poids, Coul )
Clé primaire
Clé candidate
Clé candidate
Etud (E#, Nom, Prénom, Tel, Adresse )
Participants (C#, E#, Note)
Clé étrangèreClé étrangère
Clé candidatecomposée
33
L'égalité C = F constitue le lien sémantique entre les relations correspondants
Dans un SGBD de 2-ème génération ces liens étaient les références explicites (pointeurs)
Entre C et F il peut exister la contrainte d'intégrité référentielle
En général: pas de F sans C pas de participant qui ne serait pas un étudiant connu
Les SGBD majeurs gèrent désormais de telles contraintes, MSAccess :
L'intégrité ref. 1:1 et 1:N Jointures implicites
Relations
34
Intégrité référentielle
MariM#
1 1
MariM#
FemmesM#, F#
1 N
AmieA#
M N
PP#, PS# Produit
Composé
ProduitP#
FemmeF#
1 1
N N
AmiA# Comment
faire ?
35
Les clés C et F peuvent aussi être dans une même
relation:
Emp ( E#, Enom, Tel, Chef# )
Personne ( SS#, Nom, Mère#, Père#)
De tels liens génèrent les récurrences exigeant le calcul de fermetures transitives
Les opérations relationnelles ne permettent pas de calculer les fermetures transitives
Relations
36
Une valeur nulle est un abus de langage pour designer une absence de valeur d’un attribut On dit aussi un nul
Valeurs nulles
S# SNAME STATUS CITY S1 20 London
S2 Jones 10
S3 Blake 30 Paris
S4 Clark London
S5 Adams 30 Athens
37
Valeur inconnue Ville de fournisseur inconnue
Valeur inapplicable Fournisseur connu pour être sans statut
Cette distinction est rarement appliquée en pratique
Types de nuls
39
Pourquoi ? Peut-on néanmoins en pratique
Sur MsAccess, Oracle, DB2…
Autoriser une valeur nulle pour un attribut-clé ? Créer des relations sans clé ?
Les nuls et les clés
Un attribut-clé ne peut être nul
43
UML Des diagrammes standard proposées par
OMG Données, Opérations, Messages… Notamment pour les BDs Une adaptation dans de dernier but du modèle
ER Une autre présentation de certains diagrammes Les concepts OO
Composition, Agrégation
44
UML Objet = Entité (Entity) Type = Type ou classe Propriété = Association (Relationship)
45
UML
Modèle d’une auto-école
basé sur l’ex. de M. Manouvrier
L’école peut envoyer entre
0 et 8 étudiants à un exam
Diagramme de noteen UML
Appartient à
Rôle de l’associon (directionnelle)
Nom de l’association
46
Réification d’une classe d’associations
EmpName
lname : Stringfname : Stringmi : String
SalaryHistoryAssociation
start_date : Dateend_date : Datesalary : Currency
Employee
<<PK>> emp_id : String
SalaryLevel
<<PK>> level_id : Stringmin_salary : Currencymax_salary : Currencystart_date : Dateend_date : Date
*
*
*
*
47
EmpName
lname : Stringfname : Stringmi : String
Employee
<<PK>> emp_id : String
SalaryHistoryReified
seq_num : Integerstart_date : Dateend_date : Datesalary : Currency
**
SalaryLevel
<<PK>> level_id : Stringmin_salary : Currencymax_salary : Currencystart_date : Dateend_date : Date
*
1..1
*
1..1
Réification d’une classe d’associations
58
Une base relationnelle n'est correctement définie que si son le graphe de références
est un graphe connecté.
Une BD relationnelle en général comporte plusieurs relations
Un graphe de références représente sa structure Les nœuds sont des relations Les arcs orientés sont les contraintes d'intégrité
référentielle C -> F 1:N ou 1:1
Les autres sont les liens sémantiques
Modélisation relationnelle :Graphe de références
59
Il faut minimiser le nombre de nœuds du graphe de références
Sous contraintes : D’absence d’anomalies
d’insertion, suppression, MAJ
De préservation de dépendances fonctionnelles
Modélisation relationnelle : Conception d’une BD relationnelle
60
Plusieurs relations Chaque relation consistant
d’une clé de max d’attributs identifiés
chacun comme une fonction atomique de la clé
Modélisation relationnelle : Résultat typique
61
Spécifications fonctionnelles:
Une entreprise a des fournisseurs S Un fournisseur f a un ID, un nom, un statut, et est dans
une ville Un f fournit des fournitures SP de pièces P Chaque fourniture fp comporte une certaine quantité
d'une pièce p Chaque p a un ID, un nom, un poids, une couleur Une pièce p peut être l'objet de plusieurs fournitures fp
Exemple canon
62
Exemple canon
FournisseursPièc
es
Fournitures
S#
P#
S# P#
63
S# SNAME STATUS CITYS1 Smith 20 London
S2 Jones 10 Paris
S3 Blake 30 Paris
S4 Clark 20 London
S5 Adams 30 Athens
P# PNAME COLOR WEIGHT CITYP1 Nut Red 12 London
P2 Bolt Green 17 Paris
P3 Screw Blue 14 Rome
P4 Screw Red 12 London
P5 Cam Blue 19 Paris
P6 Cog Red 19 London
S# P# QTYS1 P1 300
S1 P2 200
S1 P3 400
S1 P4 200
S1 P5 100
S1 P6 100
S2 P1 300
S2 P2 400
S3 P2 200
S4 P2 200
S4 P4 300
S4 P5 400
Exemple canon
S
P
SP
64
Pourquoi S-P est comme ça ? Avantages :
Pas de duplicata de valeurs d'attributs entre les tables S, SP, et P,
sauf le strict minimum (les clés) Pas d‘anomalies.
On verra cette notion dans le cours suivant. Efficacité de stockage.
Pas d’attribut-clé unique pour SP Compare à la conception en une seule relation
Problèmes : Comment trouver le Nom du fournisseur de pièces
rouges ? etc..
65
Solution
Opération relationnelle de jointure entre les relations
en SQL :
SELECT SNAME FROM S, SP, PWHERE S.S# = SP.S# AND SP.P# = P.P#AND COLOR = 'RED' ;
68
Base Hôpital
Les patients sont soignés par des médecins, dans des services
Un médecin peut être partagé entre plusieurs patients et services
1P SSPM
1 N N
M
N
1
La vie est moins facile
69
Un employé travaille dans un (seul) département ayant un nom et une localisation. On fait une relation ?
Emp (E#, Enom, D#, Dnom, Loc) Ou deux ?
Emp (E#, Enom, D#) D (D#, Dnom, Loc)
Et pourquoi pas les rel. atomiquesEmp (E#), Empn (E#, Enom), Empd (E#, D#), D (D#), Dn (D#, Dnom), Dl (D#, Loc), L (Loc)
Pas de bonne réponse générale dans la théorie des bases
relationnelles
La vie est moins facile
70
E# et Enom sont des clés candidates pour un étudiant.
Quel schéma choisir pour créer la base des notes des étudiants ?
E (E#, Enom, C#, Note) ? E (E#, Enom, C#, Note) ? ou peut-être :
E (E#, C#, Note) et EN (E#, Enom) ou enfin :
E (Enom, C#, Note) et EN (E#, Enom)
La vie est moins facile
71
Un cour est donné par des profs et comporte une liste de livres quelle base faut-il créer
Cours (C#, P#, Livre) ? Cours (C#, P#, Livre) ? Cours (C#, P#, Livre) ?
ou peut-être la base Cours (C#, Livre) et CP (C#, P#) ?
La vie est moins facile
73
Solutions
Théorie formelle de la conception relationnelle
Fondée dans les années 70 - 80 Codd, Boyce, Fagin, Armstrong, Bancilhon,
Spyratos, Delobel, Date, Ullman, Sagiv, Vardi... Largement délaissée depuis Mais il y a des contributions récentes
Date & Fagin, Krishnamourthy & Litwin...
74
FINMerci de votre
attention
W. Litwin
75