1 Modèle Relationnel 2011 - 12 Witold Litwin 2 2 Le Rapport de Recherche qui a lancé les SGBDs...
208
Modèle Relationnel 2011 - 12 Witold Litwin
1 Modèle Relationnel 2011 - 12 Witold Litwin 2 2 Le Rapport de Recherche qui a lancé les SGBDs Relationnels (publié uniquement en interne à IBM Almaden
2 2 Le Rapport de Recherche qui a lanc les SGBDs Relationnels
(publi uniquement en interne IBM Almaden Research Center (CA) BD
Relationnelle
Page 4
3 3 Le Rapport de Recherche qui a lanc les SGBDs Relationnels
(Rsum)
Page 5
4 4 BD Relationnelle Le Rapport de Recherche qui a lanc les
SGBDs Relationnels (Table des Matires)
Page 6
5 Base de donnes relationnelle Fichier =tableourelation Donne
=ligneouattributatomique Oprations = transformations de tables en
unetable Opration relationnelle
Page 7
6 6 SQL: Select S#, SNAME, STATUS FROM S WHERE CITY = Paris
Algbre relationnelle : (S WHERE CITY = 'Paris') [S#, SNAME, STATUS]
S Exemple
Page 8
7 Base de donnes relationnelle u Une collection d'objets : u
Relations relles (tables de base) u Liens smantiques u Contraintes
d'intgrit (surtout rfrentielle) u intra-relationnelles u
Mono-attribut et multi-attribut u inter-relationnelles u Intgrit
rfrentielle surtout u Dclencheurs (ang. triggers) notamment pour
maintenir l'intgrit u Autres (procdures stockes) u Schma conceptuel
= Dfinition de la collection
Page 9
8 Empl (E#, Nom, Prnom, N, Rue, CodePost, Ville, Dep#) ; E#
Counter ; Nom Text ; N Date ; Dep# Int... :Syst-date - N < 65*
Contrainte de validation Dep# 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#) ; u Autres Dclencheurs utiles ? u Ce schma est possible
sous MsAccess, bien que exprim diffremment Schma de BD Entreprise
cl
Page 10
9 Schmas Externes u Schma (vue) externe = Collection de vues
relationnels (tables virtuelles drives de relations relles) u Un
usager ne voit pas de diffrence entre une vue relationnelle et une
table relle u En principe ! Une vue relationnelle n'est pas une vue
externe au sens ANSI-SPARC Celle-ci serait une base virtuelle
Page 11
10 P
Page 12
11 P P1 Create View P1 as select P#, PNAME, COLOR from P;
Page 13
12 P P1 Create View P1 as select P#, PNAME, COLOR from P; P2
Create View P2 as select P#, PNAME, COLOR from P where CITY =
'London';
Page 14
13 P
Page 15
14 P P1 P2
Page 16
15 Base relationnelle Tables relles
Page 17
16 Base relationnelle Tables relles et vues
Page 18
17 Relations u D i ; i = 1,2..n des ensembles dits domaines u
Une relation R est un sous-ensemble de produit cartsien: R D i,1 x
D i,2... x... D i,k k u Les D i,j sont les attributs de R ; les
rles de domaines (Codd) u Les lments de R sont dit tuples u Il ny a
pas de tuples gaux dans une relation
Page 19
18 Relations u Dans une BD relationnelle, on na que des
relations finies u En nombre dattributs et en nombre de tuples u
Toute valeur dun d D i est atomique u Pas un ensemble u donc
mono-valeur u donc indcomposable u sans perte de la smantique u De
telles relations sont dites normales u Autrement dit en 1 NF au
moins
21 u Les noms R et D i,j constituent le schma de la relation u
Ce schma et l'ensemble des lments possibles de R constituent une
intention de R. u Les lments de R y prsent un moment donne
constituent une extension de R. u Une mise jour modifie une
extension et change l'tat de la base Schma d'une relation
Page 23
22 Un tat de la base S-P S P SP Intention de S Une extension de
S
Page 24
23 u Deux relations R et R' sont gales si elles diffrent
seulement par ordre : u d'attributs (colonnes) u de tuples (lignes)
Egalit de relations
Page 25
24 Une mme relation S
Page 26
25 u Une mise jour est correcte si la nouvelle extension est
dans l'intention de R u C'est le rle des contraintes d'intgrit de
ne permettre que les mises jour correctes u Un changement de schma
de R est une restructuration MAJ / Restructuration
Page 27
26 Emp (E#, Nom, Prnom, 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 ; SQL :
MAJ / Restructuration ?
Page 28
27 Oprations relationnelles u Une relation est un fichier qui
supporte les oprations relationnelles u Une opration relationnelle
transforme des relations arguments dans une relation rsultat : u
une relation temporaire n'appartenant pas au schma de la base. u
une relation de la base (mise jour) u une vue
Page 29
28 Oprations relationnelles u Pour une BD relationnelle, les
oprations sont dfinies sur les relations normales u Celles basiques
forment lalgbre relationnelle u Dfinie par E. Codd u En pratique,
il y a aussi des oprations additionnelles u Arithmtiques,
agrgations
Page 30
29 Oprations relationnelles u Slection : u Projection u
Restriction u Jointure naturelle ou u Division u Agrgation u
Opration suppl. u Mise jour u Cration d une vue Voir le cours sur
lalgbre relationnelle Op. ensemblistes: UNION, INTER, DIFF,
TIMES
Page 31
30 u R1 UNION R2 u R1 INTER R2 u R1 DIFF R2 u Elles sont
union-compatibles u Mme nombre dattribut u Types dattributs
compatibles u Permettant dvaluer = sur les attributs u La dfinition
de compatibilit dpend du SGBD u Mme de sa version Op. ensemblistes
UNION, INTER, DIFF
Page 32
31 u Produit cartsien u R1 TIMES R2 u Pas de contraintes sur
les types dattributs u Ni sur leur nombre u Opration trs chre que
lon vite tout prix Op. ensemblistes TIMES
Page 33
32 Base S-P S S [S#,SNAME] S [CITY] S WHERE CITY = Paris Villes
de fournisseurs Ids et noms de fournisseurs
Page 34
33 Jointure naturelle u La jointure A JOIN B de deux tables A
(X, Y) et B (Z, Y) est la table C avec les attributs : C (X, Y, Z)
et avec tous les tuples (X:x, Y:y, Z:z ) tels que (x, y) est dans A
et (y, z) est dans B u pas dautres tuples u X, Y, Z peuvent tre
composs
Page 35
34 Jointure naturelle Est-ce que la jointure naturelle est
commutative et/ou associative ? Notamment \ une slection ou une
projection ? A JOIN B =? B JOIN A A JOIN B JOIN C = ? A JOIN (B
JOIN C) A JOIN A JOIN A = ?
Page 36
35 CS SC CS JOIN SC S S [STATUS, CITY] S [S#, CITY]
37 -jointure u Table C gale : C = ( A TIMES B ) WHERE X Y est
la jointure de tables A(X,...) et B (Y,...). TIMES est un produit
cartesien X et Y sont non-composites La jointure est note : C = A
JOIN B ON X Y.
Page 39
38 - jointure / Equi-jointure C = A JOIN B ON X Y est une
equi-jointure. u A ne pas confondre avec la jointure naturelle u O
lattribut Y de jointure peut tre de plus composite Est-ce que la
jointure est commutative et/ou associative ?
Page 40
39 Division u Table C ( X ) note: A DIVIDEBY B est une division
de tables A (X, Y) et B (Y) ssi C contient tous les tuples ( x )
tels que ( y ) B, ( x, y ) A S# P# S1 P1 S1 P2 S2 P1 S2 P3 P# P1 P2
S# S1 Tout fournisseur de pices P1 et P2. DIVIDEBY est associatif
ou commutatif ?
Page 41
40 Requtes algbriques la base S-P u S JOIN SP WHERE P# = 'P2' [
SNAME] u S JOIN SP WHERE P# = 'P2' WHERE STATUS > 100 [SNAME] u
P WHERE COLOR 'Red' [P#] JOIN SP [S#] JOIN S [SNAME] u P WHERE
COLOR 'Red' [P#, PNAME] JOIN SP [S#, PNAME] JOIN S [SNAME] SP [S#,
P#] DIVIDEBY P [P#] JOIN S [SNAME] SP [S#, P#] DIVIDEBY (( SP WHERE
S# = 'S2') [P#])
Page 42
41 Requtes algbriques la base S-P u [SCITY] UNION [PCITY] u S
JOIN (SP WHERE P# = 'P2) INTER ( S JOIN SP ) WHERE P# = 'P3' [
SNAME] u S DIFF (S JOIN (SP WHERE P# = 'P2' [S#])) [ SNAME] u S
TIMES SP WHERE P# = 'P2' WHERE STATUS > 100 [ SNAME]
Page 43
42 Utilit de l'algbre u Puissance expressive: u 8 oprateurs de
Codd permettent d'exprimer toute expression logique de prdicat de
1-er ordre u note: seulement 5 sont primitives (lesquels ?) u La
puissance expressive de l'algbre dite compltude relationnelle
constitue la mesure de la puissance minimale de tout LMD
assertionnel digne de ce nom
Page 44
43 Utilit de l'algbre Technique de choix pour l'implmentation
Il n'y a que 8 oprateurs Ces oprateurs sont faciles implmenter Leur
proprits permettent de transformer les expressions en +efficaces
valuer, en gnral Amliorations algbriques Moins de valeurs lire ou
crire Moins de mmoire ncessaire pour ces valeurs Voir mon cours sur
lalgbre
Page 45
44 Utilit de l'algbre u Exemple (( S JOIN SP ) WHERE P# = 'P2'
) [SNAME] = ( S JOIN ( SP WHERE P# = 'P2' )) [SNAME] u La 2me
expression semble plus efficace ? u Rgle Gnrale dAmlioration ? (A
JOIN B WHERE A.a = C) (A WHERE a = C) JOIN B
Page 46
45 Oprations relationnelles (SQL) Voit (Im#, Pref, Mod,
Couleur) Amende (A#, I#, Nom, Addr, Pay) u Select * From Voit Where
Couleur = 'rose'; u Select Mod From Voit u Select * From Voit,
Amende u Select Nom, Addr From Amende, Voit Where Pay Is Null and
Mod = 'Ferrari' and I# = Im# ;
Page 47
46 Oprations relationnelles (SQL) Voit (Im#, Pref, Mod,
Couleur) Amende (A#, I#, Nom, Addr, Pay) u Update Amende Set Pay =
'10-01-96' where A# = '123' ; u Create View En-instance As Select *
From Amende, Voit Where Pay Is Null and Amende.I# = Voit.Im# ;
Page 48
47 Compltude relationnelle de SQL expression algbrique, une
expression quivalente de SQL et de QBE Schma de preuve: oprateur
algbrique, une expression quivalente de SQL composition d'oprateurs
algbriques, une composition quivalente de SQL
Page 49
48 u Une relation relle est dfinie partir des valeurs de ses
attributs u Une relation virtuelle (vue) est drive (hrite) par une
opration relationnelle partir de relations relles ou de vues
Relations
Page 50
49 u En gnral, une valeur dun domaine et donc dun attribut peut
tre un ensemble u XML, Access 2007 u Pour les oprations
relationnelles dans les SGBD actuels, ils ne sont nanmoins en
principe que des valeurs atomiques u Toute dcomposition fait perdre
la smantique de la valeur u De telles relations sont dites normales
Relations
Page 51
50 P1 P2 P3 P4 S1 S2 P1 P2 P3 P1 P2 P3 P4 P1 P2 P3 S1 S2 Norm.
O NF1 NF Toute valeur de S# et toute de P#Une ligne S#P#S#P#
Contrainte trs importante ! 9 valeurs14 valeurs
Page 52
51 P1 P2 P3 P4 IBM HP P1 P2 P3 IBM IBN IMB HP S1 S2 Norm. O NF1
NF Toute valeur de S#, Name et toute valeur de P# Une ligne
NameP#S#Name Redondances & Erreurs Peu acceptables en gnral S1
S2 S# P1 P2 P3 P4 P1 P2 P3 P# DF: S# -> Name 11 valeurs21
valeurs
53 Normalization en 1-NF u Explosion combinatoire de la taille
de la table ! u Etud (E#, Tel, Hobby, Dipl, Enfants, Voit) u
Etudiant Dupont: u 3 tel, 5 hobbies, 3 diplmes, 3 enfants, 2
voitures u Un tuple due relation en 0-NF suffit u Il faut 3*5*3*3*2
= 270 tuples u Un tuple pour toute combinaison dun tl, un hobby, un
dipl. u Pour une relation en 1-NF avec 1 tuple seulement !
Page 55
54 Normalization en 1-NF u Il faut 1620 valeurs en 1NF u Pour
17 valeurs seulement en 0NF u Sous peine de perte dinfo u Une
requte pourrait alors donner le rsultat faux u L tout est
inacceptable en gnral u Et si on a aussi le nom pour chaque tudiant
?
Page 56
55 Solutions pour la Conception u Alors comment faire ? u
Dcomposition sans perte de donnes en plusieurs tables sans
anomalies ET (E#, Tel), EH (E#, Hobby) ED (E#, Dipl), EE (E#,
Enfants) EV (E#, Voit) u Seulement 16 tuples au lieu de 270 u
Seulement 32 valeurs au lieu de 1620
Page 57
56 Solutions pour la Conception u Peut-on gnraliser cette ide ?
u Oui, mais ce nest pas simple u Deux voies principales 1.
Empirisme et Exprience u Entity Relationship u Merise u u UML
Page 58
57 Solutions pour la Conception 2. Dmarche Formelle u Par les
Maths u Normalisation en i-NF i 5 u Voir mon cours sur la
normalisation pour + u En gnral 4 NF suffit u On fait la
dcomposition sans perte de donnes u Quand on peut, sans pertes de
DFs aussi
Page 59
58 Solutions pour la Conception u On limine dabord les DMs u
Gnratrices danomalies les plus graves u Quand passe une table de 0
NF 1 NF u Comme on a vu u La dcomposition conduit aux plusieurs
tables sans DMs
Page 60
59 Solutions pour la Conception u Certaines tables peuvent
avoir encore des DFs anormales u A lorigine danomalies aussi u
Comme on a vu u Moins graves que celles de DMs u On dcompose ces
tables aussi u En se dbarrassant des anomalies cres par ces
DFs
Page 61
60 Solutions pour la Conception u Lest tables rsultantes sont
alors en toutes en BCNF u A fortiori en 4NF u Le rsultat est en
gnral optimal pour le relationnel classique u Pas danomalies ou de
redondances u Toute table est en 5NF u Voir le cours sur la
normalisation
Page 62
61 Solutions pour la Conception u Restent la prsence de valeurs
nulles u Elle peut crer des anomalies aussi u On y remdie par une
dcomposition sans perte aussi u Mais, avec la recomposition par
jointures externes u On verra + tard
Page 63
62 Solutions pour la Conception u Dans lensemble, pour
progresser il nous faut un petit rappel sur: u Les cls u Les liens
smantiques u Les valeurs nulles u Ce que lon va faire trs bientt
dans ce qui suit
Page 64
63 Et la Manipulation ? u Le problme reste ouvert dans le
relationnel classique: u SELECT E#, Tel, Hobby, Dipl., Enfants,
Voit FROM ET, EH, ED, EE, EV WHERE ET. E# = ET. E# AND ET. E# = ED.
E# u Produit une relation virtuelle en 1-NF u Fera revenir les 270
tuples discuts u Une solution: la fonction agrgat LIST u Votre
prof. & SQL Anywhere u Le cours sur SQL avanc
Page 65
64 u Dans toute relation R il existe une combinaison C
d'attributs dite cl telle que u dans tout tuple t d'intention de R,
la valeur C(t) identifie t, u il n'y a pas de sous-combinaison de C
avec cette proprit u Dmontrez cette assertion ! u Exemples: N SS, N
tudiant, Nom de pays, (Nom, Prnom, Tel), Oid,... Cls
Page 66
65 u Le choix de C est dict par l'intention de R u Soit R =
Pers (Nom, Prnom, SS#, Tel) u Dans une famille Pers (Nom, Prnom,
SS#, Tel) /* Tout membre u A la SS Pers (Nom, Prnom, SS#, Tel) /*
Assur seuelement u A l'tat civil Pers (Nom, Prnom, SS#, Tel) /*
Toute personne u Les valeurs d'un attribut d'une extension peuvent
un moment donn tre toutes diffrentes sans qu'il s'agisse d'une cl !
Cls
Page 67
66 u C atomique consiste dun attribut u C composite en contient
plusieurs u Tout attribut dune cl est dit attribut- cl u Tout autre
attribut est un attribut non- cl u Cest une fonction de toute cl de
la table u Cette proprit est la base dune dfinition du concept de
la cl Cls
Page 68
67 u Il ne faut pas confondre le concept de la cl avec celui
dun attribut-cl u Ce dernier ne pas la cl ds que la cl est
composite u Dans Pers (Nom, Prnom, SS#, Tel) u SS# est la cl et
lattribut-cl u Dans Pers (Nom, Prnom, SS#, Tel) u Nom nest que
lattribut-cl Cls
Page 69
68 u Si C est cl de R, alors tout ensemble dattributs de R
strictement incluant C est appel sur-cl ou super-cl Dans notre base
S-P, S# est une cl de S, donc (S#, SNAME) est une sur-cl de S. Et
les attributs (SNAME, STATUS) ne sont mme pas une sur-cl
Relations
Page 70
69 u R peut avoir plusieurs cls. Dans ce cas: u Une cl est
arbitrairement choisie est dite primaire u Les autres deviennent
cls candidates seulement u Une seule cl existante dans une table
est formellement une cl candidate aussi u Pour certains, nanmoins,
la cl primaire nest plus une cl candidate Relations
Page 71
70 u R peut avoir plusieurs cls de cardinalits diffrentes. u La
cl avec le plus petit nombre dattributs est dite alors minimale u A
choisir de prfrence Relations
Page 72
71 u Une cl C d'une relation R peut tre des attributs F d'une
autre relation R' u F deviennent une cl trangre dans R u F n'est
pas en gnral une cl de R' Relations
Page 73
72 u L'galit C = F constitue le lien smantique entre les
relations correspondants u Entre C et F il peut exister la
contrainte d'intgrit rfrentielle u Pas de F sans C u Pas de
participant qui ne serait pas un tudiant connu u Dans un SGBD de
2-me gnration ces liens taient les rfrences implicites (pointeurs)
u Dans UML aussi en principe Relations
74 u Les SGBD majeurs grent dsormais des contraintes IR ainsi
que les liens smantiques u MSAccess : u IR 1:1 et 1:N entre deux
tables u Sur un ou plusieurs attributs la fois u Quelques bugs pour
1:1 u Voir la suite du cours u Jointures implicites ou automatiques
partir de liens smantiques u Voir la suite du cours Relations
Page 76
75 Intgrit rfrentielle Mari M# 1 1 Mari M# Femmes F# 1 N Amie
A# M N PP#, PS# Produit Compos Produit P# Femme F# 11 N N Ami A#
Comment faire ?
Page 77
76 u Les cls C et F peuvent aussi tre dans une mme relation:
Emp ( E#, Enom, Tel, Chef# ) Personne ( SS#, Nom, Mre#, Pre#) u De
tels liens gnrent les rcurrences exigeant le calcul de fermetures
transitives u Les oprations relationnelles ne permettent pas de
calculer les fermetures transitives u Les SGBD en gnral ne grent
pas de tels liens smantiques Intgrit Rfrentielle
Page 78
77 u Nanmoins MsAccess le fait u Dune manire limite u Par la
dclaration adquate de relations u Pour lintgrit rfrentielle u Par
la dfinition correcte de sous- feuille (sous-table) u On dclare la
table elle-mme comme sa propre sous-feuille (sous-table) u Pour
voir en un clic les employs dun chef etc. Intgrit Rfrentielle
Page 79
78 u Une valeur nulle est un abus de langage pour designer une
absence de valeur dun attribut u On dit aussi un nul ou un null
(ang. null) Valeurs nulles
Page 80
79 u Valeur inconnue u Ville de fournisseur inconnue u Valeur
inapplicable u Fournisseur connu pour tre sans statut u Nom de
jeune fille pour un monsieur u Cette distinction est rarement
applique en pratique Types de nuls
Page 81
80 Comment faire alors sil le faut ? Pour lattribut # TEL faut
distinguer entre: # tel portable inconnu on relancera la personne
pour connatre son numro Personne sans tlphone portable Types de
nuls
Page 82
81 u Ce type de nul peut indiquer alors lexistence dune
sous-classe u Etudiants dans Etud qui nont pas denfants u Il est
souvent utile davoir une ou des tables ddies la sous-classe u Sous
peine danomalies graves u On reverra ce problme Types de nuls
Page 83
82 Pourquoi ? Une proprit qui peut sembler anodine En fait elle
est dune importance capitale pour une base relationnelle conduit la
dmarche dite de modlisation relationnelle notamment aux formes
normales Le nul et la cl primaire Un attribut-cl de la cl primaire
ne peut tre nul
Page 84
83 u Cette proprit est lorigine de lanomalie dinsertion u Il
s`agit de limpossibilit de linsertion dun tuple cause dun attribut
cl nul u Dans Etud en 1NF il pourrait sagir de tout attribut Etud
(E#, Tel, Hobby, Dipl, Enfants, Voit) Le nul et la cl primaire
Page 85
84 u P. ex. impossible dinsrer une donne quelconque dun tudiant
sans enfants u La dcomposition sans perte peut tre la solution u
Voir Etud dcompos u Mais pas toujours Le nul et la cl primaire
Page 86
85 u On peut interdire la prsence dun nul pour un attribut Dans
la dfinition de lextension de la relation Notamment dans MsAccess
La thorie initiale du modle relationnel ne prvoyait pas de tables
avec des nuls Les nuls en perspective
Page 87
86 Lintroduction de nuls a t faite par les praticiens Elle a
cre de nombreux problmes thoriques et pratique Beaucoup restent
non-rsolus Voir + dans le cours sur le site et dans le cours sur
SQL Les nuls en perspective
Page 88
87 Ex. MsAccess Deux nuls sont gaux pour Distinct et Group By
Ils sont diffrents pour lequi-jointure Un nul est plus grand que
tout non-nul Somme (nul, a) = a mais nul + a = nul Une requte peut
gnrer une table avec tous les tuples entirement nuls Vous avez dit
bizarre ? Les nuls en pratique
Page 89
88 Modlisation relationnelle u Passage du monde rel vers une
base relationnelle u Le schma conceptuel u Schmas de tables u Liens
smantiques & contraintes IR u Oprations permises u Les schmas
externes
Page 90
89 Modlisation relationnelle u Souvent fort simple u Lattrait
de bases relationnelles u Exemples typiques comments en cours u
Fournisseurs et Pices (Supplier Part DB) u Conseillers en
assurances et Produits dAssurances u Etudiants et Cours
Page 91
90 Modlisation relationnelle BDR
Page 92
91 Modlisation relationnelle u Mthodes grand-public
semi-formelles u ER u ERG u Merise u UML u Le rsultat peut tre
optimal u Mthode formelle u Le rsultat optimal garanti
Page 93
92 Dmarche semi-formelle u Trois phases 1. Modlisation
conceptuelle par spcifications fonctionnelles u Pas particulire au
relationnel u Ni aux BDs mme 2. Conceptuel Relationnel u
Transformation du modle conceptuel en CS et ESs dune BD 3.
Normalisation u Amlioration du CS par suppression des
anomalies
Page 94
93 Dmarche formelle u Deux phases 1. Modlisation conceptuelle
par spcifications fonctionnelles 2. Conceptuel > Relationnel 1.
Normalis 2. Sans sous-classes caches
Page 95
94 Une base relationnelle n'est correctement dfinie que si son
le graphe de rfrences est un graphe connect u Une BD relationnelle
en gnral comporte plusieurs relations u Un graphe de rfrences
reprsente sa structure u Les nuds sont des relations u Les arcs
orients sont les contraintes d'intgrit rfrentielle C -> F u 1:N
ou 1:1 u Les autres sont les liens smantiques Modlisation
Relationnelle Graphe de rfrences
Page 96
95 u Graphe des rfrences nombre minimal de nuds u Sous
contraintes : u Dabsence danomalies u Dinsertion, suppression, MAJ
u De minimisation de redondance globale de donnes u Par rapport 0NF
surtout u Les deux contraintes sont duales Rsultat Attendu: Graphe
de rfrences optimal
Page 97
96 u Graphe des rfrences nombre minimal de nuds u Sous
contraintes : u Prservation du max. de dpendances fonctionnelles
(FDs) u Absence de dp. multivalues (DMs) u Pas ou peu de valeurs
nulles u Cette contrainte peut contredire celle sur les anomalies
& redondances u Il faut alors exercer le bon sens Rsultat
Attendu: Graphe de rfrences optimal
Page 98
97 u Anomalie dinsertion u On ne peut pas insrer de valeurs
quil faudrait u Soit la table S = (S#, Sname, Status, City, P#,
Qty) u Fournisseur S1 ne fournit encore aucune pice u On ne peut
pas insrer ses donnes: u Fournisseur S1 est Smith, a le statut 20
et est Londres Modlisation relationnelle : Anomalies
Page 99
98 u Anomalie dinsertion u Il faut insrer une mme donne plus de
fois que ncessaire u En idal : une donne nest insre quune fois dans
la base u Ex. 1 Revoir notre exemple illustrant 1NF u La conception
en une table prsente lanomalie Modlisation relationnelle :
Anomalies
Page 100
99 u Ex. 2 Dans S, si S1 fournit 5 pices, alors on insre aussi
Sname, City, Status 5 fois u Soit maintenant la conception en deux
tables S = (S#, Sname, City, Statuts) SP (S#, P#, Qty) Modlisation
relationnelle : Anomalies
Page 101
100 u La conception est libre de deux aspects discuts de
lanomalie u On peut insrer les donnes sur S1 mme sil ne fournit
rien actuellement u La base peut contenir davantage de donnes u On
ninsre aussi Sname, City, Status quune fois Modlisation
relationnelle : Anomalies
Page 102
101 u En supposant quen gnral un fournisseur fournit plusieurs
pices, on diminue la redondance globale u Bien que lon laugmente
ncessairement localement pour S# Modlisation relationnelle :
Anomalies
Page 103
102 u Ex. 3 Soit la table S' = (S#, Sname, Status, ZIP, City) u
Pour chaque ZIP il ny a quune ville u Il faut rpter la ville
inutilement quand plusieurs fournisseurs partagent un mme code
postal u La solution ? u Problme dit de 3NF Modlisation
relationnelle : Anomalies
Page 104
103 u Ex. 4 Soit notre table S S = (S#, Sname, Status, City,
P#, Qty) u Supposons que S# et Sname sont deux cls candidates dans
S (S#, Sname, Status, City) u S prsente la mme anomalie quavant u
Une autre solution en plus de celle de la dcomposition en tables S
et SP dj discutes? u Problme dit de BCNF Modlisation relationnelle
: Anomalies
Page 105
104 u Ex. 4 Soit la table P = (P#, Pname, E#, Sal, S#, Dipl,
V#, Lab) u Une personne est en gnral soit un employ, soit un
tudiant, soit un visiteur u Une insertion gnre un gnral beaucoup de
nuls u Une anomalie non considre par le relationnel classique u On
y reviendra + tard Modlisation relationnelle : Anomalies
Page 106
105 u Enfin, supposons que lon conoit au lieu de S trois tables
S1 (S#, Sname), S2 (S#, City), S3 (S#, Status) u On insre S1 trois
fois de trop, par rapport S u Trop de tables conduit lanomalie
aussi Modlisation relationnelle : Anomalies
Page 107
106 u Anomalie de MAJ u On MAJ plusieurs valeurs au lieu dune
seule u Pour une bonne conception u Dans S, si S1 fournit 5 pices
et dmnage Paris, alors il faut mettre jour 5 valeurs u Dans S, il
suffit dune seule Modlisation relationnelle : Anomalies
Page 108
107 u Anomalie de suppression u On supprime les valeurs quil ne
faudrait pas u Dans S, si S1 fournit 5 pices u Si lon supprime 4
fournitures, les donnes de S1 restent dans la base u Si lon
supprime la dernire fourniture, on les perd u Pas si lon a la
conception en S et SP Modlisation relationnelle : Anomalies
Page 109
108 u Plusieurs relations u Chaque relation consistant u dune
cl u de max dattributs identifis chacun comme fonctions de la cl u
On respecte aisment la condition ncessaire u Pas celle suffisante
Modlisation relationnelle : Rsultat gnral
Page 110
109 u Dtails dans les exercices vus en classe u Pas sur le Web
u Dabord, on traduit les spcifications fonctionnelles en une seule
relation u Relation universelle Dmarche formelle
Page 111
110 u Relation universelle est souvent note U u U est un
fourre-tout u Sans nuls u Selon le relationnel classique u Si U est
sans anomalie et en 5NF on a fini u Cas trs rare Dmarche
formelle
Page 112
111 u Sinon on procde en deux tapes u On limine les DMs u Par
Th. de Fagin u On limine des anomalies dues aux certaines DFs u Par
Th. de Heath Dmarche formelle
Page 113
112 u Le Thorme de Fagin dcompose sans pertes une table u En
commenant par U u Chaque dcomposition u Liquide une DM u Remplace
une table R par deux projections R1 et R2 telles que R = R1 Join R2
Dmarche formelle
Page 114
113 u On continue rcursivement pour toute table rsultante u
Jusquaux tables sans DMs u Ncessairement Dmarche formelle
Page 115
114 u Une table R sans une DM peut prsenter encore des
anomalies u En prsence de DFs sur des attributs que ne seraient pas
des cls u Dterminants (mono-valeur) u On passe alors a Etape 2 u Y
compris peut-tre pour U demble Dmarche formelle
Page 116
115 u On applique rcursivement le Th. de Heath u A chaque fois,
on dcompose une table sans pertes de donnes en deux projections u
Lune contient seulement la DF max. pour un dterminant qui nest pas
une cl u Avec tous les attributs cibles sur ce dterminant Dmarche
formelle
Page 117
116 u On liquide chaque dcomp. une anomalie u Due la DF max.
mise dans sa table ddie u Comme dans notre ex. de passage 0NF ->
1NF u Revoir aussi la def. de la BCNF u Il en rsulte le schma avec
le minimum de tables sans anomalies dues de telles DFs u En 4NF, vu
llimination des DM dj Dmarche formelle
Page 118
117 u La dcomposition doit chercher des projections
indpendantes u Pour ne pas perdre de DFs u Ce nest pas toujours
possible u Voir la discussion de la BCNF dans le cours sur la
normalisation u On sarrte quand il ny a plus de table avec une
anomalie due une DF Dmarche formelle
Page 119
118 u Si les nuls sont permis, alors on passe ltape 3 u On
suppose quune table R dans le rsultat peut contenir de nuls non-cl
u Contrairement aux relationnel classiques u Donc a la Thorie des
NFs Dmarche formelle
Page 120
119 u On regarde si R na pas alors trop de nuls u Seul le bon
sens dit si trop de nuls cest vraiment trop u Ces nuls peuvent
indiquer des sous- classes u Sous-types Dmarche formelle
Page 121
120 u Il faut alors encore dcomposer u Mais comment et sur quel
base thorique ? u On ne peut plus employer de jointures naturelles
(internes) u Le principe de la dcomposition sans perte classique u
Notamment des Etapes 1 et 2 Dmarche formelle
Page 122
121 u On peut nanmoins dcomposer sans perte R alors en
utilisant les jointures externes u Thorme de votre prof. u Dtails
dans les exercices Dmarche formelle
Page 123
122 u Ensuite, on cre rcursivement entre les relations obtenues
u Les liens smantiques u Les contraintes dintgrit rfrentielle u Le
tout selon lapplication u Entre les cls primaires ou candidates et
les cls trangres Modlisation relationnelle : Toute Dmarche
Page 124
123 u Enfin, on choisi pour chaque contrainte rfrentielle et
chaque lien smantique sa jointure implicite u Une jointure ajoute
automatiquement la requte en mode QBE Modlisation relationnelle :
Toute Dmarche
Page 125
124 u Requtes courantes deviennent + simples u - procdurales, u
+ assertionnelles u Cette possibilit dans les SGBDs commerciaux
vient de MsAccess u Suivi par quelques autres SGBDs u SQL Server,
DB2, Sybase (?) Modlisation relationnelle : Toute Dmarche
Page 126
125 u En fait, les jointures implicite ont t proposes dans les
80 u Par votre prof. et son Thsard A. Abdellatif (INRIA) u Prof.
Tunis Modlisation relationnelle : Toute Dmarche
Page 127
126 u Dveloppes avec Prof. G. Wiederhold u Ont donn lieu au
Ph.D. de son tudiant B. Lee (Stanford) u Papiers sur la page Web de
votre prof. Modlisation relationnelle : Toute Dmarche
Page 128
127 u Type de jointure implicite pour le lien smantique
(MsAccess) u Interne (dfaut) u Produit seulement les tuples de deux
tables ou les valeurs jointes sont gales Modlisation relationnelle
: Toute Dmarche
Page 129
128 u Externe u Prserve toutes les tuples dune de deux tables u
Au choix sous MsAccess u Mais pas les deux tables la fois u Pas de
jointure externe complte sous MsAccess Modlisation relationnelle :
Toute Dmarche
Page 130
129 u On peut demander N, nom, NomJF, tel, ville, email en QBE
u Sans spcifier les jointures u La rponse serait OK u Elle serait
errone pour les jointures internes u Pourquoi ? 300 Modlisation
relationnelle : Toute Dmarche
Page 131
130 u Jointures externes sont mal supportes par MsAccess u
Absence de la thorie cohrente u Bugs u Voir + dans le cours sur SQL
u A nutiliser comme implicites que quand cest vraiment utile et
test Modlisation relationnelle : Toute Dmarche
Page 132
131 u Exprience dapplication u Les exercices u Voir ceux du
cours u La pratique u Voir la vie autour u Dauphine, Votre
entreprise, Facebook, Ecole de Conduite, vos CDs Modlisation
relationnelle : Dmarche formelle
Page 133
132 Spcifications fonctionnelles u Une entreprise a des
fournisseurs S u Un fournisseur f a un ID, un nom, un statut, et
est dans une ville u Un f fournit des fournitures SP de pices P u
Chaque fourniture fp comporte une certaine quantit d'une pice p u
Chaque p a un ID, un nom, un poids, une couleur u Une pice p peut
tre l'objet de plusieurs fournitures fp Exemple canon
Page 134
133 Schma Conceptuel S S# Sname Status City P P# Pname Color
Weight City SP P# S# Qty 1 * * 1
Page 135
134 Jointure Implicite (S SP) Choix de jointure interne
(dfaut)
Page 136
135 Rsultat pour une requte QBE SQL
Page 137
136 Exemple canon S P SP
Page 138
137 u Cas particulier u Tout employ a un et un seul poste tl, u
Il y a aussi des postes non-assigns u Dans la base cre par la
dmarche gnrale on aurait que : Empl (E#, Nom, PosteTel,)
Modlisation relationnelle : Dmarche gnrale
Page 139
138 u On a une anomalie dinsertion u Laquelle ? u On ajoute la
table Postes (PosteTel) u Avec une contrainte dintgrit u Laquelle ?
Modlisation relationnelle : Dmarche gnrale
Page 140
139 u Cas particulier Pers (P#, Nom, Pnom, DNaiss, CP,) CV (CP,
Ville) u Que faire si lon sait que P1 est Paris, mais lon ne connat
pas CP ? u Pas de problme par contre avec la conception dnormalise
Pers (P#, Nom, Pnom, DNaiss, CP, Ville) Modlisation relationnelle :
Toute Dmarche
Page 141
140 u Cas particulier VRP (P#, Nom, Pnom,) Planning (P#, Ville,
JourSem) u Un VRP fait plusieurs villes avec des jours spcifiques
pour chaque u Les deux attributs Ville et JourSem seraient
multi-valeur par rapport P# u On les met nanmoins dans la mme table
Modlisation relationnelle : Dmarche gnrale
Page 142
141 u La raison est quil sagit dune (plus rare) association
ternaire u les valeurs de ces attributs ne sont pas indpendantes u
VRP X visite Ville Y le JourSem Z u Le mme principe sapplique toute
association de dgre + lev u Rares Modlisation relationnelle :
Dmarche gnrale
Page 143
142 u Cas particulier Pers (P#, Nom, Pnom, Sex, Clib,) PJf (P#,
NJf) u NJf existe seulement pour les dames maries u Si les Jeunes
Filles sont rares, la conception normalise est fortement redondante
sur P# Modlisation relationnelle : Dmarche gnrale
Page 144
143 u En + il faut une jointure systmatique pour voir NJf avec
le reste du dossier u La conception dnormalise na pas ceux problmes
Pers (P#, Nom, Pnom, Sex, Clib, NJf) u Elle est alors prfrable en
pratique Modlisation relationnelle : Dmarche gnrale
Page 145
144 u En pratique u Un attribut seul avec nuls frquents tel que
NomJF reste en gnral dans sa table, p.ex., Pers u Attribut quelques
de valeurs, p.ex. NTels, donne lieu un nombre fixe dattributs
atomiques dans sa table u Tel_M, Tel_B, Tel_P u Email1, Email2,
Email3 Modlisation relationnelle : Dmarche gnrale
Page 146
145 u En pratique u On complte ventuellement la table par une
extension pour les autres valeurs si besoin u Pers (P#, Tel_M,
Tel_B, Tel_P..) u AutresTels (P#, Tel) u Quel type de jointures
implicite serait appropri alors ? Modlisation relationnelle :
Dmarche gnrale
Page 147
146 u En pratique u On scarte de la dmarche gnrale par pas mal
de raisons hors celle-ci, internes surtout u Cot de traitement de
jointures u Cot mmoire des indexes pour rendre les jointures
rapides u Problmes thoriques avec jointures externes u .
Modlisation relationnelle : Dmarche gnrale
Page 148
147 u Conclusion u Il y a ceux et dautres cas spciaux u Il faut
commencer par la dmarche gnrale u Aprs il faut exercer son bon sens
u Selon les contraintes spcifiques de la base u Dnormaliser si
utile Modlisation relationnelle : Dmarche gnrale
Page 149
148 Modle Conceptuel An mille sept cent quatre-vingt-dix-neuf ?
MDCCXCIX ? 1799 Votre modle / standard prfr ? MDCCLXXXXVXXXX
Modlisation Relationnelle Avance
Page 150
149 Modlisation Conceptuelle u Univers u Objets u Entits u
Proprits u Associations entre les objets u Fonctions u Ensembles
spcifiques dobjets u Types u Classes...
Page 151
150 Modlisation Conceptuelle u Universal Modeling Language u
Standard Intl. de OMG u Une variante de EER u Extended Entity
Relationship Model u ER avait t propos par Peter Chen u Prof. U. de
Baton Rouge (LU) u Il y a une trentaine dannes u Trs populaire dans
le temps u Un peu tort peut-tre
Page 152
151 Passage UML - Relationnel u Entits et Associations doivent
devenir u Tables du CS ou des ES u Liens smantiques u Contraintes
dIR u Oprations sur les tables
Page 153
152 UML u Des diagrammes standard proposes par OMG u Donnes,
Oprations, Messages u Notamment pour les BDs u Une adaptation dans
de dernier but du modle ER u Une autre prsentation de certains
diagrammes u Les concepts OO u Composition, Agrgation
Page 154
153 UML u Objet = Entit (Entity) ou Occurrence dentit u Entit
faible u Identifiable seulement dans une autre entit (forte) u Type
dobjets = Type ou classe u Proprit = Association
(Relationship)
Page 155
154 UML : Type dEntit Nom Attributs cl et non-cl Oprations
Page 156
155 UML : Type dEntit Pour le relationnel Attributs atomiques
ou drivs seulement Tout attribut atomique est fonctionnellement
dpendants sur la cl On note une dpendance fonctionnelle (FD) de B
sur A comme A -> B Pas dattributs multivalus ou composs
Attributs drivs sont pour les schmas externes et les sous-tables
(Access) Les spcifs des oprations sont rares
Page 157
156 UML : Type dEntit Personne P# Nom Prnom Nom de famille
Hobbies 0..10 Amis 0..10 Restaurants 0..10 Valide pour XML Pas pour
le relationnel Il faut mettre tout composite ou multivalu en type
dentit spar (en principe)
Page 158
157 UML : Type dEntit Personne P# Nom Prnom Nom de famille
Hobbies Hobby Amies Ami Restaurants Restaurant 1..* 1 0..10 1..*
0..10 1..*
Page 159
158 UML Assur Client# Produit dass.# Prix Prix/Prix total per
client Valide pour le relationnel Mais ralisable seulement comme
une table et une vue Attribut driv Prix total = Prix de tous les
produits du client
Page 160
159 UML Associations Modle dune auto-cole bas sur lex. de M.
Manouvrier Lcole peut envoyer entre 0 et 8 tudiants un exam
Diagramme de note en UML Appartient Rle de lassocion
(directionnelle) Nom de lassociation Abrviation de 0..* Exactement
6 sries / CD
Page 161
160 UML : Association n-aire u Les patients P sont soigns par
des mdecins M, dans des services S u Un mdecin peut tre partag
entre plusieurs patients et services 1 P S 1 1..4 100 1..5 1 Soin M
Que disent les chiffres ?
Page 162
161 UML : Association 1-aire Personne P# Nom Prnom Pre Mre
Anctre
Page 163
162 UML u Concept de composition u Les entits composantes nont
pas dexistence propre u Ex. Les salles dun btiment u La suppression
de la composition supprime aussi les composantes u Contrainte
dintgrit rfrentielle u Symbolise par losange noir u Les entits
composes peuvent tre agrges par ailleurs u Losange transparent
Batiment Salle Conf 1 0..* 1..4 1..7 Les cardinalits x..y sont des
exemples
Page 164
163 UML : Classe / Sous-classe u Concept de sous-classes u
Spcialisation/gnralisation u Symbolises par la flche u
Mandatory/Optional u Tout membre de la classe est obligatoirement
dans une sous-classe u And/Or u Il peut tre dans plusieurs
sous-classes ou pas Assurance Ass-maisonAss-voitureAss-maladie A#
Montant Val-maison Bonus Complment Optional / OR 1 11 0..1
Page 165
164 UML / Relationnel Client C# Prnom Nom de famille Ville CP
Acceptable pour le relationnel Mais une mauvaise conception Si
statut, comme son nom lindique ne dpend que de C# Si CP implique la
ville Assurance A# C# Statut du client Prime 1 *
Page 166
165 UML / Relationnel Acceptable pour le relationnel Mais une
trs mauvaise conception Personne P# Hobby Ami Restaurant Prnom Nom
de famille
Page 167
166 Passage UML - Relationnel u Entits et associations doivent
devenir u Tables u Liens smantiques et contraintes IR u Oprations
sur les tables u Dans le modle UML la reprsentation des
associations nest pas spcifie u Pourrait tre les listes de
pointeurs (rfrences) u Manipules alors diffremment dans un langage
de programmation que les valeurs directes de donnes u Principe
rejet par le modle relationnel
Page 168
167 Passage UML - Relationnel u Les associations sont les
tables comme les autres ou existent entre les valeurs des attributs
comme les autres (Codd) u Entre les cls primaire et trangre en
gnral u Associations triviales : une mme valeur dattribut cl
trangre dune table que celle dune cl dune autre table indique un
mme objet rel u Do lintroduction et limportance capitale du concept
de la cl dans le modle relationnel
Page 169
168 Passage UML - Relationnel u Egalement important est le
principe que la table est un ensemble donc tout tuple a
ncessairement une cl u Constitue peut-tre par tous les attributs,
mais quand mme u Pas une bonne ide u Rsultat global: une mme
expression de manipulations de toutes les donnes dans la base
(Codd) u Un norme avantage pour le but de non- proceduralit
Page 170
169 Rification (Etape 1) u Outil Fondamental de passage UML
Relationnel : u On rifie : u Toute classe dassociations en une
classe dentits u Toute classe dentits deviendra plus tard une table
relationnelle
Page 171
170 Rification (Etape 1) u Une classe dassociations est
peut-tre rifie en celle dentits avec ses classes dentits aux
extrmits u Si lassociation est une bijection notamment u Autrement,
on transforme une association en celle triviale entre les attributs
des entits u Tout attribut structur ou multivalu est rifi en une
entit (spare et associe par des cls trangres)
Page 172
171 Rification (Etape 2) u Toute entit rifie devient une table
relationnelle u Les associations triviales deviennent u Les liens
smantiques u Les contraintes dintgrit rferentielle
Page 173
172 Rification u Le concept de rification est rarement explicit
u La rification est en gnral manuelle u A lheure actuelle u Cest la
principale limitation de lemploi dune BD relationnelle par un
usager Tout-le-Monde
Page 174
173 Rification : Principe Gnral A A# A1 . B B# B1 . C C1 . A A#
A1 . B B# B1 . C A# B# C1 . Association triviale: les deux B#
identifient la mme entit. Cest une rification adapte au relationnel
pour viter les anomalies Dautres rifications sont possible (ex. A
et B et C en une entit commune) Relation universelle
Page 175
174 Rification : Principe Gnral A A# A1 . B B# B1 . C A# B# C1
. A A# A1 . B B# B1 . C A# B# C1 . A lorigine, il ny avait pas de
liens smantiques explicites dans une BD Rel. Les associations
triviales devenaient des liens implicites : lgalit du nom de la cl
primaire et celle trangre
Page 176
175 Rification : Principe Gnral A A# A1 . B B# B1 . C RoleA#
RoleB# C1 . Aprs, oui, notamment pour lintgrit rfrentielle en
utilisant le nom de rle (nom de lassociation, uni ou
bidirectionnelle : ex. Prop._de_la_voiture_) A A# A1 . B B# B1 . C
A# B# C1 .
Page 177
176 Rification & Pointeurs dans les Langages de
Programmation u Une association triviale reprsente dune manire
explicite un pointeur dune table vers une autre u La valeur dun
pointeur est explicite u Contrairement en principe aux langages de
programmation u Dans le modle relationnel elle est celle dune
attribut comme dautres u Presque, car il y a en gnral les
contraintes rfrentielles grer u Un pointeur peut tre alors manipule
comme toute autre donne u Une des ides fondamentales de E.Codd u En
fait le concept de la cl est une consquence de cette ide u Une
reprsentation la fois compacte et explicite dun pointeur
Page 178
177 Rification : Association n-aire 1 P S 1 1..4 100 1..5 1
Soin M 1 P S 1 1..4 100 1..5 1 M S# P# M# Soin
Page 179
178 Rification : Attribut compos Personne P# Prnom Nom de
famille Hobbies Amis Restaurants Nom Tel Personne P# Prnom Nom de
famille Hobbies P# Hobby Amis P# Ami Restaurant P# Nom Tel Les
cardinalits des associations ? Le processus est transitif pour une
valeur compose dans un attribut
Page 180
179 Rification : Attribut compos Personne P# Prnom Nom de
famille Hobbies Amis Restaurants Nom Tel Personne P# Prnom Nom de
famille P_H P# H# Amis P# Ami Restaurant P# Nom Tel Approche utile
pour un entrept de donnes Peut faire gagner de la place en mmoire
de stockage (encombrement de H# est souvent bien plus petit que
celui du texte de Hobby) Hobbies H# Hobby
Page 181
180 Rification : Entit Faible C# Client Cl# Conseiller Cl# Nom
1 0..1 Cl# nest pas la cl C# Client Conseiller Cl# C# Nom 1 *
Page 182
181 Rification : Entit Faible C# Client Cl# Conseiller Cl# Nom
1 0..1 Cl# nest pas la cl La cl de Client si Conseiller est une
entit faible aussi ? C# Client Conseiller ? . Nom 1 *
Page 183
182 Rification : Cas Spcifiques Bijection Mari M# A1 . Femme F#
B1 . Maris Date . 1 1 Mariage M# ou F# F# ou M# Date A1 . B1 .. On
rifie en une entit ( laquelle ?). Changement du modle conceptuel.
On gagne en en gnral en efficacit en liminant une jointure Il nest
plus possible dintroduire une Femme dont on ne connat pas la Mari
ou vice versa (pourquoi ?) Unique (un seul mariage, pas de
personnes remaris ensemble)
Page 184
183 Rification : Cas Spcifiques Bijection Client C# A1 .
Voiture V# B1 . Accident Date . 1 1 On mmorise tous les accidents
dun client avec sa voiture Peut-on en gnral rifier comme auparavant
? Sinon pourquoi pas ?
Page 185
184 Rification : Cas Spcifiques Injection Mari M# A1 . Femme F#
B1 . Maris Date . 0..1 1 Femme Marie ou pas F# M# Date A1 . B1 ..
Changement du modle conceptuel On gagne en souvent en efficacit en
liminant une jointure / lapproche de base Unique (un seul mariage
de personnes remaris ensemble)
Page 186
185 Rification :Cas Spcifiques Mari M# A1 . Femme F# B1 . Maris
Date . 0..1 Mari M# A1 . Femme F# B1 . Maris M# F# C1 .
Page 187
186 Rification : Hirarchie Mari M# A1 . Femme F# B1 . Maris
Date . 1 0..4 Mari M# A1 . Femme-m F# M# Date B1 . On na que les
femmes maries (changement du modle conceptuel) On limine une
jointure et une redondance/ lapproche gnrale
Page 188
187 Rification : Les Veuves ? Mari M# A1 . Femme F# B1 . Maris
Date . 0..1 0..4 Votre Proposition ici
Page 189
188 Rification : Classe / Sous-classe Assurance Ass-maison
Ass-voitureAss-maladie A# Montant A# Val-maison A# Bonus A#
Complment Assurance Ass-maisonAss-voitureAss-maladie A# Montant
Val-maison Bonus Complment Optional / OR Les tables sont comme les
entits rifies. Comment faire pour lIR ? 0..1 1 1 1
Page 190
189 Rification : Classe / Sous-classe Client HommeFemme C# Nom
Mandatory/ OR Nom_JF Client C# Nom Nom_JF Sinon votre proposition
ici OK ?
Page 191
190 Rification : Classe / Sous-classe Schma MsAccess
Page 192
191 Rification : Classe / Sous-classe Schma MsAccess u Le schma
permet daisment formuler les requtes: u Toute donne de personne P1
dans Pers et, sil y a lieu, ses donnes En tant quun employ En tant
quun tudiant u MsAccess gnre alors les jointures implicites
externes u Cours SQL
Page 193
192 Rification : Autres Cas u Le jeu de cls en gnral facile
voir de diagrammes UML u Agrgation u Composition u Associations
1-res u Sauf celle dite Anctre u Calcul de la fermeture transitive
u Peu performant dans les BDs Relationnelles
Page 194
193 Rification : Cardinalits u 1 * ou 1 1 prsent avant ou aprs
la rification, se rifie en contrainte dintgrit rfrentielle cl
primaire cl trangre u 0 * ou 0 1 se rifie en un lien smantique u
Autre cardinalits, p.ex. 1 6 ncessitent en gnral des dclencheurs u
Pas une sincure pour Mme/M Tout le Monde
Page 195
194 Rification : Autres Cas u Lexemple dune Personne avec les
Amies, Hobbies? u Attributs drives ? u Il faut les mettre dans les
vues Select Sum (Prix) as PrixTotal from Client Group By
Client#
Page 196
195 Aprs la Rification u Le rsultat peut tre OK u Exercice :
Modle relationnel de lauto- cole u Mais il peut tre pas bon du tout
pour une BD relationnelle u A cause danomalies et de redondances u
Do la phase de normalisation u Peut-tre applique partir de la
relation universelle directement u Par lanalyse des DFs et des
DMs
Page 197
196 Spcifications fonctionnelles: u Une entreprise a des
fournisseurs S u Un fournisseur f a un ID, un nom, un statut, et
est dans une ville u Un f fournit des fournitures SP de pices P u
Chaque fourniture fp comporte une certaine quantit d'une pice p u
Chaque p a un ID, un nom, un poids, une couleur u Une pice p peut
tre l'objet de plusieurs fournitures fp Exemple canon
Page 198
197 Exemple canon S S# Sname Status City P P# Pname Color
Weight City SP Qty * *
Page 199
198 Exemple canon S S# Sname Status City P P# Pname Color
Weight City SP P# S# Qty 1 * * 1 Association triviale
Page 200
199 Exemple canon S P SP
Page 201
200 Pourquoi S-P est comme a ? u Avantages : u Pas de duplicata
de valeurs d'attributs entre les tables S, SP, et P u sauf le
strict minimum (les cls) u Pas danomalies. u On verra cette notion
dans le cours suivant. u Efficacit de stockage. u Pas dattribut-cl
unique pour SP u Compare la conception en une seule relation u
Problmes : u Comment trouver le Nom du fournisseur de pices rouges
? u etc..
Page 202
201 Solution u Opration relationnelle de jointure entre les
relations u en SQL : SELECT SNAME FROM S, SP, P WHERE S.S# = SP.S#
AND SP.P# = P.P# AND COLOR = 'RED' ;
Page 203
202 Exemple Projet BD Assurance 07
Page 204
203 UML -> XML 123 Jean Dupont Ski, Tennis, Voile Jean, Paul
Sinbade, Caf Court, Gargote Personne P# Nom Prnom Nom de famille
Hobbies 0..10 Amis 0..10 Restaurants 0..10 Plusieurs SGBD
relationnels offrent les interfaces XML Type dentit UML Une entit
XML (dite document)
Page 205
204 Exercices (adaptez svp au programme de votre cours
spcifique, voir aussi ceux des TDs) u Proposer les schmas
relationnels pour les exemples en cours u Modliser en UML et en
relationnel un livre typique u Modliser en UML et en relationnel
laffectation de salles de cours Dauphine. Justifiez le choix si
plusieurs solutions sont possibles. Indiquez les cls primaires et
candidates. u Modle 1: Une rservation se dfinit par le n de la
salle, le nom du cours, la date, lheure dbut et lheure fin. (i) Un
cours nest quune fois par jour dans la mme salle. (ii)
Alternativement, une rptition est possible. u Modle 2 : On ajoute
le type de la salle, si cest: lamphi, une salle quipe vido ou une
salle TP u Modle 3 : On ajoute le nom du prof enseignant le cours
(i) Un enseignant par cours. (ii) Plusieurs.
Page 206
205 Exercices u Modliser une bibliothque possdant un ou
plusieurs exemplaires dun livre sur des rayons, en prt ou en retour
dun prt mais pas encore sur les rayons. u Proposez une modlisation
usuelle en UML dune personne ayant un ID, un nom, une mre et un
pre. Proposez ensuite un schma relationnel. u Ce schma
satisfait-t-il: u Un DBA soucieux de lespace de stockage de la
base. Sinon, que lui conseillez-vous ? u Un DBA voulant minimisant
le temps de requtes donnant pour certains chefs identifis par leurs
IDs, les IDs de tous leurs employs u Modliser un certificat de
naissance dun bb en sachant que les parents peuvent ou pas tre
maris u Modliser les assurances proposes par une compagnie pour une
personne : voiture, maison, resp. civile u Voir les livres en BDs
pour 1 millier dautres exercices du type : u Spcifs fonctionnelles
-> UML -> rif. -> Schma Rel.
Page 207
206 Exercices u On cre le modle pour la base des enfants. Pour
chaque enfant on a le pre et la mre. Proposez le modle UML. Lenfant
doit tre modlis comme une entit ou une association ? u On constitue
une base de produits. Chaque produit a un ID et nom, une photo et
appartient plusieurs catgories de produits identifies par leur
noms. Plusieurs produits peuvent appartenir une mme catgorie. La
photo comporte plusieurs produits agencs dune manire typique pour
leur application. Plusieurs produits partagent une mme photo. u
Proposez la modlisation typique UML, puis la rification, enfin le
schma relationnel. u Le DBA sait en plus quil a en moyenne 100
produits par catgorie et autant par photo. Il y a 100 catgories et
photos en tout. Le nom dune catgorie est un champ fixe de 50
octets. Une photo ncessite 1 MOctets. u Le DBA sait quil y a 10 000
produits. Il souhaiterait minimiser lencombrement de la base.
Est-ce que la modlisation typique minimise le satisfait ? u Sinon,
proposez en UML et en relationnel une autre qui serait plus
optimale. Evaluez le gain. u Un autre DBA a comme proccupation
principale de minimiser le temps dune requte demandant des noms de
produits avec leurs catgories et les photos. Il veut minimiser le
nombre de jointures. Quelle modlisation lui conseillez vous?