Université de la Méditerranée Ecole Supérieure d'Ingénieurs de Luminy Etudes Supérieures en Ingénierie Informatique Projet Bases de Données _______________ GESTION D'UN CENTRE MEDICAL _______________ Enseignant responsable : C. Sabatier Stéphane BENOLIEL Samuel CHRISTOFEUL Virginie COLLOMB Philippe ELDIN Guillaume LEVASSEUR Thomas PEYRIC Promotion 1998 - 1999 Mai 1997

Sommaire

Analyse
I. Etudes préalables
A. Dictionnaire des données
B. Les règles de gestions
C. Les règles de calcul
II. Modèle conceptuel
III. Modèle relationnel
A. Les classes d'entités
B. Les classes d'associations
C. Les simplifications
D. Les contraintes

Programmation
I. Les déclencheurs
II. Les traitements
Algorithme de recherche de date
III. Les modifications
Les insertions
Les suppressions
Les modifications

Contenu des tables
I. Notre base
II. Les tests
Test du trigger sur la table intervention

Analyse

I. Etudes préalables

A. Dictionnaire des données

B. Les règles de gestions

· Un malade ne subit qu'une intervention par jour.
Ä Une intervention = un malade.
Ä Une intervention = un jour.
· Une intervention est composée d'un certains nombres d'actes.
· Une intervention est composée d'un certains nombres d'actes ordonnés chronologiquement.
· Un service est ouvert 4 jours sur 7.
· Un service est ouvert durant 5 créneaux ( 8h à 18h ).
· Un service est compétent dans 3 types d'actes différents maximums.
· Un service exécute 6 actes maximums par créneau.
· Une ordonnance donne le jour de l'intervention.
· Une ordonnance donne les 4 actes maximums qui constituent l'intervention.
· Une ordonnance donne pour chaque acte le créneau horaire et le service.
· Un acte peut être réalisé par plusieurs services.

C. Les règles de calcul

· A tout moment, le bureau planning doit être capable de proposer à un client des rendez-vous précis pour l'exécution d'une intervention qui lui a été prescrite.
· En début de journée, le bureau planning doit fournir à chaque service, la liste ordonnée des clients attendus et des actes à exécuter.
· Au départ du client, le service facturation doit établir la facture globale de chaque intervention, en détaillant les coûts de chacun des actes qui la composent, ainsi que la part restant à la charge du patient.
· En fin de mois, pour chaque service, et chaque acte qu'il peut assurer, afficher le nombre total d'actes effectués et le pourcentage de ces actes par rapport à l'activité globale mensuelle du service.
Pour réaliser ceci, nous avons besoin d'effectuer les calculs suivants :
- A = S actes[x] par service et par mois.
- B = S actes[x] par mois et pour le centre.
- C = A*100/B
· En fin de trimestre, pour chaque acte pris en charge par le centre, et pour chaque service assurant un tel acte, pourcentage réalisé par ce service relatif au nombre total d'actes de ce type réalisé au centre, pendant le trimestre écoulé.
Pour réaliser ceci, nous avons besoin d'effectuer les calculs suivants :
- A = S actes[x] par service et par trimestre.
- B = S actes[x] par trimestre et pour le centre.
- C = A*100/B.

II. Modèle conceptuel

La classe d'entités PATIENT décrit les caractéristiques des patients.
La classe d'entités INTERVENTION donne uniquement les numéros des interventions.
La classe d'entités ACTES décrit les actes médicaux.
La classe d'entités SERVICE décrit les services du centre médical.
La classe d'entités JOUR SEMAINE donne tous les jours de la semaine.
La classe d'entités ORDRE donne les ordres dans lesquels les actes doivent être exécutés (de 1 à 4).
Chaque association ORDONNANCE entre un patient et une intervention indique une intervention subie par ce patient à une date donnée.
Chaque association SE COMPOSE indique pour une intervention les actes effectués (ou prévus), ainsi que l'ordre dans lequel ils doivent avoir lieu.
Chaque association RENDEZ VOUS indique les services concernés par une intervention, ainsi que l'ordre dans lequel ils interviennent. Elle donne aussi le moment où a lieu l'intervention (créneau, section).
Chaque association COMPETENT donne pour un acte les services capables de le pratiquer, et pour un service les actes qu'il peut effectuer.
Chaque association OUVERT donne un jour d'ouverture pour un service.

