Conception dune base de donnes relationnelle Partie 1

1
Conception d une base de données
relationnellePartie 1

• Witold Litwin

2
Base de données relationnelle
Fichier
table
ou
relation
Donnée
ligne
ou
attribut
atomique
Opérations transformations de tables en
une
table
Opération relationnelle
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é
• Schéma conceptuel Définition de la collection

4
Schéma de BD Entreprise
clé

• Empl (E, Nom, Prénom, Né, Rue, CodePost, Ville,
  Dep)
• E Counter Nom Text Né Date Dep Int...
• Syst-date - Né lt 65 Contrainte de validation
• Dep Not Null Contrainte d'existence
• Taches (T, Description)
• Planning (E, T, Date-fin, Avancement)
• Dep (Dep, Name)
• Trigger on EmplOn Insert Check-Ref-Int (Dep,
  Empl.Dep)
• Autres Déclencheurs utiles ?
• Ce schéma est possible sous MsAccess, bien que
  exprimé différemment

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
7
P
Create View P1 as select P, PNAME, COLOR from P
P1
8
P
Create View P1 as select P, PNAME, COLOR from P
Create View P1 as select P, PNAME, COLOR from P
where CITY 'London'
P1
P2
9
P
10
P
P1
P2
11
Base relationnelle
Tables réelles
12
Base relationnelle
Tables 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 na que des
  relations finies
• Les Di,j sont les attributs de R les rôles de
  domaines (Codd)

14

Schéma d'une relation

• 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

15
Un état de la base S-P
Intention de S
SP
S
Une extension de S
P
16
Egalité de relations

• 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

17
Une même relation S
18
MAJ / Restructuration

• 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

19
SQL MAJ / Restructuration ?

• Empl (E, Nom, Prénom, Age, Rue, CodePost, Ville,
  Dep) Age lt 65 Contrainte de validation Dep
  Not Null Contrainte d'existence
• Update Empl Set Age 35 Where E '123'
• Update Empl 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

20
Opérations relationnelles

• 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

21
Opérations relationnelles

• Sélection
• Projection
• Restriction
• Jointure
• Agrégation
• Mise à jour
• Création d une vue

22
Opérations relationnelles (SQL)

• Voit (Im, Pref, Mod, Couleur) Amende (A,
  I, Nom, Addr, Payée)
• 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
Relations

• 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

24
Relations

• En théorie, un domaine et donc un attribut peut
  être un ensemble
• 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

25
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
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 due relation en 0-NF suffit
• Il faut 35332 270 tuples pour une relation
  en 1-NF !
• Solution normalisation en i-NF i gt 1
• voir le cours sur la normalisation relationnelle

27
Relations

• 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)
• Un langage de base de données peut mélanger les
  types d'expression ci-dessus (SQL)
• Calcul de tuple et algèbre

28
Clés

• 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 Etudiant, Nom de pays, (Nom,
  Prénom, Tel), Oid,...

29
Clés

• 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é !

30
Relations

• 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é

31
Relations

• 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é d'une relation R peut être des attributs F
  d'une autre relation R'
• F deviennent une clé étrangère
• F n'est pas en général une clé de R'

32
Clé primaire

• Voit (Châssis, Moteur, Plaque, Mod, Poids,
  Coul )

Etud (E, Nom, Prénom, Tel, Adresse
) Participants (C, E, Note)
Clé étrangère
33
Relations

• 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. 11 et 1N
• Jointures implicites

34
Intégrité référentielle
Produit P
Mari F
Femme F
1
1
1
1
N
N
PP, PS Produit Composé
Mari M
Femmes M, F
1
N
Amie A
Ami A
?
?
35

Relations

• 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

36

Les nuls

• Tout tuple doit contenir la clé primaire
• D'autres attributs peuvent être nuls
• Un nul est une valeur inconnue
• En d'autres termes, c'est une absence de valeur
• Par abus de langage on parle néanmoins de valeurs
  nulles
• Est-ce que la clé dans un tuple peut être nulle ?

37

38
Modélisation relationnelle (démarche intuitive)

• Une base est un modèle d'une entreprise
  (ANSI-SPARC)
• Une entreprise est une collection structurée
• d'objets réels
• éléments identifiables de l'univers de
  l'entreprise
• un fournisseur, une pièce, une fourniture, un
  nom, une ville...
