Conception relationnelle

1
Conception relationnelle

• Witold Litwin

2
Modélisation du monde réel

• Une base est un modèle d'une entreprise
  (ANSI-SPARC)
• Innombrable méthodes d'aide à la conception
  (intégré) d'une base
• empiriques
• semi-empiriques (E-R)
• formelles (normalisation relationnelle)
• OO

3
Modélisation relationnelle (démarche intuitive)

• Un type T d'objets réel O ? une relation R
• Un O ? une valeur d'une clé primaire C de R
• Une propriété fonctionnelle de tout O ? un
  attribut de R
• Une référence à O ? une valeur de clé étrangère
  F C
• détails dans le cours correspondant

4
Modélisation relationnelle

• Graphe de références
• Les relations sont les noeuds
• Les contraintes d'intégrité référentielle C -gt F
  sont les arcs orientés.
• Une base relationnelle n'est correctement définie
  que si son graphe de références est un graphe
  connecté.
• Approche naturelle est souvent suffisante en
  pratique
• mais, pas toujours, ni suffisante ni univoque même

5
Base S-P (Exemple canon)
SP
S
P
6
Exemple canon

• Graphe de références
• Clés primaires n'ont pas de sémantique
• Jouent les rôles de OIDs définis à la main
• Les fournisseurs le les pièces sont en relation m
  n.

SP
S
P
7
Famille canon
Femme
Mari
Enfants
8
Famille canon
Femme
Mariage
Mari
Dipl
Enfants
Comptes
9
Famille canon
Femme
Mariage
Amants
Mari
Amantes
Dipl
Enfants
Comptes
A qui
Jouets
10
Famille canon (un ES)
Femme
Mariage
Mari
Dipl
Enfants
Comptes
A qui
Jouets
11
Problèmes

• Duplications de données (à éviter)
• adresse d'une famille
• mêmes diplômes
• code postal et ville
• autres (nombreux)

12
Restructuration du schéma (fragment)
CP - V
Adresse
Femme
Mariage
Mari
Dipl
Enfants
13
Normalisation

• Processus de création formelle d'une base
  relationnelle (Codd)
• Un remplacement de relations avec des anomalies
  par une ou plusieurs relations "meilleures"
• Le remplacement appelé aussi décomposition sans
  perte est basé en général sur la notion de
• dépendances fonctionnelles
• dépendances multivaluées

14
Normalisation
Base
Attributs (rel. universelle)
Couverture de Dépendances (FDs, MDs, IDs...)
15
Normalisation
Base
Cuverture minimale (FDs, MDs, IDs...)
Base en 1-O NF
table en 1 NF
16
Normalisation
Base
Cuverture minimale (FDs, MDs, IDs...)
17
Normalisation
Base
Cuverture minimale (FDs, MDs, IDs...)
etc jusqu'à n ? 7 fois (5NF)
18
Normalisation

• Une de plus belles pièces de la théorie des BDs
• Mais, d'un intérêt discutable en pratique
• L'idée première séparation de torchons et de
  serviettes mélangées dans une même relation R
• en deux ou plus relations- projections, séparant
  les torchons de serviettes et telles que
• si besoin, la jointure de ces projections donne
  de nouveau R
• Très à la mode il y a 20 ans
• Peu de nouveautés depuis sauf
• 1-O NF (1991)
• Théorème sur l'équivalence de 3 NF et n NF n gt 3
  (1993)

19
Formes normales connues

• 1-O NF (Ketabchi, Krishnamourthy, Litwin, 1991)
• 1 NF (Codd, 1971)
• 2 NF (Codd, 1971)
• 3 NF (Codd, 1971)
• BCNF (Heath, Codd, 1971)
• 4 NF (Fagin, 1977)
• ???5 NF (Fagin, 1979)
• 1-O NF est une forme d'une base, toute autre est
  une forme d'une relation
• Une relation en n NF ou BCNF est toujours en n NF
  ou BCNF si n gt 3.

20
Forme normale de 1-er ordre (1st Order Normal
Form)

• Une base relationnelle B est en 1-O NF si tous
  les arguments de manipulations prévues sont les
  valeurs d'attributs
• En général, un argument peut être un nom ou une
  valeur
• SELECT SNAME FROM S WHERE CITY 'Paris'

21
1-O NF

• Considère maintenant la requête à la base S-P
• Les noms de tables avec S 'S1'
• Si cette requête est permise, alors la base S-P
  n'est pas en 1-O NF
