Exercices avec le langage SQL

Un article de SQLpro    2 Commentaires

Le cabinet médical du Mourillon désire d’équiper d’une solution informatique de gestion du planning des rendez-vous et pour cela vous confie la mission de modéliser la base de données nécessaire à cette activité.

Le cabinet compte actuellement 4 médecins et 5 salles d’auscultation et compte s’agrandir dans l’avenir. De temps à autre un médecin remplaçant prend des vacations journalières pour remplacer les médecins malades ou en vacances.
Il y a des rendez-vous au cabinet et des rendez-vous sur place (chez le patient).

Chaque médecin à des jours et des horaires de présence différents sur la semaine.
Par exemple le docteur Bondu travaille le lundi, le mercredi et le jeudi de 9h à 12h30 puis de 14h à 19h. Le mardi de 14h30 à 20h et le samedi de 8h à 13h. Les lundi, mercredi et jeudi matin sont consacré à des visites chez les patients. Le reste du temps il est au cabinet.
Pour le docteur Pochet, il travaille du mardi au samedi avec les mêmes horaires : de 10h à 19h. Les mardi, jeudi et vendredi il visite ses patients.

Bien entendu il y a les vacances, c’est à, dire des jours pendant lesquels certains médecins ne travaillent pas.

Les rendez-vous au cabinet ont lieu pour un même médecin dans la même salle au cours de la journée.

La fréquence des rendez-vous a été fixée comme suit : pour les visites sur place des patients, une fois par 20 minutes. Pour les rendez-vous en cabinet, une fois toutes les 10 minutes.

Sur le planning doit figurer :

• les horaires de disponibilité de chacun des médecins;
• les rendez-vous déjà fixé;
• les créneaux de rendez-vous disponible;
• le type de rendez-vous possible par plage (sur place ou au cabinet);
• le nom du patient qui a rendez-vous;

En outre il a été demandé que l’on puisse saisir l’heure exacte d’arrivée du patient au cabinet et l’heure de passage en salle d’auscultation afin de connaître le temps d’attente moyen. De la même façon, le système doit permettre de saisir l’heure exacte à laquelle le médecin est arrivé chez son patient pour un rendez-vous sur place.

Il ne faut pas que :

• un même médecin ait deux rendez-vous avec deux patients différents le même jour à la même heure.
• une salle d’auscultation soit occupée par deux médecins en même temps.

Sur le patient on collectera les informations suivantes : nom, prénom, sexe, date de naissance, téléphone, adresse.

Travail à faire :

Il vous est demandé de présenter un modèle conceptuel de données assorti des contraintes et des règles afférentes au modèle élaboré.

SOLUTION

1 – Le modèle conceptuel de données :

Description des principales entités :

CALENDRIER est une entité comprenant toutes les dates.
CHRONO est une entité contenant tous les horaires de rendez-vous au pas horaire spécifié (10 minutes) avec indication booléenne s’il s’agit d’un créneau de RV au domicile (un tuple sur deux), soit toutes les 20 minutes.
RV est une entité faible composé de liens identifiants provenant des entités PATIENT, MEDECIN, CALENDRIER et CHRONO,
OCCUPATION est entité faible composé de liens identifiants provenant des entités CALENDRIER, SALLE, MEDECIN
JOUR_DE_SEMAINE est une entité contenant les 7 jours de la semaine.
PRESENT est une entité partiellement faible composée d’un attribut clef (PSC_HEURE_DEBUT) et de liens identifiants provenant des entités JOUR_DE_SEMAINE et MEDECIN

Description des principales associations :

remplace est une association ternaire portant sur un médecin, son remplaçant et une date.
absent est une association n:m qui spécifie à quelles date les médecins sont absents.

2 – Le modèle physique de données (version MS SQL Server) :

Contraintes :
Les contraintes à mettre en œuvre sont les suivantes :
1) un médecin ne peut se remplacer lui même (exclusion entre PRS_ID_REMPLACE et PRS_ID_REMPLACANT dans la table de jointure « remplace »)
2) une heure de fin doit être supérieure à une heure de début (PSC_HEURE_DEBUT inférieure à PSC_HEURE_FIN dans la table PRESENT)
3) différentes périodes de présence ne peuvent se recoupées pour un même médecin à un même jour de semaine (dans la table PRESENT).
Cette dernière contrainte est assez complexe et peut se résumer à une assertion du genre :

CREATE ASSERTION A_PRESENCE_NON_RECOUVRANTE 

AS 

   CHECK (NOT EXISTS(SELECT * 

                     FROM   PRESENT AS P1 

                            INNER JOIN PRESENT AS P2 

                                  ON     P1.PRS_ID = P2.PRS_ID 

                                     AND P1.JSM_ID = P2.JSM_ID 

                                     AND (    (P2.PSC_HEURE_DEBUT >= P1.PSC_HEURE_DEBUT AND P2.PSC_HEURE_DEBUT < P1.PSC_HEURE_FIN) 

                                           OR (P2.PSC_HEURE_FIN   >= P1.PSC_HEURE_DEBUT AND P2.PSC_HEURE_FIN   < P1.PSC_HEURE_FIN))))

NOTA : pour toutes les problématiques de planning, des vues suffisent. Par exemple l’ensemble des créneaux disponible par médecin peut se faire par un produit cartésien des jours de semaine avec le chrono restreint sur les plages de présence et auquel on soustrait (par l’opérateur ensembliste EXCEPT) les RV déjà pris.

——–
Frédéric BROUARD, Spécialiste modélisation, bases de données, optimisation, langage SQL.
Le site sur le langage SQL et les S.G.B.D. relationnels : http://sqlpro.developpez.com/
Expert SQL Server http://www.sqlspot.com : audit, optimisation, tuning, formation
* * * * * Enseignant au CNAM PACA et à l’ISEN à Toulon * * * * *