Modle Relationnel UML 09 10 Witold Litwin 1

Modèle Relationnel & UML 09 - 10 Witold Litwin 1

BD Relationnelle Le Rapport de Recherche qui a lancé les SGBDs Relationnels (publié uniquement en interne à IBM Almaden Research Center (CA) 2 2

BD Relationnelle Le Rapport de Recherche qui a lancé les SGBDs Relationnels (Résumé) 3 3

BD Relationnelle Le Rapport de Recherche qui a lancé les SGBDs Relationnels (Table des Matières) 4 4

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 5

Exemple S SQL: Select S#, SNAME, STATUS FROM S WHERE CITY = ‘Paris’ Algèbre relationnelle : (S WHERE CITY = 'Paris') [S#, SNAME, STATUS] 6 6

Base de données relationnelle u Une collection d'objets : u Relations réelles (tables de base) u Liens sémantiques u Contraintes d'intégrité (surtout référentielle) u intra-relationnelles u monoattribut et multiattribut u inter-relationnelles u Intégrité référentielle surtout Déclencheurs (ang. triggers) notamment pour maintenir l'intégrité u Autres (procédures stockées…) u u 7 Schéma conceptuel = Définition de la collection

clé Schéma de BD Entreprise Empl (E#, Nom, Prénom, Né, Rue, Code. Post, 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 Déclencheurs utiles ? u Ce schéma est possible sous Ms. Access, bien que exprimé différemment 8

Schémas Externes u Schéma (vue) externe = Collection de vues relationnels (tables virtuelles dérivées de relations réelles) u Un usager ne voit pas de différence entre une vue relationnelle et une table réelle u En principe ! L Une vue relationnelle n'est pas une vue externe au sens ANSI-SPARC L Celle-ci serait une base virtuelle 9

P 10

P Create View P 1 as select P#, PNAME, COLOR from P; P 1 11

P Create View P 1 as select P#, PNAME, COLOR from P; Create View P 2 as select P#, PNAME, COLOR from P where CITY = 'London'; P 2 12 P 1

P 13

P P 1 P 2 14

Base relationnelle Tables réelles 15

Base relationnelle Tables réelles et vues 16

Relations u Di ; i = 1, 2. . n des ensembles dits domaines u Une relation R est un sous-ensemble de produit cartésien: u. R u Les Di, 1 x Di, 2. . . x. . . Di, k k = 1, 2. . . Di, j sont les attributs de R ; les rôles de domaines (Codd) u Les éléments de R sont dit tuples u Il n’y a pas de tuples égaux dans une relation 17

Relations u Dans une BD relationnelle, on n’a que des relations finies u En nombre d’attributs et en nombre de tuples u Toute u Pas u un ensemble De telles relations sont dites normales u 18 valeur d’un d Di est atomique Aussi en 1 NF au moins

Relations Attribut multi-valeur 0 NF 19 S 1 P 2 P 3 P 4 S 2 P 1 P 2 P 3 Attribut atomique 1 NF S 1 S 1 S 2 S 2 P 1 P 2 P 3 P 4 P 1 P 2 P 3

Schéma d'une relation u Les noms R et Di, j constituent le schéma de la relation u Ce schéma et l'ensemble des éléments possibles de R constituent une intention de R. u Les éléments de R y présent à un moment donnée constituent une extension de R. u Une mise à jour modifie une extension et change l'état de la base 20

Intention de S Un état de la base S-P SP S Une extension de S P 21

Egalité de relations u. Deux relations R et R' sont égales si elles diffèrent seulement par ordre : u d'attributs (colonnes) u de tuples (lignes) 22

Une même relation S 23

u Une MAJ / Restructuration mise à jour est correcte si la nouvelle extension est dans l'intention de R u C'est le rôle des contraintes d'intégrité de ne permettre que les mises à jour correctes u Un changement de schéma de R est une restructuration 24

SQL : MAJ / Restructuration ? Emp (E#, Nom, Prénom, Age, Rue, Code. Post, 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 ; 25

Opérations relationnelles u Une relation est un fichier qui supporte les opérations relationnelles u Une opération relationnelle transforme des relations arguments dans une relation résultat : u une relation temporaire n'appartenant pas au schéma de la base. u une relation de la base (mise à jour) u une vue 26

Opérations relationnelles u Pour une BD relationnelle, les opérations sont définies sur les relations normales u Celles basiques forment l’algèbre relationnelle u u Définie par E. Codd En pratique, il y a aussi des opérations additionnelles u Arithmétiques, 27 agrégations…

Op. ensemblistes: UNION, INTER, DIFF, TIMES u Sélection : u Projection u Restriction u Jointure naturelle ou u Division u Agrégation u Opération suppl. u Mise à jour 28 u Création d ’une vue Opérations relationnelles Voir le cours sur l’algèbre relationnelle

Base S-P Ids et noms de fournisseurs S S [S#, SNAME] S [C IT Y S WHERE CITY = Paris ] Villes de fournisseurs 29

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 d’autres tuples u X, Y, Z peuvent être composés Est-ce que la jointure naturelle est commutative et/ou associative ? A JOIN B =? B JOIN A A JOIN B JOIN C = ? A JOIN (B JOIN C) q q q 30

CS S [STATUS, CITY] SC ] S[ S#, Y T I C CS JOIN SC 31 S

S SP 32 S# SName Status City s 1 smith 20 London s 2 Jones 10 Paris s 3 Blake 30 Paris s 4 Clark 20 london S JOIN SP s 5 Adams 30 Athens S# SName Status City P# qty s 1 smith 20 London p 2 200 s 1 smith 20 London p 3 400 qty Fournisseur avec les fournitures s# P# s 1 p 1 300 s 1 smith 20 London p 4 200 s 1 p 2 200 s 1 smith 20 London p 5 100 s 1 p 3 400 s 1 smith 20 London p 6 100 s 1 p 4 200 s 1 smith 20 London p 1 300 s 1 p 5 100 s 2 Jones 10 Paris p 1 300 s 1 p 6 100 s 2 Jones 10 Paris p 2 400 s 2 p 1 300 s 3 Blake 30 Paris p 2 200 s 2 p 2 400 s 4 Clark 20 london p 2 200 s 3 p 2 200 s 4 Clark 20 london p 4 300 s 4 p 2 200 s 4 Clark 20 london p 5 400 s 4 p 4 300 s 4 p 5 400

-jointure u Table C égale à : C = ( A TIMES B ) WHERE X Y est la jointure de tables A(X, . . . ) et B (Y, . . . ). X et Y ne sont pas ici composites et , , , , u La jointure est notée : C = A JOIN B ON X Y. 33

-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ù l’attribut Y de jointure peut être de plus composite que la jointure est commutative et/ou associative ? u. Est-ce 34

Division u Table C ( X ) notée: 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 Tout fournisseur de pièces P 1 et P 2. S# P# S 1 P 1 S 1 P 2 S 2 P 1 S 2 P 3 DIVIDEBY est associatif ou commutatif ? 35 P# P 1 P 2 S# S 1

Requêtes algébriques à la base S-P u u (( S JOIN SP ) WHERE P# = 'P 2' ) [ SNAME] (( S JOIN SP ) WHERE P# = 'P 2' ) WHERE STATUS > 100 ) [ SNAME] (((P WHERE COLOR <> 'Red' ) [P#] JOIN SP ) [S#] JOIN S [SNAME] (((P WHERE COLOR <> 'Red' ) [P#, PNAME] JOIN SP ) [S#, PNAME] JOIN S [SNAME] (( SP [S#, P#] DIVIDEBY P [P#] ) JOIN S ) [SNAME] M SP [S#, P#] DIVIDEBY (( SP WHERE S# = 'S 2') [P#]) M 36

Utilité de l'algèbre u. Puissance u 8 expressive: opérateurs de Codd permettent d'exprimer toute expression logique de prédicat de 1 -er ordre unote: seulement 5 sont primitives (lesquels ? ) u La puissance expressive de l'algèbre dite complétude relationnelle constitue la mesure de la puissance minimale de tout LMD assertionnel digne de ce nom 37

Utilité de l'algèbre J Technique de choix pour l'implémentation J J J 38 Il n'y a que 8 opérateurs Ces opérateurs sont faciles à implémenter Leur propriétés permettent de transformer les expressions en +efficaces à évaluer, en général J Améliorations algébriques J Moins de valeurs à lire ou écrire J Moins de mémoire nécessaire pour ces valeurs J Voir mon cours sur l’algèbre

Utilité de l'algèbre u. Exemple (( S JOIN SP ) WHERE P# = 'P 2' ) [SNAME] = ( S JOIN ( SP WHERE P# = 'P 2' )) [SNAME] u La 2 -ème expression semble plus efficace ? u Règle Générale d’Amélioration ? (A JOIN B WHERE A. a = C) (A WHERE a = C) JOIN B 39

Opérations relationnelles (SQL) Voit (Im#, Pref, Mod, Couleur) Amende (A#, I#, Nom, Addr, Payé) 40 u Select * From Voit ; u Select Mod From Voit Where Couleur = 'rose' ; u Select Nom, Addr From Amende, Voit Where Payé Is Null and Mod = 'Ferrari' and I# = Im# ; 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# ;

Complétude relationnelle de SQL expression algébrique, une expression équivalente de SQL et de QBE " Schéma de preuve: opérateur algébrique, une expression équivalente de SQL " composition d'opérateurs algébriques, une composition équivalente de SQL 41

Relations u Une relation réelle est définie à partir de ses attributs u Une relation virtuelle (vue) est dérivée (héritée) par une opération relationnelle à partir de relations réelles ou de vues 42

Relations u En général, une valeur d’un domaine et donc d’un attribut peut être un ensemble u XML , Access 2007 u Pour les opérations relationnelles dans les SGBD actuels, ils ne sont néanmoins en principe que des valeurs atomiques u Toute décomposition fait perdre la sémantique de la valeur u De 43 telles relations sont dites normales

O NF 1 NF S# P# S 1 P 2 P 3 P 4 S 1 S 1 S 2 S 2 P 1 P 2 P 3 P 4 P 1 P 2 P 3 S 2 Norm. P 1 P 2 P 3 Toute valeur de S# et toute de P# Une ligne Contrainte très importante ! 44

Normalization en 1 -NF u Contrainte très importante ! u Etud (E#, Tel, Hobby, Dipl, Enfants, Voit) u Etudiant Dupont: u 3 tel, 5 hobbies, 3 diplômes, 3 enfants, 2 voitures u Un tuple d’ue relation en 0 -NF suffit u Il faut 3*5*3*3*2 = 270 tuples pour une relation en 1 -NF ! u Un tuple pour toute combinaison d’un tél, un hobby, un dipl…. u sous peine de perte d’info 45 u Inacceptable en général

Solutions pour la Conception u. Empirisme et Expérience u. UML u. Théorie Mathématique u Normalisation en i-NF ; i > 1 et BCNF u Surtout BCNF et 4 -NF u Cours sur la normalisation relationnelle 46

Et la Manipulation ? u Le problème reste ouvert dans le relationnel de base u SELECT E#, Tel, Hobby, Dipl, Enfants, Voit FROM R 1, R 2…Rn WHERE… u Produit une relation virtuelle en 1 -NF u Fera revenir les 270 tuples discutés u Une sortie: la Fonction LIST u SQL Anywhere & votre prof. u Voir le cours sur SQL avancé 47

Clés u Dans toute relation R il existe une combinaison C d'attributs dite clé telle que udans tout tuple t d'intention de R, la valeur C(t) identifie t, uil n'y a pas de sous-combinaison de C avec cette propriété u. Démontrez u Exemples: cette assertion ! N° SS, N° Étudiant, Nom de pays, (Nom, Prénom, Tel), Oid, . . . 48

Clés u Le choix de C est dicté par l'intention de R u Soit R = Pers (Nom, Prénom, SS#, Tel) u Dans une famille 49 Pers (Nom, Prénom, SS#, Tel) /* Tout membre u A la SS Pers (Nom, Prénom, SS#, Tel) /* Assuré seuelement u A l'état civil Pers (Nom, Prénom, SS#, Tel) /* Toute personne u Les valeurs d'un attribut d'une extension peuvent à un moment donné être toutes différentes sans qu'il s'agisse d'une clé !

Clés u. C atomique consiste d’un attribut u. C composite en contient plusieurs u. Tout attribut d’une clé est dit attributclé u. Tout autre attribut est un attribut nonclé C’est une fonction de toute clé de la table u Cette propriété est la base d’une définition du concept de la clé u 50

Clés u. Il ne faut pas confondre le concept de la clé avec celui d’un attribut-clé u. Ce dernier ne pas la clé dès que la clé est composite u Dans Pers (Nom, Prénom, SS#, Tel) u 51 SS# est la clé et l’attribut-clé Nom n’est que l’attribut-clé

Relations u. Si C est clé de R, alors tout ensemble d’attributs de R strictement incluant C est appelé sur-clé ou super-clé JDans notre base S-P, S# est une clé de S, donc (S#, SNAME) est une sur-clé de S. J Et les attributs (SNAME, STATUS) ne sont même pas une sur-clé 52

Relations u. R peut avoir plusieurs clés. Dans ce cas: u Une clé est arbitrairement choisie est dite primaire u Les autres deviennent clés candidates u. R peut avoir plusieurs clés de cardinalités différentes. u La clé avec le plus petit nombre d’attributs est dite alors minimale u. A 53 choisir de préférence

Relations u Une clé C d'une relation R peut être des attributs F d'une autre relation R' u F deviennent une clé étrangère dans R’ u F n'est pas en général une clé de R' 54

Clé primaire Clé candidate Voit (Châssis#, Moteur#, Plaque#, Mod, Poids, Coul ) Clé candidate composée Etud (E#, Nom, Prénom, Tel, Adresse ) Clé étrangère Participants (C#, E#, Note) 55

Relations u. L'égalité C = F constitue le lien sémantique entre les relations correspondants u. Entre C et F il peut exister la contrainte d'intégrité référentielle u Pas de F sans C u Pas de participant qui ne serait pas un étudiant connu u Dans un SGBD de 2 -ème génération ces liens étaient les références implicites (pointeurs) 56 u Dans UML aussi en principe

Relations u. Les SGBD majeurs gèrent désormais des contraintes IR ainsi que les liens sémantiques 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 sémantiques u Voir la suite du cours 57

Intégrité référentielle Mari M# 1 1 Femme F# Produit P# 1 N Mari M# Ami A# 58 1 N Femmes F# M N Amie A# Comment faire ? 1 N PP#, PS# Produit Composé

u Intégrité Référentielle 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#) u De 59 tels liens génèrent les récurrences exigeant le calcul de fermetures transitives u Les opérations relationnelles ne permettent pas de calculer les fermetures transitives u Les SGBD en général ne gèrent pas de tels liens sémantiques

Valeurs nulles u Une valeur nulle est un abus de langage pour designer une absence de valeur d’un attribut u On 60 dit aussi un nul

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

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 62

Le nul et la clé primaire Un attribut-clé de la clé primaire ne peut être nul $Pourquoi ? $Une propriété qui peut sembler anodine $ En fait elle est d’une importance capitale pour une base relationnelle $ conduit à la démarche dite de modélisation relationnelle $ 63 notamment aux formes normales

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 64

Modélisation relationnelle u Passage du monde réel vers une base relationnelle u Le schéma conceptuel u Schémas de tables u Liens sémantiques & contraintes IR u Opérations permises u Les schémas externes 65

Modélisation relationnelle u Souvent fort simple u L’attrait de bases relationnelles u Exemples typiques commentés en cours u Fournisseurs et Pièces (Supplier Part DB) u Conseillers en assurances et Produits d’Assurances u Etudiants et Cours 66

Modélisation relationnelle BDR 67

Modélisation relationnelle u On procède le plus souvent en trois étapes 1. Modélisation conceptuelle u u 2. Conceptuel – Relationnel u 3. Transformation du modèle conceptuel en CS et ESs d’une BD Normalisation u u 68 Pas spécifique au relationnel Ni aux BDs même Amélioration du CS Spécifique au relationnel plat

Résultat Attendu: Graphe de références u Une BD relationnelle en général comporte plusieurs relations u Un graphe de références représente sa structure u Les nœuds sont des relations u Les arcs orientés sont les contraintes d'intégrité référentielle C -> F u 1: N ou 1: 1 u Les autres sont les liens sémantiques 69 Une base relationnelle n'est correctement définie que si son le graphe de références est un graphe connecté

Modélisation relationnelle : Conception d’une BD relationnelle u Il faut minimiser le nombre de nœuds du graphe de références u Sous contraintes : u D’absence d’anomalies u. D’insertion, suppression, MAJ u De minimisation de redondance globale de données u Par rapport à 0 NF surtout u Les deux contraintes sont en fait duales 70

Modélisation relationnelle : Conception d’une BD relationnelle Il faut minimiser le nombre de nœuds du graphe de références u Sous contraintes : u u Préservation de dépendances fonctionnelles (FDs) u Pas ou peu de valeurs nulles u Cette contrainte peut contredire celle sur les anomalies & redondances u Il faut alors exercer son bon sens 71

Modélisation relationnelle : Conception d’une BD relationnelle u Anomalie d’insertion u On ne peut pas insérer de valeurs qu’il faudrait u Soit la table S’ = (S#, Sname, Status, City, P#, Qty) u Fournisseur S 1 ne fournit encore aucune pièce u On ne peut pas insérer ses données: u Fournisseur S 1 est Smith, a le statut 20 et est à Londres 72

Modélisation relationnelle : Conception d’une BD relationnelle u Anomalie d’insertion u Il faut insérer une même donnée plus de fois que nécessaire u En idéal : une donnée n’est insérée qu’une fois dans la base u Revoir notre exemple illustrant 1 NF u La conception en une table présente l’anomalie 73

Modélisation relationnelle : Conception d’une BD relationnelle u Dans S’, si S 1 fournit 5 pièces, alors on insère Sname, City, Status 5 fois u Soit la conception en deux tables S = (S#, Sname, City, Statuts) SP (S#, P#, Qty) u On n’insère ces valeurs qu’une fois 74

Modélisation relationnelle : Conception d’une BD relationnelle u La conception est libre de deux aspects discutés de l’anomalie u On peut insérer les données sur S 1 même s’il ne fournit rien actuellement u En supposant qu’en général un fournisseur fournit plusieurs pièces, on diminue la redondance globale u Bien que l’on l’augmente nécessairement localement pour S# 75

Modélisation relationnelle : Conception d’une BD relationnelle u Enfin, supposons que l’on conçoit au lieu de S trois tables S 1 (S#, Sname), S 2 (S#, City), S 3 (S#, Status) u On insère S 1 deux fois de trop, par rapport à S u Trop de tables conduit à l’anomalie aussi 76

Modélisation relationnelle : Conception d’une BD relationnelle u Anomalie de MAJ u On MAJ plusieurs valeurs au lieu d’une seule Pour une bonne conception u u Dans S’, si S 1 fournit 5 pièces et déménage à Paris, alors il faut mettre à jour 5 valeurs u Dans S, il suffit d’une seule 77

Modélisation relationnelle : Conception d’une BD relationnelle u Anomalie de suppression u On supprime les valeurs qu’il ne faudrait pas u Dans S’, si S 1 fournit 5 pièces u Si l’on supprime 4 fournitures, les données de S 1 restent dans la base u Si l’on supprime la dernière fourniture, on les perd u Pas si l’on a la conception en S et SP 78

Modélisation relationnelle : Résultat générale u Plusieurs relations u Chaque relation consistant u d’une clé u de max d’attributs identifiés chacun comme fonctions de la clé u On respecte aisément la condition nécessaire u Pas celle suffisante 79

Modélisation relationnelle : Démarche générale u. On commence par modélisation d’un ensemble d’objets réels par une relation dite souvent universelle, soit R 1 u Les personnes u Les fournisseurs u Les produits u On dit que toute BD modélise une 80 entreprise (ANSI – SPARC)

Modélisation relationnelle : Démarche générale u. On cherche surtout des OIDs et, plus 81 généralement, des déterminants u Attributs (peut-être composés) sur lesquels d’autres attributs sont fonctionnellement dépendants u Autrement dit, ces attributs sont une fonction du déterminant ou d’OID en particulier

Modélisation relationnelle : Démarche générale u On trouve, ou on crée, un OID d’un objet u P# identifiant une personne u On trouve un ou des attribut(s), dits aussi attribut composé, que OID détermine u Pers (P#, Nom, Pnom, DNaiss, …) u Statut d’un fournisseur u Poids, couleur d’une pièce commandée dans P 82

Modélisation relationnelle : Démarche générale u. On crée une nouvelle relation, soit R 2, chaque fois que l’on rencontre: u Un attribut (composé) à une liste de valeurs Les #Tel d’une personne, ses émails, diplômes… u On répète dans R 2 le OID ou le ou un déterminant de R 1 u 83

Modélisation relationnelle : Démarche générale u. Dans notre exemple, on aurait u. PT (P#, Tél) u PE (P#, Email) u PD ((P#, Dipl) u Il y aurait un tuple par élément de liste u 84 La décomposition sans perte d’info en 4 NF par le Théorème Heath -Fagin

Modélisation relationnelle : Démarche générale u On crée une nouvelle relation, soit R 2, chaque fois que l’on rencontre: u Un attribut (composé) D qui n’est pas une clé candidate, mais DF un autre u Code postale (CP) -> Ville u En supposant une seule ville par CP u Indice -> Salaire de base u On répète D dans R 2 85

Modélisation relationnelle : Démarche générale u. Dans notre exemple, on aurait R 1 = Pers (P#, Nom, Pnom, DNaiss, CP, …) R 2 = CV (CP, Ville) u Le tout est la décomposition sans perte d’info u Par Théorème de Heath u Dite en BCNF, 3 NF… u Voir mon cours sur la normalisation 86

Modélisation relationnelle : Démarche générale u. On crée une nouvelle relation, soit R 2, chaque fois que l’on rencontre: u Un attribut (composé) qui aurait crée souvent une valeur (composée) nulle u Nom de jeune fille u Personnes à Dauphine : 87 u Etudiants avec leur Form. Note. G… u Employées avec #Emp, Sal… u Visiteurs

Modélisation relationnelle : Démarche générale u. On répète dans R 2 le déterminant de R 1 Pers (P#, Nom, Sex, Célib… ) et PJf (P#, NJf) u C’est l’extraction de sous-classes u Méthodologie hors la normalisation relationnelle (FNs) u Autres exemples plus loin u 88

Modélisation relationnelle : Démarche générale u. On crée pour R 2 sa clé selon le cas u Union des déterminants de R 1 et de R 2 u P#, Tel ; P#, Email ; Comme dans notre exemple u Mais, il peut y avoir plusieurs choix u Quand on a plusieurs clés et déterminants u 89

Modélisation relationnelle : Démarche générale Le déterminant de la DF u CP ; Indice… u Comme dans notre exemple u Notre démarche réalise en fait la décomposition en projections indépendantes u Meilleure que celle dite en projections dépendantes u Perdant les DFs u 90

Modélisation relationnelle : Démarche générale u Le déterminant de R 1 ou, peut-être, de R 2 u Pour les personnes qui sont les étudiants R 2 = Et (P#, Form, Note. Glob…) u Ou, peut-être, on crée E# attribué aux étudiants seulement R 2 = Et (P#, E#, Form, Note. Glob…) u. On continue le processus de décomposition pour R 1 et R 2 etc. 91

Modélisation relationnelle : Démarche générale u. On crée récursivement entre les relations obtenues u. Les liens sémantiques u Les contraintes d’intégrité référentielle u Le tout selon l’application u Entre les déterminants u Entre les clés et les clés étrangères 92

Modélisation relationnelle : Démarche générale u Jointure implicite pour le lien sémantique u Ms. Access d’abord, suivi par quelques autres SGBDs u Une jointure ajoutée automatiquement à la requête en mode QBE u On ne spécifie que les attributs et agrégats u Requêtes + simples u - procédurales, + assertionnelles… 93

Modélisation relationnelle : Démarche générale u. Jointures implicite ont été proposées dans les 80 u Par votre prof. et son Thésard A. Abdellatif (INRIA) u Développées avec Prof. G. Wiederhold & son Thésard B. Lee (Stanford) u Papiers sur la page Web de votre prof (CERIA) 94

Modélisation relationnelle : Démarche générale u Type de jointure implicite pour le lien sémantique (Ms. Access) u Interne (défaut) Produit seulement les tuples de deux tables ou les valeurs jointes sont égale u Signification des attributs en conséquence u Attr. Nom dans Pers est le nom d’étudiant pour le lien Pers <-> Et u C’est le nom d’employé pour P <-> Emp u 95

Modélisation relationnelle : Démarche générale u Type de jointure implicite pour le lien sémantique (Ms. Access) u Externe Préserve toutes les tuples d’une de deux tables, au choix u Signification des attributs en conséquence u Si l’on préserve P, alors Nom est le nom d’une personne pour tout lien u. P <-> Et, P <-> Emp, P <-> Vst, … u 96

Modélisation relationnelle : Démarche générale u. Expérience d’application u Les exercices Voir ceux du cours u u La pratique Voir la vie autour u u Dauphine, Votre entreprise, Facebook, Ecole de Conduite, vos CDs… 97

Exemple canon Spécifications 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 pièces P u Chaque fourniture fp comporte une certaine quantité d'une pièce p u Chaque p a un ID, un nom, un poids, une couleur u Une pièce p peut être l'objet de plusieurs fournitures fp 98

Schéma Conceptuel SP * S S# Sname Status City 99 P# S# Qty * 1 1 P P# Pname Color Weight City

Jointure Implicite (S – SP) • Choix de jointure interne 100

Résultat pour une requête QBE SQL 101

Exemple canon S P 102 SP

Modélisation relationnelle : Démarche générale u. Cas spéciaux Pers (P#, Nom, Pnom, DNaiss, CP, …) CV (CP, Ville) u Que faire si l’on sait que P 1 est à Paris, mais l’on ne connaît pas CP ? u Pas de problème par contre avec la conception dénormalisée Pers (P#, Nom, Pnom, DNaiss, CP, Ville) 103

Modélisation relationnelle : Démarche générale u. Cas spéciaux Pers (P#, Nom, Pnom, Sex, Célib, …) PJf (P#, NJf) u Si les Jeunes Filles sont rares, la conception dénormalisée peut présenter – d’anomalies et redondance (sur P#) en moyenne Pers (P#, Nom, Pnom, Sex, Célib, NJf…) u Etant donc globalement + avantageuse 104

Modélisation relationnelle : Démarche générale u. Conclusion u Il y a ceux et d’autres cas spéciaux u Il faut commencer par la démarche générale u Après il faut exercer son bon sens u Selon les contraintes spécifiques de la base u Dénormaliser si utile 105

Modélisation Relationnelle Approfondie Modèle Conceptuel MDCCXCIX ? MDCCLXXXXVXXXX An mille sept cent quatre-vingt-dix-neuf ? 1799 • Votre modèle / standard préféré ? 106

Modélisation Conceptuelle u Univers u Objets u u Entités Propriétés Associations entre les objets Fonctions… u u u 107 Ensembles spécifiques d’objets u Types u Classes. . .

Modélisation Conceptuelle u Universal Modeling Language Standard Intl. de OMG u Une variante de EER u u u Extended Entity Relationship Model ER avait été proposé par Peter Chen Prof. à U. de Baton Rouge (LU) u Il y a une trentaine d’années u Très populaire dans le temps u u 108 Un peu à tort peut-être

Passage UML - Relationnel u Entités et Associations doivent devenir u u 109 Tables du CS ou des ES Liens sémantiques Contraintes d’IR Opérations sur les tables

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

UML u Objet = Entité (Entity) ou Occurrence d’entité u Entité faible u. Identifiable seulement dans une autre entité (forte) u Type d’objets = Type ou classe u Propriété = Association (Relationship) 111

UML : Type d’Entité Nom Attributs clé et non-clé Opérations 112

UML : Type d’Entité • Pour le relationnel • Attributs atomiques ou dérivés seulement • Tout attribut atomique est fonctionnellement dépendants sur la clé • On note une dépendance fonctionnelle (FD) de B sur A comme A -> B • Pas d’attributs multivalués ou composés • Attributs dérivés sont pour les schémas externes et les sous-tables (Access) • Les spécifs des opérations sont rares 113

UML : Type d’Entité Personne <PK> P# Nom Prénom Nom de famille Hobbies 0. . 10 Amis 0. . 10 Restaurants 0. . 10 114 • Valide pour XML • Pas pour le relationnel • Il faut mettre tout composite ou multivalué en type d’entité séparé (en principe)

UML : Type d’Entité Personne <PK> P# 1. . * 1 Nom <PK> Prénom <PK> Nom de famille Amies 0. . 10 Restaurants <PK> Restaurant 115 0. . 10 Hobbies <PK> Hobby <PK> Ami

UML Assuré Attribut dérivé <PK> Client# <PK> Produit d’ass. # Prix/Prix total per client Prix total = Prix de tous les produits du client • Valide pour le relationnel 116 • Mais réalisable seulement comme une table et une vue

UML Abréviation de 0. . * Associations Nom de l’association Diagramme de note en UML L’école peut envoyer entre 0 et 8 étudiants à un exam Exactement 6 séries / CD Modèle d’une auto-école basé sur l’ex. de M. Manouvrier 117 Appartient à Rôle de l’associon (directionnelle)

UML : Association n-aire u Les patients P sont soignés par des médecins M, dans des services S u Un médecin peut être partagé entre plusieurs patients et services P 1 1. . 4 Soin 1. . 5 Que disent les chiffres ? 1 M 118 100 1 S

UML : Association 1 -aire Mère Personne <PK> P# Nom Prénom 119 Père Ancêtre

UML u Concept de composition u u u Les entités composantes n’ont pas d’existence propre Ex. Les salles d’un bâtiment La suppression de la composition supprime aussi les composantes u u u Contrainte d’intégrité référentielle Symbolisée par losange noir Les entités composées peuvent être agrégées par ailleurs u Conf Batiment 1. . 7 1 1. . 4 0. . * Salle Losange transparent Les cardinalités x. . y sont des exemples 120

UML : Classe / Sous-classe Assurance u Concept de sous-classes u u u Tout membre de la classe est obligatoirement dans une sousclasse And/Or u 121 Montant Spécialisation/généralisation Symbolisées par la flèche Mandatory/Optional u u <PK> A# Il peut être dans plusieurs sous -classes ou pas 1 1 1 Optional / OR 0. . 1 Ass-maison Ass-voiture Ass-maladie Val-maison Bonus Complément

UML / Relationnel Client 1 * <PK> C# Prénom Nom de famille Ville CP … Assurance <PK> A# <PK> C# Statut du client Prime … • Acceptable pour le relationnel • Mais une mauvaise conception • Si statut, comme son nom l’indique ne dépend que de C# 122 • Si CP implique la ville

UML / Relationnel Personne <PK> P# <PK> Hobby <PK> Ami <PK> Restaurant Prénom Nom de famille • Acceptable pour le relationnel • Mais une très mauvaise conception 123

Passage UML - Relationnel u Entités et associations doivent devenir u Tables u Liens sémantiques et contraintes IR u Opérations sur les tables u Dans le modèle UML la représentation des associations n’est pas spécifiée u Pourrait être les listes de pointeurs (références) u Manipulées alors différemment dans un langage de programmation que les valeurs directes de données u Principe 124 rejeté par le modèle relationnel

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 clés primaire et étrangère en général u Associations triviales : une même valeur d’attribut clé étrangère d’une table que celle d’une clé d’une autre table indique un même objet réel u D’où l’introduction et l’importance capitale du concept de la clé dans le modèle relationnel 125

Passage UML - Relationnel u Egalement important est le principe que la table est un ensemble donc tout tuple a nécessairement une clé u Constituée peut-être par tous les attributs, mais quand même u Pas une bonne idée u Résultat global: une même expression de manipulations de toutes les données dans la base (Codd) u Un énorme avantage pour le but de nonproceduralité 126

Réification (Etape 1) u Outil Fondamental de passage UML – Relationnel : u On réifie : u Toute classe d’associations en une classe d’entités u Toute classe d’entités deviendra plus tard une table relationnelle 127

Réification (Etape 1) u Une classe d’associations est peut-être réifiée en celle d’entités avec ses classes d’entités aux extrémités u Si l’association est une bijection notamment u Autrement, on transforme une association en celle triviale entre les attributs des entités u Tout attribut structuré ou multivalué est réifié en une entité (séparée et associée par des clés étrangères) 128

Réification (Etape 2) u Toute entité réifiée devient une table relationnelle u. Les associations triviales deviennent u. Les liens sémantiques u. Les contraintes d’intégrité réferentielle 129

Réification u Le concept de réification est rarement explicité u La réification est en général manuelle u A l’heure actuelle u C’est la principale limitation de l’emploi d’une BD relationnelle par un usager Tout-le-Monde 130

Réification : Principe Général A B <PK> A# A 1 …. C <PK> B# B 1 …. Association triviale: les deux B# identifient la même entité. C 1 …. A <PK> A# A 1 …. 131 C <PK> A# <PK> B# C 1 …. B <PK> B# B 1 …. • C’est une réification adaptée au relationnel pour éviter les anomalies • D’autres réifications sont possible (ex. A et B et C en une entité commune) • Relation universelle

Réification : Principe Général C A <PK> A# A 1 …. B <PK> A# <PK> B# C 1 …. <PK> B# B 1 …. C A <PK> A# A 1 …. <PK> A# <PK> B# C 1 …. B <PK> B# B 1 …. • A l’origine, il n’y avait pas de liens sémantiques explicites dans une BD Rel. • Les associations triviales devenaient des liens implicites : • 132 l’égalité du nom de la clé primaire et celle étrangère

Réification : Principe Général C A <PK> A# A 1 …. <PK> A# <PK> B# C 1 …. C <PK> Role. A# <PK> Role. B# C 1 …. B <PK> B# B 1 …. Après, oui, notamment pour l’intégrité référentielle en utilisant le nom de rôle 133 (nom de l’association, uni ou bidirectionnelle : ex. Prop. _de_la_voiture_)

Réification & Pointeurs dans les Langages de Programmation u Une association triviale représente d’une manière explicite un pointeur d’une table vers une autre u La valeur d’un pointeur est explicite Contrairement en principe aux langages de programmation u Dans le modèle relationnel elle est celle d’une attribut comme d’autres u u Presque, car il y a en général les contraintes référentielles à gérer Un pointeur peut être alors manipulée comme toute autre donnée u Une des idées fondamentales de E. Codd u u En fait le concept de la clé est une conséquence de cette idée u 134 Une représentation à la fois compacte et explicite d’un pointeur

Réification : Association n-aire P 1 1. . 4 100 Soin 1 S 1. . 5 1 M Soin P 1 1. . 4 <PK> S# <PK> P# <PK> M# 1. . 5 1 M 135 100 1 S

Réification : Attribut composé Personne <PK> P# Prénom Nom de famille Hobbies Amis Restaurants Nom Tel Personne <PK> P# Prénom Nom de famille <PK> P# <PK> Hobby Amis <PK> P# <PK> Ami Les cardinalités des associations ? Le processus est transitif pour une valeur composée dans un attribut 136 Hobbies Restaurant <PK> P# Nom <PK> Tel

Réification : Attribut composé Personne <PK> P# Prénom Nom de famille Hobbies Amis Restaurants Nom Tel Personne <PK> P# Prénom Nom de famille Peut faire gagner de la place en mémoire de stockage (encombrement de H# est souvent bien plus petit que celui du texte de Hobby) Approche utile pour un entrepôt de données 137 P_H Hobbies <PK> P# <PK> H# Hobby Amis <PK> P# <PK> Ami Restaurant <PK> P# Nom <PK> Tel

Réification : Entité Faible Conseiller <PK> C# Cl# 1 1 0. . 1 Client Cl# n’est pas la clé 138 Cl# Nom * Client <PK> Cl# <PK> C# Nom

Réification : Entité Faible Conseiller C# Cl# 1 1 0. . 1 * Client Cl# n’est pas la clé 139 Cl# Nom Client La clé de Client si Conseiller est une entité faible aussi ? <PK> ? …. Nom

Réification : Cas Spécifiques Bijection Mari 1 <PK> M# A 1 …. Unique (un seul mariage, pas de personnes remariés ensemble) On réifie en une entité (laquelle ? ). Changement du modèle conceptuel. 140 1 Mariés Date …. Mariage <PK> M# ou F# F# ou M# Date A 1 …. B 1 …. . Femme <PK> F# B 1 …. On gagne en en général en efficacité en éliminant une jointure Il n’est plus possible d’introduire une Femme dont on ne connaît pas la Mari ou vice versa (pourquoi ? )

Réification : Cas Spécifiques Bijection Client 1 <PK> C# A 1 …. 1 Accident Voiture <PK> V# B 1 …. Date …. üOn mémorise tous les accidents d’un client avec sa voiture üPeut-on en général réifier comme auparavant ? üSinon pourquoi pas ? 141

Réification : Cas Spécifiques Injection Mari 0. . 1 <PK> M# A 1 …. Unique (un seul mariage de personnes remariés ensemble) 1 Mariés 142 <PK> F# B 1 …. Date …. Femme Mariée ou pas Changement du modèle conceptuel Femme <PK> F# M# Date A 1 …. B 1 …. . On gagne en souvent en efficacité en éliminant une jointure / à l’approche de base

Réification : Cas Spécifiques Mari <PK> M# A 1 …. 0. . 1 Mariés Femme <PK> F# B 1 …. Date …. Mari <PK> M# A 1 …. 143 Mariés <PK> M# <PK> F# C 1 …. Femme <PK> F# B 1 ….

Réification : Hiérarchie Mari <PK> M# A 1 …. 1 0. . 4 Mariés Femme <PK> F# B 1 …. Date …. Mari <PK> M# A 1 …. 144 Femme-m On élimine une jointure et une redondance/ à l’approche générale <PK> F# M# Date B 1 …. On n’a que les femmes mariées (changement du modèle conceptuel)

Réification : Les Veuves ? Mari <PK> M# A 1 …. 0. . 1 0. . 4 Mariés Date …. Votre Proposition ici 145 Femme <PK> F# B 1 ….

Réification : Classe / Sous-classe Assurance <PK> A# Montant 1 0. . 1 <PK> A# Montant 1 Optional / OR 0. . 1 Ass-maison Ass-voiture Val-maison Bonus 1 0. . 1 Ass-maladie Complément Ass-maison Ass-voiture <PK> A# Val-maison <PK> A# Bonus Ass-maladie <PK> A# Complément Les tables sont comme les entités réifiées. Comment faire pour l’IR ? 146

Réification : Classe / Sous-classe Client <PK> C# Nom Client OK ? <PK> C# Nom_JF Mandatory/ OR Homme Femme Nom_JF 147 Sinon votre proposition ici

Réification : Classe / Sous-classe Schéma Ms. Access 148

Réification : Classe / Sous-classe Schéma Ms. Access u Le schéma permet d’aisément formuler les requêtes: u Toute donnée de personne P 1 dans Pers et, s’il y a lieu, ses données u En tant qu’un employé u En tant qu’un étudiant u Ms. Access génère alors les jointures implicites externes u Cours SQL 149

Réification : Autres Cas u Le « jeu de clés » en général facile à voir de diagrammes UML u u u 150 Agrégation Composition Associations 1 -ères u Sauf celle dite Ancêtre u Calcul de la fermeture transitive u Peu performant dans les BDs Relationnelles

Réification : Cardinalités u u u 1 * ou 1 1 présent avant ou après la réification, se réifie en contrainte d’intégrité référentielle clé primaire – clé étrangère 0 * ou 0 1 se réifie en un lien sémantique Autre cardinalités, p. ex. 1 6 nécessitent en général des déclencheurs u 151 Pas une sinécure pour Mme/M Tout le Monde

Réification : Autres Cas L’exemple d’une Personne avec les Amies, Hobbies…? Attributs dérivées ? u u 152 u Il faut les mettre dans les vues Select Sum (Prix) as Prix. Total from Client Group By Client#

Après la Réification u. Le résultat peut être OK u. Exercice école : Modèle relationnel de l’auto- u. Mais il peut être pas bon du tout pour une BD relationnelle u. A cause d’anomalies et de redondances u D’où la phase de normalisation u Peut-être appliquée à partir de la relation universelle directement 153 u Par l’analyse des DFs et des DMs

Exemple canon Spécifications 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 pièces P u Chaque fourniture fp comporte une certaine quantité d'une pièce p u Chaque p a un ID, un nom, un poids, une couleur u Une pièce p peut être l'objet de plusieurs fournitures fp 154

Exemple canon SP Qty S S# Sname Status City 155 * P# Pname Color Weight City

Exemple canon SP Association triviale * S S# Sname Status City 156 P# S# Qty * 1 1 P P# Pname Color Weight City

Exemple canon S P 157 SP

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 clés) u Pas d‘anomalies. u On verra cette notion dans le cours suivant. u Efficacité de stockage. u Pas d’attribut-clé unique pour SP u Compare à la conception en une seule relation u Problèmes : u Comment trouver le Nom du fournisseur de pièces rouges ? u etc. . 158

Solution u Opération 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' ; 159

Exemple Projet BD Assurance 07 160

UML -> XML Personne <PK> P# Nom Prénom Nom de famille Hobbies 0. . 10 Amis 0. . 10 Restaurants 0. . 10 Type d’entité UML 161 Plusieurs SGBD relationnels offrent les interfaces XML <Personne> <P_Id> 123 </ P_Id> <Nom> <Prenom> Jean </Prenom> <Nom de famille> Dupont </Nom de famille> </Nom> <Hobbies> Ski, Tennis, Voile </Hobbies> <Amis> Jean, Paul</Amis> <Restaurants> Sinbade, Café Court, Gargote </Restaurants> </Personne> Une entité XML (dite document)

Exercices u u u Proposer les schémas relationnels pour les exemples en cours Modéliser en UML et en relationnel un livre typique Modéliser en UML et en relationnel l’affectation de salles de cours à Dauphine. Justifiez le choix si plusieurs solutions sont possibles. Indiquez les clés primaires et candidates. u Modèle 1: Une réservation se définit par le n° de la salle, le nom du cours, la date, l’heure début et l’heure fin. (i) (ii) u u Un cours n’est qu’une fois par jour dans la même salle. Alternativement, une répétition est possible. Modèle 2 : On ajoute le type de la salle, si c’est: l’amphi, une salle équipée vidéo ou une salle TP Modèle 3 : On ajoute le nom du prof enseignant le cours (i) Un enseignant par cours. (ii) Plusieurs. 162

Exercices u u u Modéliser une bibliothèque possédant un ou plusieurs exemplaires d’un livre sur des rayons, en prêt ou en retour d’un prêt mais pas encore sur les rayons. Proposez une modélisation usuelle en UML d’une personne ayant un ID, un nom, une mère et un père. Proposez ensuite un schéma relationnel. Ce schéma satisfait-t-il: u u u Modéliser un certificat de naissance d’un bébé en sachant que les parents peuvent ou pas être mariés Modéliser les assurances proposées par une compagnie pour une personne : voiture, maison, resp. civile… Voir les livres en BDs pour 1 millier d’autres exercices du type : u 163 Un DBA soucieux de l’espace de stockage de la base. Sinon, que lui conseillez-vous ? Un DBA voulant minimisant le temps de requêtes donnant pour certains chefs identifiés par leurs IDs, les IDs de tous leurs employés Spécifs fonctionnelles -> UML -> réif. -> Schéma Rel.

Exercices u u On crée le modèle pour la base des enfants. Pour chaque enfant on a le père et la mère. Proposez le modèle UML. L’enfant doit être modélisé comme une entité ou une association ? On constitue une base de produits. Chaque produit a un ID et nom, une photo et appartient à plusieurs catégories de produits identifiées par leur noms. Plusieurs produits peuvent appartenir à une même catégorie. La photo comporte plusieurs produits agencés d’une manière typique pour leur application. Plusieurs produits partagent une même photo. Proposez la modélisation typique UML, puis la réification, enfin le schéma relationnel. Le DBA sait en plus qu’il a en moyenne 100 produits par catégorie et autant par photo. Il y a 100 catégories et photos en tout. Le nom d’une catégorie est un champ fixe de 50 octets. Une photo nécessite 1 MOctets. u u u 164 Le DBA sait qu’il y a 10 000 produits. Il souhaiterait minimiser l’encombrement de la base. Est-ce que la modélisation typique minimise le satisfait ? Sinon, proposez en UML et en relationnel une autre qui serait plus optimale. Evaluez le gain. Un autre DBA a comme préoccupation principale de minimiser le temps d’une requête demandant des noms de produits avec leurs catégories et les photos. Il veut minimiser le nombre de jointures. Quelle modélisation lui conseillez vous?

Exercices suppl. Clic hors diaporama 165

FIN Merci de votre attention 166 W. Litwin

167