• Un exemple pratique la base Bourse
• IBM (C, prix, jour)
• HP (C, prix, jour, variation)
• Alcatel (C, prix, jour, dividende)
• Les noms des entreprises dont prix au jour x gt 50
• Si cette requête est permise, alors Bourse n'est
  pas en 1-O NF

22
Normalisation en 1-O NF

• Créer une table où les noms deviennent les
  valeursComp (C, Nom, prix, jour, variation...)
• Prix à payer
• valeurs nulles
• performances
• Le futur LMs de prédicats d'ordre supérieur,
  ex. IDL (Krishnamourthy, Kent, Litwin 1991)
• La normalisation en 1-O NF compose n gt 1 tables
  en 1 les autres normalisations décomposent 1
  table en n.

23
Dépendances Fonctionnelles

• Un attribut B d'une table R est fonctionnellement
  dépendant sur A de R A -gt B ssi, pour tout
  tuple t de toute extension de R t a1 t a2
  t b1 t b2
• A et B peuvent être composites
• DFs sont des propriétés sémantiques (du schéma de
  R), donc s'appliquent à toute extension (légale)
  de R
• Une DF d'une extension de R n'est pas
  nécessairement une DF de R
• Pour tout B non-clé et tout A clé, on a A -gt B

24
SP
S
P

• S S -gt SNAME S -gt STATUS S -gt CITY
  STATUS -gt CITY ?S S -gt SNAME, CITY S,
  CITY -gt STATUS, CITY
• Combien y a-t-il de DFs dans S ?
• Et en général dans une R n-aire avec une clé
  atomique ?
• ? SP S -gt P P -gt QTY

25
Règles d'inférence de DFs

• Si F est un ensemble de DFs sur R, (couverture
  fonctionnelle de R), une DF X -gt Y est inférée
  (déduite) de F si X -gt Y est valide pour tout
  tuple d'une extension légale de R (donc
  respectant F).
• On note F Þ X -gt Y
• F S -gt SNAME S -gt STATUS S -gt CITY Þ
  S -gt SNAME, CITY S, CITY -gt STATUS,
  CITY
• Règles d'inférence des DFs (Armstrong ,1974)
• X, Y, Z sont des attributs de R
• (i) X???Y Þ??X -gt Y Réflexivité
• (ii) X????Y Þ????XZ -gt YZ Augmentation
• (iii) X????Y, Y????Z Þ????X -gt Z
  Transitivité??

26
Règles d'inférence de DFs

• Il est prouvé que les règles d'Armstrong sont
  fondées (sound) et complètes
• Toute DF inférée en utilisant (i) à (iii) est une
  DF inférée
• Toute DF qui peut être inférée de F, peut être
  inférée en utilisant seulement (i) à (iii)
• Fermeture F de F l'ensemble des DFs qui
  peuvent être inférées de F

F
F
27
Règles additionnelles

• X????YZ Þ????X -gt Y Projection
• X????Y, X????Z Þ?????X -gt YZ Union
  (addition)
• X????Y, WY????Z Þ?????WX -gt Z
  Pseudo-transitivité
• X????YZ Þ????X -gt Y, X -gt Z Décomposition
• X????X Auto-détermination
• X -gt Y, V -gt Z Þ XV -gt YZ Composition
• X????Y Þ????XW -gt Y Augmentation à gauche
• ....
• Prouver ces règles

28
Fermeture d'un attribut sous F

• Fermeture X (ou X ( F) ) d'un attribut X par
  rapport à F
• l'ensemble des attributs fonctionnellement
  dépendant de X sous la couverture fonctionnelle F
• X peut-être composé
• S S, SNAME, CITY, STATUS dans S
• Algorithme pour trouver X
• X X
• répète
• vieuxX X
• pour tout FD Y -gt Z dans F fait
• si Z???X alors X X ?????
• jusqu'à (vieuxX X)

29
Exemple

• Employes et leurs projetsEP ( SS, ENom, P,
  PNom, Loc, Temps)F SS -gt ENom P -gt
  PNom, Loc SS, P -gt Temps
• SS SS , ENom
• P P , PNom, Loc
• SS, P SS, P, ENom, PNom, Loc,
  Temps
• R (X1, X2, X3... X55)F Xi -gt Xi1 i
  1,2..54
• X3 ?

30
Recouvrement de couvertures de DFs

