Analyse des systèmes Modélisation des données Cours 1 / Partie B P.-A. Sunier Créé Version Modifié Fichier 25.10.95 1.0a DONTH01B.DOC P.-A. Sunier Règles et conventions d’écriture du document Les références à d’autres documents sont mises entre crochet. Exemple: [NANCI-92] Les éléments repris d’autres documents sont écrits en italique et précédés ou suivis de la référence. Exemple: [ISO 9000-1 Introduction] Les organismes... Le terme d’organisme est utilisé de manière générique en lieu et place d’entreprise. Selon [ISO-8402] il a la signification suivante: compagnie, société, firme, entreprise ou institution, ou partie de celle-ci, à responsabilité limitée ou d’un autre statut, de droit public ou privé, qui a sa propre structure fonctionnelle et administrative. Les figures reprises ou inspirées de documents sont référencées comme pour le texte. Exemple: [Tiré de NANCI-92] [Inspiré de NANCI-92] 2 Table des matières 1. Modèle logique de données _________________________________________________ 7 1.1 Objectifs ______________________________________________________________________7 1.2 Modèle relationnel ______________________________________________________________7 1.3 Modèle logique de données relationnel _____________________________________________8 1.3.1 Représentation graphique ______________________________________________________________ 8 1.3.2 Table _____________________________________________________________________________ 9 1.3.3 Bases de données relationnelles ________________________________________________________ 11 1.3.4 Clés _____________________________________________________________________________ 11 1.3.5 Les contraintes d’intégrité ____________________________________________________________ 12 1.3.6 Représentation des tables _____________________________________________________________ 14 1.3.7 Relation entre tables _________________________________________________________________ 16 1.3.8 Degré d’une relation _________________________________________________________________ 19 1.3.9 Cardinalité d’une relation _____________________________________________________________ 20 1.4 Représentation par les outils d’aide à la conception _________________________________22 2. Passage du MCD au MLD _________________________________________________ 23 2.1 Règle No 1 ___________________________________________________________________23 2.2 Règle No 2 ___________________________________________________________________23 2.2.1 Cardinalité (-,n) et (-1) _______________________________________________________________ 24 2.2.2 Cardinalité (-,1) et (-1) _______________________________________________________________ 25 2.2.3 Association n-aire, n>2 ______________________________________________________________ 25 2.3 Règle No 3 ___________________________________________________________________26 2.4 Rôles multiples d’une table source _______________________________________________29 3. Les formes normales _____________________________________________________ 31 3.1 Introduction __________________________________________________________________31 3.2 Dépendance fonctionnelle _______________________________________________________31 3.2.1 Dépendance fonctionnelle élémentaire ___________________________________________________ 32 3.2.2 Dépendance fonctionnelle directe ______________________________________________________ 32 3.3 1ère forme normale (1NF) ______________________________________________________33 3.3.1 ¬1NF - Attribut formé d’une relation ___________________________________________________ 33 3.3.2 ¬ 1NF - Attribut contenant un groupe répétitif_____________________________________________ 35 3.4 2ème forme normale (2NF) _____________________________________________________37 3.4.1 Règle de normalisation _______________________________________________________________ 37 3.4.2 Exemple __________________________________________________________________________ 37 3.5 3ème forme normale (3NF) _____________________________________________________39 3.5.1 Règle de normalisation _______________________________________________________________ 39 3.5.2 Exemple __________________________________________________________________________ 39 3 4. Modèle physique MPD ____________________________________________________ 41 4.1 Objectifs du MPD _____________________________________________________________41 4.2 Règles de Codd _______________________________________________________________41 4.3 Choix d’un SGBD _____________________________________________________________43 4.4 Passage du MLD au MPD ______________________________________________________44 4.5 Norme SQL __________________________________________________________________44 5. Recommandations d’écriture _______________________________________________ 45 6. Bibliographie ___________________________________________________________ 46 4 Table des illustrations Figure 1 - Représentation d'un schéma de table ___________________________________ 10 Figure 2 - Représentation d'une extension de table ________________________________ 10 Figure 3 - Exemple de référence étrangère _______________________________________ 13 Figure 4 - Représentation d'une clé primaire _____________________________________ 14 Figure 5 - Représentation d'une clé secondaire __________________________________ 14 Figure 6 _Représensation d'une clé étrangère ____________________________________ 14 Figure 7 - Représentation d'un champ calculé ____________________________________ 15 Figure 8 - Relation ensembliste (fonction) _______________________________________ 16 Figure 9 - Représentation d'une relation ________________________________________ 17 Figure 10 - Fonction 1er postulat ______________________________________________ 18 Figure 11 - Cardinalité côté parent ____________________________________________ 20 Figure 12 - Cardinalité côté enfant_____________________________________________ 21 Figure 13 - Représentation inspirée des outils CASE _______________________________ 22 Figure 14 - MCD-> MLD Association de cardinalité (-,n) et (-,1) _____________________ 24 Figure 15 - MCD-> MLD Relation 1:n _________________________________________ 24 Figure 16 - MCD-> MLD Association de cardinalité (-,1) et (-,1) _____________________ 25 Figure 17 - MCD-> MLD Relation 1:1 _________________________________________ 25 Figure 18 - MCD -> MLD Association n-aire ____________________________________ 26 Figure 19 - MCD -> MLD Table associative _____________________________________ 27 Figure 20 - Respect de la dimension d'une table associative ________________________ 27 Figure 21 - Exemple MCD-> MLD Association binaire ____________________________ 28 Figure 22 - Exemple MCD-> MLD Table associative binaire ________________________ 28 Figure 23 - MCD-> MLD Association réflexive ___________________________________ 29 Figure 24- MCD-> MLD Table associative avec rôles de réflexivités __________________ 29 Figure 25 - MCD-> MLD Rôle de l'entité _______________________________________ 30 5 Figure 26 - MCD-> MLD Table associative avec rôles _____________________________ 30 Figure 27 - (1NF) Transformation d'un attribut-relation ____________________________ 34 Figure 28 - (1NF) Transformation d'un attribut - Groupe répétitif ____________________ 36 Figure 29 - Transformation en 2NF ____________________________________________ 38 Figure 30 - Transformation en 3NF ___________________________________________ 40 6 1. Modèle logique de données 1.1 Objectifs La démarche de conception du Modèle conceptuel de données (MCD) ne tient pas compte des contraintes liées aux choix techniques de mise en oeuvre automatisée. Le Modèle logique de données (MLD) permet d’élaborer un plan d’enregistrement des données pour une classe de solutions technologiques indépendamment de la mise en oeuvre par un produit spécifique. Actuellement nous pouvons retenir 2 classes de Modèle logique de données: navigationnel ou réseau relationnel Le modèle navigationnel ou réseau a été proposé par Bachman en 1969 Les recommandations CODASYL permettent d’unifier les produits SGBD. Dans la suite de ce cours nous traiterons exclusivement le modèle relationnel supporté par les bases de données dites relationnelles SGBD-R1. 1.2 Modèle relationnel Le concept de modèle relationnel a été présenté par E. F. Codd en 1970 et fait l’objet, encore actuellement, de nombreux travaux de recherche. [MERISE-3] En première approximation une relation peut être définie comme un tableau de données . Chacun de ces tableaux aura un nom et sera caractérisé par des attributs. Le métamodèle s’inspire de la notion mathématique de relation, il est basé sur deux aspects fondamentaux: Aspect statique - Une démarche de conception permettant de définir une collection de relations ou Modèle logique de données relationnel. Aspect dynamique - Une algèbre permettant de manipuler des tables ou relations. Dans la suite de cours nous ne traiterons que de l’aspect statique. L’algèbre relationnelle, partie dynamique, est traitée dans la partie de modélisation des traitements. 1 SGBD-R Système de gestion de base de données relationnelle 7 1.3 Modèle logique de données relationnel Remarque Le terme de relationnel s’applique à la notion de la structure tabulaire. Formellement le terme de relation s’applique à une table où la relation s’établit entre attributs. Afin d’éviter toute confusion d’interprétation, dans la suite ce cours le terme de table sera utilisé en lieu et place de relation et le terme relation sera réservé aux relations entre tables. 1.3.1 Représentation graphique Tout comme pour la représentation des objets du modèle conceptuel, plusieurs solutions textuelles ou visuelles permettent d’améliorer la lisibilité du modèle logique de données. Selon les outils utilisés la représentation devra être adaptée pour permettre l’automatisme du passage entre les différents niveaux d’abstraction. Afin d’avoir une unité de représentation, cette partie est illustrée en respectant les choix du standard CPLN. 1.3.1.1 Standard de représentation (CPLN) La représentation adoptée dans le cadre du CPLN est inspirée des modèles Anglo-saxon et plus particulièrement de la méthode Stradis implantée par ProKit*WorkBench2. Elle permet d’élaborer un modèle sans devoir recourir à un outil sophistiqué de graphisme, un simple traitement de texte suffit. 2 Stradis et ProKit#WorkBench sont des méthodes et outils qui étaient distribués dans les années ‘80 par le constructeur d’avions McDonnel-Douglas 8 1.3.2 Table Une table est l’équivalent d’un tableau de valeurs. Il s’agit d’une entité ou d’une association dans le cadre du MCD / E-A. Le tableau ci-après fixe le vocabulaire utilisé dans le cadre du modèle relationnel. Vocabulaire tabulaire Vocabulaire relationnel Remarques Colonne Attribut Propriété dans le cadre du MCD / E-A Un attribut prend ses valeurs dans un domaine par exemple: domaine des entiers {... -2; -1; 0; 1; 2 ...} domaine des continents {Europe; Asie ...} Ligne Occurrence d’entité ou d’association dans le cadre du MCD / E-A. Tuple ou n-uplet Equivalent d’un enregistrement dans le cadre d’une gestion de fichiers structurés. Nombres de colonnes Degré Nombre de lignes Cardinalité 9 1.3.2.1 Schéma d’une table Le schéma d’une table permet de définir la structure d’une table. Il est constitué du nom de la table suivi de la liste de ses attributs avec leurs domaines de valeurs. Remarque Par souci de concision dans les représentations graphiques manuelles les domaines de valeur ne sont pas reportés. Plus tard nous adjoindrons au schéma l’ensemble des contraintes d’intégrité. Une contrainte d’intégrité prend en compte des restrictions dans le cadre du domaine de valeur du ou des attributs concernés. COUREUR Dossard Nom Prenom Categorie MODDO31 Figure 1 - Représentation d'un schéma de table 1.3.2.2 Extension d’une table Extension ou Instantiation - Ensemble de lignes ou tuples définies par les valeurs prises par les différents attributs du schéma de la table concernée. COUREUR Dossard Nom Prenom Categorie tuple1 1000 Dupont Alain A tuple2 1001 Frey Claude B tuple3 1002 Girard Jacques A tuple4 1003 Deloup Grégoire B MODDO32 Figure 2 - Représentation d'une extension de table 10 1.3.3 Bases de données relationnelles [NANCI-92] Une base de données relationnelle est un ensemble de tables. Le schéma de la base de données sera l’ensemble des schémas des tables la composant. Remarque Cette définition sous-entend que les relations entre tables sont prises en charges par les contraintes d’intégrité référentielles définies plus tard. 1.3.4 Clés Théoriquement, l’ensemble des valeurs prises par les différents attributs d’une table devrait permettre de qualifier un et un seul tuple. Pratiquement il s’avère difficile de mettre en oeuvre cette règle. Pour optimiser le fonctionnement des SGBD-R des clés permettent l’accès à un ou plusieurs tuples sans ambiguïté. Une clé est composée d’un ou plusieurs attributs d’une table. Clé simple - Un seul attribut Clé composée - Plusieurs attributs 1.3.4.1 Clé primaire La clé primaire doit permettre d’identifier chaque tuple d’une table sans ambiguïté. En règle générale, il s’agit de l’identifiant du MCD / E-A. 1.3.4.2 Clé secondaire La clé secondaire doit permettre d’identifier un ou éventuellement plusieurs tuples à partir de la connaissance d’un ou plusieurs attributs. L’identifiant alternatif du MCD / E-A devient une clé secondaire. 1.3.4.3 Clé étrangère Clé principale d’une autre table avec laquelle une relation est établie. En cas de relation réflexive, une clé étrangère réfère à la table elle-même. 11 1.3.5 Les contraintes d’intégrité [NANCI-92] Une contrainte d’intégrité est une assertion qui doit être vérifiée par les valeurs d’attributs de tables constituant une base de données. [MERISE-3] Le modèle conceptuel de données ne peut suffire à donner une image complète de la réalité. En effet, c’est une vision synthétique qui se veut également la plus simple possible d’un point de vue représentation. C’est pourquoi associé à ce modèle conceptuel on définit un système d’intégrité constitué d’un ensemble de prédicats relatifs aux entités conceptuelles de façon à fournir une image cohérente de la réalité. Les contraintes ci-après sont présentées selon la syntaxe de la norme SQL3. 1.3.5.1 Contrainte d’unicité de valeurs (UNIQUE) Une contrainte d’unicité de valeur assure l’unicité de valeurs d’un ou de plusieurs attributs pour chacun des tuples de l’extension de la table. 1.3.5.2 Contrainte de valeur non nulle (NOT NULL) Une contrainte de valeur non nulle assure la présence obligatoire d’une valeur d’un ou de plusieurs attributs pour chacun des tuples de l’extension d’une table. 1.3.5.3 Contrainte de clé primaire (PRIMARY KEY) Une contrainte de clé primaire est construite théoriquement à partir des contraintes de base suivantes: Contrainte d’unicité de valeur (UNIQUE) Contrainte de valeur non nulle (NOT NULL) Contrainte d’interdiction de changement de valeur ou de mise à jour en cascade en cas de changement. 3 SQL - Strutured Query Language / Langage d’interrogation structuré 12 1.3.5.4 Contrainte d’intégrité référentielle (FOREIGN KEY, REFERENCE) Une contrainte d’intégrité référentielle est un lien sémantique défini entre 2 tables. Ce lien est réalisé par une duplication de la clé primaire dans une autre table, ou autre champ de la même table en cas d’association réflexive. Commandes Clients N° Cde N° Cli Date Montant 100 28 11.09.94 1200.00 101 12 25.10.94 248.00 12 13 Dubois Dupécé Marie Cynthia 102 403 18.11.94 479.00 28 Dupont Claude 402 403 Farine Armata Marc Jean N° Client Nom CLE ETRANGERE Prenom REFERENCE MODDO33 Figure 3 - Exemple de référence étrangère Une contrainte d’intégrité référentielle doit assumer que: En cas d’ajout de la valeur d’une clé étrangère, la valeur équivalente doit exister comme valeur de clé primaire dans la table référencée. Dans l’exemple ci-dessus, nous ne pourrions pas ajouter une commande pour un client qui n’existe pas dans le fichier des clients En cas de suppression d’un tuple, aucune valeur de clé étrangère référant à la valeur de la clé primaire du tuple supprimé ne doit exister dans l’ensemble des tables de la base de données. Dans l’exemple ci-dessus, nous ne pourrions pas supprimer le client N° 28 Dupont Claude car il est référencé par la facture N° 100. Selon un vocabulaire que nous définirons plus tard, nous pouvons définir une contrainte d’intégrité référentielle par les 2 règles suivantes: En cas d’ajout d’un enfant, le père doit exister. En cas de suppression d’un père, il ne doit pas exister de fils. 13 1.3.6 Représentation des tables Les éléments qui sont généralement mis en évidence sont: La clé primaire Les clés étrangères Les clés secondaires ou alternatives Les champs calculés 1.3.6.1 Clé primaire Les attributs formant la clé primaire sont soulignés et écrits en gras. Dossard CYCLISTE Nom Prenom MODDO34 Figure 4 - Représentation d'une clé primaire 1.3.6.2 Clé secondaire ou alternative Les attributs formant la clé secondaire ou alternative sont complétés par le symbole ^. CYCLISTE Dossard Nom^ Prenom MODDO35 Figure 5 - Représentation d'une clé secondaire 1.3.6.3 Clé étrangère Les attributs formant la clé étrangère sont complétés par le symbole +. CYCLISTE Dossard Nom^ Prenom + Categorie MODDO36 Figure 6 _Représensation d'une clé étrangère 14 1.3.6.4 Attribut calculé Les attributs calculés sont complétés par le symbole *. CYCLISTE Dossard Nom^ Prenom + Categorie Points* MODDO37 Figure 7 - Représentation d'un champ calculé 15 1.3.7 Relation entre tables Une relation est une fonction d’un ensemble de départ vers un ensemble d’arrivée. La terminologie ci-après est souvent utilisée. Table source Table destination Table parent Table enfant La table source ou parent représente l’ensemble de départ. La table de destination ou enfant représente l’ensemble d’arrivée. Ensemble Parent Ensemble Enfant BATIMENT APPARTEMENT BATIMENT partagé en APPARTEMENT Figure 8 - Relation ensembliste (fonction) 16 Une relation entre 2 tables se met en place par le mécanisme des clés étrangères que nous avons décrit précédemment. La cohérence des relations doit être assurée par les contraintes d’intégrité référentielle. 1.3.7.1 Représentation d’une relation Une relation est représentée par un arc orienté allant de la table source vers la table de destination. Le nom de la relation est porté sur l’arc. Numero BATIMENT Descriptif partage en APPARTEMENT Numero Num_bat + Etage Descriptif MODDO38 Figure 9 - Représentation d'une relation 17 1.3.7.2 Postulats de base Les relations du modèle relationnel sont basées sur des fonctions ensemblistes respectant les 2 postulats ci-dessous: 1. Un élément de l’ensemble d’arrivée ne peut avoir qu’une et une seule image dans l’ensemble de départ. 2. Un élément de l’ensemble de départ peut avoir une ou plusieurs images dans l’ensemble d’arrivée. Le premier postulat est illustré dans l’exemple du BATIMENT partagé en APPARTEMENT par l’appartenance d’une occurrence d’APPARTEMENT à une seule occurrence de BATIMENT. Ensemble Parent Ensemble Enfant BATIMENT APPARTEMENT BATIMENT partagé en APPARTEMENT Figure 10 - Fonction 1er postulat Physiquement un appartement ne peut se situer que dans un seul bâtiment. 18 1.3.8 Degré d’une relation Le degré d’une relation représente la mise en oeuvre des 2 postulats de base. Il s’exprime sous forme de 2 combinaisons de 2 chiffres. Le premier chiffre représente le premier postulat et le deuxième chiffre, le deuxième postulat. Degré Signification 1:1 1: :1 Un seul parent pour chaque enfant Un seul enfant possible pour chaque parent Exemple: Une et une seule facture est établie pour une commande. Une facture ne concerne qu’une commande. COMMANDE FACTURE 1:n 1: :n Un seul parent pour chaque enfant Plusieurs enfants possibles pour chaque parent Exemple: Un ou plusieurs appartements sont situés dans un bâtiment BATIMENT APPARTEMENT La difficulté essentielle de la démarche consiste à déterminer précisément quel est le parent et quel est l’enfant dans une relation. Le premier postulat permet souvent d’orienter le choix en s’assurant qu’un enfant n’a bien qu’un seul père. Le degré d’une relation n’est en principe par reporté sur le graphisme du modèle car il est déductible des cardinalités qui seront présentées dans le prochain chapitre. 19 1.3.9 Cardinalité d’une relation La cardinalité d’une relation est un concept totalement identique à la cardinalité du modèle conceptuel. Toutefois de par: l’orientation de la relation d’une table source vers une table parent, l’unicité de l’image de l’élément d’arrivée dans l’ensemble de départ (1er postulat), le mode de mise en oeuvre et de représentation de la cardinalité est différente. 1.3.9.1 Cardinalité du côté parent Du côté du parent la cardinalité représente l’obligation pour l’enfant d’avoir un parent ou pas, sous-entendu que l’enfant ne peut avoir qu’un parent. E1 Numero E1 R1 E2 Numero Numero R1 + Num_E1 E2 Parent obligatoire MCD (1,1) Numero + Num_E1 Parent optionnel MCD (0,1) MODDO39 Figure 11 - Cardinalité côté parent 20 1.3.9.2 Cardinalité du côté enfant La cardinalité du côté de l’enfant représente le degré de la relation et l’obligation pour le parent d’avoir au moins un enfant ou pas. Les valeurs possibles et l’analogie avec le MCD est reporté dans le tableau ci-après: Symbole relationnel Degré de la relation Equivalent MCD Z 1:1 (0,1) 1 1:1 (1,1) Absence de symbole 1:n (0,n) P 1:n (1,n) E1 Numero E1 R1 Remarque Obligatoirement un et un seul enfant Obligatoirement au moins un enfant Numero R1 Z E2 Numero 1 + Num_E1 E2 Un seul enfant optionnel MCD (0,1) E1 Numero + Num_E1 Obligatoirement un et un seul enfant MCD (1,1) Numero E1 R1 Numero R1 P E2 Numero + Num_E1 E2 Plusieurs enfants optionnels MCD (0,n) Numero + Num_E1 Obligatoirement au moins un enfant MCD (1,n) MODDO40 Figure 12 - Cardinalité côté enfant 21 1.4 Représentation par les outils d’aide à la conception Le mode de représentation présenté et utilisé jusqu’ici se prête bien pour des modèles de peu de tables et de relations. Dès que la complexité augmente la représentation des tables peut se faire selon le symbolisme mis en place pour les entités du MCD. Les outils d’aide à la conception (CASE) représentent, en principe, les tables sous forme de rectangle et les relations sous forme d’arcs orientés. Selon les besoins de lisibilité, différents niveaux de représentation peuvent être choisis: Clés Clés étrangères Attributs Cardinalité ... ECOLE Numero PROFESSEUR Employe Numero + Num_ecole Mnemonique^ Code^ Libelle Nom Prenom MODDO41 Figure 13 - Représentation inspirée des outils CASE 22 2. Passage du MCD au MLD Le passage du modèle conceptuel de données (MCD) au modèle logique de données (MLD) peut se faire automatiquement en appliquant 3 règles de transformation. 2.1 Règle No 1 Chaque Entité du MCD est représentée sous forme d’une table L’identifiant de l’entité (MCD) devient la clé primaire de la table (MLD). Une clé primaire abstraite, sous forme de numéro d’ordre, est créée si l’identifiant de l’entité (MCD) ne peut respecter les contraintes de clé primaire (MLD). Les propriétés de l’entité (MCD) deviennent les attributs de la table (MLD). 2.2 Règle No 2 Chaque association binaire qui a sur une de ses pattes une cardinalité maximale de 1 (-,1) est traduite en une relation Les attributs de l’association (MCD) sont reportés dans la table enfant (MLD). Les tables parent et enfant de la relation (MLD) devront être choisies en fonction des cardinalités (MCD). 23 2.2.1 Cardinalité (-,n) et (-1) La relation sera de degré 1:n. L’entité située du côté de la cardinalité maximale de n (-,n) devient la table parent. Un parent peut avoir n enfant. L’entité située du côté de la cardinalité maximale de 1 (-,1) devient la table enfant. Un enfant ne peut avoir qu’un parent. L’exemple ci-dessous représente l’association possible d’une personne habitant une commune. Nous ne prenons pas en compte le changement de domicile, ce qui explique la cardinalité maximale de 1 (-,1) du côté de l’entité PERSONNE. PERSONNE COMMUNE Numero -,1 Nom Prenom Numero -,n Nom Surface habite MODOO42 Figure 14 - MCD-> MLD Association de cardinalité (-,n) et (-,1) Le MLD ci-dessous montre la transformation de l’association habite en une relation. Numero COMMUNE Nom Surface habitee PERSONNE Numero Num_commune + Nom Prenom MODDO43 Figure 15 - MCD-> MLD Relation 1:n Dans notre exemple l’association habite est traduite en une relation habitée car la table COMMUNE est le parent de la relation. 24 2.2.2 Cardinalité (-,1) et (-1) Une des deux entités doit être choisie comme table parent, logiquement ou arbitrairement si ce n’est pas possible. L’exemple ci-dessous montre l’association de mariage entre un homme et une femme. Nous ne prenons pas en compte de notion d’historisation de mariages, ce qui explique les 2 cardinalités maximales de 1 (-,1). HOMME FEMME Numero -,1 Nom Prenom -,1 mari Numero Nom Prenom MODOO44 Figure 16 - MCD-> MLD Association de cardinalité (-,1) et (-,1) Le MLD ci-dessous montre la transformation de l’association mari en une relation. Numero HOMME Nom Prenom mari Z Numero FEMME Num_mari + Nom Prenom MODDO45 Figure 17 - MCD-> MLD Relation 1:1 2.2.3 Association n-aire, n>2 Si une association n-aire, où n>2, inclut une cardinalité maximale de 1 (-,1) elle doit être préalablement transformée en une entité associative. 25 2.3 Règle No 3 Chaque association qui ne comporte sur ses pattes que des cardinalités maximales de n (-,n) est traduite en une table associative Une table associative est un objet abstrait qui matérialise une association . La clé primaire de la table associative est formée par les clés étrangères de toutes les tables sources de relations. Les attributs de l’association (MCD) sont reportés dans la table associative (MLD). Le MCD ci-dessous représente une association de dimension 4 à transformer en modèle logique. E1 Numero -,n E2 Numero E3 -,n A ai E4 -,n Numero -,n Numero MODDO46 Figure 18 - MCD -> MLD Association n-aire 26 Le MLD ci-dessous montre la table associative créée à partir de l’association du MCD précédent. Nous voyons que la clé primaire de la table associative est formée des 4 clés étrangères des tables qui participent à l’association. Numero E1 R1 R2 Num_E1 + A Numero E2 Num_E2 + Num_E3 + Num_E4 R3 ai R4 Numero E3 + Numero E4 MODDO47 Figure 19 - MCD -> MLD Table associative Les relations issues de la transformation de l’association du MCD sont obligatoirement de degré 1:n. Pour respecter la dimension initiale de l’association la présence du parent est obligatoire pour toutes les relations. Le MLD précédent est reproduit ci-dessous en mettant en évidence le respect de la dimension de la table associative. E1 Numero E2 R1 A Num_E1 + R2 Num_E2 + Num_E3 + Num_E4 + R4 R3 E3 Numero Numero E4 ai Num_E1 <> Null Num_E2 <> Null Num_E3 <> Null Num_E4 <> Null Numero MODDO47 Figure 20 - Respect de la dimension d'une table associative 27 L’exemple ci-dessous illustre un cas d’association binaire. Le modèle représente une historisation des prix de l’entité ARTICLE. L’entité TERME représente la durée de validité du prix d’un ou de plusieurs articles. ARTICLE Numero TERME 1,n Coute 0,n Numero Prix Reference^ Nom MODDO22 Figure 21 - Exemple MCD-> MLD Association binaire Le MLD ci-dessous montre la transformation de l’association Coute en une table associative COUTE. La clé primaire de la table associative est formée par les 2 clés étrangères des tables ARTICLE et TERME. La propriété Prix de l’association est reportée comme attribut de la table associative. ARTICLE Numero Reference^ Nom P COUTE TERME + Num_article + Num_terme Prix Code^ Numero MODDO48 Figure 22 - Exemple MCD-> MLD Table associative binaire 28 2.4 Rôles multiples d’une table source Si une table doit comporter plus d’une clé étrangère issues de la même table source il est nécessaire de les nommer différemment. Les identifiants des différentes clés étrangères d’une même table devront permettre de déterminer les différents rôles que joue la table source. L’exemple ci-dessous représente par une association réflexive la distance et le temps de parcours entre 2 villes qui sont différents selon le sens du trajet. VILLE 0,n Depart Numero Nom ^ Relie Distance Temps 0,n Arrivee MODDO51 Figure 23 - MCD-> MLD Association réflexive Le MLD ci-dessous montre les 2 rôles, ville de départ et ville d’arrivée, joués par la table VILLE pour la table associative RELIE. VILLE Numero Depart RELIE Nom^ Arrivee + Num_villedepart + Num_villearrivee Distance Temps MODDO50 Figure 24- MCD-> MLD Table associative avec rôles de réflexivités 29 L’exemple ci-dessous illustre une sortie en bateau pour laquelle un pilote et un copilote sont requis. L’entité EMPLOYE de laquelle sont issus les pilotes et copilotes joue dans ce cas 2 rôles bien distincts dans l’association. ARTICLE EMPLOYE BATEAU 0,n Pilote Numero Sortie Nom Prenom 0,n 0,n Numero Immatriculation^ Nom Copilote 0,n PERIODE Numero Code^ MODDO52 Figure 25 - MCD-> MLD Rôle de l'entité Les 2 rôles seront reportés dans le nom des attributs formant les 2 clés étrangères issues de la table EMPLOYE. EMPLOYE Numero Nom^ Pilote Copilote SORTIE Prenom + Num_pilote + Num_copilote + Num_bateau Sort BATEAU Numero Immatr^ + Num_periode Duree Nom PERIODE Numero Code^ MODDO53 Figure 26 - MCD-> MLD Table associative avec rôles 30 3. Les formes normales 3.1 Introduction La normalisation est un processus qui permet de s’assurer de la relationnalité de la structure des tables d’un schéma de base de données et de l’absence de redondances. La normalisation est basée sur le concept de dépendance fonctionnelle. Les formes normales sont un formalisme qui permet de vérifier l’absence de redondance. La première forme normale (1NF) permet de vérifier qu’une table puisse être considérée comme étant relationnelle. La deuxième forme normale (2NF) permet de valider le choix de la clé primaire en contrôlant les dépendances fonctionnelles entre clé primaire et attribut. La troisième forme normale (3NF) permet de vérifier l’absence de redondances dans la table en contrôlant les dépendances fonctionnelles entre attributs non clés. Une table est dite normalisée si elle est en troisième forme normale (3NF). 3.2 Dépendance fonctionnelle Un attribut (ou groupe d’attribut) B d’une table R est dit en dépendance fonctionnelle d’un autre attribut (ou groupe d’attribut) A de R, si, à tout instant chaque valeur ai de A n’a qu’une valeur associée bi de B. On note: AB R tuple i A ai B bi C ci L’exemple ci-dessous montre une dépendance fonctionnelle de la table CONCURRENT: Dossard Nom CONCURRENT Dossard 1000 1004 1005 1008 Nom Dupont Frey Girard Deloup Prenom Claude Jean Marc Claude 31 3.2.1 Dépendance fonctionnelle élémentaire Une dépendance fonctionnelle A B est dite élémentaire s’il n’existe pas A’ A tel que A’ B. A R tuple i A’ ai’ A’’ ai’’ B C bi ci L’exemple ci-dessous montre une dépendance fonctionnelle élémentaire de la table CONCURRENT: Dossard, Categorie Nom. CONCURRENT Dossard 1000 1004 1005 1005 1008 1008 Categorie A B A B B A Nom Dupont Frey Girard Dupont Deloup Farine Prenom Claude Jean Marc René Claude Daniel La connaissance du numéro de dossard seul ou de la catégorie seule ne permet pas de déterminer le nom du concurrent! 3.2.2 Dépendance fonctionnelle directe Une dépendance fonctionnelle A B est dite directe s’il n’existe pas d’attribut non clé C tel que A C et C B. L’exemple ci-dessous montre une dépendance fonctionnelle directe de la table CONCURRENT: Dossard Prenom. CONCURRENT Dossard 1000 1004 1005 1008 Nom Dupont Frey Girard Deloup Prenom Claude Jean Marc Claude La connaissance du nom d’un concurrent ne permet pas de déterminer son prénom, seule la valeur de la clé primaire, Dossard, le permet. 32 3.3 1ère forme normale (1NF) Une table est en première forme normale (1NF) si aucun attribut qui la compose: n’est lui-même une relation; ne contient de groupes répétitifs. Par définition, la première forme normale impose d’avoir des attributs de tables qui soient atomiques. Les 2 chapitres qui suivent illustrent la manière de transformer une table non relationnelle en une table relationnelle, donc en 1ère forme normale. 3.3.1 ¬1NF - Attribut formé d’une relation 3.3.1.1 Règle de normalisation Lorsqu’une table comporte un attribut qui est lui-même une relation, il faut transformer l’attribut en une nouvelle table et établir une relation. Cette relation aura comme source la nouvelle table et comme destination la table initiale dans laquelle l’attribut a été remplacé par une clé étrangère. 3.3.1.2 Exemple Dans la table CONCURRENT, la catégorie à laquelle appartient le coureur dépend de son âge. Par exemple, catégorie A pour 12 ans. CONCURRENT Dossard 1000 1004 1005 1008 Nom Dupont Frey Girard Deloup Age_cat 12 , A 13 , B 12 , A 14 , B Nous avons les dépendances fonctionnelles suivantes: Dossard Nom Dossard Age_cat 33 Age_cat contient une relation qui peut être exprimée par la dépendance fonctionnelle: Age Categorie L’attribut Age_cat doit être retiré de la table CONCURRENT et transformé en une nouvelle table AGE_CAT. Cette nouvelle table sera source d’une relation Categorie vers la table CONCURRENT comme illustré ci-dessous: CONCURRENT Dossard 1000 1004 1005 1008 Nom Dupont Frey Girard Deloup Age 12 13 12 14 AGE_CAT Age 12 13 14 Categorie A B B La structure de la table initiale CONCURRENT est transformée en un mini modèle logique de données qui se présente sous la forme suivante: AGE_CAT Age Categorie categorie CONCURRENT Dossard Age + Nom MODDO54 Figure 27 - (1NF) Transformation d'un attribut-relation 34 3.3.2 ¬ 1NF - Attribut contenant un groupe répétitif 3.3.2.1 Règle de normalisation L’attribut contenant un groupe répétitif doit être retiré de la table pour être transformé en: une table de référence des éléments du groupe répétitif; une table associative. Cette table associative doit mettre en oeuvre l’association implicite qui existait entre la table initiale et le groupe répétitif qu’elle contenait. 3.3.2.2 Exemple Dans la table CONCURRENT, l’attribut Long_saut contient 2 valeurs représentant le résultat que peut obtenir un coureur à chacun des 2 sauts auxquels il a droit. CONCURRENT Dossard 1000 1004 1005 1008 Nom Dupont Frey Girard Deloup Long_saut 7.28 , 7.07 0 , 7.00 8.10 , 0 6.35 , 6.50 Nous avons les dépendances fonctionnelles suivantes: Dossard Nom Dossard Long_saut Pour que notre exemple soit suffisamment significatif, nous avons comme contrainte supplémentaire que les concurrents doivent effectuer leurs premier et deuxième saut dans un intervalle de temps imparti. L’attribut Long_saut doit être retiré de la table CONCURRENT et transformé en: une table de référence SAUT pour les 2 sauts avec les heures de début et de fin de saut; une table associative SAUT_CONC pour l’enregistrement des 2 résultats de sauts des concurrents. Les tables SAUT et CONCURRENT sont sources de relations vers la table SAUT_CONC. La clé primaire de SAUT_CONC est formée par la concaténation des 2 clés étrangères des relations mentionnées. 35 CONCURRENT Dossard 1000 1004 1005 1008 Nom Dupont Frey Girard deloup SAUT_CONC Dossard+ 1000 1000 1004 1004 1005 1005 1008 1008 Num_saut+ 1 2 1 2 1 2 1 2 SAUT Numero+ 1 2 Hre_debut Hre_fin 13:00 14:00 16:00 16:30 Longueur 7.28 7.07 0 7.00 8.10 0 6.35 6.50 La structure de la table initiale CONCURRENT doit être transformée en un mini modèle logique de données qui se présente sous la forme suivante: SAUT Numero Hre_deb Hre_fin Ordre_saut SAUT_CONC Dossard + Num_saut + Longueur Saute CONCURRENT Dossard Nom MODDO55 Figure 28 - (1NF) Transformation d'un attribut - Groupe répétitif 36 3.4 2ème forme normale (2NF) Une table est en deuxième forme normale (1NF) si: elle est déjà en 1NF; les dépendances fonctionnelles entré la clé primaire et les autres attributs sont élémentaires. Seule une table comportant une clé primaire composée de plus d’un attribut doit être vérifiée en 2NF de la par la définition de la dépendance fonctionnelle élémentaire. 3.4.1 Règle de normalisation Lorsqu’une table comporte un ou plusieurs attributs qui ne sont pas en dépendance fonctionnelle élémentaire, il faut créer une ou plusieurs nouvelles tables. Cette ou ces nouvelles tables auront: comme clé primaire, la partie de la clé de la table initiale qui est source de la dépendance fonctionnelle; comme attribut(s), le ou les attributs cibles de la dépendance fonctionnelle. Cette ou ces nouvelles tables seront sources de relations vers la table initiale. 3.4.2 Exemple La clé primaire de la table CONCURRENT ci-dessous est formée des 2 attributs Dossard, Categorie. CONCURRENT Dossard 1000 1004 1005 1005 1008 1008 Categorie A B A B B A Nom Dupont Frey Girard Dupont Deloup farine Prenom Claude Jean Marc René Claude Daniel Points 15 20 15 20 20 15 Les dépendances fonctionnelles suivantes doivent être vérifiées: Dossard, Categorie Nom Dossard, Categorie Prenom Dossard, Categorie Points Si nous avons comme règle de gestion que le nombre de points attribués n’est pas propre à un concurrent, mais qu’il dépend de la catégorie à laquelle il appartient alors: Dossard, Categorie Points 37 n’est plus une dépendance fonctionnelle élémentaire, car nous avons la dépendance fonctionnelle suivante avec une partie de la clé primaire: Categorie Points Pour être en 2NF, la table initiale doit être complétée par une table mettant en oeuvre la dépendance fonctionnelle ci-dessus. Cette nouvelle table sera source de la relation permettant de trouver le nombre de points reçus par un concurrent en fonction de la catégorie à laquelle il appartient. CONCURRENT Dossard Cat+ Nom 1000 1004 1005 1005 1008 1008 A B A B B A Dupont Frey Girard Dupont Deloup farine Prenom Claude Jean Marc René Claude Daniel RECOIT Cat A B Points 15 20 La structure de la table initiale CONCURRENT doit être transformée en un mini modèle logique de données qui se présente sous la forme suivante: FORFAIT Cat Points Recoit CONCURRENT Dossard Cat + Nom Prenom MODDO56 Figure 29 - Transformation en 2NF 38 3.5 3ème forme normale (3NF) Une table est en troisième forme normale (3NF) si: elle est déjà en 2NF; Les dépendances fonctionnelles entre la clé primaire et les autres attributs sont directes. 3.5.1 Règle de normalisation Lorsqu’une table comporte un ou plusieurs attributs qui ne sont pas en dépendance fonctionnelle directe, il faut créer une ou plusieurs nouvelles tables. Cette ou ces nouvelles tables auront: comme clé primaire, le ou les attributs qui sont source de la dépendance fonctionnelle; comme attribut(s), le ou les attributs cibles de la dépendance fonctionnelle; Cette ou ces nouvelles tables seront sources de relations vers la table initiale. 3.5.2 Exemple Dans la table CONCURRENT, la catégorie à laquelle appartient le coureur dépend de son âge. Par exemple, catégorie A pour 12 ans. CONCURRENT Dossard 1000 1004 1005 1008 Nom Dupont Frey Girard Deloup Age 12 13 12 14 Cat A B A B Nous avons les dépendances fonctionnelles directes suivantes: Dossard Nom Dossard Age La dépendance fonctionnelle ci-dessous n’est pas directe: Dossard Cat Car, nous avons la dépendance fonctionnelle: Age Cat 39 Pour être en 3NF, la table initiale doit être complétée par une table mettant en oeuvre la dépendance fonctionnelle ci_dessus. Cette nouvelle table sera source de la relation permettant de trouver la catégorie à laquelle appartient un concurrent en fonction de son âge. CONCURRENT Dossard 1000 1004 1005 1008 Nom Dupont Frey Girard Deloup Age 12 13 12 14 AGE_CAT Age 12 13 14 Categorie A B B La structure de la table initiale CONCURRENT est transformée en un mini modèle logique de données qui se présente sous la forme suivante: AGE_CAT Age Categorie categorie CONCURRENT Dossard Age + Nom MODDO54 Figure 30 - Transformation en 3NF 40 4. Modèle physique MPD Ce cours ne fait qu’effleurer la problématique du MPD. Il introduit quelques notions relativement stables liées aux SGBD-Relationnels. Le lecteur est prié de se référer aux ouvrages consacrés plus spécifiquement aux normes, standards ou produits spécifiques. Dans la ligne de l’option choisie et présentée au chapitre MLD nous ne traiterons que du MPD destiné aux bases de données de la classe relationnelle. 4.1 Objectifs du MPD Le modèle physique de données (MPD) est élaboré à partir du modèle logique de données (MLD) normalisé. Le MPD est obtenu par une adaptation et une complétude du MLD en tenant compte des spécificités du SGBD, des performances, des traitements et de la sécurité. Le MPD représente la structure définitive de la base de données implantée. Le schéma est dit physique, car il tient compte des caractéristiques techniques du SGBD et du matériel, et des exigences en interfaces et en performance des modules de programmation. Autrement dit, si on choisit un autre SGBD et si on modifie l’architecture et les exigences des modules, seul le MPD change. 4.2 Règles de Codd E. F. Codd a défini à partir de son modèle relationnel les règles que doit respecter un SGBD pour mériter la classification SGBR-Relationnel. Règle Libellé Règle n°1 Représentation des informations Les informations sont représentées au niveau logique et non physique, ce qui signifie que l’on ne se préoccupe pas de l’implémentation réelle des données. Elles sont décrites par des valeurs contenues dans des tables (ces tables sont également nommées relations). Règle n°2 Accès aux données Une donnée est accessible logiquement - c’est-à-dire sans connaissance de son implantation physique - grâce à la combinaison du nom de table (relation), de la clé primaire et du nom de la colonne (attribut). 41 Règle n°3 Gestion des valeurs absentes Cette règle précise que la valeur NULL - correspondant à l’absence d’information est interprétable et de nature différente d’une chaîne de caractères vide (ou composée de caractères << blancs >>) ou d’une valeur numérique à zéro par exemple. Règle n°4 Le dictionnaire de données La description de la base de données est représentée par des informations accessibles comme s’il s’agissait de données ordinaires. Le langage relationnel permet donc de manipuler indifféremment des données du système d’information ou des données écrivant la base elle-même. La description de la base est donc stockée dans des tables faisant partie de ce que l’on nomme le dictionnaire de données. Règle n°5 Le langage Le SGBD doit inclure au moins un langage comportant l’ensemble des fonctionnalités suivantes : définition des données, définition des vues, manipulation de données, contraintes d’intégrité, autorisations, gestion des transactions. (Le langage SQL répond à cette description.) Règle n°6 La mise à jour à travers une vue Une vue est un mode de représentation logique de la base de données. Si une vue peut être mise à jour, elle peut aussi l’être par le système. Règle n°7 La mise à jour des tables Le langage relationnel doit disposer d’ordres de haut niveau s’appliquant non seulement à la lecture des données, mais aussi à la création, la mise à jour ou la suppression d’informations. Règle n°8 L’indépendance physique Les programmes d’applications et les transactions interactives sont indépendants de la représentation physique des données et des méthodes d’accès sous-jacentes. (Cela garantit la souplesse d’évolution du système d’information et exige que le SGBD dissocie efficacement la représentation logique d’une part et les aspects d’organisation physique d’autre part.) Règle n°9 L’indépendance logique Cette règle stipule que les programmes ne sont pas remis en cause lorsque des modifications - sans perte d’informations structurelles sont opérées sur les relations de la base. Citons, par exemple, l’éclatement d’une relation en deux relations ou, à l’opposé, la fusion de relations. Règle n°10 L’indépendance vis-àvis des contraintes d’intégrité Les contraintes d’intégrité, susceptibles d’évoluer dans le temps, doivent pouvoir être formulées en dehors de tout programme applicatif et être référencées dans le dictionnaire des données. Une application informatique constitue une réponse à un problème qui se pose à un moment précis. Si l’entreprise et son environnement évoluent, le système d’information doit par conséquent pouvoir s’adapter à cette évolution sans que soit remis en cause l’acquis applicatif. 42 Règle n°11 L’environnement applicatif n’est pas affecté par la répartition des L’indépendance vis-à- données - sur des sites et des supports physiques distincts - ou toute vis de la répartition modification de cette éventuelle répartition. des données Règle n°12 La non-subversion Cette dernière règle stipule que si le langage dispose d’un haut niveau (C par exemple), ce langage ne peut pas contourner ou remettre en cause les contraintes de sécurité et les règles d’intégrité énoncées au plus haut niveau (par le langage SQL en l’occurrence). 4.3 Choix d’un SGBD [Merise-3] - Un SGBD, Système de Gestion de Base de Données représente un ensemble coordonné de logiciels permettant de décrire, de mémoriser, d’interroger et de manipuler les données constituant la base. Ce système assure également la confidentialité et la sécurité des données. Ces deux dernières fonctions sont importantes car dans le contexte d’utilisation d’une base de données de nombreux utilisateurs sont amenés à agir de manière simultanée sur les données. Dans la classe des SGBD-Relationnel, la comparaison de produits et donc le choix peut être fait de manière rigoureuse et sans à-priori en appliquant les règles de Codd. Actuellement, le support du modèle Client/serveur est certainement un critère complémentaire qui peut s’avérer primordial. 43 4.4 Passage du MLD au MPD Le MLD et sa normalisation nous a permi de créer un schéma sous forme de table en 3NF. A titre d’illustration nous mentionnons ci-dessous quelques actions à effectuer pour transformer notre MLD en un MPD: créer les tables et les colonnes techniques, optimiser les accès en choisissant les colonnes qui doivent être indexées, calculer des paramètres de stockage des tables et des index. introduire éventuellement des redondances calculées, définir les vues, définir les contraintes d’intégrité et les déclencheurs (triggers) sur les tables, définir les droits d’accès aux données, Ces différentes actions seront conduites en fonction des spécificités du produit choisi. Néanmoins, la norme SQL a tendance à réduire les disparités entre produits et peut-être qu’un jour le MPD sera égalemment une démarche formalisée et universelle. 4.5 Norme SQL [NANCI-92] Le langage de description et de manipulation de données adopté par la plupart des SGBD relationnels commercialisés est le langage SQL. Le langage SQL fut à l’origine le langage de consultation défini pour System R, SGBD prototype de recherche d’IBM. C’est l’acronyme de Structured Query Language. Ce langage a été normalisé dès 1986 (SQL1) puis en 1990 (SQL2 et3). 44 5. Recommandations d’écriture Dans le cadre de la modélisation des MLD et MPD effectuée au CPLN nous recommandons d’appliquer les règles ci-après. Ces règles s’ajoutent à celles indiquées dans la partie 1 du cours traitant du MCD. Objet Identifiant Commentaire Exemples Clé primaire NUMERO Entier long Autoséquencé Clé étrangère NUM_table Table CLIENT NUM_CLIENT NUM_role Table PERSONNE, Rôle de pupille et de tuteur NUM_PUPILLE , NUM_TUTEUR NUM_roletable Table PAYS, Rôle origine et domicile Table COMMUNE, Rôle Origine NUM_ORIGINEPAYS , NUM_DOMICILEPAYS NUM_ORIGINECOMMUNE DUPL_nom DUPL_AGE calculé à partir de la date de naissance Champ calculé 45 6. Bibliographie [PL-93] Petit Larousse illustré 1993 [MERISE-1] La méthode MERISE Tome 1, Principes et outils, H. Tardieu, A. Rochfeld, R. Colletti, Les Editions d’Organisation, 1983 [MERISE-2] La méthode MERISE Tome 2, Démarche et pratiques, H. Tardieu, A. Rochfeld, R. Colletti, G. Panet, G. Vahée, Les Editions d’Organisation, 1985 [MERISE-3] La méthode MERISE Tome 3, Gamme opératoire, A. Rochfeld, J. Moréjon, Les Editions d’Organisation, 1986 [NANCI-92] Ingénierie des systèmes d’information avec Merise D. Nanci, B. Espinasse, B. Cohen et H. Heckenroth 1992 c/o SYBEX [GUINIER-92] Sécurité et qualité des systèmes d’information D. Guinier, MASSON, 1992 [PLANCHE-88] Maîtriser la modélisation conceptuelle R. Planche, MASSON, 1988 46