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 : 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.

Deuxième phase : Création de la base de données (annexes 2 à 5)

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 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 %

Troisiè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.

Quatrième phase : Evolution de la base de données (annexe 5)

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. La liste des régions se trouve dans le fichier MDB. Effectuer les changements nécessaires et réaliser le schéma relationnel 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. Réaliser les demandes en détaillant votre projet par tâches et leurs répartitions au sein du groupe sous Framaboard (annexe 9) puis le mettre à jour à chaque séance.
  3. Réaliser une documentation technique en s’appuyant sur l’annexe 6.
  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. Compléter la fiche type E4 et le tableau de synthèse du portefeuille de compétences (annexe 7 et document joint).
  6. S’appuyer sur l’annexe 8 pour préparer un diaporama pour une présentation structurée de 20 minutes de votre solution qui sera évaluée par GSB lors de la prochaine réunion.

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 :

https://frebourg.es/wp-content/uploads/bd.zip
https://frebourg.es/wp-content/uploads/gsb.txt
https://frebourg.es/wp-content/uploads/E4_besoin.docx

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

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

Annexe 3–Schéma physique de données

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 – Migration d’une base de données Access vers une base de données MySQL

Dans votre système d’exploitation

  1. Télécharger et installer le driver odbc pour Mysql (Rerchercher « odbc driver mysql », attention à la version, plus souvent 32b)
  2. Sélectionner source ODBC avec la bonne version (32b)
  3. Onglet « pilote ODBC », si MySQL ODBC est présent, c’est que le driver est bien installé.
  4. Onglet « sources de données utilisateurs » -> ajouter -> MySQL ODBC X.X driver »->terminer
  5. Remplir les données nécessaires en précisant par exemple comme nom projet_SQL.

Sous Access

  1. option->base de données active->afficher le volet de navigation
  2. sur chaque table, exporter->base de données ODBC->ok->source de données machine->projet_SQL

Sous MySQL

  1. Vérifier que les tables et les données se sont bien insérées

Annexe 6 – Présentation d’une documentation technique

 

Annexe 7 – Description d’une situation professionnelle

cf document en bas de page

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

Annexe 9 – FRAMABOARD

Framaboard est un service en ligne libre de gestion des tâches.

Pour vous connecter au service :

  1. https://framaboard.org/
  2. Procéder à une inscription en ligne,
  3. Lisez la documentation,
  4. Commencez à gérer vos tâches.

Il est conseillé aux enseignants de coordonner les inscriptions (éviter les inscriptions individuelles, préférer les inscriptions par groupe) afin de protéger au mieux les données personnelles de nos étudiants dans le cadre de leur formation.

Annexe 10 – Exemple de topologie réseau

  1. Conformément au référentiel du BTS SIO, le contexte doit être conforme au cahier des charges national en matière d’environnement technologique dans le domaine de spécialité correspondant au parcours du candidat.
  2. En référence à la description des activités des processus prévue dans le référentiel de certification.
  3. Conformément au référentiel du BTS SIO « Dans tous les cas, les candidats doivent se munir des outils et ressources techniques nécessaires au déroulement de l’épreuve. Ils sont seuls responsables de la disponibilité et de la mise en œuvre de ces outils et ressources. Les candidats qui n’en sont pas munis sont pénalisés dans les limites prévues par la grille d’aide à l’évaluation proposée par la circulaire nationale d’organisation. ». Il s’agit par exemple des identifiant, mot de passe, URL d’un espace de stockage et de la présentation de l’organisation du stockage.