• Une couverture F couvre une couverture E si
  toute FD de E est dans F
• Toute FD dans E peut être inférée de F

F
Donc, E ??? F (facile à prouver)
F
E
E

• F couvre E si
• pour toute FD X -gt Y dans E,
• X ( F) inclue tous les attributs composant Y
• ceci est un algorithme

31
Exercices

• Est-ce que F couvre E ?
• E S -gt S F S -gt S, S -gt CITY
• E S -gt S F S -gt CITY
• E S -gt SNAME F S -gt CITY, SNAME
• E S -gt CITY, STATUS F S -gt CITY,
  SNAME

32
Equivalence de couvertures

• E et F sont équivalentes E?? F si F E

F
F
E
E
33
Couverture minimale

• Un ensemble F de DFs est minimal si
• Pour tout f X -gt Y dans F, Y est atomique
• ??f ??F , F \ f n'est pas équivalent à F
• ??f ??F, ?? A ??X, F \ f ????????Y n'est pas
  équivalent à F
• Une couverture minimale de F est l'ensemble
  minimal Fmin équivalent to F
• En général, il existe plusieurs Fmin pour F
• En général, on peut aisément trouver au moins une
  couverture minimale de R, mais....

34
Couverture minimale

• L'on ne connaît pas d'algo simple et général pour
  trouver tout Fmin ni même un Fmin
• La couverture minimale est aussi appelée
  irréductible
• Fmin avec tous les DFs X -gt Y, X -gt Z...
  unionées en une DF unique X -gt YZ.. est
  appelée canonique
• Fmin (P) dans la base S-P ?

35
Clés candidates

• Soit R une relation, C tous les attributs de R, F
  une couverture de R et X ???C
• X est une clé candidate de R si et seulement
  si on a X (F) C et X est minimal en ce
  qui concerne cette condition
• condition alternative X (Fmin ) C

36
Exercices

• Quelles couvertures sont minimales ou
  canoniques ?
• Pour P de la base SP
• F1 P -gt Pname, Color P -gt Weight
• F2 P -gt Pname P -gt Color P -gt Weight
• Pour SP avec
• F1 S, P -gt QTY S -gt S
• F2 S, P -gt QTY
• Pour EPM (E, SS, ENom, Dep) avec F E -gt
  SS E, Dep -gt ENom SS, Dep -gt
  ENom SS, Dep -gt E

37
1 NF

• Relation R est en 1 NF si toute valeur d'attribut
  est atomique
• cette normallisation crée des redondances !

O NF
1 NF
P1 P2 P3 P4
P1 P2 P3 P4
S1 S1 S1 S1
S1
Norm.
P1 P2 P3
S2 S2 S2
P1 P2 P3
S2
38
2 NF

• R est en 2 NF si R est en 1 NF et tout attribut A
  non-clé est pleinement (fonctionnellement)
  dépendant de la clé primaire
• S (S, STATUS, CITY) est en 2 NF
• FIRST (S, STATUS, CITY, P, QTY ) n'est pas en
  2 NF

STATUS
S
QTY
Contrainte additionnelle
P
CITY
39
Base S-P (Exemple canon)
SP
S
P
40
Pourquoi 2NF ?

• Considère la table
• FIRST (S, STATUS, CITY, P, QTY )

STATUS
S
QTY
Contrainte additionnelle
P
CITY
41
STATUS
S
QTY
CITY
P
42
1 NF

• Anomalies
• d'insertion
• pas de nouveau fournisseur sans aucune pièce
• de suppression
• suppression de la dernière pièce supprime toute
  info sur le fournisseur
• de mise à jour
• il faut mettre à jour des valeurs inutilement
  dupliquées
• consistance peut être détruite
• S'agit-t-il vraiment d'anomalies ??
• Rédondances !!
• les données d'un fournisseur sont dupliquées
  plusieurs fois

43
Table FIRST
Dernière info sur S3
44
Solution

• Décomposition sans perte
• On remplace une relation R par ses projections Ri
  i1,2.. telles que la jointure naturelle R' des
  Ri est égale à R, c. à d. pour toute extension
  de R
• il n'y a pas de tuples de R qui manquent dans R'
• il n'y a pas de tuples en plus dans R' par
  rapport à R

R1
R
R'
R2
45
Décomposition sans perte

• En général, il existe une décomposition sans
  perte en deux projections R1 et R2
• mais il y a en des cas où la seule décomposion
  sans perte est en i gt 2 projections
