U.A.7 : ELABORATION D'UN MPD

# U.A.7 : ELABORATION D'UN MPD ## Objectifs et prérequis Ce chapitre vise à permettre à l’élève de Première de comprendre et de réaliser concrètement l’élaboration d’un Modèle Physique de Données (MPD) à l’aide d’un logiciel de modélisation, de créer une base de données en mode graphique à partir de ce MPD, et d’exécuter des requêtes simples pour interroger ces données. Il s’inscrit dans la continuité du Modèle Conceptuel de Données (MCD) et prépare à l’exploitation opérationnelle des systèmes d’information. Les prérequis sont : la maîtrise du MCD (entités, associations, attributs, cardinalités), la connaissance des types de données simples (entier, chaîne, date, booléen), et une familiarité de base avec l’environnement informatique (souris, clavier, navigation dans des menus). Aucun langage SQL avancé n’est attendu à ce stade. ## Introduction L’élaboration d’un Modèle Physique de Données (MPD) constitue l’étape concrète où le modèle abstrait du MCD est traduit en une structure directement implémentable dans un Système de Gestion de Base de Données (SGBD). Au Cameroun, comme dans de nombreux pays africains, cette compétence est essentielle pour la conception de systèmes d’information adaptés aux contextes locaux : gestion des dossiers élèves dans les établissements scolaires, suivi des patients dans les centres de santé, ou gestion des stocks dans les petites entreprises commerciales. Le passage du MCD au MPD implique des choix techniques : transformation des entités en tables, des attributs en champs, des associations en clés étrangères, et prise en compte des contraintes d’intégrité. L’utilisation d’un logiciel de modélisation graphique (comme MySQL Workbench, DbSchema, ou même des outils éducatifs tels que Draw.io ou Lucidchart adaptés au modélisation de bases de données) permet de visualiser cette transformation et de générer automatiquement le code SQL de création. La création en mode graphique simplifie l’accès pour les débutants, tandis que l’exécution de requêtes (sélection, insertion, mise à jour) permet de vérifier la pertinence du modèle et d’initier l’élève à l’interrogation des données. Ce chapitre privilégie une approche progressive : modélisation → création → interrogation, en s’appuyant sur des exemples camerounais pertinents. ## I. Représentation d'un MPD grâce à un logiciel de modélisation ### Contexte Le Modèle Physique de Données (MPD) décrit comment les données sont stockées physiquement dans une base de données : tables, champs, types de données, clés primaires et étrangères, contraintes d’intégrité. Il dérive directement du Modèle Conceptuel de Données (MCD) après validation de sa pertinence fonctionnelle. Au Cameroun, les établissements scolaires utilisent de plus en plus des logiciels de gestion (comme Gesco, Scolarité+, ou des solutions open source adaptées) qui reposent sur des MPD bien conçus. La modélisation graphique permet de représenter visuellement cette structure, facilitant la compréhension et la communication entre concepteurs, développeurs et utilisateurs finaux (enseignants, administratifs). ### Définitions et faits clés > Un Modèle Physique de Données (MPD) est une représentation détaillée de la structure d’une base de données, spécifiant les tables, les champs, leurs types, les clés primaires et étrangères, et les contraintes d’intégrité, destiné à être implémenté dans un SGBD. > Une entité du MCD devient une table dans le MPD. > Un attribut simple devient un champ (ou colonne) dans la table correspondante. > Une association binaire du MCD devient généralement une clé étrangère dans une des tables liées, selon les cardinalités. > La clé primaire identifie de manière unique chaque enregistrement (ligne) d’une table. > Une contrainte d’intégrité référentielle garantit que les valeurs d’une clé étrangère correspondent à des valeurs existantes dans la clé primaire d’une autre table. > Un logiciel de modélisation graphique permet de créer, visualiser et modifier un MPD sous forme de diagrammes, et souvent de générer le code SQL correspondant. ### Étapes de la représentation du MPD 1. **Analyse du MCD validé** : Identifier chaque entité, chaque attribut (simple ou composé), chaque association et ses cardinalités. 2. **Transformation des entités** : Chaque entité devient une table. Le nom de la table est généralement au singulier ou au pluriel (convention à définir ; ici, nous utiliserons le singulier pour cohérence avec certains SGBD). 3. **Transformation des attributs** : Chaque attribut simple devient un champ de la table. On précise son nom, son type de données (ex : ENTIER, CHAINE(50), DATE, BOOLEEN), et s’il peut être nul (NULL) ou non (NOT NULL). 4. **Définition de la clé primaire** : Pour chaque table, choisir un attribut (ou un groupe d’attributs) qui garantit l’unicité. Souvent, un identifiant artificiel (ex : ID_Eleve, ID_Cours) est ajouté s’il n’existe pas d’attribut naturellement unique. 5. **Transformation des associations** : Pour chaque association binaire : * Si cardinalité est (1,1) ou (0,1) du côté A et (1,N) du côté B → ajouter la clé primaire de A comme clé étrangère dans la table de B. * Si cardinalité est (1,N) des deux côtés → créer une table d’association contenant les clés primaires des deux entités comme clés étrangères (et souvent une clé primaire composée). * Si cardinalité est (N,M) → même traitement que (1,N) des deux côtés : table d’association. 6. **Ajout des contraintes** : Préciser les NOT NULL, les valeurs par défaut (DEFAULT), les contrôles de validité (CHECK si le logiciel le permet, sinon à gérer applicativement). 7. **Vérification** : S’assurer que le MPD respecte les règles de normalisation (au moins la 1NF, idéalement 2NF ou 3NF pour éviter les redondances) et qu’il répond aux besoins fonctionnels du MCD. ### Exemple concret camerounais : Gestion des élèves dans un lycée Considérons un MCD simplifié pour la gestion scolaire dans un lycée de Yaoundé : * Entité **ELEVE** : {NumInsc (identifiant national), Nom, Prenom, DateNaissance, Sexe, Adresse, ID_Classe} * Entité **CLASSE** : {ID_Classe, Niveau (ex : 6ème, 5ème), Serie (ex : A, B, C, D, TI), AnneeScolaire} * Association **appartient à** : Un élève appartient à une classe (cardinalité (1,1) du côté élève, (1,N) du côté classe). **Transformation en MPD :** * Table **ELEVE** : * NumInsc : CHAINE(20), PRIMARY KEY (identifiant national unique) * Nom : CHAINE(50), NOT NULL * Prenom : CHAINE(50), NOT NULL * DateNaissance : DATE, NOT NULL * Sexe : CHAINE(1) ('M' ou 'F'), NOT NULL * Adresse : CHAINE(100), NULL * ID_Classe : ENTIER, NOT NULL, FOREIGN KEY référant à CLASSE(ID_Classe) * Table **CLASSE** : * ID_Classe : ENTIER, PRIMARY KEY (clé artificielle, auto-incrémentée si possible) * Niveau : CHAINE(10), NOT NULL * Serie : CHAINE(5), NOT NULL * AnneeScolaire : CHAINE(9) (ex : "2023-2024"), NOT NULL * La clé étrangère ID_Classe dans ELEVE établit le lien avec la table CLASSE, respectant la cardinalité (un élève appartient à une seule classe, une classe contient plusieurs élèves). ### Vocabulaire à retenir - Modèle Physique de Données (MPD) - Table - Champ (ou colonne) - Type de données - Clé primaire - Clé étrangère - Contrainte d’intégrité référentielle - Cardinalité - Normalisation - Logiciel de modélisation graphique ### Exemple de sujet type examen avec plan **Sujet :** Voici un extrait du MCD validé pour la gestion d’une coopérative agricole camerounaise spécialisée dans la production de cacao : * Entité **PRODUCTEUR** : {ID_Prod, Nom, Prenom, Village, Téléphone} * Entité **RECOLTE** : {ID_Recolte, DateDebut, DateFin, QuantiteKg, Qualité} * Entité **PARCELLE** : {ID_Parcelle, Localisation, SuperficieHa} * Association **réalise** : Un producteur réalise une ou plusieurs récoltes (cardinalité (1,N) du côté producteur, (1,1) du côté récolte). * Association **localise** : Une récolte est localisée sur une parcelle (cardinalité (1,1) du côté récolte, (1,N) du côté parcelle). **Questions :** 1. Expliquez en quoi consiste le Modèle Physique de Données (MPD) et son rôle dans la conception d’un système d’information. (4 points) 2. À partir du MCD ci-dessus, représentez le MPD correspondant en détaillant pour chaque table : le nom, les champs avec leur type de données, et la clé primaire. Indiquez clairement les clés étrangères et leurs références. (10 points) 3. Nommez deux logiciels de modélisation graphique pouvant être utilisés pour représenter ce MPD. (2 points) **Plan attendu :** * Partie 1 : Définition du MPD (objet, lien avec MCD, objectif d’implémentation). * Partie 2 : Transformation détaillée : * Table PRODUCTEUR : ID_Prod (ENTIER, PK), Nom (CHAINE), Prenom (CHAINE), Village (CHAINE), Téléphone (CHAINE). * Table RECOLTE : ID_Recolte (ENTIER, PK), DateDebut (DATE), DateFin (DATE), QuantiteKg (ENTIER), Qualité (CHAINE), ID_Prod (ENTIER, FK → PRODUCTEUR.ID_Prod). * Table PARCELLE : ID_Parcelle (ENTIER, PK), Localisation (CHAINE), SuperficieHa (REEL ou ENTIER selon précision). * Table issue de l’association *localise* : Comme la récolte est (1,1) et parcelle (1,N), on ajoute ID_Parcelle comme FK dans RECOLTE. Donc RECOLTE contient aussi : ID_Parcelle (ENTIER, FK → PARCELLE.ID_Parcelle). (Pas besoin de table d’association séparée ici). * Partie 3 : Exemples : MySQL Workbench, DbSchema, Draw.io (en mode base de données), Lucidchart. ## II. Création d'une base de données en mode graphique ### Contexte Une fois le MPD représenté (sur papier ou dans un logiciel de modélisation), l’étape suivante consiste à le concrétiser en créant effectivement la base de données dans un SGBD. Le mode graphique permet de réaliser cette création à l’aide d’interfaces visuelles (menus, boîtes de dialogue, glisser-déposer) plutôt que d’écrire directement du code SQL. Cette approche est particulièrement adaptée aux apprenants en Première, car elle réduit la barrière syntaxique initiale et permet de se concentrer sur la logique de la structure des données. Au Cameroun, de nombreux SGBD utilisés dans l’éducation ou les petites entreprises (comme MySQL avec son Workbench, ou des versions éducatives de SQL Server ou Access) offrent des modes graphiques puissants pour créer des tables, définir des champs, et établir des relations. ### Définitions et faits clés > Créer une base de données en mode graphique consiste à utiliser l’interface visuelle d’un SGBD ou d’un outil de modélisation pour définir les tables, les champs, leurs types, les clés primaires et étrangères, et les contraintes, sans écrire manuellement du code SQL de définition (DDL). > Le mode graphique génère généralement automatiquement le code SQL correspondant en arrière-plan. > Une table dans le mode graphique est représentée sous forme d’une boîte ou d’un formulaire où l’on ajoute les champs. > Un champ est défini en spécifiant son nom, son type de données, sa longueur (si applicable), et des propriétés comme autoriser les NULL, valeur par défaut, ou être une clé primaire. > Une relation (clé étrangère) est établie en mode graphique en sélectionnant le champ enfant et en le liant au champ parent de la table de référence, souvent via un outil de connexion ou un menu déroulant. > Le SGBD vérifie alors la cohérence des types et applique la contrainte d’intégrité référentielle lors des opérations de modification des données. ### Étapes de la création en mode graphique (exemple avec MySQL Workbench) 1. **Création du schéma (base de données)** : Dans l’interface, choisir l’option pour créer un nouveau schéma. Donner un nom (ex : `gestion_lycee_cm`), choisir le jeu de caractères (souvent `utf8mb4` pour supporter le français et les langues africaines), et valider. 2. **Création d’une table** : Dans le schéma créé, faire un clic droit sur "Tables" → "Create Table...". Donner un nom à la table (ex : `eleve`). 3. **Définition des champs** : Dans l’éditeur de table qui s’ouvre : * Ajouter une ligne pour chaque champ. * Pour chaque ligne : saisir le nom du champ (ex : `NumInsc`), choisir le type de données dans la liste déroulante (ex : `VARCHAR(20)` pour chaîne, `INT` pour entier, `DATE` pour date), préciser la longueur si nécessaire (ex : 20 pour VARCHAR), cocher la case `PK` si c’est la clé primaire, cocher `NN` (Not Null) si le champ ne peut pas être vide, définir une valeur par défaut si besoin. 4. **Définition de la clé primaire** : Cocher la case `PK` devant le champ choisi comme identifiant unique (souvent un champ `ID` de type INT avec `AUTO_INCREMENT` coché). 5. **Création des relations (clés étrangères)** : * Aller dans l’onglet "Foreign Keys" de l’éditeur de table. * Cliquer sur le bouton pour ajouter une nouvelle clé étrangère. * Donner un nom à la contrainte (ex : `fk_eleve_classe`). * Dans le champ "Column", sélectionner le champ de la table actuelle qui sera la clé étrangère (ex : `ID_Classe`). * Dans le champ "Reference Table", sélectionner la table référencée (ex : `classe`). * Dans le champ "Reference Column", sélectionner le champ de la table référencée qui est la clé primaire (ex : `ID_Classe`). * Choisir l’action de mise à jour et de suppression (souvent `RESTRICT` ou `NO ACTION` par défaut pour empêcher la suppression d’une classe si des élèves y sont rattachés). * Valider. 6. **Enregistrement et génération du SQL** : Cliquer sur "Apply". MySQL Workbench affiche alors le script SQL généré (CREATE TABLE, ALTER TABLE pour ajouter les FK) qu’on peut examiner avant de l’exécuter définitivement sur le serveur. 7. **Vérification** : Dans l’onglet "Schemas", rafraîchir la liste. La nouvelle base de données et ses tables doivent apparaître. On peut faire un clic droit sur une table → "Table Inspector" pour voir sa structure (colonnes, types, clés). ### Exemple concret camerounais : Création de la base pour la coopérative cacao En suivant le MPD déduit précédemment pour la coopérative agricole (Producteur, Recolte, Parcelle) : 1. Créer le schéma `coop_cacao_cm`. 2. Créer la table `producteur` : * Champs : `ID_Prod` (INT, PK, AUTO_INCREMENT), `Nom` (VARCHAR(50), NN), `Prenom` (VARCHAR(50), NN), `Village` (VARCHAR(100)), `Telephone` (VARCHAR(20)). 3. Créer la table `parcelle` : * Champs : `ID_Parcelle` (INT, PK, AUTO_INCREMENT), `Localisation` (VARCHAR(150)), `SuperficieHa` (DECIMAL(5,2)) pour permettre des valeurs comme 2.50 ha. 4. Créer la table `recolte` : * Champs : `ID_Recolte` (INT, PK, AUTO_INCREMENT), `DateDebut` (DATE), `DateFin` (DATE), `QuantiteKg` (INT), `Qualite` (VARCHAR(20)), `ID_Prod` (INT, NN), `ID_Parcelle` (INT, NN). 5. Créer la relation : * Clé étrangère `fk_recolte_producteur` : `recolte.ID_Prod` → `producteur.ID_Prod`. * Clé étrangère `fk_recolte_parcelle` : `recolte.ID_Parcelle` → `parcelle.ID_Parcelle`. 6. Appliquer les changements. Le SGBD crée maintenant la base de données avec ses trois tables et ses deux contraintes d’intégrité référentielle. ### Vocabulaire à retenir - Schéma de base de données - Interface graphique (GUI) - Créer une table - Définir un champ - Type de données (VARCHAR, INT, DATE, DECIMAL, etc.) - Clé primaire (PK) - Clé étrangère (FK) - Contrainte d’intégrité référentielle - Appliquer les changements (Apply) - AUTO_INCREMENT ### Exemple de sujet type examen avec plan **Sujet :** Après avoir représenté le MPD pour la gestion d’une bibliothèque scolaire camerounaise (livres, emprunts, élèves), vous devez créer la base de données en mode graphique à l’aide de MySQL Workbench. * Table **LIVRE** : {ISBN (PK), Titre, Auteur, AnneePublication, Disponible (BOOLEEN)} * Table **ELEVE** : {ID_Eleve (PK), Nom, Prenom, Classe} * Table **EMPRUNT** : {ID_Emprunt (PK), DateEmprunt, DateRetourPrevu, DateRetourEffectif, ISBN (FK), ID_Eleve (FK)} **Questions :** 1. Citez deux avantages de la création d’une base de données en mode graphique par rapport à l’écriture directe du code SQL. (2 points) 2. Décrivez précisément les étapes, en mode graphique dans MySQL Workbench, pour créer la table `EMPRUNT` et établir ses deux relations de clé étrangère avec les tables `LIVRE` et `ELEVE`. (6 points) 3. Que se passe-t-il si vous tentez d’insérer un emprunt avec un ISBN qui n’existe pas dans la table `LIVRE`, et pourquoi ? (2 points) **Plan attendu :** * Partie 1 : Avantages : pas besoin de connaître la syntaxe SQL DDL, réduction des erreurs de frappe/syntaxe, visualisation immédiate de la structure, génération automatique du SQL. * Partie 2 : Étapes détaillées : * Ouvrir le schéma de la bibliothèque. * Clic droit sur "Tables" → "Create Table..." → Nommer la table `emprunt`. * Ajouter les champs : `ID_Emprunt` (INT, PK, AUTO_INCREMENT), `DateEmprunt` (DATE), `DateRetourPrevu` (DATE), `DateRetourEffectif` (DATE, NN autorisé si retour en cours), `ISBN` (VARCHAR(13) ou CHAR(13) selon format, NN), `ID_Eleve` (INT, NN). * Cocher PK pour `ID_Emprunt`. * Aller dans l’onglet "Foreign Keys". * Ajouter première FK : Nom `fk_emprunt_livre`, Column `ISBN`, Reference Table `livre`, Reference Column `ISBN`. * Ajouter deuxième FK : Nom `fk_emprunt_eleve`, Column `ID_Eleve`, Reference Table `eleve`, Reference Column `ID_Eleve`. * Cliquer sur "Apply", vérifier le script SQL généré, puis exécuter. * Partie 3 : Le SGBD rejette l’insertion avec une erreur d’intégrité référentielle (ex : "Cannot add or update child row: a foreign key constraint fails") parce que la valeur de ISBN fournie ne correspond à aucune clé primaire existante dans la table référencée `livre`, violant ainsi la contrainte de clé étrangère qui garantit que les références pointent vers des données existantes. ## III. Exécution des requêtes ### Contexte Après avoir conçu le MPD et créé la base de données en mode graphique, l’étape fondamentale suivante est d’interroger les données stockées pour en extraire de l’information utile. L’exécution de requêtes permet de sélectionner, insérer, modifier ou supprimer des enregistrements. Au niveau de la Première, l’accent est mis sur les requêtes de sélection simples (SELECT) et éventuellement sur les requêtes d’insertion (INSERT) pour peupler la base, car elles sont les plus directement liées à la consultation et à l’alimentation initiale du système d’information. Ces opérations peuvent être réalisées en mode graphique (via des assistants de requête) ou en écrivant du code SQL simple. L’objectif est de comprendre comment le langage interrogatoire permet d’interagir avec la structure définie par le MPD, et de vérifier que le modèle répond bien aux besoins fonctionnels initialement exprimés dans le MCD. Au Cameroun, cette compétence est utilisée par exemple pour générer des listes d’élèves par classe, suivre l’état des stocks dans une pharmacie de district, ou connaître la production totale de cacao d’une coopérative sur une période. ### Définitions et faits clés > Une requête est une instruction formulée dans un langage d’interrogation (comme le SQL) destinée à un SGBD pour récupérer, ajouter, modifier ou supprimer des données stockées dans une base de données. > Le langage SQL (Structured Query Language) est le langage standard utilisé pour interagir avec la plupart des SGBD relationnels (MySQL, PostgreSQL, SQL Server, Oracle, etc.). > Une requête de sélection (SELECT) permet de récupérer un sous-ensemble de données provenant d’une ou plusieurs tables, selon des critères spécifiés. > La clause SELECT spécifie les champs à retourner. > La clause FROM spécifie la ou les tables source(s). > La clause WHERE permet de filtrer les lignes selon une condition (ex : égalité, comparaison, intervalle). > La clause ORDER BY permet de trier le résultat selon un ou plusieurs champs (croissant ASC par défaut, décroissant DESC). > Une requête d’insertion (INSERT) permet d’ajouter un nouvel enregistrement dans une table. > Dans une requête INSERT, on spécifie la table cible, les champs à remplir (optionnel mais recommandé pour la clarté), et les valeurs correspondantes. > L’exécution d’une requête en mode graphique peut se faire via un éditeur SQL intégré au outil (comme dans MySQL Workbench) où l’on tape la requête et on appuie sur un bouton d’exécution, ou via un assistant qui construit la requête visuellement. ### Exécution d’une requête de sélection simple (SELECT) La forme de base est : `SELECT champ1, champ2, ... FROM nom_table WHERE condition;` * Pour sélectionner tous les champs : `SELECT * FROM nom_table;` (à utiliser avec prudence en production, mais utile en apprentissage). * Exemple camerounais : Dans la base `gestion_lycee_cm`, lister les noms et prénoms des élèves de sexe féminin : `SELECT Nom, Prenom FROM eleve WHERE Sexe = 'F';` * Exemple avec tri : Lister les classes par niveau puis par série, dans l’ordre alphabétique : `SELECT Niveau, Serie, AnneeScolaire FROM classe ORDER BY Niveau, Serie;` * Exemple avec intervalle : Trouver les élèves nés entre le 1er janvier 2005 et le 31 décembre 2006 : `SELECT Nom, Prenom, DateNaissance FROM eleve WHERE DateNaissance BETWEEN '2005-01-01' AND '2006-12-31';` ### Exécution d’une requête d’insertion simple (INSERT) La forme de base (en spécifiant les champs) est : `INSERT INTO nom_table (champ1, champ2, ...) VALUES (valeur1, valeur2, ...);` * Il est fortement recommandé de spécifier les champs pour éviter les erreurs si l’ordre des colonnes dans la table change ou si des colonnes sont ajoutées. * Les valeurs doivent respecter le type et les contraintes du champ (ex : pas de texte dans un champ ENTIER, respect du format DATE 'AAAA-MM-JJ'). * Exemple camerounais : Insérer un nouvel élève dans la table `eleve` de la base `gestion_lycee_cm` : `INSERT INTO eleve (NumInsc, Nom, Prenom, DateNaissance, Sexe, Adresse, ID_Classe) VALUES ('CM202400123', 'KAMGA', 'Marie', '2008-05-15', 'F', 'Quartier Mfoundi, Yaoundé', 105);` * Si on omet les champs et qu’on fournit les valeurs dans l’ordre exact de la définition de la table : `INSERT INTO eleve VALUES ('CM202400123', 'KAMGA', 'Marie', '2008-05-15', 'F', 'Quartier Mfoundi, Yaoundé', 105);` (mais cette forme est moins sûre et moins lisible). ### Exécution de requêtes sur plusieurs tables (jointures simples - concept introduit) Bien que les jointures explicites (JOIN) puissent être au programme supérieur, l’idée de récupérer des données liées via les clés étrangères est accessible en Première à travers des requêtes WHERE impliquant plusieurs tables. * Pour obtenir des informations combinées, on liste les tables dans la FROM et on spécifie la condition de lien dans le WHERE. * Exemple camerounais : Dans la base `coop_cacao_cm`, obtenir le nom du producteur et la localisation de la parcelle pour chaque récolte enregistrée : `SELECT producteur.Nom, parcelle.Localisation FROM recolte, producteur, parcelle WHERE recolte.ID_Prod = producteur.ID_Prod AND recolte.ID_Parcelle = parcelle.ID_Parcelle;` * Ici, la condition `recolte.ID_Prod = producteur.ID_Prod` établit le lien via la FK, et `recolte.ID_Parcelle = parcelle.ID_Parcelle` établit l’autre lien. Le SGBD effectue implicitement une jointure. * Cette approche montre concrètement comment les clés étrangères définies dans le MPD permettent de reconstituer l’information liée initialement modélisée dans le MCD. ### Vocabulaire à retenir - Requête SQL - Langage SQL (Structured Query Language) - SELECT (clause) - FROM (clause) - WHERE (clause) - ORDER BY (clause) - INSERT INTO (instruction) - VALUES (clause) - Champ (dans le contexte de la requête) - Condition - Jointure implicite (via WHERE) - Enregistrement (ou ligne) - Résultat de la requête ### Exemple de sujet type examen avec plan **Sujet :** La base de données `sante_district_cm` gère le suivi des patients dans un centre de santé camerounais. Elle contient les tables suivantes (structure simplifiée) : * Table **PATIENT** : {ID_Patient (PK), Nom, Prenom, DateNaissance, Sexe, Téléphone} * Table **CONSULTATION** : {ID_Consult (PK), DateConsult, Symptomes, Diagnostic, ID_Patient (FK → PATIENT.ID_Patient)} * Table **MEDICAMENT** : {ID_Med (PK), NomMed, PrixUnitaire} * Table **PRESCRIPTION** : {ID_Prescript (PK), ID_Consult (FK → CONSULTATION.ID_Consult), ID_Med (FK → MEDICAMENT.ID_Med), Quantite, Posologie} **Questions :** 1. Quelle est la différence entre une requête de sélection (SELECT) et une requête d’insertion (INSERT) ? Donnez un exemple simple de chacun en lien avec le contexte du centre de santé. (4 points) 2. Écrivez la requête SQL permettant d’obtenir la liste de tous les patients de sexe masculin, en affichant leur nom, prénom et date de naissance, triés par ordre alphabétique du nom. (3 points) 3. Écrivez la requête SQL permettant d’insérer un nouveau médicament appelé « Paracétamol 500mg » au prix unitaire de 150 FCFA dans la table MEDICAMENT. Supposons que l’ID_Med soit généré automatiquement. (3 points) **Plan attendu :** * Partie 1 : Différence : SELECT récupère des données existantes, INSERT ajoute de nouvelles données. * Exemple SELECT : `SELECT Nom, Prenom FROM patient WHERE Sexe = 'M';` (lister les patients hommes). * Exemple INSERT : `INSERT INTO medicament (NomMed, PrixUnitaire) VALUES ('Paracétamol 500mg', 150);` (ajouter un médicament). * Partie 2 : Requête de sélection avec filtre et tri : * `SELECT Nom, Prenom, DateNaissance FROM patient WHERE Sexe = 'M' ORDER BY Nom;` * (Justification : SELECT des champs Nom, Prenom, DateNaissance ; FROM patient ; WHERE Sexe = 'M' pour filtrer les hommes ; ORDER BY Nom pour trier par nom croissant). * Partie 3 : Requête d’insertion avec champ auto-généré : * `INSERT INTO medicament (NomMed, PrixUnitaire) VALUES ('Paracétamol 500mg', 150);` * (Justification : On ne spécifie pas ID_Med car il est AUTO_INCREMENT/PK ; on donne les valeurs pour NomMed et PrixUnitaire dans le bon ordre correspondant aux champs listés ; les valeurs respectent les types : chaîne pour NomMed, entier pour PrixUnitaire). ## Synthèse du chapitre - Le Modèle Physique de Données (MPD) traduit le Modèle Conceptuel de Données (MCD) en une structure implémentable dans un SGBD, en transformant les entités en tables, les attributs en champs, et les associations en clés étrangères avec contraintes d’intégrité. - Un logiciel de modélisation graphique permet de représenter visuellement le MPD, facilitant sa conception, sa vérification, et souvent la génération automatique du code SQL de définition. - Créer une base de données en mode graphique consiste à utiliser l’interface visuelle d’un SGBD pour définir les tables, les champs, leurs types, et les relations (clés étrangères) sans écrire manuellement du code SQL, le SGBD générant le SQL correspondant en arrière-plan. - L’exécution de requêtes, notamment les requêtes de sélection (SELECT) et d’insertion (INSERT) en langage SQL, permet d’interroger la base de données pour extraire de l’information utile ou d’en ajouter de nouvelles, en respectant la structure définie par le MPD et les contraintes d’intégrité. - Les clés étrangères établies dans le MPD et créées en mode graphique sont essentielles pour garantir la cohérence des données liées et permettent, via des requêtes (même simples avec WHERE sur plusieurs tables), de reconstituer l’information liée initialement modélisée dans le MCD. - Au Cameroun et en Afrique, ces compétences sont directement applicables à la conception de systèmes d’information locaux : gestion scolaire, suivi sanitaire, gestion agricole, commerciale ou artisanale, contribuant à l’efficacité et à la transparence des organisations. ## Glossaire - **Attribut** : Propriété caractéristique d’une entité dans un MCD (ex : Nom, DateNaissance). - **Clé étrangère** : Champ d’une table qui fait référence à la clé primaire d’une autre table, établissant un lien et assurant l’intégrité référentielle. - **Clé primaire** : Champ ou groupe de champs qui identifie de manière unique chaque enregistrement dans une table. - **Contrainte d’intégrité référentielle** : Règle du SGBD qui garantit que les valeurs d’une clé étrangère correspondent toujours à une valeur existante dans la clé primaire de la table référencée. - **Entité** : Objet du monde réel modélisé dans un MCD (ex : Eleve, Cours, Patient). - **Modèle Conceptuel de Données (MCD)** : Représentation abstrait des données et de leurs relations, indépendant de toute technologie d’implémentation. - **Modèle Physique de Données (MPD)** : Représentation détaillée de la structure physique d’une base de données (tables, champs, types, clés, contraintes) destinée à être implémentée dans un SGBD. - **Requête SQL** : Instruction écrite en langage SQL destinée à un SGBD pour manipuler ou interroger les données (ex : SELECT, INSERT). - **SGBD** : Système de Gestion de Base de Données (logiciel qui permet de définir, manipuler, et administrer des bases de données relationnelles, ex : MySQL, PostgreSQL). - **Table** : Structure dans une base de données composée de lignes (enregistrements) et de colonnes (champs), correspondant généralement à une entité du MCD après transformation.