Dossier du projet

PPE 1.3 Contexte GSB
Gestion des visites Projet MediBase
Version 1.1
LUIS Maxime - DIMET Alan - BOURLANGE Eden – PLOUVIEZ Maxime
1
Partie informatique
Première phase
CREATION DE LA BASE DE DONNEES
Le choix du SGBDR
Nous avons choisi le SGBDR PostgreSQL, c’est un logiciel libre avec une
communauté très active avec de très bonnes critiques. De plus, notre choix s’est basé
sur la popularité de ce SGBDR sur les forums de développements tel que
developpez.net (voir sources pour plus de détails) où d’ailleurs il est majoritaire à un
sondage d’outils SQL. D’autant plus que cet outil a déjà été utilisé en cours ce qui
prouve ses performances notamment avec la gestion des utilisateurs. Ce logiciel est
même inclus d’office sur certaines distributions Linux.
2
Le script SQL :
Après avoir étudié le schéma conceptuel de données (MCD) et le schéma relationnel
de données (MLD), nous avons fini le script SQL permettant la création des tables
souhaitées dans notre SGBDR.
CREATE TABLE Visiteur(
vis_NumSecu
Int NOT NULL ,
vis_nom
Varchar (25) ,
vis_adresse
Varchar (25) ,
vis_cp
Varchar (25) ,
vis_ville
Varchar (25) ,
vis_dateEmbauche Date ,
PRIMARY KEY (vis_NumSecu )
);
CREATE TABLE Visite(
v_num
Int NOT NULL ,
v_date
Date ,
v_lieu
Varchar (25) ,
v_description Varchar (25) ,
vis_NumSecu Int NOT NULL,
pra_num Int NOT NULL,
PRIMARY KEY (v_num )
);
CREATE TABLE Medicament(
med_depotLegal Int NOT NULL ,
med_nomCommercial Varchar (25) ,
med_composition Varchar (25) ,
med_effets
Varchar (25) ,
med_contreIndic Varchar (25) ,
PRIMARY KEY (med_depotLegal )
);
CREATE TABLE EchantillonMedic(
ech_num
Int NOT NULL ,
ech_prixHT Double ,
med_depotLegal Int NOT NULL,
PRIMARY KEY (ech_num )
);
CREATE TABLE proposer(
v_num Int NOT NULL ,
3
ech_num Int NOT NULL ,
PRIMARY KEY (v_num ,ech_num )
);
CREATE TABLE Praticien(
pra_num Int NOT NULL,
pra_nom Varchar (25),
pra_coef_notoriete Double NOT NULL,
PRIMARY KEY (pra_num)
);
CREATE TABLE posseder(
pra_num Int NOT NULL,
spe_code Varchar (25),
PRIMARY KEY(pra_num,spe_code)
);
CREATE TABLE Specialite(
spe_code Varchar (25),
spe_libelle Varchar (25),
PRIMARY KEY(spe_code)
);
Migration des données
Après avoir fait des recherches sur l’implantation d’un fichier .xls (tableau excel) sur
notre SGBDR nous avons trouvé qu’il était possible, en convertissant le fichier en .csv
et d'effectuer la commande «COPY» afin d’implanter les données du tableau dans une
table souhaitée. Le fichier .mdb peut être lu sous Microsoft Excel et, par conséquent, il
suffit d’enregistrer une fiche de données souhaitées, de la convertir à son tour et
d'effectuer la même commande.
4
Requêtes SQL
-Afficher les visiteurs classés par ordre alphabétique :
Select vis_nom from Visiteur order by vis_nom ASC ;
-Afficher le nom et l’adresse des praticiens qui ont reçu une visite ces deux dernières
années :
Select pra_nom, pra_adresse from praticien natural join visite where v_date
between [29/01/12] and [20/01/14];
5
-Augmenter les prix HT des échantillons inférieurs à 1,5 euros de 5%:
Update EchantillonMedic set ech_prixht=ech_prixht * 1,05
where ech_prixHT < 1.5 ;
-Afficher les noms commerciaux des médicaments qui n’ont pas été proposé aux
praticiens :
Select med_nomCommercal, med_depotLegal from medicament natural join
echantillonmedic natural join proposer where v_num is not exists;
From Medicament, EchantillonMedic natural join proposer where v_num is not
exists ;
6
Partie informatique
Deuxième phase
DEPLOIEMENT
Nous avons choisi de déployer notre base de données sur la distribution Linux
DEBIAN 7.
Debian a été conçu pour être stable, c’est-à-dire que les versions des logicielles
peuvent être plus anciennes que les autres distributions. Mais Debian a pour vocation
de toujours être stable ce qui conviendra donc parfaitement pour un serveur !
Les paquets envoyés et reçus sont vérifiés comme étants stables et fonctionnels.
Nous avons configuré notre serveur de telle sorte (en ip statique):
Et voici la configuration réseau du poste de travail des utilisateurs GSB :
Requête SQL - Création des trois comptes d’utilisateurs
Création des trois comptes d’utilisateurs :
-Create group staff WITH user admin, praticien, visiteur;
-Grant select, insert, delete, update on * to admin with grant option ;
-Grant select on EchantillonMedic to praticien;
-Grant select, insert, delete, update on Visite to visiteur;
7
8
Partie informatique
Troisième phase
EVOLUTION DE LA BASE DE DONNEES
MLD :
Visiteur(vis_NumSecu, vis_nom, vis_adresse, vis_cp, vis_prenom, vis_ville,
vis_dateembauche, sec_code, lab_code)
vis_numsecu clé primaire de la relation visiteur
Visite(v_num, v_date, v_lieu, vis_numsecu, v_description, pra_num)
v_num clé primaire de la relation visite
pra_num clé étrangère en référence à la relation praticien
vis_numsecu clé étrangère en référence à la relation visiteur
Specialite(spe_code, spe_libelle)
spe_code clé primaire de la relation specialite
Region(code_reg, libellé)
code_reg clé primaire de la relation region
Proposer(v_num, ech_num)
v_num, ech_num clé primaire de la relation proposer
Praticien(pra_num, pra_nom, pra_coef_notoriete)
pra_num clé primaire de la relation praticien
Pra_diplome(code_diplome, pra_num)
code_diplome, pra_num clé primaire de la relation pra_diplome
Posseder(pra_num, spe_code)
pra_num, spe_code clé primaire de la relation posseder
9
Medicament(med_depotlegal, med_nomcommercial, med_composition, med_effets,
med_contreindic)
med_depotlegal clé primaire de la relation medicament
Habite(code_region, vis_num)
code_region, vis_num clé primaire de la relation habite
Echantillonmedic(ech_num, ech_prixHT, med_depotlegal, tauxtva)
ech_num clé primaire de la relation echantillonmedic
med_depotlegal clé étrangère en reference à la relation medicament
Diplome(codediplome, libelle)
codediplome clé primaire à la relation Diplome
Requêtes SQL
Réaliser une vue qui permette d’afficher la somme des prix TTC des échantillons par visites :
CREATE VIEW tva_visite as select sum(tva*echprixHT) from ech_medic natural join
proposer group by v_num;
Afficher le praticien le plus diplômé :
Select pra_num, pra_nom from Praticien natural join pra_diplome where
code_diplome = (select max(count(code_diplome)) from praticien natural join pra_diplome);
Afficher la moyenne des sommes des prix TTC des échantillons par visites :
Select avg(*) from tva_visite;
Afficher les visiteurs par région qui ont effectué plus de 5 visites :
Select vis_nom from visiteurs natural join region where count(vis_num)>5 order by
code_reg;
Afficher le prix TTC maximum de toutes les visites réalisés (de tous les échantillons réalisés) :
Select max(echprixht*TVA) from echantillonmedic natural join visite group by
vis_num;
10
Partie Juridique
DROITS INFORMATIQUE
Première partie :
Deuxième partie :
Troisième partie :
Dans le cadre d'une amélioration de la gestion des données de l'entreprise GSB pour les
praticiens. Il est donc obligatoire de respecter les formalités à réaliser afin de respecter la
législation Informatique et Libertés :
- Notifier la mise en œuvre du fichier et ses caractéristiques à la CNIL, sauf cas de dispense
prévus par la loi ou par la CNIL.
- Mettre les personnes concernées en mesure d’exercer leurs droits en les en informant.
11
- D'assurer la sécurité des informations afin d'empêcher qu’elles soient modifiées,
endommagées ou que des tiers non autorisés n'y aient accès.
- Se soumettre aux contrôles et vérifications de la CNIL et répondre à toute demande de
renseignements qu’elle formule dans le cadre de ses missions.
SOURCES
http://fadace.developpez.com/sgbdcmp/ (Site pour trouver SGBD )
http://www.marche-public.fr/Marches-publics/Definitions/Entrees/Donne-caracterepersonnel.htm (Définition juridique des données perso).
http://doc.ubuntu-fr.org/postgresql
http://www.cnil.fr/linstitution/qui-sommes-nous/ (CNIL)
http://www.developpez.net/forums/d30385/bases-donnees/postgresql/importer-fichier-xlsvers-postgresql/
12