III. Modèle relationnel

A. Les classes d'entités

INTERVENTION(NumInter, NumInsee, DateInter)
PATIENT(NumInsee, NomPat, PrePat, AdrPat, CodePat, VillePat, NaisPat, SexePat)
SERVICE(NumServ, NomServ, TelServ)
ACTE(NumActe, PrixActe, TauxSecu)

B. Les classes d'associations

RENDEZVOUS(NumInter, NumOrd, NumServ, Creneau, Section)
OUVERT(NumServ, JourOuv)
COMPETENT(NomServ, NumActe)
COMPOSITION(NumInter, NumOrd, NumActe)

C. Les simplifications

Nous pouvons remarquer que la classe d'entités ORDRE ne contient qu'un attribut clef et ne participe qu'à des classes d'associations avec une cardinalité maximale de N. Nous pouvons donc supprimer la relation ORDRE en la remplaçant par une contrainte de domaine :

NumOrdre appartient à {1, 4}

D. Les contraintes

La relation PATIENT

NumInsee est différent pour chaque patient et strictement positif.
SexePat est soit le caractère 'F' soit 'M'.

La relation INTERVENTION

NumInter est différent pour chaque intervention et strictement positif.
NumInsee fait référence à la table PATIENT(NumInsee).
DateInter la date ne peut être nulle.
Le couple (NumInsee,DateInter) est unique.

La relation SERVICE

NumServ est différent pour chaque service et strictement positif.
Le couple (NumServ, NomServ, TelServ) est unique.

La relation OUVERT

NumServ fait référence à la table SERVICE(NumServ).
JourOuv appartient à l'ensemble des jours de la semaine.
Le couple (NumServ, JourOuv) est différent pour chaque élément de ouvert.

La relation ACTE

NumActe est différent pour chaque acte et strictement positive.
PrixActe est strictement positive.
TauxSecu est un décimal compris entre 0 et 1.

La relation COMPETENT

NumServ fait référence à la table SERVICE(NumServ).
NumActe fait référence à la table ACTE(NumActe).
Le couple (NumServ, NumActe) est différent pour chaque élément de compétent.

La relation COMPOSITION

NumInter fait référence à la table INTERVENTION(NumInter).
NumOrd est un entier compris entre 1 et 4.
Le couple (NumInter, NumOrd) est différent pour chaque élément de composition.
NumActe fait référence à la table ACTE(NumActe) et ne peut être nul.

La relation RENDEZVOUS

NumInter fait référence à la table INTERVENTION(NumInter).
NumOrd est un entier compris entre 1 et 4.
Le couple (NumInter, NumOrd) est différent pour chaque élément de rendezvous.
Le couple (NumInter, NumOrd) est une clef étrangère et fait référence à la table COMPOSITION(NumInter, NumOrd).
NumServ fait référence à la table SERVICE(NumServ) est ne peut être nul.
Creneau est un entier non nul compris entre 1 et 5.
Section est un entier non nul compris entre 1 et 6.
Le couple (NumInter, Creneau) est unique.

Programmation

I. Les déclencheurs

Trig_competant_3_actes_max

Ce trigger permet de vérifier, avant une insertion dans la table COMPETENT, qu'un service X n'a pas plus de 3 compétences.Notre algorithme se comporte de la façon suivante :
Grâce au SELECT, nous calculons le nombre de lignes dans COMPETENT en faisant une jointure avec le numéro de service où nous voulons rajouter une compétence et tous les services ayant des compétences. En fait ce SELECT nous renvoie le nombre de compétences du service X.
Tant que ce nombre est inférieur ou égal à 2 on peut rajouter une compétence à ce service, sinon nous affichons un message d'erreur.

Trig_ouvert_jour_semaine

