Contexte

Le laboratoire GalaxySwiss Bourdin (GSB) emploie des visiteurs chargés de prospecter des praticiens (pharmaciens de ville ou hospitaliers, des médecins de ville ou hospitaliers, des personnels de santé et centres paramédicaux) afin que ceux-ci puissent faire découvrir les produits vendus par le laboratoire auprès de leurs patients.

A cet effet, les visiteurs sont chargés de couvrir une région et de se déplacer auprès des praticiens afin de leur distribuer gratuitement des échantillons des produits du laboratoire.

Pour gérer l’ensemble de ses visites, le laboratoire dispose actuellement d’une base de données mais elle ne le satisfait pas pleinement. Vous êtes chargé de mettre en place cette nouvelle base de données (BDD) sous MySQL ou MariaDB.

Il vous appartient de réaliser les insertions nécessaires au cas où les fichiers disponibles ne contiennent pas suffisamment d’informations pour avoir des affichages cohérents.

Première phase : Création de la base de données

Après une analyse des besoins, le chef de projet en charge du déploiement de la nouvelle solution vous fournit un schéma de la BDD souhaitée. Ce schéma est sous forme de modèle conceptuel et de schéma logique de données pour plus de facilité d’implémentation. Le deuxième schéma permet de faire apparaître clairement les tables nécessaires avec les clefs étrangères.

La migration est laborieuse. L’ancien système contenant les données ne permet pas d’export sous SQL. On vous fournit un fichier MDB de l’ancienne base de données. Les tables ne correspondent pas aux besoins. Toutefois certaines données nous intéressent. Les listes des visiteurs, des médicaments, des praticiens, des types de praticiens et des visites sont dans ce fichier. Il vous appartient de récupérer ces données et de les insérer dans le nouveau SGBDR. Les numéros de sécurité sociale correspondant à la clef primaire de la table visiteur ne sont pas dans ce fichier mais dans gsb_identifiant_visiteur.xlsx

Un stagiaire a réalisé une partie de la BDD via un script SQL fournit en annexe. Il n’a pas eu le temps de finaliser la création des tables.

Pour planifier les différentes visites des praticiens, GSB souhaite obtenir :

  • L’identifiant, le nom et la ville des visiteurs classés par ordre alphabétique
  • le nom et l’adresse des praticiens qui ont reçu une visite ces deux dernières années
  • les noms commerciaux des médicaments qui n’ont pas été proposés aux praticiens même s’il n’existe pas d’échantillon correspondant.

L’augmentation récente des matières premières permettant l’élaboration de certains excipients doit être répercutée sur les prix. Pour cela, GSB souhaite :

  • une augmentation des prix HT des échantillons inférieurs à 1,5 euros de 5 %

Deuxième phase : Déploiement

La solution devra être déployée sur un serveur distant. Les visiteurs pourront accéder aux données sans installation sur leurs postes.

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

Après une nouvelle analyse, GSB souhaite répartir ses visiteurs par région. Pour chaque région, on souhaite enregistrer un code région et un libellé. Un visiteur peut être en charge de plusieurs régions et une région peut accueillir plusieurs visiteurs. On réalisera la table région nécessaire. Réaliser le diagramme de classe correspondant. Attribuer au hasard les visiteurs dans une ou plusieurs régions.

Suite à une étude marketing, le groupe de travail souhaite enregistrer les diplômes des praticiens pour mieux cibler les échantillons proposés. Les diplômes correspondent aux spécialités dans l’ancienne base de données. Un praticien peut posséder plusieurs diplômes. Intégrez cet élément au SGBDR et attribuer au hasard des diplômes aux praticiens.

Pour réaliser le bilan de l’opération commerciale, GSB souhaite calculer le coût des échantillons de chaque visite. Ce coût doit tenir compte de la TVA qui est variable selon le médicament. Il faut donc enregistrer un taux de TVA pour chaque médicament. Le chef de projet vous fournis les anciens codes TVA à reporter dans la nouvelle base de données. Les médicaments possédaient déjà ces codes.

