Le monde du SQL

Chapitre 3 : Modèle conceptuel

Learning by Doing.

Service de location de voitures
ER digram location de voiture

Le diagramme ci-dessus peut représenter le service de location de voitures. L'entité "client" permet de stocker tous les clients du service et l'entité "voiture" permettra de sauvegarder les informations sur le parc de voitures disponibles. Un client est décrit pas ses attributs, tels que nom, prénom, date de naissance et e-mail qui est un attribut unique (on ne veut pas avoir deux clients ayant le même e-mail). Il est possible d'imaginer d'autres attributs à propos du client que le gérant peut vouloir stocker, comme, par exemple, le numéro de sa pièce d'identité, l'adresse, le numéro de téléphone, etc. La voiture est décrite pas des attributs tels que la marque, la couleur, le modèle et la plaque d'immatriculation qui est unique. Le fait qu’un client a loué une voiture est modélisé par l'association "loue" entre le client et la voiture. Les dates de location et la date de réservation sont attachées à cette association "loue" car ses attributs décrivent la location et non un client ou une voiture: on voudrait savoir non seulement qu'un client a loué une voiture, mais quand et sur quelle période pour pouvoir proposer cette voiture en location aux autres clients quand elle est disponible. 

Le client peut louer plusieurs voitures, mais doit effectuer au moins une location pour apparaitre dans notre base de données. Une même voiture peut être louée par plusieurs clients (probablement pas en même temps, mais en général, par principe même de la location). Il est néanmoins possible d'avoir une voiture dans la base de données qui n'a jamais été louée. L'association entre "client" et "voiture" est donc de type Many-to-many.

 

Il est préférable de modéliser l'agence comme une entité au lieu de l'ajouter comme un attribut, car on peut imaginer une agence décrite par plusieurs attributs, comme son nom, son adresse et probablement son gérant. Chaque agence gère seulement une partie des voitures. On peut associer une agence à un voiture avec une association "propose" pour savoir quelle voiture est proposée en location par quelle agence. Chaque voiture est proposée par une et seulement une agence, mais l'agence peut proposer plusieurs voitures pour la location.

 

Dans le cas plus complexe (et plus rare) ou les agences peuvent louer toutes les voitures aux clients, les trois entités client, voiture et agence sont impliquées dans la location. Pourtant, une association peut relier seulement deux entités. Pour modéliser un tel cas, il est nécessaire de créer une nouvelle entité "Location" pour associer la voiture, le client et l'agence à la location.

ER diagram location voiture 2
Podcast
ER diagram Podcast 1

"Les émissions aussi appelées les podcasts sont des canaux de diffusion qui proposent régulièrement des épisodes audios sur une thématique définie." Nous comprenons donc qu'il faudra enregistrer les informations sur les émissions et aussi sur leurs épisodes. Nous devons ajouter une entité supplémentaire pour les épisodes. On peut supposer que chaque émission est composée de plusieurs épisodes et que chaque épisode est lié à une émission en particulier. Cela donne les cardinalités suivantes: une émission doit contenir au moins un épisode mais peut en contenir plusiers, chaque épisode est proposée dans le carde d'une émission bien specifique et seulement une.

 

"Chaque utilisateur doit pouvoir créer plusieurs podcasts sur son compte." Ceci nous donne l'information sur les cardinalités. On suppose aussi que chaque podcast est créé par un utilisateur qui est son propriétaire. Les utilisateurs ne sont pas obligés de proposer des émissions pour s"inscrire sur la plateforme pour écouter. Cela donne les cardinalités suivantes: une émission doit contenir au moins un épisode, mais peut en contenir plusieurs; chaque épisode est proposé dans le cadre d'une émission bien spécifique et seulement une.

ER diagram Podcast 2

"Chaque podcast doit avoir un nom, une image de couverture, une catégorie, une description, un lien vers son flux RSS et au moins un épisode. Chaque épisode doit également avoir un nom, une image de couverture, une description ainsi que le lien vers l’enregistrement. Les dates de création de podcasts et des épisodes doivent également être sauvegardées." Ceci sont des attributs qui doivent être stocké dans notre base de donnée.

ER diagram Podcast 3

