PROJET PERSONNEL ENCADRE Partie SQL

BELLAZAAR Rayane Corizzi Lucas

DU BOISHAMON Arnaud PICO Alexandre

Table des matières

INTRODUCTION .......................................................................................................................................................................... 1

PREMIERE PARTIE : CREATION DE AL BASE DE DONNEES ..................................................................................................... 1 - 11

CREATION DES TABLES ........................................................................................................................................................................... 3

DECLARATION DES CLES ETRANGERES ....................................................................................................................................................... 3

INSERTION DES DONNEES .................................................................................................................................................................. 3 - 9

LES REQUETES SQL ....................................................................................................................................................................... 9 - 11

SECONDE PARTIE : DEPLOIEMENT DE LA BASE DE DONNEES ............................................................................................. 12 - 29

CREATION DES MACHINES VIRTUELLES ............................................................................................................................................. 12 - 13

INSTALLATION DU SYSTEME D'EXPLOITATION WINDOWS 7 ......................................................................................................................... 14

INSTALLATION DU SYSTEME D'EXPLOITATION WINDOWS SERVER 2008 ......................................................................................................... 15

CONFIGURATION DES MACHINES VIRTUELLES ..................................................................................................................................... 17 - 29

TROISIEME PARTIE : EVOLUTION DE LA BASE DE DONNEES .............................................................................................. 29 - 36

CONCLUSION ...................................................................................................................................................................... 37 - 38

1

L’entreprise pharmaceutique Galaxy Swiss Bourdin possède de nombreuse force de vente qui traverse chaque jour le continent Européen et qui agissent également sur le plan internationale afin de rendre visite à des professionnels de santé dispatchés dans le monde. Ils ont pour mission de présenter des médicaments à des professionnels de santé au sein de différents établissements. Disposant actuellement d'une base de donnée ne répondant pas à ses attentes, le laboratoire Galaxy Swiss Bourdin souhaite revaloriser sa base de donnée en l'optimisant et en intégrant la gestion de l'ensemble des visites réalisées par les visiteurs médicaux. De ce fait, GSB à fait appel à nos services afin de mettre en place cette nouvelle base de données en nous laissant le choix du système de gestion de bases de données relationnelles. Nous allons donc à travers un projet s'étendant sur une durée d'un mois réaliser la base de donnée que désire le laboratoire pharmaceutique en vous démontrant chaque étape de sa conception.

Première phase : Création de la base de données Suite à une étude approfondit de l'ensemble des besoins du laboratoire pharmaceutique, le groupe de travail présent sur place a conçu et nous a fait parvenir un schéma de la base de donnée sous forme de modèle conceptuel ainsi qu'un schéma logique de données qui fait apparaitre l'ensemble des tables à créer et qui nous démontre de façon clair et explicite les relations entre chaque tables.

Le modèle conceptuel :

2

Le schéma logique de données :

Un stagiaire a réaliser une partie de la base de donnée dans un script enregistrer au format SQL et pouvant être exécuter directement dans le SGBDR. Ce script à pour objectif de créer les différentes tables et de déclarer les clé étrangère. Voici un aperçu du script :

Comme on peut le constater, l'auteur de ce script n'a pas eu le temps de finaliser la création de toute les tables. Nous allons donc compléter le script suivant en ajoutant les éléments manquant.

3

a) Création des tables. Si on compare le script qui nous a été fournit au modèle conceptuel on constate qu'il manque 3 tables. En effet, Les tables Proposer, Spécialité et Praticien n'ont pas été créer nous allons donc les ajouter sur le script.

1) Création de la table Posséder

2) Création de la table Praticiens

3) Création de la table Spécialités

b) Déclaration des clés étrangères.

Suite à la création des tables manquantes, il nous a fallut déclarer les nouvelles relations quelles entretiennent avec les autres tables.

Dans un premier temps nous avons ajouter un champ pra_num dans la table Visites puis nous avons créer un index sur celui-ci. Ensuite nous avons déclarer ce champs en tant que clé étrangère. Dans un second temps, nous avons affirmé la relation entre les tables Posséder et Praticien en déclarant le champs pra_num en tant que clé étrangère. Etant déjà considéré comme une clé primaire dans la table Posséder, il était inutile de créer un index sur ce champ. Enfin nous avons affirmé la relation entre les tables Posséder et Spécialités en déclarant le champs spe_code en tant que clé étrangère.

c) Insertion des données.

a) Modification de la table Visiteurs. Avant de procéder à l'insertion des données il nous a fallut changer le type de la clé primaire. En effet, les valeurs saisies en tant que clé primaire dépassaient la capacité maximal d'un INTEGER. De ce fait, la valeur saisie en tant que clé primaire doit être comprise entre - 2 147 483 648 et 2 147 483 647.

