Modèle Relationnel de Données

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é < 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 n’a 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 < 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 relationnelletransforme 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 Voir aussi le cours sur l’algèbre relationnelle • Sélection : • Projection • Restriction • Jointure • Division • Agrégation • Opération suppl. • 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 attributpeut ê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 S1 S1 S1 S1 P1 P2 P3 P4 S1 Norm. S2 S2 S2 P1 P2 P3 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 d’ue relation en 0-NF suffit • Il faut 3*5*3*3*2 = 270 tuples pour une relation en 1-NF ! • Solution : normalisation en i-NF ; i > 1 • voir le cours sur la normalisation relationnelle

27. 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,... • Catomiqueconsiste d’un attribut • Ccomposite 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 SSPers (Nom, Prénom, SS#, Tel) • A l'état civilPers (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 uneclé étrangère dans R’ • F n'est pas en général une clé de R'

32. Clé candidate Clé candidate Clé candidate composée 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. 1:1 et 1:N • Jointures implicites

34. Intégrité référentielle Produit P# Femme F# Mari F# 1 1 1 1 N N PP#, PS# Produit Composé Femmes M#, F# Mari M# N 1 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. Valeurs nulles • Une valeurnulle est un abus de langage pour designer une absence de valeur d’un 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 s’il le faut ? • Pour l’attribut #TEL faut distinguer entre: • # tel portable inconnu • on relancera la personne pour connaître son numéro • Personne sans téléphone portable • L’utilisation d’un nul pour un attribut peut être interdite

39. Les nuls et les clés • 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é ? Un attribut-clé ne peut être nul

40. Les nuls en perspective On peut interdire en pratique la présence d’un nul pour un attribut • Dans la définition de l’extension 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 n’est pas sa valeur • un fournisseur... Nom Code postal S# Code postal S City Name Status

43. 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 1:1, 1:N ou M:N • évalue à un ensemble de valeurs atomiques • père d’une personne • fournitures d'un fournisseur • hobbies d’une personne • pièces fournies par un fournisseur • non-fonctionnelle m-aires ; m > 2 • fournitures d'un fournisseur pour des projets • patients soignés dans des services et suivis par des médecins

44. Modélisation relationnelle (Réel -> 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 1:N • Un typeT d'objets structurés O  une relationR • Un type d'objets atomiques  un domaine D • Noms, Entier, Texte,... • Une propriété fonctionnellenon-transitive de tout O  un attribut de R • une application nommée d'un objet dans un domaine • SNAME, WEIGHT, COLOR...

45. 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# ?

46. Modélisation relationnelle (clés étrangères) • Une propriété non-fonctionnelle 1:N 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èreF =C • C est la clé primaire de la table-source de la propriété • On verra plus tard les propriétés M:N • 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#,..

47. 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 s’agit 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

48. 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 1:NP.P# -> SP>P# • On sépare "les torchons et les serviettes" • On fait intuitivement une normalisationrelationnelle • formelle, en 2NF, 3NF et BCNF...

49. Modélisation relationnelle (propriétés binaires M:N) • Une propriété P non-fonctionnelle de O peut s'avérer M:N • 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 1:N • les fournitures SP, l'amitié… • peut avoir ses propres propriétés (QTY) Amitié SP P S Amis S P

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