Ce trigger permet avant l'insertion d'un jour d'ouverture d'un service, de réaliser deux tests. Il s'assure que le service n'est pas déjà ouvert et que le nombre de journées ouvrables est bien inférieur ou égale à quatre.Notre algorithme se comporte de la façon suivante :
Nous utilisons une variable NB qui servira de compteur auxiliaire dans le premier SELECT, nous calculons le nombre de jours où le service Y est ouvert en faisant une jointure entre le numéro du service de Y et tous les autres services. Tant que ce nombre est strictement inférieur à trois nous pouvons rajouter un jour d'ouverture, sinon nous indiquons qu'il y a une erreur.
Ensuite dans le second SELECT nous cherchons dans la table OUVERT si le service Y n'est pas déjà ouvert ce jour là. Nous utilisons pour cela la même jointure que ci-dessus plus une seconde jointure (jour à insérer / jours déjà existant pour Y). Si nous trouvons au moins une ligne il y a une erreur.

Trigger_patient_nais

Ce trigger permet avant l'insertion et la modification dans la table patient, de vérifier la validité d'une date, en effet nous comparons cette date à la date système.

Date système - 150 ans < La Date < Date système.

En cas d'anomalie une erreur est détectée "  error 20000 date erronée "

Trigger_intervention

Ce trigger permet de garder les interventions qui ont eu lieu. En effet il nous faut garder les données pour effectuer d'éventuels calculs statistiques. Nous comparons la date à la date système en nous assurant que cette dernière est bien passée en cas d'erreur : "  Intervention Passée ".

Trigger_rdv

Ce trigger permet avant l'insertion et la modification, de réaliser trois tests. Le rendez-vous doit être pris un jour où le service est ouvert; les créneaux du rendez-vous pris doivent être conformes à la hiérarchie des actes prescrits dans l'ordonnance; le créneau Û section dans ce service doit être libre.

II. Les traitements

· A tous moments, le bureau planning doit être capable de proposer à un client des rendez-vous précis pour l'exécution d'une intervention qui lui a été prescrite.

Cette fonction permet de trouver, à partir d'une ordonnance composée au plus de 4 actes, la date, les créneaux, et les services pouvant permettre l'intervention.
Il s'agit d'une transaction PL/SQL avec une interface utilisateur. Au début, nous demandons le numéro d'INSEE du patient, les différents actes qu'il doit subir, et la date d'intervention souhaitée. La fonction retourne alors un rendez-vous possible et propose l'insertion de l'intervention dans la base de données.

Le principe de cette transaction est le suivant :
Pour une date donnée, on regarde si le patient n'a pas d'intervention prévue. Si c'est le cas, on recherche pour chaque acte, un créneau et une section, où un service compétent pour cet acte, est libre. Si tous les actes trouvent leur place dans une même journée, alors un rendez-vous est proposé au patient. Sinon, nous incrémentons le jour et nous recommençons.

Algorithme de recherche de date:

début
..Tant que date non trouvée faire
....pour chaque acte faire
......pour chaque créneau faire
........pour chaque section faire
..........pour chaque service faire
............si le service est compétent dans l'acte
............et si il est libre au moment donné (jour,créneau,section)
............alors faire
...............enregistrer rendez-vous concernant l'acte;
...............supprimer le créneau;
............finsi
..........finpour
........finpour
......finpour
....finpour
....si tous les actes ont été enregistré alors faire
......sortir;
....sinon faire
......jour ¬ jour + 1;
....finsi
..fintantque
fin

La programmation de cette transaction à nécessité un apprentissage approfondi du PL/SQL. Nous avons choisi d'utiliser des tables temporaires que le programme parcourt tout au long de son exécution. Pour cela, nous avons dû nous servir de nombreux curseurs car les requêtes select into pouvaient provoquer des exceptions dans certains cas.
Pour l'enregistrement dans la base, nous avons fait une autre boucle PL/SQL.

Proposition de rendez-vous :

TROUVER UNE DATE POUR UNE ORDONNANCE
____________________________________

LISTE DES ACTES DISPONIBLE
__________________________

Numéro insee du patient :1
Composition de L'ORDONNANCE
Acte numero 1 :2
Acte numero 2 (entrer '-1' pour aucune action) :3
Acte numero 3 (entrer '-1' pour aucune action) :-1
Acte numero 4 (entrer '-1' pour aucune action) :-1
Date souhaitee (au plus tot):25-05-97

VALIDATION DU RENDEZ-VOUS
Voulez-vous confirmer le rendez-vous (O/N):O