• on verra l'ex. en discutant la 5 NF
• Souvent R1 ou R2 est décomposable à son tour
• en conduisant aussi à une décomposition sans
  perte de R en i gt 2 relations

46
Décomposition sans perte

• Une décomposion de R muni de la couvert. fonct. F
  en R1 et R2 est sans perte ssi
• (R1???R2) -gt R1 \ R2 est dans F
• ou (R1???R2) -gt R2 \ R1 est dans F
• à vérifier sur notre exemple

47
Une autre formulation

• Toute relation R avec
• A, B, C attr. atomiques ou composés
• une DF A -gt B
• peut être décomposée sans perte enR1 (A, B) et
  R2 (A, C)
• Cette formulation est connue comme
• Théorème de Heath (1971)

48
Décomposition préservant les DF

• L'union des couvertures fonctionnelles des
  projections de R est équivalente à la couverture
  de R
• Une décomposition sans perte
• peut préserver les DFs
• décompositions en 3NF
• peut ne pas préserver les DFs
• décompositions en BCNF

49
Exercices

• Etud (E, Nom, Cours, Note)F E -gt Nom E
  , Cours -gt Note
• Décompositions sans perte ?
• EN (E, Nom), EC (E, Cours, Note)
• ENC (E, Nom, Cours), CN (Cours, Note)
• Et les DFs, sont-elles préservées ?

50
Normalisation en 2-NF

• Décomposition sans perte préservant les DFs
• Il peut y avoir plusieurs

SECOND (S, STATUS, CITY)
SP (S, P, QTY)
STATUS
S
S
QTY
P
CITY
51
SP (S, P, QTY)
S
QTY
P
SECOND (S, STATUS, CITY)
STATUS
S
CITY

• Vérifie que cette décomp. est sans perte !

52
Exemples courants

• Pers (SS, Nom, Ville, CP, Voit, Type)
• Pers (SS, ...) et PV (SS, Voit,..)
• Empl (E, Nom, Dep, Loc, Tache, Def, Perf)
• à décomposer en combien d'étapes ?
• Ami1 (SS, Nom, Tel, A-SS, A-Nom, A-Tel)
• Ami2 (SS, Nom, Tel, A-SS)
• Dessine les graphes de ref. pour Ami1 et Ami2 ?
• Equipe (E, Nom, EqNom)
• La modélisation naturelle souvent évite ces cas
  "naturellement"

53
2NF et clé candidates

• Une relation peut être en 2NF par rapport à la
  clé primaire (P), sans être en 2NF par rapport à
  une clé candidate (Nom, Tel)
• Pers (P, Nom, Tel, Salaire, Prefix-0?)
• Une définition stricte de 2NF est alors
  possible
• R est en 2NF, si R is en NF et tout A non-clé est
  pleinement dépendant de toute clé
• Divergences existent sur l'utilité de cette
  définition
• la décomposion en 3NF n'en dépend pas

54
3-NF

• Problème avec 2-NF
• DFs transitives conduisent à des anomalies
• ex. CITY et STATUS, CP et Ville,...
• R est en 3 NF si R est en 2 NF et tout attribut
  non-clé est non-transitivement et pleinement
  dépendent de la clé primaire

55
Décomposition 2 NF -gt 3 NFen projections
indépendantes

• On isole les attributs non-clé en DFs dans des
  projections telles que
• la décomposition est sans perte
• aucune MAJ d'une projection ne peut violer une
  DF originelle
• Les MAJ de SC ou de CS peuvent être faites sans
  violer une FD de SECOND
• Ce n'est pas le cas de projections dépendantes
• bien qu'une telle décomposition peut être aussi
  sans perte

56
Décomposition 2 NF -gt 3 NF
CS (CITY, STATUS)
SECOND (S, STATUS, CITY)
STATUS
STATUS
S
CITY
S
CITY
SC (S, CITY)
CITY
57
CITY
STATUS
CS (STATUS, CITY)
SECOND
CS Join SC
SC (S, CITY)
S
CITY
58
CITY
STATUS
CS (STATUS, CITY)
MAJ de CS
CS Join SC
SC (S, CITY)
S
CITY
59
CITY
STATUS
CS (STATUS, CITY)
MAJ de CS
CS Join SC
SC (S, CITY)
S
CITY
60
Décomposition en proj. dép.
S
STATUS
SS (S, STATUS)
SECOND
SC (S, CITY)
SC Join SS
S
CITY
61
S
STATUS
SS (S, STATUS)
MAJ de SS
CITY --gt STATUS
SC (S, CITY)
SC Join SS
S
CITY
62
Décomposition 2 NF -gt 3 NF

