Introduction Le but de ce projet est de réaliser une base de données

Introduction
Le but de ce projet est de réaliser une base de données permettant de gérer les
remboursements de clients dans une pharmacie.
Concrètement, il y a des clients, des médecins, des produits. Un client peut être inscrit
dans un centre de gestion des remboursements, cotiser à une mutuelle et être inscrit à une
sécurité social.
Un docteur soigne des clients, fait des ordonnances et les donnes aux clients.
Une ordonnance donne lieu à des factures qui sont envoyés au centre de gestion des
remboursements.
Ce dernier calcule la somme remboursée par la sécurité sociale et la mutuelle et en déduit la
somme à payer par le patient.
Il faut donc créer une base de données dynamique capable de gérer ces données, les
actualiser et les modifier. Il faut donc procéder par étapes pour réaliser un projet tel que celui
la.
En premier lieu, nous allons parler de la conception de la base de données. Il y a
plusieurs stades avant de créer les tables de la base de données sous oracle et nous allons les
détailler. Il faut d’abord réaliser un modèle conceptuel de données, puis le transposer en
modèle logique de données relationnelles et enfin établir le modèle physique des données.
A la suite de ça, nous aborderons tout ce qui touche aux requêtes SQL. A quoi serventelles ? Comment introduire des données dans la base ? Comment modifier des données ?
Ensuite nous expliquerons l’utilisation du langage PL/SQL, langage qui permet de
dynamiser la base. Nous montrerons son utilité et l’utilisation que nous en avons fait dans
notre base de données.
Nous avons rencontré je pense comme beaucoup de personnes des problèmes durant la
réalisation de cette base de données. C’est pourquoi notre base a des limites. Nous essaierons
dans cette partie de déterminer les causes des limites de notre projet.
Enfin, un petit mode d’emploi ne serait pas de trop pour expliquer comment s’utilisent
toutes les procédures et les astuces à connaître pour le bon fonctionnement de la base.
A. La conception
1. Le modèle conceptuel de données
Le modèle conceptuel des données est un premier travail de réflexion sur la base. Il
faut en effet utiliser les données que l’on nous a données pour créer un modèle de données.
Dans notre cas, nous avons un sujet qui définit les possibilités de notre base.
Nous allons dans un premier temps établir des phrases clés qui vont nous servir à créer le
modèle.
Un produit peut être remboursable ou non.
Le type de remboursement est déterminé par la sécurité sociale.
Le prix d’un produit varie selon les périodes.
Le pourcentage de remboursement dépend de la classe pharmaceutique.
Une ordonnance est une liste de médicaments.
Une facture est aussi une liste de médicaments.
Un médecin délivre les ordonnances.
Un client reçoit les ordonnances de la part du médecin.
Une ordonnance permet d’établir des factures.
Une facture est envoyée au centre de gestion des remboursements.
Le centre de gestion des remboursements détermine les sommes remboursées par la sécurité
sociale et la mutuelle et la somme à payer par le patient.
Un client peut être inscrit à un centre de gestion et peut aussi cotiser à une mutuelle.
Ces phrases clés vont nous permettre d’établir les entités présentes dans la base de données.
Les entités sont :
- Fabricant
- Produit
- Secu
- Ordonnance
- Facture
- CentreGestion
- Medecin
- Client
- Mutuelle
Une fois les entités trouvées, il faut lister tous les champs des entités.
Un produit est caractérisé par son nom, sa classe pharmaceutique, le nom de son fabricant, la
date d’expiration, le pourcentage de remboursement, son numéro de lot et son prix.
Un fabricant est caractérisé par son nom, son taux qui permet de définir les promotions et les
augmentations et le prix de départ d’un produit.
Une ordonnance est caractérisée par sa date de consultation.
Un médecin est caractérisé par son nom, son adresse et sa spécialité.
Un client est caractérisé par son nom, son adresse et son numéro de téléphone.
Une facture est caractérisée par la somme à payer par le patient et la date d’achat.
La sécu n’a pas de caractérisation précise.
La mutuelle est caractérisée par son nom, et les cotisations des clients.
Enfin le centre de gestion est caractérisé par son nom et son emplacement.
Il faut maintenant établir les différentes relations entre les entités.
On sait qu’une ordonnance est une liste de médicaments avec prescriptions.
Une facture est une liste de produit avec la quantité.
Une ordonnance permet d’établir une ou plusieurs factures.
Un fabricant fournit les produits.
Sécurité sociale détermine le type de remboursement des produits.
La facture est envoyée au centre de gestion des remboursements.
Les médecins établissent les ordonnances et les donnes au client.
Les clients sont inscrits à un centre de gestion des remboursements.
Ils peuvent aussi cotiser pour la mutuelle.
Le centre de gestions des remboursements détermine la somme remboursée par la mutuelle et
la somme remboursée par la sécurité sociale.
Il faut ensuite identifier les cardinalités et le résultat est le modèle conceptuel de données (voir
annexe 1).
2. Le modèle logique de données relationnelles
Une fois le modèle conceptuel de données établit il faut le transposer en modèle
logique de données relationnelles. Dans notre cas les clés primaires sont soulignées alors que
les clés étrangères sont précédées de #.
Nous avons :
- Produit( id_produit, nom, classe, pourcentage, date_expiration, numerot_lot, prix)
- Fabricant( id_fabricant, nom, taux, #id_produit)
- Ordonnance( id_ordonnance, date)
- Facture(id_facture,somme, date_achat)
- PO( id_produit, id_ordonnance, prescriptions)
- PF(id_ produit, id_facture, quantité)
- FO(id_facture, id_ordonnance)
- Secu( id_secu, #id_produit, type_remboursement, #id_centre)
- CentreGestion( id_centre, nom, lieu, numerocentre)
- Mutuelle( id_mutuelle, #id_client, cotisation, #id_centre, nom)
- CF( id_centre, id_facture, somme_secu, somme_mutuelle)
- Client( id_client, nom, adresse, numero_tel)
- Medecin( id_medecin, nom, adresse, specialite)
- MCO( id_medecin, id_client, id_ordonnance)
- CC( id_centre, id_client, numero_identification)
3. Le modèle physique de données
Il faut maintenant établir le modèle physique de données.
Il y a différentes règles qui permettent de construire le modèle physique de données
relationnelles.
Relation binaire aux cardinalités (X,1) - (X,n), X=0 ou X=1.
La clé primaire de la table à la cardinalité (X,n) devient une clé étrangère dans la table à la
cardinalité (X,1).
Relation binaire aux cardinalités (X,n) - (X,n), X=0 ou X=1.
Il y a création d'une table supplémentaire ayant comme clé primaire une clé composée des
identifiants des 2 entités.
On dit que la clé primaire de la nouvelle table est la concaténation des clés primaires des deux
autres
tables.
Si la relation est porteuse de donnée, celles ci deviennent des attributs pour la nouvelle table.
Relation binaire aux cardinalités (0,1) - (1,1).
La clé primaire de la table à la cardinalité (0,1) devient une clé étrangère dans la table à la
cardinalité (1,1).
Ces différentes règles nous permettent de construire le modèle visible dans l’annexe 2.
B. Création de tables et requêtes
Une fois le modèle bien conçu et construit, nous pouvons alors créer la base de donnée
avec oracle. Les entités vont devenir des tables. Nous comptons 15 tables à créer pour notre
base de données.
Prenons un exemple de table et décrivons le brièvement :
create table Fabricant(
id_fabricant number primary key not null,
id_produit number,
nom varchar2(20),
taux number,/*fait varier le prix selon les periodes*/
prix_depart number,
constraint
cleprod
foreign
key
(id_produit)
Produit(id_produit)
);
references
Nous avons créé une table avec 5 attributs, 4 attributs qui sont des nombres et un
attribut qui est une chaîne de caractères qui pourra contenir entre 1 et 20 caractères.
Nous avons une clé primaire et une clé étrangère.
id_fabricant est la clé primaire, elle est unique (elle ne peut pas avoir deux valeurs
différentes), primary key est suivi de son nom et de son type pour indiquer que c’est une clé
primaire. L’option not null signifie qu’elle ne peut pas être nulle. A chaque fois que l’on
ajoutera des informations dans cette table, il faudra absolument mettre une valeur à cette clé
primaire. Nous avons choisi de ne mettre que les clés primaires en not null, de cette manière
toutes les clés primaires et étrangères ne seront pas nulles et les liens entre les tables peuvent
toujours se faire.
id_produit est une clé étrangère, à la fin de la table on précise que c’est une clé
étrangère et il faut aussi préciser la clé avec laquelle elle a un lien (table et attribut) à l’aide de
references.
Il faut répéter cette manipulation pour toutes les tables à créer.
Une fois les tables créées, on peut stocker des informations, choisir des informations à
consulter, et ceux à l’aide des requêtes SQL.
Pour insérer des données dans une table, on utilise insert into.
Par exemple :
insert into Fabricant( id_fabricant, id_produit, nom, taux, prix_depart)
values(1,2,’fabricant’,50,10) ;
Le fabricant ‘fabricant’ possède le produit numéro 2, son taux est de 50 et son prix de
départ est 10.
Les données sont modifiables et on peut les changer à l’aide de update.
Exemple :
Update Fabricant set prix_depart=25 where id_fabricant=1;
Cette commande va modifier le prix du produit que l’on avait inséré précédemment
dans la base.
On peut maintenant sélectionner des données dans les tables à l’aide des requêtes.
Exemple :
Select * from fabricant ;
Cette requête va afficher toutes les informations de la table.
Select id_produit from Fabricant where prix_depart=25;
Cette requête va afficher le numéro de tous les produis qui ont un prix de départ égal à
25.
C. Le langage PL/SQL
Nous avons dans ce projet utiliser le langage PL/SQL qui nous a permis de dynamiser
la base, de mettre à jour les informations automatiquement. Un attribut d’une table peut se
mettre à jour automatiquement quand un événement se passe. Sa valeur peut se calculer avec
les valeurs d’autres attributs dans d’autres tables que l’on peut récupérer grâce à ce langage.
1. Séquences et triggers
Les séquences permettent de générer des entiers uniques qui appartiennent à une suite
arithmétique. Nous avons utiliser des séquences pour auto incrémenter certaines de nos clés
primaires qui sont des entiers. Mais pour que des clés primaires soient incrémentées
automatiquement, il ne suffit pas de créer des séquences, ces séquences devront être
accompagnées de triggers (déclencheurs) qui au moment de l’insertion d’une valeur dans une
table remplira automatiquement la clé primaire de la valeur entière supérieure.
Nous avons d’abord créé quelques séquences qui s’incrémentent de 1 et qui démarre
de 1.
CREATE
CREATE
CREATE
CREATE
CREATE
CREATE
CREATE
SEQUENCE
SEQUENCE
SEQUENCE
SEQUENCE
SEQUENCE
SEQUENCE
SEQUENCE
seq_ord START WITH 1 INCREMENT BY 1;
seq_client START WITH 1 INCREMENT BY 1;
seq_medecin START WITH 1 INCREMENT BY 1;
seq_facture START WITH 1 INCREMENT BY 1;
seq_produit START WITH 1 INCREMENT BY 1;
seq_mutuelle START WITH 1 INCREMENT BY 1;
seq_secu START WITH 1 INCREMENT BY 1;
Les noms de ces séquences montrent pour quelles tables elles vont server.
Il y a une séquence pour la clé primaire de l’ordonnance, une pour celle de la table
client, de même pour les tables medecin, facture, produit, mutuelle et secu.
Pour connaître la valeur actuelle d’une séquence il faut faire la commande :
Select seq_client.currval from dual;
Pour incrémenter d’une unité et pour connaître la valeur suivante il suffit de taper :
Select seq_client.nextval from dual;
Comme je le disais, pour que les clés s’auto incrémentent il faut créer des triggers qui
vont insérer automatiquement la valeur suivante de la suite arithmétique lorsque l’on veut
insérer des données dans la table.
Exemple :
create or replace trigger client
before insert on Client for each row
begin
select seq_client.nextval into
:new.id_client
from
dual;
end;
Avant d’insérer des données dans la table client, on insère dans id_client la valeur
suivante de la suite.
Un trigger accompagne chaque séquence que l’on a vue plus haut.
Les triggers peuvent avoir des fonctions différentes, par exemple le trigger prix permet
de mettre à jour le prix de tous les produits avant une insertion dans la table facture. Car
comme les prix varient selon les périodes alors suivant la date de la facture qui détermine la
date d’achat des produits les prix vont changer.
create or replace trigger prix
after insert on Facture
for each row
n number;
begin
select seq_facture.currval into n from dual;
execute calcule_prix(n);
end;
2. Procédures et fonctions
Les procédures ou fonctions sont des petits programmes permettant d’effectuer des
opérations sur les tables et attributs.
Une fonction doit renvoyer une valeur, on exécute une fonction à l’aide de la
commande
select nom_fonction(attribut1 type …..) from dual ;
Une procédure effectue des modifications sur les tables et attributs. On exécute une
procédure à l’aide de la commande
execute nom_procédure( attribut1 type …) ;
Nous allons détailler une procédure pour mieux comprendre son utilité et son
utilisation.
create or replace procedure calcule_prix(f number)
as
d varchar2(10);
n number;
t number;
pd number;
cursor cur_produit is select * from produit;
p produit%rowtype;
begin
select to_char(date_achat,'MM') into d from facture where
id_facture=f;
if d>=01 and d<=03 then t:=-20;
else
if d>=04 and d<=06 then t:=-10;
else
if d>=07 and d<=09 then t:=10;
else t:=20;
end if;
end if;
end if;
open cur_produit;
fetch cur_produit into p;
while cur_produit%found loop
update fabricant
set taux = t
where fabricant.id_produit=p.id_produit;
select prix_depart into pd from fabricant where
fabricant.id_produit=p.id_produit;
update produit
set prix=pd+(pd*t/100)
where
produit.id_produit=p.id_produit;
fetch cur_produit into p;
end loop;
end;
Cette procédure permet de modifier le prix des produits en fonction d’une facture,
donc d’une date. Cette procédure prend donc en argument le numéro d’identification d’une
facture.
Après as il faut déclarer toutes les variables locales.
d permettra de stocker la date.
t sera le taux à insérer dans la table fabricant.
pd sera le prix de départ à aller chercher dans la table fabricant.
On créé ensuite un curseur cur_produit. Un curseur permet de stocker le résultat de
toute une requête. C’est une table. L’intérêt d’un curseur, c’est qu’on peut le parcourir attribut
par attribut.
Ici mon curseur stockera toutes les données des produits.
Ensuite on déclare un attribut de type ligne de produit, c’est une spécificité du langage
PL/SQL, on peut stocker toute une ligne dans une variable et on peut accéder à toutes les
variables de la ligne à l’aide d’un point.
Begin signifie le début de l’implémentation du code.
En premier on stocke le mois de la date de la facture dans la variable d.
On donne une valeur au taux en fonction du mois. Par exemple si on est en janvier février ou
mars le taux sera de -20, ce qui signifie que le prix du produit diminuera. Ces valeurs peuvent
être bien sur modifiable à souhait.
Ensuite on ouvre le curseur, on stocke la première ligne dans l’attribut de ligne que l’on a
déclaré plus haut à l’aide du mot clé fetch.
On fait une boucle de manière à parcourir toutes les lignes de la table produit, et tant qu’on
trouve des informations on modifie le prix des produits en fonction du taux calculé, grâce à la
commande update.
Nous avons utilisé beaucoup d’autres procédures et fonctions qui effectuent de
multiples opérations.
Nous avons une procédure commande qui permet de passer une commande de
médicaments au fabricant, c'est-à-dire insérer un nouveau produit dans le marché avec toutes
les informations nécessaires.
La procédure ajoute_client ajoute un client et l’inscrit dans une mutuelle et un centre
de gestion des remboursements.
La procédure ajoute_medecin ajoute un médecin dans la base.
La procédure inscrire_client inscrit un client dans un centre de gestion des
remboursements.
La procédure soigne permet à un client d’aller voir un médecin, ce dernier lui délivre
une ordonnance avec la date.
Il faut utiliser la procédure prescriptions pour ajouter des prescriptions à cette
ordonnance.
La fonction nb_medoc calcule le nombre de produits qui se trouvent dans une
ordonnance, elle retourne ce nombre.
La procédure créé une facture à partir d’une ordonnance, elle ne met pas encore la
somme à payer, elle créé la facture avec la date.
La fonction calcule retourne la somme totale des produits sans les remboursements
pour une facture donnée.
La procédure calcule_secu calcule la somme à rembourser par la sécu.
La procédure calcule_mutuelle calcule la somme à rembourser par la mutuelle.
La procédure calcule déduit la somme à payer par le client à l’aide des procédures et
fonctions ci-dessus.
D. Mode d’emploi
Une fois toutes ces procédures créées, il faut savoir les utiliser.
- Pour ajouter un client par exemple.
execute
ajoute_client('damien','rue
marc',0490099099,100,’LMDE’,2,100);
saint
Les informations à entrer sont le nom, l’adresse, le numéro de téléphone, les
cotisations à la mutuelle( pourcentage, 100 est la cotisation max), le nom de la mutuelle, le
numéro d’identification du centre où il s’inscrit et son numéro d’identification.
- Il faut bien sur avoir au préalable ajouter des centres :
insert into Centregestion(id_centre,nom,lieu) values(1,'MEP','marseille');
- Pour ajouter un produit :
execute commande('stressam','pol',28,5,102145,10,'10/12/2010',10);
Avec le nom du produit, le nom du fabricant, le prix de départ, la classe pharmaceutique
(entre 1 et 5, 5 signifie que le produit sera le mieux remboursé), le numéro du produit, le
numéro de lot, la date d’expiration et le type de remboursement (qu’on donnera à la secu)/
- Pour ajouter un médecin :
execute ajoute_medecin('forre','marseille','chirurgien');
Avec son nom, son adresse et sa specialite.
- Pour soigner un client :
execute soigne(1,2,'12/04/2000');
Le client numéro 1 va se faire soigner par le médecin numéro 2 le 12 avril 2004.
- Pour ajouter des prescriptions :
execute prescriptions(1,2,'matin midi et soir');
Le médecin qui a prescrit l’ordonnance numéro a donné le produit numéro 2 à prendre matin
midi et soir.
-
Pour créer une facture :
execute creer_facture(1,'10/03/2005');
On créé une facture à partir de l’ordonnance numéro 1 le 10 mars 2003.
Il vaut mieux insérer des prescriptions à l’ordonnance avant de créer un facture à partir de
cette ordonnance.
- Pour mettre à jour le prix de chaque médicaments :
execute calcule_prix(1);
On recalcule le prix suivant la date de la facture numéro 1.
-
Pour calculer la somme remboursée par la sécurité sociale :
execute calcule_secu(1,2);
On calcule la somme remboursée pour la facture 1 qui est envoyée au centre de gestion des
remboursements numéro 2.
-
Pour calculer la somme remboursée par la mutuelle :
execute calcule_mutuelle(1,2);
On calcule la somme remboursée pour la facture 1 qui est envoyée au centre de gestion des
remboursements numéro 2.
-
Pour calculer la somme à payer par le patient :
execute calcule_somme(1);
On calcule la somme à payer par le patient pour la facture numéro 1.
Les 4 dernières procédures sont à effectuer dans l’ordre donné. Il faut mettre à jour le
prix des médicaments avant de calculer la somme remboursée par la sécurité sociale puis celle
de la mutuelle qui se sert de la somme remboursée par la sécu et la somme à payer par le
patient qui se sert des sommes remboursées par la mutuelle et la sécu.
Conclusion
Le but du projet était de réaliser une base totalement dynamique, avec des événements
qui se déclenchent automatiquement. La conception de la base joue un rôle important car
suivant la conception, les données sont plus ou moins accessibles. Nous pensons que certains
de nos choix nous ont empêcher d’aller jusqu’au bout de ce que nous voulions faire.
Par exemple les sommes des mutuelles et sécu ne sont stockées que sur les factures, un client
est automatiquement inscrit dans un centre et cotise automatiquement, mais aussi le lien entre
le centre de gestion des remboursements et la mutuelle et la sécu ne sert à rien.
Cependant ce projet nous a permis d’apprendre un langage inconnu jusqu’à présent, en
outre nous avons compris que toute l’importance de ce projet résidait dans la conception. Une
mauvaise conception de la base ne pardonne pas.