U.A.6 : ÉLABORATION D'UN MLD

# U.A.6 : ÉLABORATION D'UN MLD ## Objectifs et prérequis Comprendre les règles de passage du Modèle Conceptuel de Données (MCD) au Modèle Logique de Données (MLD) dans le cadre de la conception d’un système d’information. Savoir appliquer ces règles pour transformer un MCD en MLD relationnel, en respectant les contraintes d’intégrité et les bonnes pratiques de normalisation. Prérequis : maîtrise du MCD (entités, associations, attributs, cardinalités), notions de base sur les bases de données relationnelles (table, clé primaire, clé étrangère, jointure). ## Introduction Le passage du MCD au MLD constitue une étape cruciale dans la conception d’un système d’information. Il s’agit de traduire une représentation sémantique et indépendante du SGBD (MCD) en une structure adaptée à un SGBD relationnel (MLD), tout en préservant la sémantique du domaine modélisé. Cette transformation repose sur des règles formelles et éprouvées, permettant de garantir la cohérence, l’intégrité et l’efficacité du schéma logique obtenu. Au Cameroun, cette compétence est essentielle pour les futurs informaticiens appelés à concevoir des systèmes de gestion dans des secteurs tels que l’administration publique, la santé, l’éducation ou l’agro-industrie, où la modélisation des données doit répondre à des contraintes locales (ex. : gestion des dossiers patients dans les hôpitaux de référence, suivi des subventions agricoles dans les régions du Nord-Ouest et de l’Ouest). ## I. Règles de passage du MCD en MLD : entités simples ### Contexte Une entité simple du MCD représente un objet du monde réel doté d’une identité propre et indépendante. Son passage en MLD doit produire une table relationnelle où chaque ligne correspond à une instance de l’entité et chaque colonne à un de ses attributs. ### Définitions et faits > **Entité simple** : Entité du MCD qui ne participe à aucune association ou dont les associations sont déjà traitées séparément. > **Table relationnelle** : Structure de données composée de lignes (tuples) et de colonnes (attributs), où chaque colonne possède un nom et un type défini. > **Clé primaire** : Attribut ou ensemble d’attributs permettant d’identifier de manière unique chaque tuple d’une table. ### Règles de transformation 1. Chaque entité simple du MCD devient une table dans le MLD. 2. Tous les attributs simples de l’entité deviennent des colonnes de la table. 3. Un attribut identifiant de l’entité (clé principale du MCD) devient la clé primaire de la table. 4. Si aucun attribut n’est identifiant, on crée une clé artificielle (ex. : identifiant numérique auto-incrémenté). ### Exemple concret (Cameroun) Dans un système de gestion des élèves dans un lycée technique de Yaoundé : - MCD : Entité `ÉLÈVE` avec attributs `{numéro_inscription, nom, prénom, date_naissance, sexe}` et clé principale `numero_inscription`. - MLD : Table `ELEVE` avec colonnes `(numero_inscription INTEGER PRIMARY KEY, nom VARCHAR(50), prenom VARCHAR(50), date_naissance DATE, sexe CHAR(1))`. ### Vocabulaire à retenir - Entité simple - Table relationnelle - Clé primaire - Attribut identifiant - Clé artificielle ### Sujet type examen avec plan **Sujet** : Donné le MCD suivant : Entité `PRODUIT` (`référence`, `désignation`, `prix_unitaire`, `stock`) avec clé principale `référence`. Écrire les règles de passage du MCD en MLD et donner le MLD correspondant. **Plan** : 1. Rappel de la règle pour une entité simple. 2. Application : transformation de `PRODUIT` en table. 3. Vérification de la clé primaire. 4. Schéma SQL du MLD obtenu. ## II. Règles de passage du MCD en MLD : associations binaires 1:1 ### Contexte Une association binaire 1:1 lie deux entités telles qu’une instance de l’une est associée à au plus une instance de l’autre, et vice versa. Son traitement en MLD dépend de la présence ou non de l’attribut d’identification dans chaque entité. ### Définitions et faits > **Association binaire 1:1** : Relation entre deux entités où chaque instance de l’une est associée à zéro ou une instance de l’autre, et réciproquement. > **Attribut d’identification** : Attribut permettant d’identifier une entité dans le cadre d’une association (quand l’entité est faible). ### Règles de transformation 1. Si les deux entités sont fortes (possèdent chacune leur propre clé primaire) : - Option A : Fusionner les deux entités en une seule table (si l’association est obligatoire des deux côtés). - Option B : Garder deux tables séparées et ajouter dans chacune une clé étrangère référant la clé primaire de l’autre (si l’association est facultative d’un côté ou des deux). 2. Si une entité est faible (dépendante de l’autre pour son identification) : - La table de l’entité faible contient la clé primaire de l’entité forte comme clé étrangère, et cette clé étrangère fait partie de sa clé primaire composée. ### Exemple concret (Afrique) Dans un système de gestion des passeports au Cameroun : - MCD : Entité forte `CITOYEN` (`numero_cni`, `nom`, `prenom`) et entité faible `PASSEPORT` (`numero_passeport`, `date_delivrance`, `date_expiration`) liée par association 1:1 `détient` (un citoyen détient au plus un passeport, un passeport est détenu par exactement un citoyen). - Puisque `PASSEPORT` est faible (son identification dépend de `CITOYEN` via la CNI), le MLD donne : - Table `CITOYEN` : `(numero_cni PRIMARY KEY, nom, prenom)` - Table `PASSEPORT` : `(numero_passeport, date_delivrance, date_expiration, numero_cni FOREIGN KEY REFERENCES CITOYEN(numero_cni), PRIMARY KEY (numero_passeport, numero_cni))` ### Vocabulaire à retenir - Association binaire 1:1 - Entité forte - Entité faible - Clé étrangère - Clé primaire composée ### Sujet type examen avec plan **Sujet** : MCD avec entités `PERSONNE` (`id_personne`, `nom`) forte et `ADRESSE` (`numero`, `rue`, `ville`) faible liée par association 1:1 `habite` (une personne habite à une adresse, une adresse est habitée par une personne). Déduire le MLD. **Plan** : 1. Identifier la nature des entités (forte/faible). 2. Appliquer la règle pour entité faible en association 1:1. 3. Déterminer la clé primaire de `ADRESSE`. 4. Écrire les tables avec leurs clés primaires et étrangères. 5. Vérifier l’intégrité référentielle. ## III. Règles de passage du MCD en MLD : associations binaires 1:N ### Contexte L’association 1:N est la plus courante en modélisation : une instance de l’entité du côté « 1 » peut être associée à plusieurs instances de l’entité du côté « N », tandis qu’une instance du côté « N » est associée à au plus une instance du côté « 1 ». ### Définitions et faits > **Association binaire 1:N** : Relation où une instance de l’entité A est associée à zéro, une ou plusieurs instances de l’entité B, tandis qu’une instance de B est associée à zéro ou une instance de A. > **Clé étrangère** : Attribut d’une table qui fait référence à la clé primaire d’une autre table, permettant d’établir un lien entre les deux. ### Règles de transformation 1. L’entité du côté « N » (multiple) reçoit en plus de ses propres attributs une clé étrangère faisant référence à la clé primaire de l’entité du côté « 1 » (unique). 2. Cette clé étrangère est placée dans la table correspondant à l’entité du côté « N ». 3. Aucune nouvelle table n’est créée pour l’association elle-même. 4. Si l’association est obligatoire du côté « N », la clé étrangère ne peut pas être nulle ; si elle est facultative, elle peut l’être. ### Exemple concret (Cameroun) Dans un système de gestion des enseignants et des cours dans une université de Douala : - MCD : Entité forte `ENSEIGNANT` (`matricule`, `nom`, `specialite`) et entité forte `COURS` (`code_cours`, `intitule`, `credits`) liée par association 1:N `enseigne` (un enseignant peut enseigner plusieurs cours, un cours est enseigné par exactement un enseignant). - MLD : - Table `ENSEIGNANT` : `(matricule PRIMARY KEY, nom, specialite)` - Table `COURS` : `(code_cours PRIMARY KEY, intitule, credits, matricule FOREIGN KEY REFERENCES ENSEIGNANT(matricule))` ### Vocabulaire à retenir - Association binaire 1:N - Côté « 1 » (unique) - Côté « N » (multiple) - Clé étrangère - Contrainte d’intégrité référentielle ### Sujet type examen avec plan **Sujet** : MCD : `CLASSE` (`code_classe`, `niveau`) 1:N `ÉLÈVE` (`matricule`, `nom`, `prenom`) via `appartient à`. Écrire le MLD. **Plan** : 1. Identifier le côté « 1 » (`CLASSE`) et le côté « N » (`ÉLÈVE`). 2. Appliquer la règle : ajouter la clé étrangère de `CLASSE` dans la table `ÉLÈVE`. 3. Définir les tables avec leurs attributs et clés. 4. Préciser si la clé étrangère peut être nulle (selon si l’appartenance est obligatoire). 5. Donner le schéma SQL. ## IV. Règles de passage du MCD en MLD : associations binaires M:N ### Contexte Une association binaire M:N (ou plusieurs-à-plusieurs) permet à plusieurs instances d’une entité d’être associées à plusieurs instances d’une autre entité. Cette association ne peut pas être représentée directement par une clé étrangère dans une des tables des entités, car cela violerait la première forme normale (1FN). ### Définitions et faits > **Association binaire M:N** : Relation où une instance de l’entité A peut être associée à zéro, une ou plusieurs instances de l’entité B, et vice versa. > **Table d’association** (ou table de liaison) : Table créée spécialement pour représenter une association M:N, contenant les clés primaires des deux entités liées comme clés étrangères, formant généralement sa clé primaire composée. ### Règles de transformation 1. Créer une nouvelle table, appelée table d’association. 2. Cette table contient : - La clé primaire de l’entité A comme clé étrangère. - La clé primaire de l’entité B comme clé étrangère. - Les éventuels attributs propres à l’association. 3. La clé primaire de la table d’association est constituée par l’ensemble des deux clés étrangères (clé primaire composée). 4. Aucune des tables originales des entités A et B n’est modifiée pour inclure une clé étrangère directe vers l’autre. ### Exemple concret (Afrique) Dans un système de gestion des inscriptions aux formations professionnelles dans un centre de formation de Garoua : - MCD : Entité `STAGIAIRE` (`id_stagiaire`, `nom`, `prenom`) et entité `FORMATION` (`code_formation`, `intitule`, `durée`) liés par association M:N `suit` (un stagiaire peut suivre plusieurs formations, une formation peut être suivie par plusieurs stagiaires). - MLD : - Table `STAGIAIRE` : `(id_stagiaire PRIMARY KEY, nom, prenom)` - Table `FORMATION` : `(code_formation PRIMARY KEY, intitule, duree)` - Table `SUIT` : `(id_stagiaire FOREIGN KEY REFERENCES STAGIAIRE(id_stagiaire), code_formation FOREIGN KEY REFERENCES FORMATION(code_formation), date_inscription, PRIMARY KEY (id_stagiaire, code_formation))` ### Vocabulaire à retenir - Association binaire M:N - Table d’association - Table de liaison - Clé primaire composée - Attribut propre à l’association ### Sujet type examen avec plan **Sujet** : MCD : `AUTEUR` (`id`, `nom`) M:N `LIVRE` (`isbn`, `titre`) via `écrit`. Ajouter l’attribut `date_publication` à l’association. Déduire le MLD. **Plan** : 1. Reconnaître l’association M:N. 2. Créer la table d’association. 3. Lister les colonnes : clés étrangères des deux entités + attribut de l’association. 4. Définir la clé primaire composée. 5. Écrire les trois tables avec leurs contraintes. 6. Vérifier qu’aucune redondance ou violation de 1FN n’apparaît. ## V. Règles de passage du MCD en MLD : attributs multivalués et composés ### Contexte Les attributs du MCD peuvent être simples, composés (formés de plusieurs sous-attributs) ou multivalués (pouvant prendre plusieurs valeurs pour une même instance). Leur traitement en MLD diffère selon leur type, car le modèle relationnel ne gère pas nativement ces structures. ### Définitions et faits > **Attribut composé** : Attribut pouvant être subdivisé en sous-attributs indépendants (ex. : `adresse` = `{numero, rue, ville}`). > **Attribut multivalu** : Attribut pouvant avoir plusieurs valeurs pour une même instance d’entité (ex. : `telephone` d’une personne peut avoir plusieurs numéros). > **Première forme normale (1FN)** : Exigence du modèle relationnel selon laquelle chaque cellule d’un tableau doit contenir une valeur atomique (indivisible). ### Règles de transformation 1. **Attribut composé** : Chaque sous-attribut devient une colonne séparée dans la table de l’entité. 2. **Attribut multivalu** : - Créer une nouvelle table dédiée à cet attribut. - Cette table contient : - La clé primaire de l’entité propriétaire comme clé étrangère. - L’attribut multivalu comme attribut simple (une valeur par ligne). - La clé primaire de cette nouvelle table est composée de la clé étrangère de l’entité et de l’attribut multivalu (si ce dernier suffit à garantir l’unicité) ou d’un identifiant supplémentaire si nécessaire. - Aucun attribut multivalu ne reste dans la table principale. ### Exemple concret (Cameroun) Dans un système de gestion des entreprises au registre du commerce de Yaoundé : - MCD : Entité `ENTREPRISE` avec attribut composé `adresse` (`numero`, `rue`, `ville`, `pays`) et attribut multivalu `activite` (une entreprise peut exercer plusieurs activités). - MLD : - Table `ENTREPRISE` : `(rc PRIMARY KEY, nom, numero_voie, voie, ville, pays)` — l’attribut composé est décomposé. - Table `ACTIVITE` : `(rc FOREIGN KEY REFERENCES ENTREPRISE(rc), activite VARCHAR(100), PRIMARY KEY (rc, activite))` — chaque ligne associe l’entreprise à une de ses activités. ### Vocabulaire à retenir - Attribut composé - Attribut multivalu - Première forme normale (1FN) - Valeur atomique - Table de dépendance ### Sujet type examen avec plan **Sujet** : MCD : `FOURNISSEUR` (`id`, `nom`) avec attribut composé `contact` (`telephone`, `email`) et attribut multivalu `specialite`. Déduire le MLD. **Plan** : 1. Traiter l’attribut composé : décomposer `contact` en `telephone` et `email` dans la table `FOURNISSEUR`. 2. Traiter l’attribut multivalu `specialite` : créer une table `SPECIALITE`. 3. Définir les colonnes et la clé primaire de `SPECIALITE`. 4. Écrire les deux tables avec leurs contraintes. 5. Justifier le passage à 1FN. ## VI. Règles de passage du MCD en MLD : généralisation/spécialisation (héritage) ### Contexte La généralisation/spécialisation permet de modéliser des hiérarchies d’entités où une entité générique (supertype) partage des attributs avec des entités spécialisées (soustypes), qui possèdent également des attributs propres. Son traitement en MLD dépend du choix de stratégie de représentation (tables séparées, table unique avec discriminateur, etc.). ### Définitions et faits > **Généralisation** : Processus consistant à identifier des caractéristiques communes à plusieurs entités et à les regrouper dans une entité de niveau supérieur (supertype). > **Spécialisation** : Processus consistant à définir des sous-groupes d’une entité en fonction de caractéristiques distinctives (soustypes). > **Discriminateur** : Attribut permettant de distinguer les instances des différents sous-types dans une stratégie de table unique. ### Règles de transformation (stratégie recommandée : tables séparées par sous-type) 1. Créer une table pour le supertype contenant : - Sa clé primaire (qui sera aussi celle des sous-types). - Ses attributs propres. 2. Créer une table pour chaque sous-type contenant : - La clé primaire du supertype comme clé primaire et étrangère (référençant le supertype). - Les attributs propres au sous-type. 3. Aucune redondance de la clé primaire ni des attributs du supertype dans les tables des sous-types. 4. L’intégrité est assurée par les contraintes de clé étrangère : chaque instance d’un sous-type doit correspondre à une instance existante du supertype. ### Exemple concret (Afrique) Dans un système de gestion des véhicules dans une compagnie de transport de Bamako : - MCD : Supertype `VEHICULE` (`immatriculation`, `marque`, `annee`) avec spécialités `VOITURE` (`nombre_portes`) et `CAMION` (`poids_utile`). - MLD : - Table `VEHICULE` : `(immatriculation PRIMARY KEY, marque, annee)` - Table `VOITURE` : `(immatriculation PRIMARY KEY, FOREIGN KEY REFERENCES VEHICULE(immatriculation), nombre_portes)` - Table `CAMION` : `(immatriculation PRIMARY KEY, FOREIGN KEY REFERENCES VEHICULE(immatriculation), poids_utile)` ### Vocabulaire à retenir - Généralisation - Spécialisation - Supertype - Sous-type - Table par sous-type - Discriminateur (dans d’autres stratégies) ### Sujet type examen avec plan **Sujet** : MCD : supertype `AGENT` (`matricule`, `nom`) avec spécialités `ENSEIGNANT` (`matiere`) et `ADMINISTRATIF` (`service`). Déduire le MLD en utilisant la stratégie des tables séparées par sous-type. **Plan** : 1. Créer la table du supertype `AGENT`. 2. Créer la table du sous-type `ENSEIGNANT` avec sa clé primaire/étrangère et son attribut propre. 3. Créer la table du sous-type `ADMINISTRATIF` idem. 4. Préciser les contraintes de clé étrangère. 5. Justifier le choix (évite les valeurs nulles, respecte 1FN, facilite les requêtes spécialisées). ## Synthèse du chapitre - Le passage du MCD en MLD repose sur des règles formelles, indépendantes du SGBD cible, mais optimisées pour le modèle relationnel. - Chaque constructif du MCD (entité simple, association 1:1, 1:N, M:N, attribut composé/multivalu, généralisation/spécialisation) possède une ou plusieurs règles de transformation bien définies. - L’objectif est d’obtenir un schéma logique relationnel sans redondance, respectant les formes normales (notamment 1FN et 2FN), avec des clés primaires clairement définies et des contraintes d’intégrité (entité et référentielles) préservées. - Le choix de la stratégie (ex. : pour l’héritage ou les associations 1:1 facultatives) dépend des contraintes du domaine (obligatorieté, fréquence d’accès, volumétrie). - Au Cameroun et en Afrique, ces règles permettent de concevoir des systèmes d’information adaptés aux réalités locales (gestion des ressources humaines dans la fonction publique, suivi des dossiers médicaux, traçabilité des produits agricoles, etc.). - La maîtrise de ces règles est fondamentale pour passer de la conception abstraite à l’implémentation concrète dans un SGBD comme MySQL, PostgreSQL ou SQL Server. ## Glossaire - **Entité simple** : Entité du MCD ne participant à aucune association ou dont les associations sont traitées séparément. - **Table relationnelle** : Structure de données en lignes et colonnes représentant un ensemble de tuples homogènes. - **Clé primaire** : Attribut ou ensemble d’attributs permettant d’identifier de manière unique chaque tuple d’une table. - **Clé étrangère** : Attribut d’une table faisant référence à la clé primaire d’une autre table pour établir un lien. - **Association binaire 1:N** : Relation où une instance d’une entité est associée à zéro, une ou plusieurs instances d’une autre, tandis que l’inverse est limité à zéro ou une. - **Association binaire M:N** : Relation permettant à plusieurs instances de chaque entité d’être associées à plusieurs instances de l’autre. - **Table d’association** : Table créée pour représenter une association M:N, contenant les clés étrangères des deux entités liées. - **Attribut composé** : Attribut pouvant être décomposé en plusieurs sous-attributs indépendants. - **Attribut multivalu** : Attribut pouvant prendre plusieurs valeurs pour une même instance d’entité. - **Première forme normale (1FN)** : Propriété d’une relation selon laquelle chaque attribut contient des valeurs atomiques. - **Généralisation** : Regroupement de caractéristiques communes à plusieurs entités dans un supertype. - **Spécialisation** : Définition de sous-types d’une entité à partir de caractéristiques distinctives. - **Supertype** : Entité générique dans une hiérarchie de généralisation/spécialisation. - **Sous-type** : Entité spécialisée héritant des attributs d’un supertype et possédant des attributs propres. - **Table par sous-type** : Stratégie de représentation de l’héritage où chaque sous-type possède sa propre table. - **Discriminateur** : Attribut permettant de distinguer les sous-types dans une stratégie de table unique (non utilisée ici mais connue). - **Contrainte d’intégrité référentielle** : Règle assurant que les valeurs d’une clé étrangère correspondent à des valeurs existantes dans la clé primaire référencée. - **Forme normale** : Ensemble de propriétés qu’un schéma relationnel doit satisfaire pour éviter les redondances et anomalies de mise à jour.