• Un autre exemple très courant

CV (CP, Ville)
Ville
Pers (SS, Ville, CP)
Ville
SS
CP
SS
PC (SS, CP)
CP
CP
63
Théorème de Rissanen

• Projections R1 et R2 de R sont indépendantes ssi
  
• toute FD de R peut être logiquement déduite de
  celles dans R1 et R2
• les attributs communs de R1 et R2 forment une clé
  candidate pour au moins une de ces relations
• Ex.
• FD S --gt STATUS peut être logiquement
  déduite de S --gt CITY et de CITY --gt STATUS
• Les projections dépendantes de SECOND
• (S, CITY) et (S, STATUS)
• Les projections indépendantes de SECOND
• (S, CITY) et (CITY, STATUS)

64
BCNF

• Problème avec 3 NF
• un attribut faisant partie d'une clé (candidate)
  peut-être non-pleinement dépendant de la clé
  primaire
• SSP (S, SNAME, P , QTY) avec S --gt SNAME
• R est en BCNF ssi tout déterminant est une clé
  candidate
• un déterminant est un attribut duquel un autre
  attribut est pleinement FD dépendant

65
SSP
66
BCNF

• SSP (S, SNAME, P , QTY) n'est pas en BCNF

S
SNAME
QTY
P
67
BCNF

• Décompositions (sans perte)
• SS (S, SNAME) SP (S, P, QTY)
• ou
• SS (S, SNAME) SP' (SNAME, P, QTY)
• SP et SP' sont en BCNF

68
Décomposition BCNF de SSP
SP
SSP
SS
69
Décomposition BCNF alternative
SSP
SP'
SS
70
Autres exemples

• Etud (E, SS, C, Note)
• Etud (E, SS) et EC (E, C, Note)
• Pers (Tel, Nom, SS, Visa, Cpte, Etat)
• Pers (Tel, Nom, SS), SC (SS, Cpte, Etat),
  SV (SS, Visa)
• Livre (ISBN, Loc, Enr, D-pret, Nom, Duree)à
  vous de jouer

71
4 NF

• R en BCNF peut mélanger les faits indépendants en
  dépendance multivaluée

COURSE TEACHER TEXT Physics Prof.
Green Basic Mechanics Prof. Brown Principles
of Optics Math Prof. Green Basic
Mechanics Vector Analysis Trigonometry
CTX
72
4-NF
COURSE TEACHER TEXT Physics Prof.
Green Basic Mechanics Physics Prof.
Green Principles of Optics Physics Prof.
Brown Basic Mechanics Physics Prof.
Brown Principles of Optics Math Prof.
Green Basic Mechanics Math Prof. Green
Vector Analysis Math Prof. Green
Trigonometry
CTX

• Anomalies
• ex. insertion d'un nouveau Prof de Math., Prof.
  Jaune ???3 tuples
• suppression d'un seul tuple de Prof. Green
  ???inconsistance dans la base.

73
4 NF

• Décomposition

CX
COURSE TEXT Physics Basic Mechanics Physics Prin
ciples of Optics Math Basic Mechanics Math Vecto
r Analysis Math Trigonometry
CT
COURSE TEACHER Physics Prof.
Green Physics Prof. Brown Math Prof. Green
74
Autre exemple(très courant)

• Pers (SS, PNom, Ville, CP, Dipl, DNom, Hobby)
• Pers (SS, PNom, Ville, CP), X (SS, .....)
• Pers (SS, PNom, CP), VC (Ville, CP), X (SS,
  .....)
• Pers (SS, PNom, CP), VC (Ville, CP), D (Dipl,
  DNom), PDH (SS, Dipl, Hobby)

Relation Troublion
75
Solution formelleDépendances multivaluées

• Soit R (A, B, C) une relation. Il y a une D M
  A --gtgt B Cdans R ssi, quelque soit C, à
  une valeur de A correspond toujours le même
  ensemble de valeur de B.
• Les attributs A, B, C peuvent être composés
• R a au moins 3 attributs
• R est en 4 NF ssi R est en BCNF et toutes les DMs
  sont des DFs

