Diagramme entité-association (entité-relation) et schéma relationnel

Avant même de commencer de développer une base de données, la première étape de tout projet est l'analyse. L'objectif de cette page est de développer l'analyse des besoins de la base de données en créant un diagramme entité-association et un schéma relationnel. Le diagramme va nous aider à identifier les entités qui composent votre base de données ainsi que leurs relations. Le schéma relationnel va concrétiser le diagramme en convertissant les entités en tables et y ajouter les champs. Cela pourra être la base pour créer votre base de données. Pour cet exercice, nous allons déterminer quel est le contenu requis pour réaliser une facture de location de véhicules.

Bottom-Up, Top-Down

La première étape est la recherche d'informations. Quels sont les entités et les champs qui seront requis lors de la création de la base de données. Je vous rappelle que les champs vont seulement se retrouver dans le schéma relationnel.

Il y a plusieurs approches que vous pouvez utiliser pour déterminer les entités qui seront utilisées dans le diagramme entité-association et les champs qui se retrouveront dans le schéma relationnel. L'approche "Bottom-up" part de l'idée de trouver les champs dont vous auriez besoin afin d'identifier les entités qui les associent. L'approche "Top-down" part des entités pour trouver les champs qui seront requis au schéma relationnel. Vous devez aussi déterminer quels seront les besoins des usagers et quelles analyses seront demandées. Vous devez poser beaucoup de questions au gestionnaire du projet et aux intervenants afin de déterminer les approches qui seront utilisées lors de l'usage de la base de données.

Donc, quels sont les entités et les gens qui se retrouvent dans une facture de taux de véhicule ? Prenez un moment et écrivez sur une feuille toutes les entités et les champs que vous pouvez inclure dans cette facture. Vous pouvez comparer avec quelques-uns des champs que vous allez retrouver ci-dessous.

Numéro de facture, Date, Heure, Numéro de véhicule, marque, modèle, année de fabrication, couleur, VIN, plaque d’immatriculation, tarif de location, numéro du client, nom, prénom, adresse, téléphone, courriel, mode de paiement, numéro de carte, date de départ, date de retour, numéro d’employé, nom, prénom …

Avec la liste des champs que vous avez trouvée et ceux mentionnés ci-dessus, avez-vous pu déterminer les entités qui vont composer le diagramme d'entité-association ? Cette liste aide à les identifier.

Client : conserve les informations sur les clients de la location.
Facture : conserve les informations sur le service qui a été fourni.
Véhicule : conserve les informations sur les véhicules loués.
Employé : conserver des informations sur l’employé qui a préparé la facture.
Tarif : conserve les informations sur le ou les tarifs qui ont été appliqués.

Regardons maintenant quelles seront les composantes du diagramme et des relations entre les entités.

Diagramme entité-association

Un diagramme entité-association, ou entité-relation selon certaines documentations, permet d’identifier les entités (personnes, choses ou événements) et de voir leur interaction dans une base de données relationnelle. Des symboles seront utilisés pour créer le diagramme.
Entité : Ce qui décrit une personne, une chose ou un événement.

Un client potentiel peut être inscrit à la liste des clients sans encore avoir passé une commande.
Un nouvel employé n’a pas encore été assigné à un poste.

Il est temps de développer le diagramme entité-association. La première chose est que les entités seront placées dans des boîtes. La prochaine étape est de déterminer la relation entre ces entités et les placer dans des losanges. La relation entre les entités est déterminée selon l’interaction entre celles-ci, mais aussi sur les informations que vous désirez conserver. C’est votre analyse et votre interaction avec les intervenants qui va aider à déterminer les entités et leurs relations. Voici une liste des interactions entre les entités.

• Des factures de location sont générées pour le client pour la location de véhicules.
• Un employé prépare ces factures
• Une facture peut inclure plusieurs véhicules.
• Un véhicule sera loué plusieurs fois et se retrouvera sur plusieurs factures.
• Un tarif sera appliqué au véhicule selon le type : économique, régulier, luxe, petit camion, camion …