APRES
TABLE INTERVENTION

TABLE COMPOSITION

TABLE RENDEZ-VOUS

· En début de journée, le bureau planning doit fournir à chaque service, la liste ordonnée des clients attendus et des actes à exécuter.

Ce traitement donne pour chaque service :
Les noms des patients et les actes qu'ils auront à subir durant la journée.Toutes ces données seront triées par services, par créneaux puis par sections.Nous avons utilisé pour cela une vue qui renvoie numserv,creneau,section,nompat et numacte et tries dans cette ordre, puis grâce à un select nous n'affichons que numserv,nompat et numacte
Voici ce que nous observons à l'écran :

Planning d'une journée par service
__________________________________

· Au départ du client, le service facturation doit établir la facture globale de chaque intervention, an détaillant les coûts de chacun des actes qui la composent, ainsi que la part restant à la charge du malade.
Cette transaction calcule le montant d'une intervention subit par un patient. Nous recherchons les actes effectués dans la table COMPOSITION et nous calculons les différents prix grâce au prix de l'acte et son taux de prise en charge par la sécurité sociale (table ACTE).

· Pour chaque service, et chaque acte qu'il peut assurer, afficher le nombre total d'actes effectués et le pourcentage de ces actes par rapport à l'activité globale mensuelle du service.
Afin de réaliser ce traitement, nous avons dû mettre en place deux vues. La première vue calcule pour le mois en cours, pour chaque service, le nombre d'actes par type d'actes qu'il a effectué. La deuxième vue recherche le nombre d'actes qui a été réalisé dans le mois et pour chaque service. Une simple requête nous permet alors de donner pour chaque service et chaque acte le pourcentage de ces actes par rapport à l'activité globale mensuelle du service en appliquant l'opération précisée dans le chapitre " Analyse " partie " Règles de calculs ".

· Pour une période donnée, pour chaque acte pris en charge par le centre, et pour chaque service assurant un tel acte, pourcentage réalisé par ce service relatif au nombre total d'actes de ce type réalisés au centre.
Afin de réaliser ce traitement, nous avons dû également mettre en place deux vues. La première vue est strictement identique à celle du traitement précédent excepté qu'elle effectue les calculs sur une période donnée. La deuxième vue recherche le nombre d'actes qui a été réalisé dans le centre durant cette période. Une simple requête nous permet alors de donner pour chaque service et chaque acte pris en charge par le centre, le pourcentage réalisé par ce service relatif au nombre total d'actes de ce type réalisés au centre, en appliquant l'opération précisée dans le chapitre " Analyse " partie " Règles de calculs ".

III. Les modifications

Les insertions

Ces transactions demandent à l'utilisateur les données à insérer et les insèrent dans la table choisie. Ces transactions offrent un interfaçage minimal, mais appréciable.

Les suppressions
Ces transactions demandent à l'utilisateur les références des lignes à supprimer, et effacent les données en tenant compte des liaisons (FOREIGN KEYS) avec les autres tables. De plus, elles facilitent les suppressions en chaîne.

Les modifications
Ces transactions demandent à l'utilisateur les références des lignes à modifier, ainsi que les nouvelles valeurs à insérer. Dans le cas où l'on ne veut pas changer une des valeurs, il suffit de taper sur ENTREE.

Contenu des tables

Afin de tester au mieux notre base de données nous avons simuler un exemple en entrant un jeu de données valides.Par la suite nous l'avons testé en entrant des données incorrectes, en vérifiant que celles-ci n'étaient pas validées.

I. Notre base

La table INTERVENTION

La table PATIENT

La table SERVICE

La table OUVERT

La table ACTE

La table COMPOSITION

La table COMPETENT

La table RENDEZ-VOUS

II. Les tests

Test du trigger sur la table intervention:

SQL> insert into intervention values (56,1,'21-05-97');
insert into intervention values (56,1,'21-05-97')
*
ERREUR à la ligne 1:
ORA-20000: Intervention Passee
ORA-06512: à ligne 2
ORA-04088: erreur lors d'exécution du déclencheur 'USR11.TRIGGER_INTERVENTION'

SQL> insert into intervention values (68,3,'29-05-97');
1 ligne créée.