76
DMs dans CTX
COURSE TEACHER TEXT Physics Prof.
Green Basic Mechanics Physics Prof.
Green Principles of Optics Physics Prof.
Brown Basic Mechanics Physics Prof.
Brown Principles of Optics Math Prof.
Green Basic Mechanics Math Prof. Green
Vector Analysis Math Prof. Green
Trigonometry
CTX

COURSE --gt TEACHER TEXT

• CTX n'est pas en 4 NF

77
DMs dans CTX
COURSE TEACHER TEXT Physics Prof.
Green Basic Mechanics Physics Prof.
Green Principles of Optics Physics Prof.
Brown Basic Mechanics Physics Prof.
Brown Principles of Optics Math Prof.
Green Basic Mechanics Math Prof. Green
Vector Analysis Math Prof. Green
Trigonometry
CTX

COURSE --gt TEXT TEACHER
A --gt B C ???????A --gt C B
78
Décomposition en 4 NF

• R peut être décomposé sans perte en projections
  (indépendantes) R(A, B) et R(A, C) ssi les DMs
  A --gtgt B C sont présentes dans R.
• Toute R peut être décomposé en collections de
  relations en 4 NF
• CT et CX sont en 4 NF

79
Décomposition de CTX en 4 NF
R est en 4 NF ssi R est en BCNF et toutes les DMs
sont des DFs
CX
COURSE TEXT Physics Basic Mechanics Physics Prin
ciples of Optics Math Basic Mechanics Math Vecto
r Analysis Math Trigonometry
CT
COURSE TEACHER Physics Prof.
Green Physics Prof. Brown Math Prof. Green
80
5 NF

• Il existe des relations qui ne peuvent être
  décomposées qu'en trois composantes au moins

SP
PJ
SJ
SPJ
S P J S1 P1 J2 S1 P2 J1 S2 P1 J1 S1 P1 J1
S P S1 P1 S1 P2 S2 P1
P J P1 J2 P2 J1 P1 J1
J S J2 S1 J1 S1 J1 S2
Contrainte cyclique (S1, P1, J2) et (S2, P1,
J1) et (S1, P2, J1)????? (S1, P1, J1)
SPJ est sujet aux anomalies d'insertion et de
suppression, ex. delete (S1, P1, J1)
81
5 NF

• La contrainte cyclique évite le "piège de
  connection" (connection trap)
• Une relation R est n-décomposable pour n gt 2 si
  elle supporte une contrainte cyclique
• Relation SPJ est 3- décomposable
• Relation SPJ est en 3-dépendance de jointure
  (DJ) JD (SP, PJ, JS)

82

• Relation SPJ est en 3-dépendance de jointure
  (DJ) JD (SP, PJ, JS)

SP
PJ
SJ
SPJ
S P J S1 P1 J2 S1 P2 J1 S2 P1 J1 S1 P1 J1
S P S1 P1 S1 P2 S2 P1
P J P1 J2 P2 J1 P1 J1
J S J2 S1 J1 S1 J1 S2
83
5 NF

• R est en 5 NF (appelé aussi PJ/NF) ssi toute DJ
  est impliquée par les clés candidates de R, c. à
  d.
• soit il n'y a pas de DJ
• soit, toute projection contient une clé
• Toute R en 5 NF est en 4 NF (les DMs sont un cas
  spécial de DJs)

R (A,B,C) satisfait JD (AB, AC) ssi on a A --gtgt
B C
84
5 NF

• SPJ n'est pas en 5 NF car S, P, J ne sont
  pas clés candidates
• S' ( S, SNAME, STATUS, CITY ) est en 5 NF
  (( S, SNAME, STATUS), (S, CITY )) (( S,
  SNAME), (S, STATUS), (SNAME, CITY))etc.
• Y a-t-il des DJs dans les tables S, P et SP ?
• Sont-elles en 5 NF ?

85
5 NF

• Il existe un algorithme (Fagin) permettant de
  tester pour une DJ et un ensemble de clés
  candidats C est-ce que DJ est impliquée par C
• L'ex. précedent mpntre que ce n'est pas toujours
  évident
• Pour R donnée, on peut toujours dire si R est en
  5 NF pourvu que l'on connaît toutes les clés et
  toutes les DJs.
• Mais, découvrir les DJ peut ne pas être trivial
• 5 NF est une ultime NF pour les PJ
• toujours existante ou possible par une
  décomposition en projections
• libre d'anomalies discutées à éliminer par une
  décomp. en projections