Il y a une relation entre Client et Facture, mais pas entre Client et Employé. Il y a certainement une interaction entre ces deux individus. Cependant, la seule information qu’on désire conserver est qui a préparé la facture. C’est pour cela qu’il y a une relation entre Facture et employé, mais pas entre Client et Employé. Aussi, il n’y a pas une relation directe entre Client et Véhicule. Une facture est préparée qui inclut les véhicules qui ont été loués. L’entité Facture sert d’intermédiaire entre Client et Véhicule. Sans les informations de la facture, il est impossible de savoir quels véhicules ont été loués.

On peut déjà commencer le diagramme et y mettre les relations.

Toutes les entités sont reliées directement ou indirectement. Aucune entité n’est mise de côté. Les losanges incluent un verbe qui détermine la relation entre les entités. Cela est important pour déterminer la cardinalité entre les entités (1-1, 1-N, N-M). Cela est déterminé en posant deux questions pour chaque relation. Voici la structure des questions :

Pour 1 de A, potentiellement combien de B ?
Pour 1 de B, potentiellement combien de A ?

Le mot « potentiellement » a été ajouté afin de considérer quel est vraiment le maximum possible. Il y a seulement deux réponses possibles à ces questions : un ou plusieurs. La réponse à ces deux questions va aider à déterminer la cardinalité ou le type de relation entre les entités. Il y a seulement trois combinaisons de réponses possibles : 1 à 1 (1-1), 1 à plusieurs (1-N) et plusieurs à plusieurs (N-M).

Une relation « 1 à 1 » indique pour chaque série de données sur une personne, une chose ou un événement (enregistrement) d’une entité, a seulement une série équivalente dans l’autre entité. Par exemple, légalement au Canada, une personne peut seulement être mariée à une autre personne à un moment précis. Ou des informations confidentielles peuvent seulement être associées à une personne.

Une relation « un à plusieurs » indique que pour un enregistrement (groupe d’informations) d’une entité peut être relié à plusieurs références dans la seconde entité. Par exemple, une personne peut être le propriétaire de plusieurs véhicules ou de plusieurs résidences.

Une relation « plusieurs à plusieurs » peut indiquer qu’une référence d’une entité peut se retrouver plusieurs fois dans l’autre entité et vice versa. Un étudiant peut suivre plusieurs cours et un cours peut être suivi par plusieurs étudiants. Cette situation peut seulement être réglée en créant une nouvelle entité qui sera appelée une entité composée. Il y aura plus de détails sur cette situation lors de la création du schéma relationnel.

Répondons aux deux questions pour chaque relation entre les entités en commençant par la relation entre Client et Facture.

Pour un client, combien de factures, potentiellement, seront générées ? Un ou plusieurs ? Vous comprenez que la réponse à cette question sera plusieurs. Il sera avantageux pour l’entreprise d’avoir des clients récurrents.

On utilise le caractère « N » dans le diagramme pour représenter plusieurs.

Pour la seconde question : pour une facture qui sera générée, elle peut être associée à combien de clients ? Un ou plusieurs ? La réponse dans ce cas est « 1 ». Une facture peut seulement être associée à un seul client.

Vous avez possiblement remarqué que le verbe « généré », qui est utilisé dans la relation entre ces entités, est aussi utilisé dans les questions. Il est important d’utiliser le verbe approprié pour décrire la relation entre les entités. Dans la majorité des cas, plusieurs verbes similaires peuvent être utilisés.

Ensuite, on prend la plus grande valeur de chaque côté des flèches. Dans ce cas, cela donne une relation de 1 à plusieurs ou 1-N. Cela donne ceci dans le diagramme.

Répétons pour la prochaine relation qui est entre Facture et Employé.

• Une facture a été préparée par un employé.
• L’employé peut préparer plusieurs factures.

