Mod

1
Modèle Relationnel de Données

• 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é
• Autres (procédures stockées)
• 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 P2 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 ?

• Emp (E, Nom, Prénom, Age, Rue, CodePost, Ville,
  Dep) Age lt 65 Contrainte de validation Dep
  Not Null Contrainte d'existence
• Update Emp Set Age 35 Where E '123'
• Update Emp Set Age 75 Where E '456'
• Alter Emp Add Tel Integer
• Alter Emp Drop Ville Create Table CP -
  V CodePost Int Ville Text Primary key
  (CodePost, Ville)
• C'est une décomposition d'une relation

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
• Division
• Agrégation
• Opération suppl.
• Mise à jour
• Création d une vue

Voir aussi le cours sur lalgèbre relationnelle
22
Opérations relationnelles (SQL)

• Voit (Im, Pref, Mod, Couleur) Amende (A,
  I, Nom, Addr, Payé)
• Select From Voit
• Select Mod From Voit Where Couleur 'rose'
• Select Nom, Addr From Amende, Voit Where Payé Is
  Null and Mod 'Ferrari' and I Im
• Update Amende Set Payé '10-01-96' where A
  '123'
• Create View En-instance AsSelect From Amende,
  Voit WherePayé Is Null and Amende.I Voit.Im

23
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 général, un domaine et donc un attribut peut
  être un ensemble
• XML
• Dans les SGBD actuels, ils ne sont considérés
  pour les opérations relationnelles que comme des
  éléments (valeurs) atomiques
• De telles relations sont dites normales

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 Étudiant, Nom de pays, (Nom,
  Prénom, Tel), Oid,...
• C atomique consiste dun attribut
• C composite en contient plusieurs

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

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 M
Femme F
1
1
1
1
N
N
PP, PS Produit Composé
Mari M
Femmes M, F
1
N
Amie A
Ami A
M
N
Comment faire ?
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

Valeurs nulles

• Une valeur nulle est un abus de langage pour
  designer une absence de valeur dun attribut
• On dit aussi un nul

37

Types de nuls

• Valeur inconnue
• Ville de fournisseur inconnue
• Valeur inapplicable
• Fournisseur connu pour être sans statut
• Cette distinction est rarement appliquée en
  pratique

38

Types de nuls

• Comment faire alors sil le faut ?
• Pour lattribut TEL faut distinguer entre
• tel portable inconnu
• on relancera la personne pour connaître son
  numéro
• Personne sans téléphone portable
• Lutilisation dun nul pour un attribut peut être
  interdite

39
Les nuls et les clés
Un attribut-clé ne peut être nul

• Pourquoi ?
• Peut-on néanmoins en pratique
• Sur MsAccess, Oracle, DB2
• Autoriser une valeur nulle pour un attribut-clé ?
• Créer des relations sans clé ?

40

Les nuls en perspective

• On peut interdire en pratique la présence dun
  nul pour un attribut
• Dans la définition de lextension de la relation
• La théorie initiale du modèle relationnel ne
  prévoyait pas de nuls
• Leur introduction (par les praticiens) a crée de
  nombreux problèmes
• beaucoup restent non-résolus
• voir les cours sur SQL

41
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
42
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
43
UML

• Des diagrammes standard proposées par OMG
• Données, Opérations, Messages
• Notamment pour les BDs
• Une adaptation dans de dernier but du modèle ER
• Une autre présentation de certains diagrammes
• Les concepts OO
• Composition, Agrégation

44
UML

• Objet Entité (Entity)
• Type Type ou classe
• Propriété Association (Relationship)

45
UML
Nom de lassociation
Diagramme de note en UML
Lécole peut envoyer entre 0 et 8 étudiants à
un exam
Modèle dune auto-école basé sur lex. de M.
Manouvrier
Appartient à
Rôle de lassocion (directionnelle)
46
Réification dune classe dassociations
47
Réification dune classe dassociations
48
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

49
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

50
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...

51
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 ?

52
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,..
53
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 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

54
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...

55
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
56
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

57
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

58

• Modélisation relationnelle
• Graphe de références

• Une BD relationnelle en général comporte
  plusieurs relations
• Un graphe de références représente sa structure
• Les nœuds sont des relations
• Les arcs orientés sont les contraintes
  d'intégrité référentielle C -gt F
• 1N ou 11
• Les autres sont les liens sémantiques

• Une base relationnelle n'est correctement
• définie que si son le graphe de références
• est un graphe connecté.

59
Modélisation relationnelle Conception dune BD
relationnelle

• Il faut minimiser le nombre de nœuds du graphe
  de références
• Sous contraintes
• Dabsence danomalies
• dinsertion, suppression, MAJ
• De préservation de dépendances fonctionnelles

60
Modélisation relationnelle Résultat typique

• Plusieurs relations
• Chaque relation consistant
• dune clé
• de max dattributs identifiés chacun comme une
  fonction atomique de la clé

61
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

62
Exemple canon
Fournitures
S
P
Fournisseurs
Pièces
S
P
63
Exemple canon
SP
S
P
64
Pourquoi S-P est comme ça ?

• Avantages
• Pas de duplicata de valeurs d'attributs entre les
  tables S, SP, et P,
• sauf le strict minimum (les clés)
• Pas danomalies.
• On verra cette notion dans le cours suivant.
• Efficacité de stockage.
• Pas dattribut-clé unique pour SP
• Compare à la conception en une seule relation
• Problèmes
• Comment trouver le Nom du fournisseur de pièces
  rouges ?
• etc..

65
Solution

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

66
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
67
Propriétés non-fonctionnelles m-aires

• Représentées 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
68
Base Hôpital
La vie est moins facile

• 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
69
La vie est moins facile

• Un employé travaille dans un (seul) département
  ayant un nom et une localisation.
• On fait une relation ?
• Emp (E, Enom, D, Dnom, Loc)
• Ou deux ?
• Emp (E, Enom, D) D (D, Dnom, Loc)
• Et pourquoi pas les rel. atomiques Emp (E),
  Empn (E, Enom), Empd (E, D), D (D), Dn (D,
  Dnom), Dl (D, Loc), L (Loc)
• Pas de bonne réponse générale dans la théorie des
  bases relationnelles

70
La vie est moins facile

• E et Enom sont des clés candidates pour un
  étudiant.
• Quel schéma choisir pour créer la base des notes
  des étudiants ? E (E, Enom, C, Note) ? E
  (E, Enom, C, Note) ?
• ou peut-être
• E (E, C, Note) et EN (E, Enom)
• ou enfin E (Enom, C, Note) et EN (E,
  Enom)

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

72
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 ?

73
Solutions

• Théorie formelle de la conception relationnelle
• Fondée dans les années 70 - 80
• Codd, Boyce, Fagin, Armstrong, Bancilhon,
  Spyratos, Delobel, Date, Ullman, Sagiv, Vardi...
• Largement délaissée depuis
• Mais il y a des contributions récentes
• Date Fagin, Krishnamourthy Litwin...

74
FIN

• Merci de votre attention
• W. Litwin

75
(No Transcript)