86
Nouvelle vie de 3 NF

• Théorème Toute R en 3 NF avec une seule clé
  atomique est en BCNF, 4 NF et 5 NF(Date et
  Fagin, 1993)
• L a modélisation OO souvent conduit à de telles
  relations ou le OID est la clé

87
Algorithmique de décompositions(ou où le bat
blesse)

• Combien de DFs n-aires R peut avoir ( F ?)
  
• 22n
• En pratique souvent n 10 - 20 et donc on peut
  avoir
• F ???220 - 240
• Algorithme de génération de F
• (1) F F
• Applique les règles d'Armstrong, dans n'importe
  quel ordre à F
• Si il n'y a pas de f ??F générée, alors exit
• F F ??f goto (1)

88
Génération de couverture minimale

• (1) Décompose F
• Remplace toute DF (X -gt A1,..Ak de F avec k DFs
  X -gt Ak
• (2) Réduction à gauche du résultat de (1), soit
  Fm1
• Fm2 Fm1
• pour tout X -gt A ??Fm2
• Z X
• pour tout attribut x ?X
• F' Fm2 \ Z -gt A ??Z \ x -gt A
• si (F' ? Fm2 ) alors Z Z \ x Fm2
  Fm1 fin
• fin
• fin
• (3) supprime les DFs redondantes dans Fm2

89
Génération de couverture minimale(discussion)

• Complexité ?
• polynomiale dans F (Helman), à condition de
  tester l'équivalence par X et non pas par le
  test (F' ? Fm2 ) (et pourquoi ?)Helman,
  P. The Science of Database Management, 1994,
  588.
• Résultat unique ?
• non, (et pourquoi ?)
• il existent d'autres algorithmes pour générer une
  couverture minimale

90
Algo général de décomposition en 3NF sans perte
et préservant les DFs

• Une décomposition de R préserve F si l'union
  des projections F' est équivalente à F
• Théorème de Rissanen s'applique si on a les
  projections en 3NF
• Il ne spécifie pas comment obtenir ces
  projections
• Le bon sens suffit souvent
• L'algorithme qui suit fournit une réponse
  générale

91
Algo général de décomposition en 3NF sans perte
et préservant les DFs

• Entrée table R (A1 ... An) et son F (les DFs
  sur R) pas de nuls
• Sortie projections R' en 3 NF
• Trouve Fm
• Pour tout X d'une f X -gt Aj dans F
• crée un schéma (X, Ai1..., Aim ) où tout Aik est
  tel que X -gt Aik ? Fm
• Forme une table des attributs restant, s'il y en
  a
• Si aucune de tables créées ne contient la clé C
  de R, alors crée encore une table avec C comme
  attributs.

92
Application à notre exemple

• FIRST (S, STATUS, CITY, P, QTY )

Fm 1, 2, 3
STATUS
1
S
2
3
QTY
P
CITY

• On retrouve nos tables en 3NF
• SP (S, P, QTY)
• SC (S, CITY)
• CS (CITY, STATUS)

93
Un autre exemple

• S-P (S, STATUS, CITY, P, COLOR )

Fm 1, 2, 3
STATUS
S
1
2
3
COLOR
P
CITY
SC (S, CITY) CS (CITY, STATUS) PC (P,
COLOR SP (S, P)
94
Algo général de décomposition en 3NF sans perte
et préservant les DFs

• Preuve dans Ullman (par ex.). Complexité ?
• C'est un alg. parmi d'autres (ex. Helman)
• Et pourquoi ?
• Note que
• R est atomique si R ne peut pas être décomposé
  en projections indépendantes (notamment en 3NF)
• S n'est pas atomique (mais est-il souhaitable de
  le décomposer ?)
• SP est atomique Ullman, J. Principles of
  Database Knowledge-Base Systems. Computer Sc.
  Press, 1988
  

95
Algo général de décomposition sans perte en BCNF

• Entrée table R et ses FDs F pas de nuls
• D R
• tant qu'il y a une table Q pas en BCNF dans D
  fait
• trouve une dépendance X -gt Y de Q non-conforme à
  BCNF
• D D \ Q ??Q \ Y , ?X ??Y
• fin
• Preuve ? Complexité ?
• Les FDs ne sont pas toujours préservées (et
  quand sont elles, notamment ?)

96
Application

• SSP (S, SNAME, P , QTY)

Fm 1, 2
2
S
SNAME
1
QTY
P
D SSP (S, SNAME, P , QTY) SSP S,
P, QTY), SN (S, SNAME)
97
Application

• SSP (S, SNAME, P , QTY)

Fm 1, 2
2
S
SNAME
1
QTY
P
D SSP (S, SNAME, P , QTY) SSP
SNAME, P, QTY), SN (S, SNAME)
98
Est-ce que toute cette théorie est pratique ?

• C'est une bonne question !
• Peu, par rapport aux espérances dans ses années
  d'or (1970 - 1980)
• L'intuition en général suffit pour 3NF
• Le Th. de Date-Fagin montre que la portée des
  intuition était même grande que l'on pensait
• Les "anomalies" qui restent pèsent en général peu
  comparées aux effets de de jointures
• détérioration de performances de requêtes
• complexité accrue de la formulation de requêtes
• Les valeurs nulles fichent les principaux
  résultats en l'air

99
Est-ce toute cette théorie pratique ?

• Il est possible d'insérer dans table S avec la FD
  CITY -gtSTATUS l'info sur le status de IBM
• (S5, IBM, nul, 400) / dans (S, SNAME, CITY,
  STATUS)
• Ceci n'est plus possible après la décomposition
  très OK sans valeurs nulles
• (S, SNAME, CITY) (CITY, STATUS)
• Un désavantage souvent
• En général, un article sur une décomposition
  n'est plus accepté pour les princip. confs de
  recherche en BDs (SIGMOD, VLDB...)

100
Concepts avancés

• Moins utiles jusque-là
• Relation universelle (voir Ullman, 1982)
• toute relation R dans la base est une projection
  d'une relation U avec tous les attributs de la
  base
• Idée éviter les equijointures
• Problèmes non-résolus
• pas toujours possible, les nuls...
• Dépendances d'inclusion (inter-relationnelle
  s), DK/NF, schémas acycliques...

