Conception d’une base de données
Cyril Gruau
17 octobre 2005 (corrigé le 13 juillet 2006)
Résumé
Ce support de cours regroupe quelques notions concernant le modélisation conceptuelle de système d’information par schéma entités-associations (via l’étude des dépendances fonctionnelles), la traduction en schéma relationnel et la démarche inverse (rétro-conception). Il présente également les extensions majeures du modèle conceptuel de données.
Mots-clef: Merise, modèle conceptuel, entité, association, dépendance fonctionnelle, graphe de couverture minimale, schéma relationnel, traduction, rétro-conception, agrégation, identifiant relatif, héritage
Compléments apportés à l’édition de novembre 2003:
– une ré-écriture complète des règles de normalisation 10
– un nouveau paragraphe sur les dépendances fonctionnelles 16
– une ré-écriture complète de la section sur les agrégations ..30
– idem pour les identifiants relatifs . 35
– et l’héritage . 38
– auxquels s’ajoutent de nouveaux exemples et donc de nombreuses figures illustratives 42
Remerciements
L’auteur tient à exprimer toute sa gratitude envers Frédéric Brouard pour son travail de correction sur ce document, ses judicieux conseils et son soutien en toutes circonstances.
1
TABLE DES MATIERES`
Table des matières
Introduction 3
1 Modèle conceptuel de données (MCD) 4
1.1 Schéma entités-associations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
1.1.1 Entités et associations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
1.1.2 Attributs et identifiants . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
1.1.3 Cardinalités . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
1.1.4 Associations plurielles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
1.1.5 Association réflexive . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
1.1.6 Associations non binaires . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
1.2 Règles de normalisation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
1.2.1 Les bonnes manières dans un schéma entités-associations . . . . . . . . . . . . . . 10
1.2.2 Les formes normales . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
1.3 Dépendances fonctionnelles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
1.3.1 Définitions et propriétés . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
1.3.2 Graphe de couverture minimale . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
1.3.3 Traduction vers un schéma entités-associations . . . . . . . . . . . . . . . . . . . . 17
1.3.4 Gestion des dates et du caractère historique . . . . . . . . . . . . . . . . . . . . . . 18
1.3.5 Dépendances plurielles et réflexives . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
1.3.6 Associations sans attributs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
1.4 Méthodologie de base . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
2 Modèle logique de données (MLD) 22
2.1 Systèmes logiques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
2.2 Modèle logique relationnel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
2.2.1 Tables, lignes et colonnes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
2.2.2 Clés primaires et clés étrangères . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
2.2.3 Schéma relationnel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
2.3 Traduction d’un MCD en un MLDR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
3 Modèle physique de données (MPD) 27
3.1 Distinction entre MLD et MPD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
3.2 Optimisations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
4 Rétro-conception 28
4.1 Traduction inverse . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
4.2 Cas particuliers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
5 Compléments 30
5.1 Agrégation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
5.1.1 Association de type 1: n . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
5.1.2 Association de type n: m . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
5.2 Identifiant relatif ou lien identifiant . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
5.2.1 Résolution d’un problème sur le schéma relationnel . . . . . . . . . . . . . . . . . . 35
5.2.2 Modèle conceptuel correspondant . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
5.2.3 Discussion autour de la numérotation des exemplaires . . . . . . . . . . . . . . . . 37
5.3 Héritage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
5.3.1 Sous-entité . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
5.3.2 Utilisation de l’héritage pour séparer les informations complémentaires . . . . . . . 39
5.3.3 Spécialisation des associations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
INTRODUCTION
Conclusion 41
Références 41
Index 43
Introduction
Quand nous construisons directement les tables d’une base de données dans un logiciel de gestion des bases de données (Oracle, SQL Server, DB2, Access, MySQL, PostGre, ), nous sommes exposés à deux types de problème:
– nous ne savons pas toujours dans quelle table placer certaines colonnes (par exemple, l’adresse de livraison se met dans la table des clients ou dans la table des commandes?);
– nous avons du mal à prévoir les tables de jonction intermédiaires (par exemple, la table des interprétations qui est indispensable entre les tables des films et la table des acteurs).
Il est donc nécessaire de recourir à une étape préliminaire de conception.
Les techniques présentées ici font partie de la méthodologie Merise (Méthode d’Etude et de Réalisation´ Informatique pour les Systèmes d’Entreprise) élaborée en France en 1978 [Tardieu et al.], qui permet notamment de concevoir un système d’information d’une fa¸con standardisée et méthodique.
Le but de ce support de cours est d’introduire le schéma entités-associations (section 1), le schéma relationnel (sections 2 et 3) et d’expliquer la traduction entre les deux (sections 2.3 et 4). La construction du schéma entités-associations peut se faire en étudiant les dépendances fonctionnelles (section 1.3) et en tenant compte d’un certain nombre d’extensions conceptuelles incontournables (section 5).
1 Modèle conceptuel de données (MCD)
Avant de réfléchir au schéma relationnel d’une application, il est bon de modéliser la problématique à traiter d’un point de vue conceptuel et indépendamment du logiciel utilisé.
1.1 Schéma entités-associations
La modélisation conceptuelle que nous proposons dans ce document pour un univers dont on veut stocker les données, conduit à l’élaboration d’un type de schéma très répandu, le schéma entités-associations.
1.1.1 Entités et associations
Une entité est une population d’individus homogènes. Par exemple, les produits ou les articles vendus par une entreprise peuvent être regroupés dans une même entité articles (figure 1), car d’un article à l’autre, les informations ne changent pas de nature (à chaque fois, il s’agit de la désignation, du prix unitaire, etc.).
|
|
|
Fig. 1 – Entités
Par contre, les articles et les clients ne peuvent pas être regroupés: leurs informations ne sont pas homogènes (un article ne possède pas d’adresse et un client ne possède pas de prix unitaire). Il faut donc leur réserver deux entités distinctes: l’entité articles et l’entité clients.
Une association est une liaison qui a une signification précise entre plusieurs entités. Dans notre exemple, l’association commander est une liaison évidente entre les entités articles et clients, tandis que l’association livrer établit le lien sémantique entre les entités articles et fournisseurs.
Fig. 2 – Associations
1.1.2 Attributs et identifiants
Un attribut est une propriété d’une entité ou d’une association.
Toujours dans notre exemple (figure 3), le prix unitaire est un attribut de l’entité articles, le nom de famille est un attribut de l’entité clients, la quantité commandée est un attribut de l’association commander et la date de livraison est un attribut de l’association livrer.
Fig. 3 – Attributs
Une entité et ses attributs ne doivent traiter que d’un seul sujet afin d’assurer une certaine cohérence au modèle. Dans notre exemple, il est donc préférable de ne pas mettre les informations relatives aux fournisseurs dans l’entité des articles mais plutôt dans une entité fournisseurs séparées (et liée à l’entité articles via l’association livrer).
Ensuite, chaque individu d’une entité doit être identifiable de manière unique. C’est pourquoi toutes les entités doivent posséder un attribut sans doublon (c’est-à-dire ne prenant pas deux fois la même valeur). Il s’agit de l’identifiant que l’on souligne sur le schéma, par convention. Le numéro de client constitue un identifiant classique pour l’entité clients (figure 4).
Fig. 4 – Identifiants
Remarques:
– une entité possède au moins un attribut (son identifiant); – au contraire, une association peut être dépourvue d’attribut.
1.1.3 Cardinalités
La cardinalité d’un lien entre une entité et une association précise le minimum et le maximum de fois qu’un individu de l’entité peut être concerné par l’association.
Exemple: un client a au moins commandé un article et peut commander n articles (nétant indéterminé), tandis qu’un article peut avoir été commandé entre 0 et n fois (même si ce n’est pas le même n que précédemment). On obtient alors le schéma entités-associations complet(figure 5).
Fig. 5 – Cardinalités
Ceci dit, la discussion autour d’une cardinalité minimale 0 ou 1 n’est vraiment intéressante que lorsque la cardinalité maximale est 1. Nous verrons en effet lors de la traduction vers un schéma relationnel (section 2.3), que lorsque la cardinalité maximale est n, nous ne pouvons pas faire la différence entre une cardinalité minimale de 0 et une cardinalité minimale de 1.
Notons que sur notre exemple, un article peut être commandé par plusieurs clients. Cela provient du fait que tous les crayons rouges ne sont pas numérotés individuellement, mais portent un numéro d’article collectif. En toute rigueur, notre entité articles aurait du s’appeler types d’article. Ainsi, un crayon rouge peut être commandé par plusieurs clients, ce n’est simplement pas le même crayon à chaque fois. Il s’agit d’un choix de modélisation, le lecteur peut très légitimement faire le choix inverse qui consiste à numéroter individuellement chaque crayon rouge.
La seule difficulté pour établir correctement les cardinalités est de se poser les questions dans le bon sens. Autour de l’association commander, par exemple:
– côté clients, la question est (( un client peut commander combien d’articles? )) et la réponse est (( entre 1 et plusieurs ));
– côté articles, la question est (( un article peut être commandé par combien de client? )) et cette fois-ci, la réponse est (( entre 0 et plusieurs )).
1.1.4 Associations plurielles
Deux mêmes entités peuvent être plusieurs fois en association (c’est le cas sur la figure 6).
Fig. 6 – Associations plurielles
1.1.5 Association réflexive
Il est permis à une association d’être branchée plusieurs fois à la même entité, comme par exemple l’association binaire réflexive de la figure 7.
Fig. 7 – Association réflexive
Dans cet exemple, tout employé est dirigé par un autre employé (sauf le directeur général) et un employé peut diriger plusieurs autres employés, ce qui explique les cardinalités sur le schéma.
1.1.6 Associations non binaires
Lorsqu’autour d’une entité, toutes les associations ont pour cardinalités maximales 1 au centre et n à l’extérieur, cette entité est candidate pour être remplacée par une association branchée à toutes les entités voisines avec des cardinalités identiques 0,n.
La deuxième condition qu’il faut impérativement satisfaire est la règle de normalisation des attributs des associations (section suivante). Cette règle conduit parfois à l’apparition d’associations qui établissent un lien sémantique entre 3 entités ou plus.
Sur l’exemple de la figure 8 issu d’un cinéma, l’entité projections est uniquement entourée d’associations dont les cardinalités maximales sont 1 côté projections et n de l’autre côté. De plus, la donnée d’un créneau, d’un film et d’une salle suffit à déterminer une projection unique. On peut donc la remplacer par une association projeter branchée aux trois entités salles, créneaux horaires et films. On parle alors d’association ternaire.
Fig. 8 – Entité rempla¸cable par une association ternaire
La difficulté de concevoir une association ternaire (ou plus) directement est d’établir les bonnes cardinalités. Il est donc conseillé d’en passer par un schéma entités-associations dans lequel on ne trouve que des associations binaires, puis de repérer les entités rempla¸cables par des associations, comme sur la figure 8 à gauche.
Fig. 9 – Contre-exemple: l’entitédéparts n’est pas rempla¸cable par une association ternaire
Par ailleurs, une association peut être branchée à plus de trois entités, comme sur la figure 10. Làencore, le conseil pour être suˆr de la légitimité de cette association 4-aire, est de vérifier les cardinalités sur un schéma intermédiaire faisant apparaˆ?tre à la place, une entité occupations et quatre associations
binaires.
Fig. 10 – Exemple d’entité quaternaire ou 4-aire
1.2 Règles de normalisation
Un bon schéma entités-associations doit répondre à 9 règles de normalisation, que le concepteur doit connaˆ?tre par cœur.
1.2.1 Les bonnes manières dans un schéma entités-associations
Normalisation des entités (importante): toutes les entités qui sont rempla¸cables par une association doivent être remplacées (comme sur la figure 8).
Normalisation des noms: le nom d’une entité, d’une association ou d’un attribut doit être unique.
Conseils:
– pour les entités, utiliser un nom commun au pluriel (par exemple: clients);
– pour les associations, utiliser un verbe à l’infinitif (par exemple: effectuer, concerner) éventuellement à la forme passive (être commandé) et accompagné d’un adverbe (avoir lieu dans, pendant, à);
– pour les attributs, utiliser un nom commun singulier (par exemple: nom, numéro, libellé, description) éventuellement accompagné du nom de l’entité ou de l’association dans laquelle il se trouve (par exemple: nom de client, numéro d’article).
Remarque: lorsqu’il reste plusieurs fois le même nom, c’est parfois symptomatique d’une modélisation qui n’est pas terminée (figure 11(a)) ou le signe d’une redondance (figure 11(b)).
|
(a) Deux entités homogènes peuvent être fusionnées
(b) Si deux attributs contiennent les mêmes informations, alorsla redondance induit non seulement un gaspillage d’espace mais également un grand risque d’incohérence: ici, les adresses risquent de ne pas être les mêmes et dans ces conditions, ou` faut-il livrer ?
Fig. 11 – Contre-exemples de la normalisation des noms
Normalisation des identifiants: chaque entité doit posséder un identifiant.
Conseils:
– éviter les identifiants composés de plusieurs attributs (comme par exemple un identifiant formé par les attributs nom et prénom), car d’une part c’est mauvais pour les performances et d’autres part, l’unicité supposée par une telle démarche finit tôt ou tard par être démentie;
– préférer un identifiant court pour rendre la recherche la plus rapide possible (éviter notamment les chaˆ?nes de caractères comme un numéro de plaque d’immatriculation, un numéro de sécurité sociale ou un code postal);
– éviter également les identifiants susceptibles de changer au cours du temps (comme les plaques d’immatriculation ou les numéros de sécurité sociale provisoires).
Conclusion: l’identifiant sur un schéma entités-associations (et donc la future clé primaire dans le schéma relationnel) doit être un entier, de préférence incrémenté automatiquement.
Normalisation des attributs (importante): remplacer les attributs en plusieurs exemplaires en une association supplémentaire de cardinalités maximales n et ne pas ajouter d’attribut calculable à partir
d’autres attributs.
En effet, d’une part, les attributs en plusieurs exemplaires posent des problèmes d’évolutivité du modèle (sur la figure 12(a) à gauche, comment faire si un employé a deux adresses secondaires?) et
|
(a) Attributs en plusieurs exemplaires remplacés par une association supplémentaire
(b) Attribut calculable qu’il faut retirer du schéma
Fig. 12 – Contre-exemples de la normalisation des attributs
d’autre part, les attributs calculables induisent un risque d’incohérence entre les valeurs des attributs de base et celles des attributs calculés, comme sur la figure 12(b).
D’autres d’attributs calculables classiques sont à éviter, comme l’âge (qui est calculable à partir de la date de naissance) ou encore le département (calculable à partir d’une sous-chaˆ?ne du code postal).
Normalisation des attributs des associations (importante): les attributs d’une association doivent dépendre directement des identifiants de toutes les entités en association.
Par exemple, sur la figure 5 la quantité commandée dépend à la fois du numéro de client et du numéro d’article, par contre la date de commande non. Il faut donc faire une entité commandes à part, idem pour les livraisons (figure 13).
Fig. 13 – Normalisation des attributs des associations
L’inconvénient de cette règle de normalisation est qu’elle est difficile à appliquer pour les associations qui ne possèdent pas d’attribut. Pour vérifier malgré tout qu’une association sans attribut est bien normalisée, on peut donner temporairement à cette association un attribut imaginaire (mais pertinent) qui permet de vérifier la règle.
Par exemple, entre les entités livres et auteurs de la figure 16, l’association écrire ne possède pas d’attribut. Imaginons que nous ajoutions un attribut pourcentage qui contient le pourcentage du livre écrit par chaque auteur (du même livre). Comme cet attribut pourcentage dépend à la fois du numéro de livre et du numéro d’auteur, l’association écrire est bien normalisée.
Fig. 14 – Cardinalité 1,1 et attributs d’une association
Normalisation des associations (importante): il fautéliminer les associations fantômes (figure 15(a)), redondantes (figure 15(b)) ou en plusieurs exemplaires (figure 15(c)).
|
(a) les cardinalités sont toutes 1,1 donc c’est une association fantôme
(b) si un client ne peut pas régler la facture d’un autre client, alors l’associationpayer est inutile et doit être supprimée (dans le cas contraire, l’association payer doit être maintenue)
(c) une association suffit pour remplacer les 4 associations participer en tant que
Fig. 15 – Contre-exemples de la normalisation des associations
En ce qui concerne les associations redondantes, cela signifie que s’il existe deux chemins pour se rendre d’une entité à une autre, alors ils doivent avoir deux significations ou deux durées de vie différentes. Sinon, il faut supprimer le chemin le plus court, car il est déductible à partir de l’autre chemin. Dans notre exemple de la figure 15(b), si on supprime l’association payer, on peut retrouver le client qui a payé le règlement en passant par la facture qui correspond.
Remarque: une autre solution pour le problème de la figure 15(b) consiste à retirer l’entité règlements et d’ajouter une association régler avec les mêmes attributs (sauf l’identifiant) entre les entités clients et factures.
Normalisation des cardinalités: une cardinalité minimale est toujours 0 ou 1 (et pas 2, 3 ou n) et une cardinalité maximale est toujours 1 ou n (et pas 2, 3, ).
Cela signifie également qu’on ne modélise pas les cardinalités minimales qui valent plus de 1 car ce genre de valeur est aussi amenée à évoluer. Par ailleurs, avec une cardinalité maximale de 0, l’association n’aurait aucune signification.
Dans un SGBD relationnel, nous pourrions assurer les cardinalités valant 2, 3 ou plus, via l’utilisation de déclencheurs. Mais cette notion n’est pas abordée dans ce document qui se contente, au contraire, de décrire ce qu’il est possible de faire sans utiliser de déclencheur.
1.2.2 Les formes normales
A ces 6 règles de normalisation, il convient d’ajouter les 3 premières formes normales traditionnel-` lement énoncées pour les schémas relationnels, mais qui trouvent tout aussi bien leur place en ce qui concerne les schémas entités-associations.
Première forme normale: à un instant donné dans une entité, pour un individu, un attribut ne peut prendre qu’une valeur et non pas, un ensemble ou une liste de valeurs.
Si un attribut prend plusieurs valeurs, alors ces valeurs doivent faire l’objet d’une entité supplémentaire, en association avec la première (figure 16).
Fig. 16 – Application de la première forme normale: il peut y avoir plusieurs auteurs pour un livre donnéDeuxième forme normale: l’identifiant peut être composé de plusieurs attributs mais les autres attributs de l’entité doivent dépendre de l’identifiant en entier (et non pas une partie de cet identifiant).
Cette deuxième forme normales peut être oubliée si on suit le conseil de n’utiliser que des identifiants non composés et de type entier. En vérité, elle a été vidée de sa substance par la règle de normalisation des attributs des associations (page 12).
Troisième forme normale de Boyce-Codd (importante): tous les attributs d’une entité doivent dépendre directement de son identifiant et d’aucun autre attribut.
Si ce n’est pas le cas, il faut placer l’attribut pathologique dans une entité séparée, mais en association avec la première.
| numéro avion | constructeur | modèle | capacité | propriétaire |
| 1 | Airbus | A380 | 180 | Air France |
| 2 | Boeing | B747 | 314 | British Airways |
| 3 | Airbus | A380 | 180 | KLM |
Tab. 1 – Il y a redondance (et donc risque d’incohérence) dans les colonnesconstructeur etcapacité
Par exemple, l’entité avions (figure 17 à gauche) dont les valeurs sont données dans le tableau 1, n’est pas en troisième forme normale de Boyce-Codd, car la capacité et le constructeur d’un avion ne dépendent pas du numéro d’avion mais de son modèle. La solution normalisée est donnée figure 17 à droite.
|
Fig. 17 – Application de la troisième forme normale de Boyce-Codd
1.3 Dépendances fonctionnelles
Pour établir efficacement un modèle entités-associations bien normalisé, on peut étudier au préalable les dépendances fonctionnelles entre les attributs puis, les organiser en graphe de couverture minimale. Cette technique est traditionnellement employée pour normaliser des schémas relationnels, mais elle s’applique très bien en amont, au niveau des modèles conceptuels.
1.3.1 Définitions et propriétés
Un attribut Ydépend fonctionnellement d’un attribut X si et seulement si une valeur de X induit une unique valeur de Y . On note une dépendance fonctionnelle par une flèche simple: X?Y .
Transitivité: si X?Y et Y ?Z alors X?Z.
Par exemple, on a numéro de commande?numéro de client?nom de client, donc on a aussi numéro de commande?nom de client. Mais la dépendance fonctionnelle numéro de commande?nom de client est dite transitive, car il faut passer par le numéro de client pour l’obtenir.
Au contraire, la dépendance fonctionnelle numéro de client?nom de client est directe . Seules les dépendances fonctionnelles directes nous intéressent. D’autres exemples sont données dans le tableau 2.
| dépendance fonctionnelle | directe ? |
| numéro de livraison ? date de livraison | oui |
| numéro de livraison ? numéro du fournisseur | oui |
| numéro du fournisseur ? nom du fournisseur | oui |
| numéro de livraison ? nom du fournisseur | non |
Tab. 2 – Exemples de dépendances fonctionnelles
Un attribut Y peut avoir une dépendance fonctionnelle qui repose sur la conjonction de plusieurs attributs, auquel cas la dépendance est dite non élémentaire . Les dépendances fonctionnelles non élémentaires sont notées par une flèche unique mais comportant plusieurs points d’entrée (regroupés autour d’un
cercle).
Par exemple, la quantité commandée (d’un article dans une commande) dépend de deux attributs: le numéro de commande et le numéro d’article (figure 18). Notons que cette dépendance numéro de commande + numéro d’article?quantité est à la fois non élémentaire et directe.
numéro de commande quantité commandée
numéro d’article
Fig. 18 – Dépendance fonctionnelle non élémentaire, mais directe
1.3.2 Graphe de couverture minimale
En représentant tous les attributs et toutes les dépendances fonctionnelles directes entre eux, nous obtenons un réseau appelé graphe de couverture minimale. Dans notre exemple sur les clients, les commandes et les articles, ce graphe est donné sur la figure 19.
Fig. 19 – Graphe de couverture minimale
été oublié sur le graphe de couverture minimal et notamment, aucun identifiant. D’ailleurs toutes les dépendances fonctionnelles du graphe doivent partir d’un identifiant. Si ce n’est pas le cas, c’est qu’un identifiant a été omis.
1.3.3 Traduction vers un schéma entités-associations
A partir du graphe de couverture minimale (figure` 19), le schéma entités-associations normalisé correspondant apparaˆ?t naturellement (figure 20), en suivant quelques étapes simples.
Fig. 20 – Identification des entités et des associations sur un graphe de couverture minimaleEtape 1´ : il faut repérer et souligner les identifiants.
Etape 2´ : puis tous les attributs non identifiant qui dépendent directement d’un identifiant et d’un seul, forment une entité (avec l’identifiant, bien suˆr).
Etape 3´ : ensuite, les dépendances élémentaires entre les identifiants forment des associations binaires dont les cardinalités maximales sont 1 au départ de la dépendance fonctionnelle et n à l’arrivée.
Etape 4´ : sauf si entre deux identifiants se trouvent deux dépendances élémentaires réflexives, auquel cas l’association binaire a deux cardinalités maximales valant 1.
Etape 5´ : enfin, les attributs (non identifiants) qui dépendent de plusieurs identifiants sont les attributs d’une association supplémentaire dont les cardinalités maximales sont toutes n.
La traduction du graphe de couverture minimale de la figure 20 en un schéma entités-associations normalisé est donnée sur la figure 21.
Fig. 21 – Schéma entités-associations normalisé obtenu à partir du graphe de couverture minimale
Dans ce genre de traduction, il faut donner un nom aux entités et aux associations, car ce n’est pas le cas sur le graphe de couverture minimale et il reste les cardinalités minimales à établir.
1.3.4 Gestion des dates et du caractère historique
Dans une bibliothèque, on peut vouloir stocker les emprunts en cours (figure 22) et/ou les emprunts historiques (figure 23). Pour les emprunts en cours, la date de retour prévu est un attribut de l’entité
Fig. 22 – Sans historisation des emprunts, pas de problème
livres car un livre ne peut faire l’objet que d’un seul emprunt en cours. Dans ce cas, l’établissement du graphe de couverture minimal ne pose aucun problème.
Par contre, un livre peut faire l’objet de plusieurs emprunts historiques et dans ces conditions, la date d’emprunt est déterminante pour connaˆ?tre la date de retour prévue (figure 23 en haut à gauche). Or une date n’est pas un identifiantet une dépendance fonctionnelle ne peut partir que d’un ou plusieurs identifiant(s). C’est le signe qu’il manque un identifiant: le numéro d’emprunt (figure 23 en haut à droite).
adresse
Fig. 23 – Même pour une entité historisée, il vaut mieux éviter que la date n’entre dans l’identifiant
Notons que l’entité emprunts historiques supplémentaire qui apparaˆ?t après traduction (figure 23 en bas) ne peut pas être transformée en une association comme on pourrait le croire au simple examen des cardinalités qui l’entourent. En effet, les attributs de l’association qui en résulterait ne vérifieraient pas la normalisation des attributs des associations. Notamment, la date de retour effectif ne dépend pas du numéro de livre et du numéro de membre, mais du numéro de livre et de la date d’emprunt.
La normalisation des entités ne s’applique donc pas aux entités qui ont un caractère historique. A` moins que les dates ne soient regroupées dans une entité séparée, ce qui n’est pas conseillé tant qu’aucune information liée aux dates (comme le caractère férié, par exemple) n’est nécessaire.
1.3.5 Dépendances plurielles et réflexives
numéro de remplacement
nom adresse date de date de professionnelle début fin
(a) dépendances plurielles (b) dépendances réflexives
Fig. 24 – Dépendances fonctionnelles commentées
Les dépendances fonctionnelles plurielles entre les médecins et le remplacements (figure 24(a)) deviennent, après traduction, des associations plurielles entre les entités médecins et remplacements. Notons que l’entité remplacements ainsi générée, a aussi un caractère historique.
Les fonctionnelles réflexives (X?X), quoique toujours vraies, ne présentent aucun intérêt, à moins qu’elles aient une signification particulière. Un exemple de dépendance réflexive licite sur un graphe de couverture minimale est la dépendance fonctionnelle personne ? personne, lorsqu’elle signifie (( diriger )), (( être en couple avec )) ou (( être le père ou la mère de )) (figure 24(b)).
Dans le même ordre d’idée, il est inutile de faire figurer sur le graphe de couverture minimal des dépendances fonctionnelles nonélémentaires vraies, mais idiotes, comme par exemple numéro de commande + numéro d’article?numéro de commande.
1.3.6 Associations sans attributs
Pour illustrer ce défaut, prenons l’exemple des films et des acteurs (figure 25). Il n’y a pas d’attribut
Fig. 25 – Utilisation d’une dépendance non élémentaire et sans enfant sur un graphe de couverture minimal
qui dépende à la fois du numéro de film et du numéro d’acteur (à moins d’imaginer le temps d’apparition à l’écran). Et pourtant, les deux entités films et acteurs sont en association. Grâce à la dépendance non élémentaire et sans enfant, on peut rendre compte de cette situation sur le graphe de couverture minimale et faire ainsi apparaˆ?tre l’association sur le schéma entités-associations qui en est traduit.
1.4 Méthodologie de base
Face à une situation bien définie (soit à travers un énoncé précis, soit à travers une collection de formulaires ou d’états que le nouveau système d’information est censé remplacer), nous pouvons procéder sans établir le graphe de couverture minimale:
– identifier les entités en présence;
– lister leurs attributs;
– ajouter les identifiants (numéro arbitraire et auto-incrémenté);
– établir les associations binaires entre les entités;
– lister leurs attributs;
– calculer les cardinalités;
– vérifier les règles de normalisation et en particulier, la normalisation des entités (c’est à ce stade qu’apparaissent les associations non binaires), des associations et de leurs attributs ainsi que la troisième forme normale de Boyce-Codd; – effectuer les corrections nécessaires.
Mais, il est parfois plus intuitif d’en passer par l’étude des dépendances fonctionnelles directes:
– identifier les entités en présence et leur donner un identifiant (numéro arbitraire et auto-incrémenté);
– ajouter l’ensemble des attributs et leur dépendances fonctionnelles directes avec les identifiants (en commen¸cant par les dépendances élémentaires);
– ajuster les cardinalités minimales et ;
– à ce stade, la majorité des règles de normalisation devraient être vérifiées, il reste tout de même la normalisation des noms, la présence d’attributs en plusieurs exemplaires et d’associations redondantes ou en plusieurs exemplaires, à corriger.
Il faut garder également à l’esprit que le modèle doit être exhaustif (c’est-à-dire contenir toutes les informations nécessaires) et éviter toute redondance qui, on ne le dira jamais assez, constitue une perte d’espace, une démultiplication du travail de maintenance et un risque d’incohérence.
Il faut par ailleurs veiller à éliminer les synonymes (plusieurs signifiants pour un signifié, exemple: nom, patronyme, appellation) et les polysèmes (plusieurs signifiés pour un signifiant, exemples: qualité, statut).
Il va de soi que cette méthodologie ne doit pas être suivie pas-à-pas une bonne fois pour toute. Au contraire, il faut itérer plusieurs fois les étapes successives, pour espérer converger vers une modélisation pertinente de la situation.
2 Modèle logique de données (MLD)
Maintenant que le MCD est établi, on peut le traduire en différents systèmes logiques et notamment les bases de données relationnelles qui proposent une vision plus concrète pour modéliser la situation.
2.1 Systèmes logiques
Avant l’apparition des systèmes de gestion de base de données (SGBD ou DBMS pour Data Base Management System), les données étaient stockées dans des fichiers binaires et gérées par des programmes exécutables (développés en Basic, Cobol ou Dbase, par exemple). [Gabay] propose à ce sujet une traduction d’un MPD vers un MLD fichier. Mais la maintenance des programmes (en cas de modification de la structure des données, notamment) était très problématique.
Aujourd’hui, ils sont largement remplacés par les SGBD relationnels (SGBDR) avec lesquels l’information peut être obtenue par une requête formulée dans un langage quasiment naturel (la langage SQL pour Structured Query Langage). Parmi les SGBDR les plus répandus nous trouvons Oracle, SQL Server et DB2. Nous nous contentons ici d’exposer le modèle logique de données relationnel (MLDR).
Plus récemment, sont apparus le modèle logique orienté objet et même des SGBD orientés objets. Pourtant, les SGBD relationnels restent extrêmement majoritaires, tandis que l’approche orienté objet est parfaitement adaptée au développement d’applications clientes dynamiques et liées aux données du
système d’information.
2.2 Modèle logique relationnel
Concentrons-nous désormais sur le MLDR.
2.2.1 Tables, lignes et colonnes
Lorsque des données ont la même structure (comme par exemple, les renseignements relatifs aux clients), on peut les organiser en table dans laquelle les colonnes décrivent les champs en commun et les lignes contiennent les valeurs de ces champs pour chaque enregistrement (tableau 3).
| numéro client | nom | prénom | adresse |
| 1 | Dupont | Michel | 127, rue |
| 2 | Durand | Jean | 314, boulevard |
| 3 | Dubois | Claire | 51, avenue |
| 4 | Dupuis | Marie | 2, impasse |
Tab. 3 – Contenu de la tableclients, avec en première ligne les intitulés des colonnes
2.2.2 Clés primaires et clés étrangères
Les lignes d’une table doivent être uniques, cela signifie qu’une colonne (au moins) doit servir à les identifier. Il s’agit de la clé primaire de la table.
De plus, la valeur de la clé primaire d’une ligne ne devrait pas, en principe, changer au cours du temps.
Par ailleurs, il se peut qu’une colonne Colonne1 d’une table ne doive contenir que des valeurs prises par la colonne Colonne2 d’une autre table (par exemple, le numéro du client sur une commande doit correspondre à un vrai numéro de client). La Colonne2 doit être sans doublons (bien souvent il s’agit d’une clé primaire). On dit alors que la Colonne1 est clé étrangère et qu’elle référence la Colonne2.
Par convention, on souligne les clés primaires et on fait précéder les clés étrangères d’un dièse # dans la description des colonnes d’une table:
clients(numéro client, nom client, prénom, adresse client) commandes(numéro commande, date de commande, #numéro client (non vide))
Remarques:
– une même table peut avoir plusieurs clés étrangères mais une seule clé primaire (éventuellement composées de plusieurs colonnes);
– une colonne clé étrangère peut aussi être primaire (dans la même table);
– une clé étrangère peut être composée (c’est le cas si la clé primaire référencée est composée);
– implicitement, chaque colonne qui compose une clé primaire ne peut pas recevoir la valeur vide (NULL interdit);
– par contre, si une colonne clé étrangère ne doit pas recevoir la valeur vide, alors il faut le préciser dans la description des colonnes.
Les SGBDR vérifient au coup par coup que chaque clé étrangère ne prend pas de valeurs en dehors de celles déjà prises par la ou les colonne(s) qu’elle référence. Ce mécanisme qui agit lors de l’insertion, de la suppression ou de la mise à jour de lignes dans les tables, garantit ce que l’on appelle l’intégrité référentielle des données.
2.2.3 Schéma relationnel
| clients | commandes | |
| - numéro client - nom client - prénom - adresse client | - n° commande - date commande - #numéro client (non vide) |
Fig. 26 – Schéma relationnel simple entre deux tables
Certains éditeur inscrivent sur le connecteur un symbole 1 côté clé primaire et un symbole ? côté clé étrangère (à condition que celle-ci ne soit pas déjà clé primaire). Il faut prendre garde avec cette convention, car le symbole ? se trouve du côté opposé à la cardinalité maximale n correspondante.
2.3 Traduction d’un MCD en un MLDR
Pour traduire un MCD en un MLDR, il suffit d’appliquer cinq règles.
Notations: on dit qu’une association binaire (entre deux entités ou réflexive) est de type:
– 1: 1 (un à un) si aucune des deux cardinalités maximales n’est n; – 1: n (un à plusieurs) si une des deux cardinalités maximales est n;
– n: m (plusieurs à plusieurs) si les deux cardinalités maximales sont n.
En fait, un schéma relationnel ne peut faire la différence entre 0,n et 1,n. Par contre, il peut la faire entre 0,1 et 1,1 (règles 2 et 4).
Règle 1: toute entité devient une table dans laquelle les attributs deviennent les colonnes. L’identifiant de l’entité constitue alors la clé primaire de la table.
Par exemple, l’entité articles de la figure 13 devient la table:
articles(numéro article, désignation, prix unitaire de vente)
Règle 2: une association binaire de type 1: n disparaˆ?t, au profit d’une clé étrangère dans la table côté 0,1 ou 1,1 qui référence la clé primaire de l’autre table. Cette clé étrangère ne peut pas recevoir la valeur vide si la cardinalité est 1,1.
Par exemple, l’association livrer de la figure 13 est traduite par:
fournisseurs(n?fournisseur, nom contact, n? téléphone contact)
livraisons(n?livraison, date de livraison, nom livreur, #n? fournisseur (non vide))
- n° livraison
- date de livraison
- nom livreur
Fig. 27 – Traduction d’une association de type 1: n
Il ne devrait pas y avoir d’attribut dans une association de type 1: n, mais s’il en reste, alors ils glissent vers la table côté 1.
Règle 3: une association binaire de type n: m devient une table supplémentaire (parfois appelée table de jonction, table de jointure ou table d’association) dont la clé primaire est composée de deux clés étrangères (qui référencent les deux clés primaires des deux tables en association). Les attributs de l’association deviennent des colonnes de cette nouvelle table.
Par exemple, l’association concerner (1) de la figure 13 est traduite par la table supplémentaire lignes de commande: lignes de commande(#n?commande, #n?article, quantité commandée)
Fig. 28 – Traduction d’une association de type n: m
Règle 4: une association binaire de type 1: 1 est traduite comme une association binaire de type 1: n sauf que la clé étrangère se voit imposer une contrainte d’unicité en plus d’une éventuelle contrainte de non vacuité (cette contrainte d’unicité impose à la colonne correspondante de ne prendre que des valeurs distinctes).
Si les associations fantômes ont été éliminées, il devrait y avoir au moins un côté de cardinalité 0,1.
C’est alors dans la table du côté opposé que doit aller la clé étrangère. Si les deux côtés sont de cardinalité 0,1 alors la clé étrangère peut être placée indifféremment dans l’une des deux tables.
Par exemple, l’association diriger de la figure 29 est traduite par:
services(n?service, nom service, #numéro employé (non vide, unique)) employés(numéro employés, nom)
| |||||||||||||||||||||||||||||||||