• de types d'objet
• ensembles d'objets ayant une intention commune
• les fournisseurs, les pièces, les villes...
• de propriétés des objets
• applications de types d'objet en types d'objet
• ville de fournisseur, fournitures d'un
  fournisseur...

Paris
Villes
Fournisseur
Villes
39
Modélisation relationnelle (démarche intuitive)

• Un objet sans propriétés est un objet atomique
• Son OID est sa valeur
• Nom, Poids, Un nombre entier
• Un objet avec les propriétés est un objet
  structuré
• Il a un OID qui nest pas sa valeur
• un fournisseur...

Nom
Code postal
S
Code postal
S
City
Name
Status
40
Modélisation relationnelle(démarche intuitive)

• Une propriété
• est nommée
• SNAME, WEIGHT
• peut être
• fonctionnelle (non-transitive ou transitive)
• elle évalue à une valeur atomique
• nom d'un fournisseur...
• non-fonctionnelle binaires 11, 1N ou MN
• évalue à un ensemble de valeurs atomiques
• père dune personne
• fournitures d'un fournisseur
• hobbies dune personne
• pièces fournies par un fournisseur
• non-fonctionnelle m-aires m gt 2
• fournitures d'un fournisseur pour des projets
• patients soignés dans des services et suivis par
  des médecins

41
Modélisation relationnelle (Réel -gt Schéma
Conceptuel)

• Identifie les objets, les types et les propriétés
• pas de règles formelles
• les propriétés non-fonctionnelles doivent être
  toute de type 1N
• Un type T d'objets structurés O ? une relation R
• Un type d'objets atomiques ? un domaine D
• Noms, Entier, Texte,...
• Une propriété fonctionnelle non-transitive de
  tout O ? un attribut de R
• une application nommée d'un objet dans un domaine
• SNAME, WEIGHT, COLOR...

42
Modélisation relationnelle (clé primaire)

• Un O ? une valeur d'une clé primaire C de R
• un ou plusieurs attributs constituant une clé
  candidate de R
• SS, (Nom, Tel), E...
• un attribut nouveau dont la valeur constituera
  l'OID
• si aucune clé candidate de R n'est un
  identifiant possible ou souhaitable pour O
• il s'agit de OID au sens valeur sans
  sémantique
• définie automatiquement ou manuellement
• S, P, SP ?

43
Modélisation relationnelle (clés étrangères)

• Une propriété non-fonctionnelle 1N d'un objet O
  ? une référence à O dans la table de l'objet
  cible
• une valeur de l'attribut qui constitue la clé
  étrangère F C
• C est la clé primaire de la table-source de la
  propriété
• On verra plus tard les propriétés MN
• La cible peut être la source elle-même
• Une relation peut être la cible de plusieurs
  propriétés
• fonctionnelles ou pas
• SP (S, P...)