"Les utilisateurs qui souhaitent écouter les podcasts doivent pouvoir s’y abonner. Aussi, les utilisateurs doivent pouvoir commenter chaque épisode disponible sur la plateforme." On suppose que l'utilisateur peut s'abonner à plusieurs podcasts sans y être obligé. Aussi, on suppose que chaque utilisateur peut laisser plusieurs commentaires et chaque épisode peut avoir plusieurs commentaires.

ER diagram Pogcast 4

"Intégrez les modifications pour assurer les fonctionnalités suivantes :

 

1.     "Les utilisateurs doivent pouvoir répondre aux commentaires d’autres utilisateurs."

Pour modéliser cette situation, il parait logique de faire une association de type "utilisateur commente le commentaire". Par contre, une association peut exister uniquement entre deux entités. Vous ne pouvez pas associer une entité "utilisateur" avec une association "commente".

Il faut donc transformer cette association "commente" en entité "commentaire". Notez que l'association "commente" est de type "Many-to-many" ce qui implique qu'elle doit devenir, dans tous les cas, une entité dans le modèle logique pour que l'on puisse la stocker dans la base de données. L'association "many-to-many" est transformée en entité intermédiaire associée aux deux autres entités avec "one-to-many". Cette nouvelle entité va stocker chaque commentaire laissé par un utilisateur pour un épisode. Un utilisateur peut laisser plusieurs commentaire et un épisode peut recevoir plusieurs commentaires. Par contre, un commentaire stocké par l'entité "commentaire" est forcement lié à un utilisateur et à un épisode en particulier.

Par contre, notons que le commentaire d'un commentaire est un commentaire à un épisode. Tous les commentaires peuvent déjà être mémorisés avec une entité "commentaire" ou une association "commente" comme sur les modèles ci-dessus. La seule information qui peut être nécessaire à ajouter est le fait qu'un commentaire n'est pas indépendant, car il est rédigé en réponse à un autre commentaire. Cela est un exemple typique d'une relation reflexive. Nous allons donc transformer l'association "commente" en entité "Commentaire" associée à l'utilisateur, à l'épisode ainsi qu'à soi-même. Un utilisateur peut laisser plusieurs commentaires et un épisode peut avoir plusieurs commentaires, par contre, un commentaire en particulier est lié à un utilisateur et à un épisode.

ER diagram podcast 6

2.     Le client souhaite pouvoir récupérer des statistiques d’utilisation de son service : la géolocalisation des abonnées, le sexe et l’âge des auditeurs, le nombre de vues pour chaque épisode, le temps d’écoute pour chaque épisode, l’appareil utilisé pour écouter un épisode.

Pour obtenir ces statistiques, il faut décider quelle information doit être récupérée, à quel moment et à quelle entité cette information est associée. Par exemple, le sexe et l'âge des auditeurs sont, à priori, des informations sur les utilisateurs. La géolocalisation ainsi que l'appareil utilisé peuvent être récupérés au moment que l'utilisateur accède au service ou au moment où l'utilisateur accède à un épisode. On peut créer une entité "Accès" pour y stocker des informations liées à chaque accès d'un utilisateur au service. On peut aussi créer une association "écoute" entre l'utilisateur et l'épisode pour y stocker les statistiques liées à l'écoute. L'association "écoute" est aussi utile pour compter le nombre de fois qu'un utilisateur lance un épisode et aussi le temps d'écoute.

3.     Le client souhaite donner la possibilité aux podcasters de découper un épisode en plusieurs sous-épisodes."

Cette question est très similaire à la première et c'est un exemple de la relation reflexive, car un sous-épisode est lui-même un épisode.

ER diagram Posdcast 5
Covoiturage

"Votre client souhaite créer un nouveau service de covoiturage. On vous a chargé de mettre en place la base de données relationnelle associée au service. Les particuliers peuvent proposer les courses pour une date donnée et une destination. Les voyageurs peuvent réserver leurs places dans la limite du disponible. Tous les clients doivent avoir un compte et renseigner un certain nombre d’informations. On demande aussi des détails sur le permis de conduire et la voiture du conducteur. Les voyageurs peuvent noter les conducteurs suite aux voyages ainsi que leur laisser un commentaire. À la demande du client, le commentaire peut apparaitre anonymement sur la plateforme."