Encore une fois, le verbe « prépare », qui détermine la relation entre ces entités, a été utilisé pour les deux questions.

On ressort la plus grande valeur de chaque côté des flèches pour déterminer qu’il s’agit d’une relation 1-N.

Regardons pour la prochaine relation entre Facture et Véhicule.

• Une facture peut avoir plusieurs véhicules en location.
• Un véhicule peut être contenu sur plusieurs factures.

Il y a des occasions ou un client peut louer plusieurs véhicules pour un événement : fête, cérémonie, déménagement … Aussi, vous désirez qu’un véhicule soit loué plusieurs fois afin de rentabiliser l’investissement. L’analyse démontre que :

Nous avons ici une relation « plusieurs à plusieurs » ou N-M. Cela apporte une série d’étapes supplémentaires. La première est la conversion de la relation entre ces deux entités en une entité composée.

AVANT

APRÈS

Le symbole , qui représente la relation, est remplacé par pour indiquer qu’il s’agit maintenant d’une entité composée.

Comme pour les entités, des informations devront être conservées dans cette entité afin de permettre la relation plusieurs à plusieurs (N-M).
La seconde étape est de donner un nom à cette nouvelle entité. Le premier nom possible est une combinaison des noms des deux entités de la relation plusieurs à plusieurs : Facture-Véhicule. Vous pouvez aussi approfondir pour trouver un nom qui décline mieux le contenu de l'entité composée. Dans ce cas, ces informations décrivent le contenu de la facture ou les items de celles-ci. La dernière étape et d'indiquer la cardinalité autour de l'entité composée.

Le « 1 » sont toujours du côté des entités initiales et le « N » son toujours du côté de l'entité composée. Cela veut dire qu'on va retrouver qu'une seule fois les informations sur une personne, une chose ou un événement (enregistrement) dans les entités initiales. Cependant, on pourra retrouver plusieurs références à ces enregistrements dans l'entité composée. Cela va nous permettre de gérer les relations plusieurs à plusieurs ou N-M.

Il reste la dernière relation entre Véhicule et Tarif.

• Un véhicule peut avoir qu’un seul tarif.
• Un tarif peut s’appliquer à plusieurs véhicules.

Nous allons simplifier ici la réalité pour avoir une relation d’un à plusieurs ou 1-N. En fait, plusieurs tarifs peuvent s'appliquer à un véhicule selon la durée de la location. Cela peut être un tarif quotidien, de fin de semaine, de semaine ou d'un mois. Nous désirons simplifier le travail pour cet exercice.

Il est temps de regrouper toutes les relations en un seul diagramme. Certaines règles doivent s'appliquer. Aucune entité ne doit être mise de côté. Elles doivent toutes être incluses dans le diagramme et vous devez pousser votre analyse pour toutes les inclure. Vous le savez possiblement oublier une autre entité où n'avez pas encore trouvé l'élément qui les relie ensemble. C'est le temps de relire vos textes et vos notes pour déterminer les relations.

Toutes les entités doivent être liées directement ou indirectement. Comme mentionné, il n'y a pas de lien direct entre les entités Client et Véhicule. Les entités Facture et Items serviront d'intermédiaires.

La cardinalité doit être clairement indiquée pour chaque relation. Cela peut être 1-1, 1-N ou 1-M. Dans le dernier cas pour indiquer une relation N-M. L'optionalité (.) doit être incluse seulement si cela a été clairement indiqué dans le texte où votre analyse avec les intervenants.

Voici donc le diagramme final pour cet exercice.

Le schéma relationnel

Un schéma relationnel et une représentation des tables, des champs et des relations de la base de données. Il indique comment la base de données sera structurée.

Le diagramme entité-association réalisé auparavant peut sembler inutile pour créer une base de données. Cependant, il est essentiel pour la création du schéma relationnel et, éventuellement, la base de données. La première étape des de convertir toutes les entités du diagramme en tables.