Propriétés (Pnom,SS...
Pers (SS,..
44
Modélisation relationnelle (propriétés
transitives)

• Une propriété fonctionnelle de O peut se révéler
  transitive
• Il s'agit en fait d'une propriété de O' qui est
  lui même une une cible d'une propriété de O
• SP' (S, P, QTY, PNAME, COLOR...)
• QTY est une propriété non-transitive de SP
• PNAME, COLOR sont des propriétés transitives de
  SP
• car il sagit en fait de propriétés non
  transitives de P
• Les propriétés transitives doivent être éliminées
• elles donnent lieu aux duplications et anomalies
  de mise à jour
• PNAME, COLOR sont inutilement répétés pour toute
  fourniture d une même pièce P
• on reverra ce concept en détail plus tard

45
Modélisation relationnelle (démarche intuitive)

• Il faut mettre une telle propriété dans R où elle
  est directe (non-transitive)
• SP (S, P,QTY) P (P, PNAME, COLOR)
• On retrouve la restructuration par décomposition
• Et une référence 1N P.P -gt SPgtP
• On sépare "les torchons et les serviettes"
• On fait intuitivement une normalisation
  relationnelle
• formelle, en 2NF, 3NF et BCNF...

46
Modélisation relationnelle (propriétés binaires
MN)

• Une propriété P non-fonctionnelle de O peut
  s'avérer MN
• Les pièces fournies par un fournisseur quand une
  pièce peut avoir plusieurs fournisseur
• S (S, SNAME..P, QTY..)
• Alors, on a omis d'identifier un type d'objet
  tel que P correspond à deux (ou plus) propriétés
  1N
• les fournitures SP, l'amitié
• peut avoir ses propres propriétés (QTY)

Amitié
SP
Amis
S
P
S
P
47
Démarche intuitive (limites pratiques)

• Ces règles sont en général suffisantes en
  pratique
• Jusqu'à 4 NF (incluse)
• Cependant, elle ne sont pas en général univoques
• elles peuvent donner lieu à plusieurs schémas
  différents
• elles ne sont pas non plus suffisantes pour tous
  les cas
• Ex. mise en 1-O NF (à voir plus tard), puis 5NF
• Il ne semble pas qu'il existe des réglés
  universelles

48
Démarche intuitive (limites pratiques)

• Les SGBD relationnels n'ont que les domaines
  génériques
• Char, Integer, Float, Counter, Memo...
• En SQL-3 il y aura des domaines spécifiques
• Age, Ville...
• En attendant, si besoin est on peut simuler les
  domaines spécifiques avec des relations unaires
  et les contraintes d'intégrité ref.
• S ( S, SNAME, Status...)STATUS (Status)
  / Les statuts possibles

49
Modélisation relationnelle

• Graphe de références
• Les noeuds sont toutes les relations
• Les arcs orientés sont toutes les contraintes
  d'intégrité référentielle C -gt F
• Une base relationnelle n'est correctement
  définie que si son le graphe de références est un
  graphe connecté.

50
Spécifications fonctionnelles
Exemple canon

• 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

51
Exemple canon
Fournisseurs
52

Exemple canon
Fournisseurs
Pièces
53

Exemple canon
Fournisseurs
M
N
Pièces
54
Exemple canon
N
Fournitures
1
1
N
Fournisseurs
Pièces
55
Exemple canon
Fournitures
S
P
Fournisseurs
Pièces
S
P
56
Exemple canon
SP
S
P
57
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)
• Moins de problèmes de cohérence des MAJ.
• Simplicité de conception
• Problèmes
• Comment trouver le Nom du fournisseur de pièces
  rouges ?
• etc..

58
Solution

• Opération relationnelle de jointure entre les
  relations
• en SQL
• SELECT SNAME FROM S, SP, P WHERE S.S
  SP.S AND SP.P P.P AND COLOR 'RED'

59
1
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 et les pièces sont en
  correspondance M N 1 lt M, N lt ?
• représentée en général comme ci-dessus par trois
  objets et deux relations 1N

1
N
N
SP
S
P
60
Propriétés non-fonctionnelles m-aires

• Représentés par un objet cible de m gt2 liens
  référentiels
• Base S-P-T chaque pièce fournie est utilisée
  pour une tache T
• SPT (S, P, T, QTY)

1
N
N
SPT
1
S
P
N
1
T
61
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

P
S
M
62
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

1
N
N
SP
1
P
S
M
63
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

1
N
N
SPM
1
P
S
N
1
M
64
La vie est moins facile

• Un employé travaille dans un (seul) département.
• A partir du schéma Emp (E, Enom, D, Dnom,
  Loc)
• on fait dhabitude
• Emp (E, Enom, D) D (D, Dnom, Loc)
• mais pourquoi pas les rel. atomiquesEmpn (E,
  Enom), Empd (E, D), Dn (D, Dnom), Dl (D,
  Loc), L (Loc)
• résultat possible de la démarche intuitive, si on
  commence par le type d'objet Localisations
• Pas de bonne réponse générale dans la théorie des
  bases relationnelles

65
La vie est moins facile

• E et Enom sont des clés candidates (OIDs) 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 peut-être E (Enom, C, Note) et EN (E,
  Enom)

66
La vie est moins facile

• 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) ?

67
La vie est moins facile

• Si une personne a les diplômes, les voitures et
  les hobbies, alors peut-on dire formellement
  pourquoi le schéma (P, Nom, Dipl, Voit, Hobby)
• n'est pas en général souhaitable ?

68
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 imp. récentes
• Date Fagin, Krishnamourthy Litwin...
• Le tout dans un autre cours ....

69
FIN

• Merci de votre attention
• W. Litwin

70
(No Transcript)