4

Dans notre cas, les numéro de sécurité sociale faisant office de clé primaire dépassaient la valeur maximum d'un INT ce qui générait un message d'erreur MYSQL. Pour cela nous avons tout simplement changer le type INT de la clé primaire par le type VARCHAR. Nous avons également ajouter le champs vis_prenom afin d'avoir un affichage cohérent pour une requête que nous réaliseront par la suite.

b) Script d'insertion des données pour la table Visiteurs.

VISITEURS : vis_NumSecu, vis_nom, vis_prenom, vis_adresse, vis_cp, vis_ville, vis_dateEmbauche

5

c) Modification de la table Visites Pour avoir un affichage cohérent pour l'une des requêtes que nous réaliseront par la suite, il nous a fallut ajouter le champs pra_num dans la table Visites , de créer un index sur celui-ci puis de le déclarer en tant que clé étrangère afin d'affirmé la relation avec la table Praticien.

d) Script d'insertion des données pour la table Visites

VISITES : Vis_NumSecu, Vis_nom, Vis_prenom, Vis_adresse, Vis_cp, Vis_ville, Vis_dateEmbauche

e) Modification de la table Médicaments.

Avant de procéder à l'insertion des données nous avons du procéder à un changement de type pour la clé

primaire de la table médicaments. En effet en récupérant les données dans l'ancienne base de données nous

avons constater que les valeurs saisies dans la clé primaire sont des chaines de caractères et non des entiers.

De ce fait, nous avons changer dans le script le type INT de la clé primaire par le type VARCHAR.

6

f) Script d' insertion des données pour la table Médicaments

MEDICAMENTS : med_depotLegal, med_nomCommercial, med_composition, med_effets, med_contreIndic

g) Script d'insertion des données pour la table EchantillonMedic

ECHANTILLONMEDIC : ech_num, ech_prixHT, med_DepotLegal

h) Script d'insertion des données pour la table Proposer

PROPOSER : v_num, ech_num

7

i) Modification de la table Praticien

Pour avoir un affichage cohérent à l'une des requêtes que nous réaliseront par la suite, il nous a fallut ajouter le

champ pra_adresse dans la table Praticien.

j) Script d'insertion des données dans la table Praticien.

PRATICIEN : pra_num, pra_nom, pra_adresse, pra_coef_notoriete

8

k) Script d'insertion des données pour la table Spécialités

SPECIALITES : spe_code, spe_libelle

l) Modification de la table Posséder

Pour améliorer la compréhension de la base de donnée, nous avons décider de changer le nom de la table Posséder par Praticien_specialites car, c'est dans cette table que nous aurons les spécialités de chaque praticien. Requête pour modifier un nom de table :

m) Script d'insertion des données pour la table Posséder

PRATICIEN_SPECIALITES : pra_num, spe_code

9

d) Les requêtes SQL

Requête n°1 : Afficher les visiteurs classés par ordre alphabétique.

Résultat :

10

Requête n° 2 : Afficher le nom et l’adresse des praticiens qui ont reçu une visite ces deux dernières années

Résultat :

11

Requête n° 3 : Augmenter les prixHT des échantillons inferieurs à 1,5 euros de 5%

Requête n° 4 : Afficher les noms commerciaux des médicaments qui n’ont pas été proposé aux praticiens

Résultat :

12

Seconde phase : Déploiement de la base de données Le laboratoire Galaxy Swiss Bourdin souhaite, que la nouvelle base de donnée soit déployée sur un serveur

ayant l'adresse IP 192.168.1.12. De plus, il souhaite accéder au SGBD sur un poste distant ayant pour adresse IP

192.168.1.25.

Pour réaliser le déploiement de la nouvelle base de donnée nous avons pris la décision de virtualiser le serveur

WAMP sur une machine virtuelle que nous avons configuré afin de répondre aux attentes du laboratoire

pharmaceutique. Nous avons également besoin d'une seconde machine virtuelle qui fera office de poste client.

Sur ce poste nous feront en sorte que la base de donnée y soit accessible.

1. Création des machines virtuelles.

Nom pour la 1ère machine virtuelle

13

Nom pour la 2nd machine virtuelle

14

2. Installation du système d'exploitation Windows 7

Suite à la création de la machine virtuelle faisant office de poste client nous avons procéder à l'installation du

système d'exploitation Windows Seven :

15

3. Installation du système d'exploitation Windows Serveur 2008 R2

Suite à la création de la machine virtuelle faisant office de serveur nous avons procéder à l'installation du