Identifiant, valeur :

(1, '0.20'), (2, '0.10'), (3, '0.05'), (4, '0.02');


Pour planifier sa prochaine campagne de publicité auprès des praticiens, GSB souhaite s’appuyer sur les données existantes. Elle aura notamment besoin d’une meilleure vision sur la répartition des visites au sein des régions et le coût de celle-ci.


Pour cela, elle nécessite d’obtenir :


    

  • le nombre de visite par région ;
    • 

  • les visiteurs qui ont effectué plus de 5 visites;
    • 

  • une vue PRIX_TTC_VISITE permettant d’afficher la somme des prix TTC des échantillons par visites. Cette vue définira le prix d’une visite pour la partie fourniture (on ne prend pas en compte le coût du personnel) ;
    • 

  • la moyenne du prix des visites pour la partie fourniture ;
    • 



Pour convaincre le plus grand nombre de praticien d’utiliser ses produits, GSB souhaite cibler les plus influents. Pour cela, nous avons besoin de connaitre :


    

  • le praticien le plus diplômé ;
    • 

  • le praticien le plus diplômé par type de praticien;
    • 



Travail à faire :


    

  1. Recenser les demandes pour l’ensemble des phases.
    2. 

  2. Réaliser les demandes en détaillant votre projet par tâches et leurs répartitions au sein du groupe sous Trello ou puis le mettre à jour à chaque séance.
    3. 

  3. Réaliser une documentation technique.
    4. 

  4. Déposer votre documentation sur la plateforme collaborative permettant de garnir votre portefeuille de compétences. Il devra être accompagné du script complet de la base de données.
    5. 

  5. Compléter la fiche type E4 et le tableau de synthèse du portefeuille de compétences (annexe 7 et document joint).
    6. 

  6. S’appuyer sur l’annexe 8 pour préparer un diaporama pour une présentation structurée de 20 minutes de votre solution.
    7. 



Cette présentation devra être structurée c’est- à-dire introduite, guidée et conclue. Elle impliquera l’ensemble du groupe et sera l’occasion de présenter les différentes tâches réalisées, de justifier vos choix techniques et de dévoiler les résultats obtenus.


Cette présentation permettra d’accéder grâce à des liens hypertexte aux différents travaux réalisés : base de donnes, requêtes, documentation technique, fiche E4 et tableau de synthèse du portefeuille de compétences complété pour chaque membre du groupe de travail.


Documents d’accompagnements :


Annexe 1–Visiteurs et export de la base de données


gsb_identifiant_visiteur


gsb


Annexe 2–Schéma conceptuel de données (MCD)


word image 8


Annexe 3–Schéma physique de données


word image 9


Annexe 4–Script partiel de création des tables de la base de données


#------------------------------------------------------------

# Script MySQL.

#------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `echantillon` (

`ECH_NUM` int(11) NOT NULL,

`ECH_PRIXHT` double DEFAULT NULL,

`MED_DEPOTLEGAL` varchar(9) DEFAULT NULL,

PRIMARY KEY (`ECH_NUM`),