Vous avez la possibilité de modéliser les voyageurs et le conducteur comme deux entités séparées ou comme une entité "utilisateur". Dans le premier cas, on suppose que les voyageurs et les conducteurs vont avoir des comptes séparés, comme, par exemple, les photographes et les acheteurs sur Shutterstock. Dans le deuxième cas, on suppose que tous les utilisateurs ont la possibilité d'être voyageurs ou conducteurs via le même compte, comme cela est le cas, par exemple, sur Blablacar.

Ci-dessous est l'exemple du modèle en supposant que tous les utilisateurs ont un même compte. Un utilisateur peut proposer ou réserver une course. Il existe donc deux associations entre utilisateur et course. Une réservation est faite pour une date et pour un nombre de places (attributs). Un utilisateur peut aussi laisser une évaluation suite à ses courses: noter le conducteur, laisser un commentaire et indiquer s'il doit apparaitre de manière anonyme. Le permis de conduire ainsi que la voiture sont des entités, car plusieurs informations (attributs) peuvent les décrire. Le numéro du permis de conduire, l'immatriculation de la voiture et l'e-mail d'un utilisateur sont des attributs uniques qui permettent d'identifier chaque entrée. Les utilisateurs ne pourront pas créer plusieurs comptes avec le même e-mail, plusieurs voitures ayant la même immatriculation ne pourront pas exister dans la base de données. L'utilisateur peut posséder une voiture, mais cela n'est pas obligatoire pour les voyageurs (cardinalité). Par contre, chaque voiture  doit avoir un propriétaire enregistré: un utilisateur.

ER diagram Covoiturage

Dans une telle modélisation, l'utilisateur évalue une course ce qui suppose qu'il a techniquement la possibilité d'évaluer une course qu'il n'a pas réservée. Il est plus logique de supposer que l'utilisateur doit pouvoir évaluer uniquement ses réservations. Pour le moment, notre réservation est modélisée comme une association entre utilisateur et course. Il est impossible d'associer une entité à une association dans un diagramme. Il faut donc transformer l'association "réserve" en entité "Réservation" qui est associée à l'utilisateur et à la course. Cela sera dans tous les cas nécessaire pour transformer ce modèle conceptuel en modèle logique plus tard, car les associations "many-to-many" et les associations ayant des attributs comme cela est le cas pour "réserve" ne peuvent pas exister tels quels dans la base de données et doivent être transformées en entité. Cela est expliqué plus en détail dans le chapitre suivant.

ER diagram Covoiturage 2
ER diagram Covoiturage 3
Club sportif « À fond la forme »

"Votre client souhaite ouvrir un club de sport. Pour mieux comprendre ses besoins, vous l’avez interrogé et avez obtenu les informations décrites ci-dessous. Proposez un diagramme entité-association pour un système adapté aux besoins de votre client.

 

Client : Bonjour. Je suis prêt à répondre à vos questions.

Vous :  Bonjour. Pouvez-vous m’en dire plus à propos du club que vous souhaitez ouvrir ?

Client : Oui, bien sûr. Mon club « À fond la forme » va proposer plusieurs types d’activités sportives à ses adhérents : une salle de sport en accès libre, un coach personnel, des activités de groupe, tel que le yoga, le pilate, des sessions de fitness, etc. On espère pouvoir élargir notre offre petit à petit.

Vous : Quelles informations voulez-vous pouvoir gérer avec votre nouveau système d’information ?

Client : Je voudrais surtout pouvoir gérer les adhésions. Chaque client doit obtenir une carte du club pour accéder aux activités.

Vous : Avez-vous plusieurs types d’adhésions ?

Client : Oui, je voudrais proposer plusieurs types d’adhésions : journaliers, mensuels et annuels. Les clients non réguliers peuvent choisir une adhésion journalière. La carte doit être rechargée à l’accueil. Le client doit régler le prix d’adhésion et charger 10 jours minimum. À chaque visite, une journée sera déduite de la carte. Le client peut accéder à la salle de sport ou aux activités de groupe.

Vous : Il faudra prévoir une borne pour gérer les accès et les recharges.