101
Relations atomiques

• Toute table contient une clé et au plus un
  attribut non-clé
• décompostion complète
• l'approche apparentée à
• Data Semantics d'Abrial
• modèle OO de système IRIS
• Il y a des liens d'intégrité ref. entre les
  tables
• Utilisés notamment pour les jointures implicites
• internes ou externes
• S (S), SN (S, SNAME), SC (S, CITY), SS (S,
  STATUS)
• P (P), PN (P, PNAME), PC (P, COLOR), PW (P,
  WEIGHT)
• SP (S, P), SP (S, P, QTY) ou SP (SP), SPS
  (SP, S), SPP (SP, P), SPQ (SP, QTY), ...

102
Relations atomiques

• Avantages
• pas d'anomalies
• pas de normalisation
• moins de problèmes de valeurs nulles
• approche pleinement applicable sous SGBD
  modernes, ex. MsAccess, surtout sous QBE
• Problèmes
• Performances
• jointures nombreuses
• place mémoire

103
Tables à attributs hérités(Litwin al, 1992)
Etudiant
Personne
Année variable système avec l'année en
cours Age Année - Né
L valeur héritée peut être surécrite
104
Tables à attributs hérités(ex. de déf. en
pseudo-SQL)

• Create Table Personne
• SS Integer
• Nom Character
• Né Integer
• Age Select Année - X.Né From Personne Personne
  X, Where SS X.SS
• Create Table Etudiant
• Select SS, Nom From Person
• Etud Integer

105
Tables à attributs hérités

• Application (limitée)
• Le SGBD relationnel-objet UNISQL ()
• Une table peut hériter une autre table en entier
• Ceci modélise le sous-typage dans les OODBMSs
• UNISQL est annoncé vendu en 500 licences (1995)
• Kim, W. Modern Database Systems. Academic
  Press, 1994

106
Et pour en savoir plus

• Lire et s'exercer en TD

Fin
107
Exercices

• Modélise naturellement tous les bases exemples
  du cours,en dessinant les graphes de référence
• Refait l'exercice en utilisant l'approche
  formelle avec les dépendances fonct. et
  mulitval.
• Compare les schémas résultants
• Définit 2 ex. de bases qui ne sont pas en
  1-ONF. Puis, normalise les schémas.
• Prouve que toute relation en BCNF est en 3NF.
• Prouve que l'inverse n'est pas vrai.
• Propose un schéma simplifié mais réaliste d'une
  Banque ou les clients ont comptes courants et
  SICAV dont les chargés de clientèle s'occupent