Conception d'une base de données

Une entité est une population d'individus homogène. 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.).

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.

Remarquons que dans ce schéma, les entités clients et fournisseurs ne sont pas liées directement, mais indirectement, via l'entité articles, ce qui est assez naturel.

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.

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ée (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).

Remarques :

• une entité possède au moins un attribut (son identifiant) ;
• au contraire, une association peut être dépourvue d'attribut.

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

Une cardinalité minimale de 1 doit se justifier par le fait que les individus de l'entité en question ont besoin de l'association pour exister (un client n'existe pas avant d'avoir commandé quoi que ce soit, donc la cardinalité minimale de l'entité clients dans l'association commander est 1). Dans tous les autres cas, la cardinalité minimale vaut 0 (c'est le cas pour une liste préétablie d'articles, par exemple).

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 « Traduction d'un MCD en un MLDR »), 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 clients ? » et cette fois-ci, la réponse est « entre 0 et plusieurs ».

Deux mêmes entités peuvent être plusieurs fois en association (c'est le cas sur la figure 6).

Dans cet exemple issu d'une agence immobilière, une personne peut être propriétaire, résider principalement ou résider secondairement dans un logement géré par l'agence. Les logements qui ne sont pas gérés par l'agence ne figurent pas dans l'entité des logements, ce qui explique certaines cardinalités 0 du schéma. Nous supposons également qu'un logement n'est détenu que par une seule personne et que ce propriétaire figure obligatoirement dans l'entité des personnes.

Il est permis à une association d'être branchée plusieurs fois à la même entité, comme l'association binaire réflexive de la figure 7.

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.

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

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çables par des associations, comme sur la figure 8 à gauche.

Cette règle de conduite permet d'éviter d'introduire une association ternaire abusive, par exemple entre les avions, les pilotes et les vols (figure 9), car le concepteur peut s'apercevoir que l'une des cardinalités maximales ne convient pas.

Par ailleurs, une association peut être branchée à plus de trois entités, comme sur la figure 10. Là encore, le conseil pour être sû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.

Normalisation des entités (importante) : toutes les entités qui sont remplaçables 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, alors la 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, où faut-il livrer ?

Normalisation des identifiants : chaque entité doit posséder un identifiant.

Conseils :

• éviter les identifiants composés de plusieurs attributs (comme un identifiant formé par les attributs nom et prénom), car d'une part c'est mauvais pour les performances et d'autre 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 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).

(a) Attributs en plusieurs exemplaires remplacés par une association supplémentaire.

(b) Attribut calculable qu'il faut retirer du schéma.

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

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.

Autre conséquence de la normalisation des attributs des associations : une entité avec une cardinalité de 1,1 ou 0,1 aspire les attributs de l'association (figure 14).

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

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 que si une cardinalité maximale est connue et vaut 2, 3 ou plus (comme sur la figure 15(c) à droite, ou pour un nombre limité d'emprunts dans une bibliothèque), alors nous considérons quand même qu'elle est indéterminée et vaut n. Cela se justifie par le fait que même si nous connaissons n au moment de la conception, il se peut que cette valeur évolue au cours du temps. Il vaut donc mieux considérer n comme une inconnue dès le départ.

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é à évoluer. Par ailleurs, avec une cardinalité maximale, 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.

À ces six règles de normalisation, il convient d'ajouter les trois premières formes normales traditionnellement é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).

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 normale 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 (paragraphe « Normalisation des attributs des associations »).

Considérons malgré tout le contre-exemple suivant : dans une entité clients dont l'identifiant est composé des attributs nom et prénom, la date de fête d'un client ne dépend pas de son identifiant en entier, mais seulement de prénom. Elle ne doit pas figurer dans l'entité clients, il faut donc faire une entité calendrier à part, en association avec l'entité clients.

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.

Tableau 17 - Il y a redondance (et donc risque d'incohérence) dans les colonnes constructeur et capacité

Par exemple, l'entité avions (figure 18 à gauche) dont les valeurs sont données dans le tableau 17, 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 améliorée est donnée figure 18 à droite.

En toute rigueur, la colonne constructeur ne doit pas être maintenue dans l'entité modèles d'avion, mais doit être placée dans une entité séparée constructeurs (en association avec modèles d'avion), afin d'éviter la redondance du nom d'un constructeur pour tous ses modèles.

Un attribut Y dé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.

Par exemple, si X est le numéro de client et Y le nom de client, alors on a bien X ? Y. Par contre, on a pas Y ? X, car plusieurs clients de numéros différents peuvent porter le même nom.

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és dans le tableau 19.

Tableau 19 - 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 20). Notons que cette dépendance numéro de commande + numéro d'article ? quantité est à la fois non élémentaire et directe.

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

La technique de traduction en un schéma entités-associations qui suit, suppose qu'aucun attribut n'a é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.

À partir du graphe de couverture minimale (figure 21), le schéma entités-associations normalisé correspondant apparaît naturellement (figure 22), en suivant quelques étapes simples.

Étape 1 : il faut repérer et souligner les identifiants.

Étape 2 : puis tous les attributs non identifiants qui dépendent directement d'un identifiant et d'un seul, forment une entité (avec l'identifiant, bien sûr).

Étape 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.

Étape 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.

Étape 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 22 en un schéma entités-associations normalisé est donnée sur la figure 23.

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.

Remarquons également qu'en réalité, il faut déjà connaître les entités en présence pour établir correctement le graphe de couverture minimale, ne serait-ce que pour y faire figurer leurs identifiants. Donc finalement, cette technique n'est qu'une aide pour établir les associations entre les entités et pour normaliser les entités et leurs associations (jusqu'en troisième forme normale de Boyce-Codd).

Dans une bibliothèque, on peut vouloir stocker les emprunts en cours (figure 24) et/ou les emprunts historiques (figure 25).

Pour les emprunts en cours, la date de retour prévue est un attribut de l'entité 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 25 en haut à gauche). Or une date n'est pas un identifiant et 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 25 en haut à droite).

Notons que l'entité emprunts historiques supplémentaire qui apparaît après traduction (figure 25 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. À 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.

Une ou plusieurs dépendances fonctionnelles peuvent partir ou arriver plusieurs fois du même attribut. Pour clarifier la signification de chaque dépendance fonctionnelle, on peut ajouter un commentaire sur la flèche (figure 26). Ce commentaire sert ensuite à donner un nom aux associations correspondantes.

Les dépendances fonctionnelles plurielles entre les médecins et les remplacements (figure 26(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 26(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 numéro de commande + numéro d'article ? numéro de commande.

La lacune majeure de cette méthode reste tout de même le fait que les associations dont toutes les cardinalités maximales sont n, mais qui sont sans attribut, ne figurent pas sur le graphe de couverture minimale. Il faut alors soit leur inventer temporairement un attribut (comme pour la normalisation des attributs des associations), soit introduire une notation spéciale (par exemple, une dépendance non élémentaire qui ne débouche sur aucun attribut).

Pour illustrer ce défaut, prenons l'exemple des films et des acteurs (figure 27).

Il n'y a pas d'attribut 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.

Lorsque des données ont la même structure (comme , 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 28).

Tableau 28 - Contenu de la table clients, avec en première ligne les intitulés des colonnes

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.

L'absence de valeur dans une clé primaire ne doit pas être autorisée. Autrement dit, la valeur vide (NULL) est interdite dans une colonne qui sert de clé primaire, ce qui n'est pas forcément le cas des autres colonnes, dont certaines peuvent ne pas être renseignées à toutes les lignes.

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). 2 doit être sans doublons (bien souvent il s'agit d'une clé primaire). On dit alors que 1 est clé étrangère et qu'elle référence 2.

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 client, 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.

On peut représenter les tables d'une base de données relationnelle par un schéma relationnel dans lequel les tables sont appelées relations et le lien entre les clés étrangères et leur clé primaire est symbolisé par un connecteur (figure 29).

Certains éditeurs 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.

Considérons l'exemple de la figure 37 issu du monde des courses hippiques. La dépendance fonctionnelle n° cheval + n° course ? n° jockey est la première dépendance fonctionnelle non élémentaire vers un identifiant que nous rencontrons. Ce type de dépendance fonctionnelle nous incite à créer une association binaire de type 1 : n entre l'entité jockeys et l'association binaire de type n : m qu'il y a entre les entités chevaux et courses. D'un point de vue sémantique, la logique est respectée puisqu'un jockey ne monte pas un cheval, mais un « cheval-qui-participe-à-une-course ».

Pour tenir compte de ce nouveau cas de dépendance fonctionnelle, il convient d'ajouter une sixième étape à la technique de traduction d'un graphe de couverture minimal en un schéma entités-associations, telle qu'elle est commencée section « Traduction vers un schéma entités-associations » :

Étape 6 : lorsqu'un identifiant dépend de plusieurs autres identifiants, son entité est en association de type 1 : n avec l'association qui lie les autres identifiants.

Certains auteurs considèrent que l'agrégation des entités chevaux, courses et de l'association participer constitue une nouvelle entité participations qui englobe ces trois éléments graphiques. Dans ce cas, l'association monter fait le lien entre les deux entités (participations et jockeys). Le résultat final sur le schéma relationnel est le même. Malheureusement, cette notation n'est pas très pratique, car le schéma entités-associations devient vite illisible lorsqu'une entité participe à plusieurs agrégations.

Nous préférons donc autoriser, dans ce document, qu'une association puisse être liée à une association binaire de type n : m ou à une association ternaire (ou plus). Cependant pour ne pas confondre les liens entre associations et entités avec les liens entre associations, nous encadrons soigneusement les associations qui interviennent dans une agrégation, comme sur la figure 37 en bas à gauche.

En tout cas, une association ne peut pas être liée à une association binaire de type 1 : n ou 1 : 1. Dans ce cas, l'association doit être directement liée à l'entité qui se trouve du côté où la cardinalité maximale est 1.

Sur le schéma relationnel final (figure 37 en bas à droite), la table de jonction participations reçoit une clé étrangère supplémentaire, mais qui contrairement aux autres, ne participe pas à la clé primaire.

À présent, ajoutons les parieurs à notre exemple de la figure 37. Étant donné que nous avons la dépendance fonctionnelle n° cheval + n° course + n° parieur ? montant de la mise (figure 38 en haut), nous pourrions avoir une association ternaire entre les entités chevaux, courses et parieurs. Mais dans ce cas, un parieur peut miser sur un cheval dans une course, alors que ce cheval ne participe pas à cette course.

Pour pallier cette lacune, on pourrait faire appel à des déclencheurs programmés dans la base de données finale. Les déclencheurs sont des procédures SQL qui, dans notre exemple, permettraient à chaque insertion ou mise à jour de lignes dans la table des paris, d'assurer qu'un pari ne puisse pas concerner un cheval dans une course à laquelle il ne participe pas. Cependant, il existe une solution plus simple qui repose uniquement sur l'intégrité référentielle.

En réalité (figure 38 en bas), la vraie dépendance fonctionnelle directe est n° cheval + n° course) + n° parieur ? montant, ce qui garantit qu'un parieur ne peut miser que sur un « cheval-qui-participe-à-une-course ».

Le fait qu'une association ternaire (ou plus) disparait au profit d'une ou plusieurs agrégations est très fréquent lorsque l'on modélise une situation complète. À tel point qu'on peut partir du principe qu'un schéma entités-associations sans agrégation est généralement faux.

Dans notre exemple, la traduction de la nouvelle dépendance fonctionnelle en une association de type n : m (figure 39 en haut) se fait en appliquant, comme d'habitude, l'étape 4 de la section « Traduction vers un schéma entités-associations ».

Sur le schéma relationnel obtenu (figure 39 en bas), la traduction de l'association binaire de type n : m liée à une autre association binaire de type n : m, fait apparaître dans la table paris une clé étrangère composite qui référence la clé primaire composite de la table participations.

Rappelons qu'il est déconseillé d'utiliser des identifiants composites. Mais la clé primaire composite de la table participations est légitime puisqu'elle est issue d'une association binaire de type n : m. En conséquence de quoi, la clé étrangère composite de la table paris est également légitime puisqu'elle est aussi issue d'une association binaire de type n : m.

On peut ainsi imaginer avoir sur un schéma relationnel des clés primaires ou étrangères composées d'un nombre arbitraire de colonnes, sans pour autant qu'il n'y ait un seul identifiant composite sur le schéma entités-associations correspondant.

Certains attributs ne peuvent prendre qu'un jeu volontairement limité de valeurs. C'est le cas sur la figure 40 à gauche, pour les attributs enseignant et matière. Cela évite sur cet exemple qu'une même matière ne soit décrite de deux manières différentes et qu'un même nom d'enseignant ne soit orthographié deux fois.

Il est recommandé de regrouper ces valeurs au sein d'une entité dite de codification (qui donnera ensuite une table de codification). Si l'attribut concerné appartient à une entité, alors cette entité est en association binaire de type 1 : n avec cette entité de codification. Par contre, si l'attribut fait partie d'une association, il faut recourir à l'agrégation afin de mettre en association l'entité de codification avec l'association de cet attribut (figure 40 à droite).

Ainsi, l'agrégation évite notamment aux entités de codification de transformer une association binaire en une association ternaire (ou plus).

Prenons par exemple, le schéma relationnel en haut de la figure 41, tiré d'une base de données pour un centre de golf. Dans la table trous, la clé primaire n° trou est en incrément automatique, tandis que la colonne n° trou dans parcours est un nombre (généralement compris entre 1 et 18) qui correspond à la numérotation des trous dans le parcours.

Le problème de ce schéma relationnel est qu'en l'état, il peut y avoir un score dans la table scores pour un trou qui n'appartient pas au parcours sur lequel la partie se joue (le lecteur est invité à bien observer la figure pour s'en apercevoir).

Pour régler ce problème, on peut à nouveau se reposer sur l'emploi de déclencheurs. Mais là encore, il existe une solution ne faisant appel qu'à l'intégrité référentielle.

Cette solution consiste à faire entrer le numéro de parcours dans la numérotation des trous (remplaçant ainsi le n° trou) ainsi que dans la numérotation des parties (en conservant cette fois-ci le n° partie en incrément automatique). Les tables trous et parties possèdent alors une clé primaire composite et partiellement étrangère (figure 41 en bas).

Les clés étrangères des tables participations et scores qui référencent ces nouvelles clés primaires sont alors complétées par une nouvelle colonne (le numéro de parcours). Dans la table des scores, comme cette colonne n° parcours n'est introduite qu'une fois, il n'est plus possible pour un joueur d'avoir un score sur un trou qui n'appartient pas au parcours sur lequel se joue la partie.

En rétroconception, pour tenir compte du fait que le numéro de parcours fera partie de la clé primaire de la table trous sur le schéma entités-associations, il suffit de mettre entre parenthèses la cardinalité 1,1 de l'association entre les entités trous et parcours (figure 42). L'identifiant de l'entité côté 1,1 devient alors relatif à celui de l'autre entité en association.

De même, sur le graphe de couverture minimal, nous introduisons une nouvelle notation (flèche en pointillés) pour représenter le caractère relatif des identifiants (figure 43).

Si les flèches étaient pleines, les numéros de trou dans un parcours et de partie sur un parcours figureraient dans des entités séparées et réduites à leur identifiant (à éviter).

Dans un magasin de location de vidéos, le gérant peut vouloir numéroter séparément les exemplaires de chaque vidéo (figure 44 colonne de gauche), alors que le concepteur de la base de données aurait tendance à vouloir numéroter globalement l'ensemble des exemplaires (colonne de droite).

La seule différence entre les deux solutions est l'entrée ou non de la colonne numéro vidéo dans la clé primaire de la table exemplaire. L'inconvénient majeur de la solution avec identifiant relatif (colonne de gauche), est le traitement de l'incrément automatique du numéro d'exemplaire, car il faut un compteur pour chaque vidéo et non pas un compteur pour l'ensemble des vidéos, contrairement à la solution de la colonne de droite. Le concepteur devrait donc retenir sa solution pour la base de données et proposer une colonne supplémentaire avec la numérotation du gérant, afin de lui faire plaisir.

Considérons l'exemple suivant : les factures d'une entreprise font l'objet d'un règlement par chèque ou par carte. Cette entreprise souhaite connaître pour chaque règlement la date, le montant et :

• le numéro et le nom de la banque des chèques ;
• ou le numéro et la date d'expiration des paiements par carte.

On a donc une entité générique règlements et deux entités spécialisées chèques et paiements par carte. Ces deux sous-entités de l'entité règlements ont des attributs propres, mais pas d'identifiant propre. Au niveau logique objet, on retrouve la notion d'héritage.

Conformément aux notations objet, sur le schéma entités-associations, on représente le lien qui unit une sous-entité à son entité générique par une flèche creuse (figure 45 au centre). Ce lien remplace une association être de type 1 : 1 (un chèque « est un » règlement et un paiement par carte « est un » règlement).

Toutefois, il ne faut pas voir d'héritage à chaque fois que l'on peut dire « est un », car il faut en plus que l'entité mère ne possède que les attributs communs de ses entités filles. Par exemple, un cercle « est mathématiquement un » ovale. Mais l'entité cercles (avec les attributs centre et rayon) n'est pas une sous-entité de l'entité ovales, car celle-ci possède davantage d'attributs (centre, rayon principal, rayon secondaire et rotation).

La traduction des sous-entités au niveau logique relationnel fait intervenir une clé primaire identique à celle de l'entité mère, mais dans les sous-entités la clé primaire est aussi étrangère (figure 45 en bas).

Sur le graphe de couverture minimale (figure 45 en haut), l'identifiant dont dépendent les attributs communs est volontairement dupliqué autant de fois que nécessaire pour les attributs spécialisés. Nous pouvons alors remarquer que les attributs qui dépendent d'un même identifiant peuvent être regroupés avec des « et » logiques tandis que dès qu'il est nécessaire de faire appel à un « ou » logique, c'est le signe d'une spécialisation.

Sur la figure 45, il est tentant de traduire directement le graphe de couverture minimale en le schéma relationnel, car il en est beaucoup plus proche que le schéma entités-associations. C'est une technique licite, à condition de traduire correctement les associations de type 1 : 1 (étape 4).

L'héritage peut être utilisé même lorsqu'il n'y a qu'une entité spécialisée. C'est utile pour stocker dans une table séparée des informations complémentaires.

Considérons la table clients dans laquelle nous stockons déjà le numéro, le nom et le code postal. Nous souhaitons désormais stocker également le numéro de téléphone, l'adresse courrier et l'adresse électronique. La première idée consiste à ajouter trois colonnes supplémentaires dans la table clients. Mais pour les clients qui ont déjà été saisis dans la table, ces trois colonnes seront vides.

Pour gagner de la place, ces trois colonnes peuvent constituer une nouvelle table annuaire clients dont la clé primaire référence celle de la table clients (figure 46). Formellement, annuaire clients est issu d'une sous-entité de l'entité clients.

La configuration d'héritage sur le schéma relationnel (figure 46 à droite) constituera une occasion d'écrire des requêtes SQL avec des jointures externes (c'est-à-dire facultatives).

La notion d'héritage est valable également pour les associations. Nous pouvons donc faire appel à des sous-associations avec des attributs spécifiques et des associations génériques qui contiennent les attributs communs. Mais sans aller jusqu'à l'introduction de sous-associations, dès qu'un schéma entités-associations fait appel à des sous-entités, il est fréquent que les associations concernées par ces sous-entités soient elles-mêmes spécialisées.

Considérons une entreprise artisanale qui vend non seulement des articles produits en série à prix unitaire fixe, mais aussi des articles fait sur mesure et dont le prix unitaire est calculé à partir de la durée de confection et d'un taux horaire. Dans ce cas, non seulement l'entité articles est spécialisée en articles en série et articles sur mesure, mais en plus, l'association concernée entre les entités commandes et articles est spécialisée selon qu'il s'agit d'un article en série ou sur mesure (figure 47 au centre).

Le fait d'avoir volontairement dédoublé l'identifiant commun des entités en héritage, permet d'utiliser chaque identifiant dupliqué dans l'association qui le concerne.

Sur le schéma relationnel (figure 47 en bas), les associations spécialisées sont traduites de manière classique. À charge ensuite pour le développeur du formulaire de facturation, d'effectuer la réunion des articles commandés.