Les tables sont les structures dans lesquelles la liste des champs et de leurs propriétés se retrouvent en plus d’y conserver les données. Il y aura plus de détails sur les tabes sur les pages pour la création d’une BD, la création et la modification de tables. Le nom des tables apparaît en majuscule au-dessus du rectangle où la liste des champs va apparaître. Tous les champs associés à cette table seront inclus.
(Liste des tables avec les champs, pas de clés primaires ou externes ou de relations)

Il faut s'assurer que les champs se retrouvent dans les bonnes tables. Il y a une page qui explique la « normalisation » et comment réaliser les trois premières formes normales pour s'assurer qu'un champ et bien associés à sa clé primaire.

CLÉ PRIMAIRE

Ceci nous amène à la prochaine étape du schéma relationnel : déterminer les clés primaires des tables. Une clé primaire est un chant qui permet de distinguer un enregistrement des autres dans la table. Mais qu'est-ce qu'un enregistrement ? C'est une série de chants qui permet d'identifier une personne, une chose ou un événement. Prenons la table suivante.

La première ligne est le nom des champs qui décrivent le contenu des colonnes. Chaque ligne est un enregistrement qui décrit un employé dans ce cas. Le champ EMPLOYÉ_No est la clé primaire. Elle est nécessaire pour distinguer un enregistrement des autres. Celle-ci a deux caractéristiques :

• Elle est obligatoire pour identifier les enregistrements et permettre la relation aux autres tables.
• Elle contient une valeur unique qui n’est pas répétée dans les autres enregistrements.

Il est impossible d’avoir deux factures avec le même numéro de référence ou deux clients ou deux employés. La clé primaire peut être composée de lettres et de chiffres.

Ex. : IMP-101, PROD-201 …

Cependant, une clé primaire doit toujours être unique à un enregistrement. Deux enregistrements ne peuvent pas avoir la même valeur pour la clé primaire.

Microsoft Access offre un type de champ appelé « NuméroAuto » qui permet à l’application d’entrer automatiquement un chiffre qui ne sera pas en conflit. C’est très pratique pour créer des relations entre un champ de type « NuméroAuto » et de type « Numérique » afin de créer des relations 1-N. Il y aura plus de détails sur la page des relations.

Plaçons une clé primaire au début de toutes les tables sauf pour Items. Afin de les distinguer, veuillez les souligner les clés primaires.

CLÉ PRIMAIRE COMPOSÉE

Une clé primaire composée requiert deux champs ou plus pour gérer les relations de plusieurs à plusieurs (N-M). Pour une clé primaire « ordinaire », il faut s’assurer que le contenu ne se retrouve pas en double. Microsoft Access a une option pour s’assurer qu’il n’y a pas de doublons dans le contenu de la clé. Pour une clé primaire composée, il faut s’assurer qu’il n’y a pas de doublons pour l’ensemble des champs de la clé. Des parties peuvent se répéter, mais la combinaison complète des champs de la clé primaire composée.

Prenons l'exemple de la relation entre les tables Étudiant et Cours. Un étudiant peut suivre plusieurs cours et un cours peut être suivi par plusieurs étudiants. Cela détermine qu'il y a une relation N-M entre ces deux tables. Il faut donc une entité composée ou une table intermédiaire entre Étudiants et Cours. La clé primaire composée sera, au minimum, composé de la clé primaire des 2 autres tables. Il y a des situations spéciales ou une clé primaire composée peut inclure trois champs ou plus. Généralement, une clé primaire composée aura deux champs.

D'autres champs peuvent aussi s'ajouter à la table intermédiaire. Par exemple, la note peut être seulement donnée à un étudiant pour un cours en particulier. Voici un exemple du contenu de la table Étudiant-Cours.

Le contenu des champs ÉTUDIANT_ID et COURS_ID se répète, mais pas la combinaison des champs de la clé primaire composée.