Client : Oui. J’aimerais mémoriser au moins les dates et les heures d’accès."

Le propriétaire du club veut pouvoir inscrire des adhérents, contrôler leurs accès au club et contrôler les payments. Nous pouvons créer une entité "Client" ou "Adherent" pour garder trace des inscriptions. Il y a plusieurs types d'inscriptions que nous pouvons sauvegarder dans une entité dédiée. L'association entre le client et l'inscription va nous permettre de savoir quelle est l'inscription choisie. Pour garder trace des accès des clients au club, il parait logique de faire une association entre l'entité "Client" et l'entité "Club". Cela peut être nécessaire si le propriétaire a plusieurs clubs, mais ceci n'est pas le cas dans la situation décrite. Il est inutile de créer une entité "Club" pour y stocker une seule entrée. Nous pouvons directement créer une entité "Accès" et associer le "client" à l'"accès". Une entité supplémentaire permettra de stocker les recharges. Il est commun de dissocier l'acquisition et la consommation des points/accès.

ER diagram Club

Vous : Pouvez-vous m’en dire plus sur les autres adhésions ?

Client : Oui. Une adhésion mensuelle donne au client un accès illimité à la salle de sport et aux activités du groupe. Le client est débité chaque fin de mois pour le mois suivant. L’adhésion annuelle — c’est la même chose, mais un mois sera gratuit.

Vous : Vous m’avez également parlé d’un coach. Est-ce une adhésion particulière ?

Client : Oui. L’offre « Premium » inclut un service personnalisé avec un coach sportif. Un de nos coachs est affecté au client. Il peut programmer jusqu’à 2 sessions par semaine avec son coach.

Vous : Voulez-vous garder trace des sessions programmées ?

Client : Si cela est possible, oui.

Pour garder trace des sessions, on va supposer que les coachs sportifs proposent des sessions et les clients s'inscrivent aux sessions. Il est commun de générer des créneaux disponibles pour ensuite associer des clients aux créneaux. Une entité "coach" nous permettra de garder les informations sur les coachs et aussi gérer leurs comptes, une entité "session" va décrire les sessions puis les associations nous donneront l'information sur le coach qui a proposé la session ainsi que sur le client qui l'a réservé. Il est aussi possible d'ajouter une entité pour sauvegarder les payments mensuels/annuels.

Vous : Comment un client peut-il adhérer aux services ?

Client : Les clients peuvent le faire soit à l’accueil, soit sur notre site web. Chaque client doit nous fournir son nom, son prénom, un numéro de téléphone, une copie de sa pièce d’identité. Le client doit choisir une adhésion puis il procède au payement. Si l’adhésion est faite à l’accueil, le client peut récupérer sa carte à sa première visite en présentant sa pièce d’identité.

Vous : Je comprends. Les clients peuvent-ils changer de type d’adhésions ?

Client : Oui. Les clients peuvent changer leurs forfaits. Il suffira de se présenter à l’accueil. D’ailleurs, j’aimerais pouvoir garder trace de ces changements dans le système, les dates.

Vous : Comment se passe un désabonnement ?

Client : Les clients peuvent suspendre leur abonnement à l’accueil. Cependant, la suspension est immédiate et le forfait non utilisé n’est pas remboursé.

Vous : Y a-t-il autre chose à savoir à propos des abonnements ?

Client : On envisage aussi de proposer une offre « étudiants » et une offre « seniors ». À part cela, rien ne me vient à l’esprit pour le moment.

Vous : Je pense que j’ai assez d’information pour le moment. Je vais revenir vers vous si j’ai d’autres questions.

Client : Bien sûr. Je reste à votre disposition. Bonne journée.

Vous : Merci et bonne journée à vous aussi."

Ici, on ajoute plusieurs attributs à l'entité "Client" en incluant un attribut pour savoir si la carte a été récupérée ou non. Comme les clients peuvent annuler ou changer leurs forfaits, il faut pouvoir sauvegarder la date de fin de souscription.

ER diagram Club

"Votre client souhaite lancer un nouveau service de podcast : un service permettant à ses utilisateurs d’écouter les émissions enregistrées par d’autres utilisateurs." Commençons par un diagramme simple représentant ce service.