système d'exploitation Windows serveur 2008 R2 :

16

Machine Virtuelle n°1

Machine Virtuelle n°2

17

Une fois que l'installation de Windows 7 fut achevé, nous avons procéder à la configuration des deux machine

virtuelle pour permettre le déploiement de la base de donnée.

4. Configuration des machines virtuelles.

a) Configuration du réseau.

Pour permettre une communication entre le poste et client et le serveur de base de donnée il nous à fallut tout

d'abord placer ces deux machines virtuelles sur le même réseau.

Il faut réaliser cette manipulation sur

les deux machines virtuelles.

Sur les machines virtuelles :

Le laboratoire Galaxy Swiss Bourdin souhaite rendre accessible sa nouvelle base de donnée sur un poste possédant l'adresse IP 192.168.1.25. De plus il souhaite déployé leur base de donnée sur un serveur ayant pour adresse IP 192.168.1.12

Il faut dans un premier ouvrir le panneau configuration puis, aller dans l'onglet Réseau et Internet. Ensuite, il faut se rendre dans le

centre Réseau et Partage (cf image 2).

Une fois arrivé sur la page, il faut

regarder sur le menu de gauche et

cliquer sur "modifier les paramètre

de la carte" (cf image 3).

Enfin, faite un clique droit sur la carte

réseau, aller dans les propriétés puis,

procéder à la configuration du

protocole IPV4 en attribuant aux

machines virtuelles une adresse IP

(cf images 4, 5, 6 et 7).

18

Machine Virtuelle : Serveur Machine Virtuelle : Poste client

19

b) Configuration du Pare Feu Windows

Pour permettre la communication entre nos deux machines virtuelles, il nous a fallut procéder à la

désactivation du pare feu Windows.

c) Mise en place du serveur WAMP.

Machines virtuelles : Serveur BDD .

Pré-requis :

Avant de procéder à l'installation du serveur WAMP, il faut au préalable installer les Tools sur la machine

virtuelle et créer un dossier de partage pour récupérer le logiciel sur un PC ayant une connexion physique.

Création du Dossier de partage :

Tout d'abord, télécharger la version 2.2 de Wamp sur le site officiel http://www.wampserver.com/ . Ensuite, pour créer le dossier de partage il faut aller dans les paramètre des deux machines virtuelles (cf images).

20

Installation du logiciel :

Suite à la création du dossier de partage nous avons procéder à l'installation du logiciel WAMP.

Sélectionner le chemin ou se trouve l'exécutable

du logiciel WAMP

21

22

d) Configuration du serveur WAMP

Pour pouvoir rendre accessible une base de donnée depuis n'importe quel poste, il suffit tout simplement de

modifier le fichier de configuration phpmyadmin.conf sur la machine virtuelle faisant office de serveur.

Les étapes :

Pour permettre l'accès à la base de donnée à

partir d'un poste distant, il faut modifier les deux

lignes encadré par :

23

Une fois la modification faite, il faut enregistrer le fichier puis procéder au redémarrage du serveur WAMP.

Par défaut, le logiciel de gestion de base de donnée PhpMyAdmin compris dans le serveur WAMP se connecte

immédiatement et n'a pas de système d'authentification.

Pour activer ce système d'authentification, il faut tout d'abord attribuer un mot de passe Root et modifier une

ligne dans le fichier de configuration config.inc.php qui se trouve à l'url suivante :

C:\wamp\apps\phpmyadmin3.5.1

Une fois les modification faites, il faudra penser à redémarrer les services de Wamp (cf image précédente).

Les lignes de commandes à remplacer

24

Depuis le poste client, saisissez dans la barre de recherche du navigateur internet l'adresse ip du serveur suivi

du logiciel de gestion de la base de donnée (PhpMyAdmin).

Si les machines virtuelles se situent sur le même réseau et que toutes les configurations requises ont été

effectuées, vous devriez atterrir sur la page d'authentification de PhpMyAdmin.

e) Création des comptes utilisateurs.

Le laboratoire Galaxy Swiss Bourdin, désire que la base de donnée soit accessible à trois type de personnes.

Pour cela, l'entreprise nous a demander de créer trois comptes utilisateurs ayant tous des droits spécifiques .

Pour procéder à la création des trois comptes utilisateurs, nous allons utiliser le client MYSQL qui est intégré

dans le logiciel WAMP.

25

Le compte Administrateur :

Le compte Praticien :

Le compte Visiteur :

26

f) Installation d'un serveur SSH.

Pour pouvoir utilisé le client MYSQL fournit avec le package du serveur WAMP, nous avons décidé de créer un