Dans le diagramme entité-association, vous avez déterminé qu'il y avait une relation plusieurs à plusieurs (N-M) entre les entités Facture et Véhicule. Cela a créé une entité composée que nous avons appelée Items. Donc, la clé primaire sera composée des champs FACTURE_No et VÉHICULE_No.

Donc, toute entité composée aura une clé primaire composée.

CLÉ EXTERNE OU ÉTRANGÈRE

Afin de permettre une relation entre deux tables, elles doivent au moins avoir un champ en commun. On relie la clé primaire d'une table à un champ en commun dans l'autre table. Ce champ est appelé une « clé externe » ou « clé étrangère ». Il contient les mêmes informations que la clé primaire afin de créer la relation entre les deux tables. Pour permettre de mieux l'identifier, on conserve le même nom que la clé primaire de l'autre table. On ajoute donc ici le champ CLIENT_No Table Facture pour pouvoir créer une relation En tout cas ces deux tables.

La relation nous permet de constater que le client numéro 1 et responsable des factures numéros 1 et 3. Grâce à une base de données relationnelle, nous n'avons pas à réécrire les coordonnées du client. On a juste besoin de son numéro de client pour l'identifier et accéder à ses coordonnées.

La prochaine étape consiste à ajouter les clés étrangères à toutes les tables requises. Notez que ceci s'applique seulement aux relations d’un à plusieurs (1-N) et non à 1-1 ou N-M. Pour les relations 1-1, la relation se fera de clés primaires à clé primaire. Pour les relations N-M, cela se fera de la clé primaire à l'élément en commun de la clé primaire composée.

Il y a trois relations 1-N dans cet exercice : entre Client et Facture, Facture et Employé et Véhicule à Tarif. Mais comment savoir dans quelle table il faut mettre la clé étrangère ? Vous devez regarder le diagramme entité-association que vous avez préparé auparavant ; surtout la cardinalité.

Le côté « 1 » sera toujours associé à la clé primaire. Par définition, une clé primaire est obligatoire et unique. On ne peut pas retrouver deux fois la même information ou valeur dans une clé primaire. La clé étrangère ou externe sera toujours du côté ayant le « N ».

Avec ces informations, vous savez que le champ CLIENT_No sera la clé externe dans la table Facture. Le champ EMPLOYÉ_No sera aussi une clé étrangère dans la table Facture. Pour terminer, le champ TARIF_No sera une clé externe dans la table Véhicule.

LES RELATIONS

Il reste une dernière étape pour terminer le schéma relationnel. Il faut mettre les relations entre les tables. Pour les relations 1-N, cela sera de la clé primaire à la clé externe. Pour les relations N-M, cela sera de la clé primaire à l'élément en commun dans l'entité composée de la table intermédiaire à la relation N-M. Vous devez aussi inclure la cardinalité qui sera la même que vous avez identifiée dans le diagramme entité-association.

Vous pouvez aussi créer ces relations directement dans Microsoft Access. Vous aurez plus de détails à la page sur les relations. Vous allez remarquer deux différences.

Access va placer le symbole (clé) pour indiquer que le champ et la clé primaire ou une composante d'une clé primaire composée. Le second et que le symbole « N » A été remplacé par (infini). Si vous devez remettre le travail pour le cours AOT5100, le diagramme des relations d'Access sera suffisant. Ça fait que vous devrez reproduire un diagramme entité association et un schéma relationnel lors de l'examen final. Vous devrez reproduire vous diagrammes manuellement en utilisant les normes mentionnées dans ce texte : titres en majuscule, clés primaires soulignées, utilisez le symbole « N » au lieu de (infini).

Pour terminer cette page, ou placeriez-vous le champ « Escompte » ? Est-il relié au type de client ? Est-il relié à une promotion sur le type de véhicule ? Est-ce un pourcentage sur le prix de la facture ? Toutes ces combinaisons peuvent arriver. C'est seulement votre analyse en profondeur et votre discussion avec les intervenants qui vont déterminer le meilleur endroit pour ce champ.