KEY `MED_DEPOTLEGAL` (`MED_DEPOTLEGAL`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `famille` (

`FAM_CODE` varchar(3) NOT NULL,

`FAM_LIBELLE` varchar(67) DEFAULT NULL,

PRIMARY KEY (`FAM_CODE`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `medicament` (

`MED_DEPOTLEGAL` varchar(9) NOT NULL,

`MED_NOMCOMMERCIAL` varchar(19) DEFAULT NULL,

`FAM_CODE` varchar(3) DEFAULT NULL,

`MED_COMPOSITION` varchar(80) DEFAULT NULL,

`MED_EFFETS` varchar(194) DEFAULT NULL,

`MED_CONTREINDIC` varchar(236) DEFAULT NULL,

PRIMARY KEY (`MED_DEPOTLEGAL`),

KEY `FAM_CODE` (`FAM_CODE`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `proposer` (

`V_NUM` int(11) NOT NULL,

`ECH_NUM` int(11) NOT NULL,

PRIMARY KEY (`V_NUM`,`ECH_NUM`),

KEY `ECH_NUM` (`ECH_NUM`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `visite` (

`VIS_NUMSECU` varchar(4) DEFAULT NULL,

`V_NUM` int(11) NOT NULL,

`V_DESCRIPTION` varchar(90) DEFAULT NULL,

`V_DATE` date NOT NULL,

PRIMARY KEY (`V_NUM`),

KEY `VIS_NUMSECU` (`VIS_NUMSECU`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `visiteur` (

`VIS_NUMSECU` varchar(4) NOT NULL,

`VIS_NOM` varchar(15) DEFAULT NULL,

`VIS_ADRESSE` varchar(31) DEFAULT NULL,

`VIS_CP` varchar(5) DEFAULT NULL,

`VIS_VILLE` varchar(19) DEFAULT NULL,

`VIS_DATE_EMBAUCHE` varchar(10) DEFAULT NULL,

PRIMARY KEY (`VIS_NUMSECU`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `echantillon`

ADD CONSTRAINT `echantillon_ibfk_1` FOREIGN KEY (`MED_DEPOTLEGAL`) REFERENCES `medicament` (`MED_DEPOTLEGAL`);

ALTER TABLE `medicament`

ADD CONSTRAINT `medicament_ibfk_1` FOREIGN KEY (`FAM_CODE`) REFERENCES `famille` (`FAM_CODE`);

ALTER TABLE `proposer`

ADD CONSTRAINT `proposer_ibfk_1` FOREIGN KEY (`V_NUM`) REFERENCES `visite` (`V_NUM`),

ADD CONSTRAINT `proposer_ibfk_2` FOREIGN KEY (`ECH_NUM`) REFERENCES `echantillon` (`ECH_NUM`);

ALTER TABLE `visite`

ADD CONSTRAINT `visite_ibfk_2` FOREIGN KEY (`VIS_NUMSECU`) REFERENCES `visiteur` (`VIS_NUMSECU`);


Annexe 5 – Présentation d’une documentation technique




Annexe 6 – Description d’une situation professionnelle


cf document


Annexe 7 – Grille d’évaluation de l’intervention orale




Annexe 8 – Exemple de topologie réseau




—————————— en reference avant suppression ———————


Première phase : Analyse du projet (annexe 1)


GSB souhaite, dans un premier temps, vérifier l’adéquation du projet à ses besoins et à la législation française.


A cet, effet, le responsable des Ressources Humaines est chargé(e) d’élaborer une note de synthèse comportant :


    

  • D’une part, une analyse du projet de mise en place d’une nouvelle BDD  :

      

    • Nature de la décision prise : stratégique ? tactique ? opérationnelle ?
      • 

    • Coût de la décision,
      • 

    • Avantages à retirer du projet,
      • 

    • Risques à prendre en compte pour que le projet soit une réussite.
      • 

    • Conclusion
      • 

    

    • 

  • D’autre part, une analyse des données contenues dans la base de données afin d’identifier la présence éventuelle de données à caractère personnel. Le DRH souhaite connaître les modalités de protection qui entourent ces données et les sanctions encourues par GSB si les données ne sont pas correctement sécurisées.
    • 



Le DRH sollicite l’aide du service informatique sensibilisé à ces problématiques. Vous vous proposez d’insérer vos réponses dans la documentation technique à fournir pour la prochaine réunion.


Annexe 1 –Recueil de documentation Web


1/ La décision :



2/ Le coût d’une base de données :



3/ Avantages / inconvénients :



4/ La protection des données à caractère personnel :



5/ Défaut de sécurisation des données :



6/ L’utilisation du numéro de sécurité sociale