serveur SSH sur la machine virtuelle faisant office de serveur pour pouvoir accéder à distance au client MYSQL

et s'y connecter.

Voici les étapes pour l'installation d'un serveur SSH :

Dans un premier il faut télécharger le logiciel FreeSSHd sur le site officiel www.freesshd.com/ puis, l'installer

sur la machine virtuelle.

27

Une fois l'installation fini , nous avons créer un compte utilisateur pour pouvoir se connecter à distance au

serveur SSH.

g) Connexion au client MYSQL.

Pour se connecter au client MYSQL qui se situe sur le serveur, il faut avant tout se connecter au serveur SSH

grâce au client SSH Putty qui est disponible à l'adresse suivante : http://www.putty.org/

Les étapes :

1) Installer putty, puis configurer le avec les informations d'identification du serveur SSH.

28

2) Enregistrer la session puis connecter vous à l'aide du compte utilisateur que nous avons créer sur le serveur

SSH.

3) Accéder au client MYSQL grâce à la commande suivante.

29

3) Connecter vous à un compte MYSQL grâce à la commande suivante.

Troisième phase : Evolution de la base de donnée

a) Création de la table Régions

Suite à une nouvelle analyse, le laboratoire Galaxy Swiss Bourdin souhaite répartir ses visiteurs par régions.

Pour réaliser cette nouvelle demande nous avons procéder à la création d'une nouvelle table dans laquelle nous

avons inscrit une liste de régions.

REGIONS : reg_code, reg_libelle

30

b) Déclaration de la clé étrangère.

Suite à la création de la table Régions, il nous a fallut déclarer la relation qu'elle entretien avec la table Visiteurs.

Pour cela, nous avons ajouter un champ reg_code dans la table Visiteurs puis nous avons créer un index sur

celui-ci. Enfin, nous avons déclarer ce champs en tant que clé étrangère.

c) Script d'insertion des données pour la table Régions

31

d) Modification du script d'insertion pour la table Visiteurs

32

e) Création de la table Diplômes

Suite à une étude marketing menée par le laboratoire pharmaceutique, le groupe de travail souhaite enregistrer

les diplômes des praticiens. Pour réaliser cette nouvelle demande nous avons du procéder à la création d'une

nouvelle table dans laquelle nous avons inscrit des diplômes au hasard.

DIPLOMES : dip_code, dip_libelle, #spe_code

f) Déclaration de la clé étrangère

Suite à la création de la table Diplômes, il nous a fallut déclarer la relation qu'elle entretien avec la table

Spécialités. Pour cela, nous avons ajouter un champ spe_code dans la table diplômes. Puis, nous avons créer un

index sur celui-ci. Enfin nous avons déclarer ce champ en tant que clé étrangère.

g) Script d'insertion des données pour la table Diplômes

33

h) Création de la table Praticien_diplomes.

PRATICIEN_DIPLOMES : #pra_num, #dip_code

Pour pouvoir attribuer des diplômes à chaque praticien, nous avons créer une nouvelle table dans laquelle nous

aurons la liste des diplômes que possèdent ces derniers.

i) Déclaration de la clé étrangère.

Suite à la création de la table Diplômes, il nous a fallut déclarer la relation qu'elle entretien avec la table

Spécialités. Pour cela, nous avons ajouter un champ spe_code dans la table diplômes. Puis, nous avons créer un

index sur celui-ci. Enfin nous avons déclarer ce champ en tant que clé étrangère.

j) Script d'insertion des données pour la table Praticien_diplomes.

34

h) Modification de la table EchantillonMedic

Afin de réaliser le bilan de l'opération commerciale, le laboratoire Galaxy Swiss Bourdin souhaite que chaque

médicaments soient associés à un taux de TVA. Pour réaliser cette demande, nous avons procéder à la

modification de la table EchantillonMedic en y ajoutant un champ ech_TxTva.

i) Modification du script d'insertion pour la table EchantillonMedic

j) Les Requêtes

Requête n° 1 : Réaliser une vue qui permette d’afficher la somme des prix TTC des échantillons par visites.

Accès aux visiteurs :

35

Résultat :

Requête n° 2 : Afficher le praticien le plus diplômé

Résultat :

36

Requête n°3 : Afficher la moyenne des sommes des prix TTC des échantillons par visites

Résultat :

Requête n°4 : Afficher les visiteurs par région qui ont effectué plus de 5 visites.

Résultat :

Requête n°5 : Afficher le prix TTC maximum de toutes les visites réalisés (de tous les échantillons réalisés).

Résultat :

37

Conclusion Pour conclure sur ce projet, voici le script final permettant la création des tables ainsi que le modèle relationnel final de la nouvelle base de données.

38