Passage du Modèle Conceptuel de Données (MCD) au Modèle Logique Relationnel (MLD)

Le passage du Modèle Conceptuel de Données (MCD) au Modèle Logique Relationnel (MLD) constitue une étape cruciale dans la conception d'une base de données. Cette transformation permet de convertir la représentation conceptuelle indépendante de toute implémentation en un schéma relationnel prêt à être implémenté dans un SGBD relationnel.

Ce cours présente les règles de transformation du MCD vers le MLD, puis aborde le processus de normalisation avec toutes les formes normales (1NF, 2NF, 3NF, BCNF, 4NF, 5NF) en utilisant un exemple concret : un système de messagerie inbox pour un site e-commerce.

Le MCD (Modèle Conceptuel de Données), également appelé Modèle Entité-Association, représente les données du point de vue métier, indépendamment de toute considération technique. Il utilise trois concepts principaux :

• Entité : Objet ou concept du monde réel (ex: Utilisateur, Message, Commande)
• Attribut : Propriété descriptive d'une entité (ex: nom, email, date)
• Association/Relation : Lien sémantique entre deux ou plusieurs entités

Les cardinalités définissent les contraintes de participation :

• 1:1 : Un à un
• 1:N : Un à plusieurs
• N:M : Plusieurs à plusieurs

Le MLD (Modèle Logique Relationnel) représente la structure de la base de données sous forme de relations (tables) avec leurs attributs, clés primaires, et clés étrangères. C'est une représentation plus proche de l'implémentation physique tout en restant indépendante du SGBD spécifique.

Les éléments du MLD incluent :

• Relation (Table) : Structure bidimensionnelle avec des lignes (tuples) et des colonnes (attributs)
• Attribut : Colonne d'une relation
• Clé primaire : Attribut(s) identifiant de manière unique chaque tuple
• Clé étrangère : Attribut faisant référence à la clé primaire d'une autre relation
• Contraintes : Règles garantissant l'intégrité des données

Règle 1 : Transformation d'une Entité

Chaque entité du MCD devient une relation (table) dans le MLD. Les attributs de l'entité deviennent les attributs de la relation.

Exemple :

• Entité Utilisateur avec attributs (id, nom, email, date_inscription)
• → Relation UTILISATEUR(id, nom, email, date_inscription)

Règle 2 : Transformation d'une Association 1:N

Pour une association 1:N, la clé primaire de l'entité du côté "1" est ajoutée comme clé étrangère dans la relation correspondant à l'entité du côté "N".

Exemple :

• Association : Utilisateur (1) envoie (N) Message
• → La relation MESSAGE contient une clé étrangère id_utilisateur référençant UTILISATEUR

Règle 3 : Transformation d'une Association N:M

Pour une association N:M, une nouvelle relation est créée avec les clés primaires des deux entités associées comme clés étrangères. Cette nouvelle relation peut également contenir des attributs propres à l'association.

Exemple :

• Association : Utilisateur (N) participe (M) Groupe
• → Nouvelle relation PARTICIPATION(id_utilisateur, id_groupe, date_ajout)

Règle 4 : Transformation d'une Association 1:1

Pour une association 1:1, la clé primaire d'une entité est ajoutée comme clé étrangère dans l'autre entité. Le choix de l'entité qui reçoit la clé étrangère dépend du contexte métier.

Pour illustrer la transformation MCD → MLD et les formes normales, nous utiliserons un système de messagerie inbox pour un site e-commerce. Ce système permet aux utilisateurs d'envoyer et recevoir des messages, de créer des groupes, et de gérer leurs conversations.

Modèle Conceptuel (MCD)

Entités :

• UTILISATEUR : id, nom, email, date_inscription
• MESSAGE : id, contenu, date_envoi, lu
• GROUPE : id, nom, date_creation
• COMMANDE : id, date_commande, montant_total

Associations :

• UTILISATEUR (1) envoie (N) MESSAGE
• UTILISATEUR (N) reçoit (M) MESSAGE
• UTILISATEUR (N) participe (M) GROUPE
• MESSAGE (N) concerne (0,1) COMMANDE
• UTILISATEUR (1) passe (N) COMMANDE

Transformation Initiale en MLD

Après application des règles de transformation, nous obtenons le schéma relationnel suivant :

UTILISATEUR(id, nom, email, date_inscription)
MESSAGE(id, contenu, date_envoi, lu, id_expediteur, id_commande)
COMMANDE(id, date_commande, montant_total, id_utilisateur)
GROUPE(id, nom, date_creation)
PARTICIPATION(id_utilisateur, id_groupe, date_ajout)
RECEPTION(id_message, id_destinataire, date_reception)

Note : L'association N:M "reçoit" entre UTILISATEUR et MESSAGE nécessite une relation de réception séparée pour gérer les destinataires multiples (messages de groupe).

La normalisation est un processus qui consiste à organiser les données dans une base de données pour réduire la redondance, éviter les anomalies d'insertion, de mise à jour et de suppression, et améliorer l'intégrité des données.

Nous allons examiner chaque forme normale en utilisant notre exemple de messagerie, en partant d'une relation non normalisée et en la transformant progressivement.

Définition

Une relation est en première forme normale (1NF) si et seulement si tous les attributs contiennent des valeurs atomiques (indivisibles) et qu'il n'y a pas de groupes répétitifs.

Règles :

• Chaque attribut doit contenir une seule valeur (pas de tableaux, listes, ou valeurs multiples)
• Pas de groupes répétitifs (pas de colonnes répétées)
• Chaque ligne doit être unique

Exemple : Relation Non Normalisée

Supposons une relation MESSAGERIE non normalisée :

MESSAGERIE(
    id_message,
    contenu,
    date_envoi,
    expediteur_nom,
    expediteur_email,
    destinataires,        -- Liste: "user1@mail.com, user2@mail.com"
    groupes,             -- Liste: "Groupe A, Groupe B"
    id_commande,
    produits_commande    -- Liste: "Produit1, Produit2"
)

Problèmes :

• L'attribut destinataires contient plusieurs valeurs (violation de l'atomicité)
• L'attribut groupes contient plusieurs valeurs
• L'attribut produits_commande contient plusieurs valeurs
• Redondance des informations d'expéditeur

Transformation en 1NF

Pour mettre en 1NF, nous devons :

1. Séparer les valeurs multiples en relations distinctes
2. Éliminer les groupes répétitifs
3. Remplacer les références textuelles par des clés étrangères

Résultat en 1NF :

MESSAGE(id_message, contenu, date_envoi, id_expediteur, id_commande)
UTILISATEUR(id_utilisateur, nom, email)
RECEPTION(id_message, id_destinataire)
MESSAGE_GROUPE(id_message, id_groupe)
GROUPE(id_groupe, nom)
COMMANDE(id_commande, date_commande, id_utilisateur)
PRODUIT_COMMANDE(id_commande, id_produit, quantite)

Maintenant, chaque attribut contient une seule valeur atomique, et les relations N:M sont gérées par des tables de liaison séparées.

Définition

Une relation est en deuxième forme normale (2NF) si et seulement si :

• Elle est en 1NF
• Tous les attributs non-clés dépendent fonctionnellement de la clé primaire complète (pas seulement d'une partie de la clé)

Cette forme normale s'applique principalement aux relations ayant des clés primaires composites (multi-attributs).

Exemple : Violation de la 2NF

Considérons la relation PARTICIPATION_GROUPE :

PARTICIPATION_GROUPE(
    id_utilisateur,      -- Partie de la clé primaire
    id_groupe,           -- Partie de la clé primaire
    nom_utilisateur,     -- Dépend de id_utilisateur uniquement
    email_utilisateur,   -- Dépend de id_utilisateur uniquement
    nom_groupe,          -- Dépend de id_groupe uniquement
    date_ajout           -- Dépend de (id_utilisateur, id_groupe)
)

Problème : Les attributs nom_utilisateur, email_utilisateur, et nom_groupe dépendent seulement d'une partie de la clé primaire, pas de la clé complète.

Anomalies :

• Anomalie de mise à jour : Si le nom d'un utilisateur change, il faut modifier toutes les lignes où cet utilisateur apparaît
• Anomalie d'insertion : Impossible d'insérer un utilisateur sans qu'il participe à un groupe
• Anomalie de suppression : Supprimer une participation supprime aussi les informations de l'utilisateur

Transformation en 2NF

Pour mettre en 2NF, nous devons séparer les attributs qui dépendent seulement d'une partie de la clé :

UTILISATEUR(id_utilisateur, nom_utilisateur, email_utilisateur)
GROUPE(id_groupe, nom_groupe, date_creation)
PARTICIPATION(id_utilisateur, id_groupe, date_ajout)

Maintenant, chaque attribut non-clé dépend fonctionnellement de la clé primaire complète de sa relation.

Définition

Une relation est en troisième forme normale (3NF) si et seulement si :

• Elle est en 2NF
• Aucun attribut non-clé ne dépend transitivement d'un autre attribut non-clé

En d'autres termes, tous les attributs non-clés doivent dépendre directement de la clé primaire, pas indirectement via un autre attribut non-clé.

Exemple : Violation de la 3NF

Considérons la relation MESSAGE avec des informations sur l'expéditeur :

MESSAGE(
    id_message,
    contenu,
    date_envoi,
    id_expediteur,
    nom_expediteur,      -- Dépend de id_expediteur
    email_expediteur,    -- Dépend de id_expediteur
    ville_expediteur,    -- Dépend de id_expediteur
    id_commande,
    montant_commande,    -- Dépend de id_commande
    date_commande        -- Dépend de id_commande
)

Problème : Les attributs nom_expediteur, email_expediteur, ville_expediteur dépendent de id_expediteur (un attribut non-clé), pas directement de id_message (la clé primaire). De même, montant_commande et date_commande dépendent de id_commande.

Anomalies :

• Si l'email d'un utilisateur change, il faut modifier tous ses messages
• Redondance des données utilisateur dans chaque message
• Risque d'incohérence si les données changent

Transformation en 3NF

Pour mettre en 3NF, nous devons éliminer les dépendances transitives en créant des relations séparées :

MESSAGE(id_message, contenu, date_envoi, id_expediteur, id_commande)
UTILISATEUR(id_utilisateur, nom, email, ville)
COMMANDE(id_commande, montant, date_commande, id_utilisateur)

Maintenant, chaque attribut non-clé dépend directement de la clé primaire de sa relation, sans dépendance transitive.

Définition

Une relation est en forme normale de Boyce-Codd (BCNF) si et seulement si :

• Elle est en 3NF
• Pour toute dépendance fonctionnelle non triviale X → Y, X est une super-clé (contient une clé candidate)

La BCNF est une version renforcée de la 3NF qui élimine certaines anomalies résiduelles que la 3NF peut laisser passer.

Exemple : Violation de la BCNF

Considérons une relation GROUPE_MESSAGE qui gère les messages dans les groupes :

GROUPE_MESSAGE(
    id_groupe,
    id_message,
    id_createur_groupe,  -- L'utilisateur qui a créé le groupe
    date_ajout_message
)

Supposons les dépendances fonctionnelles suivantes :

• id_groupe → id_createur_groupe (chaque groupe a un créateur unique)
• (id_groupe, id_message) → date_ajout_message (clé primaire)

Problème : La dépendance id_groupe → id_createur_groupe viole la BCNF car id_groupe n'est pas une super-clé (la clé primaire est (id_groupe, id_message)).

Anomalies :

• Si le créateur d'un groupe change, il faut modifier toutes les lignes de messages de ce groupe
• Redondance de l'information du créateur

Transformation en BCNF

Pour mettre en BCNF, nous devons séparer la dépendance fonctionnelle problématique :

GROUPE(id_groupe, nom, id_createur_groupe, date_creation)
MESSAGE_GROUPE(id_groupe, id_message, date_ajout_message)

Maintenant, dans chaque relation, le déterminant de toute dépendance fonctionnelle non triviale est une super-clé.

Définition

Une relation est en quatrième forme normale (4NF) si et seulement si :

• Elle est en BCNF
• Il n'y a pas de dépendances multi-valuées (MVD) non triviales

Une dépendance multi-valuée X ↠ Y existe si, pour chaque valeur de X, il existe un ensemble de valeurs de Y indépendant des autres attributs.

Exemple : Violation de la 4NF

Considérons une relation MESSAGE_DESTINATAIRES qui stocke les messages et leurs destinataires :

MESSAGE_DESTINATAIRES(
    id_message,
    id_destinataire,
    id_groupe_destinataire
)

Supposons qu'un message peut être envoyé à plusieurs destinataires ET à plusieurs groupes de manière indépendante. Cela crée une dépendance multi-valuée :

• id_message ↠ id_destinataire
• id_message ↠ id_groupe_destinataire

Problème : Si un message M1 est envoyé aux destinataires U1, U2 et aux groupes G1, G2, nous devons créer 4 lignes (M1-U1-G1, M1-U1-G2, M1-U2-G1, M1-U2-G2) alors que les destinataires et les groupes sont indépendants.

Transformation en 4NF

Pour mettre en 4NF, nous devons séparer les dépendances multi-valuées :

MESSAGE_DESTINATAIRE(id_message, id_destinataire)
MESSAGE_GROUPE_DEST(id_message, id_groupe_destinataire)

Maintenant, chaque relation ne contient qu'une seule dépendance multi-valuée, éliminant la redondance artificielle.

Définition

Une relation est en cinquième forme normale (5NF) ou forme normale de projection-jointure (PJNF) si et seulement si :

• Elle est en 4NF
• Toute dépendance de jointure est une conséquence des clés candidates

La 5NF traite des dépendances de jointure qui ne peuvent pas être exprimées comme des dépendances fonctionnelles ou multi-valuées. C'est la forme normale la plus stricte et la plus théorique.

Exemple : Violation de la 5NF

Considérons une relation complexe qui gère les relations entre messages, groupes, et commandes :

MESSAGE_GROUPE_COMMANDE(
    id_message,
    id_groupe,
    id_commande
)

Supposons les règles métier suivantes :

• Un message peut être associé à un groupe ET à une commande
• Si un message M est dans un groupe G et concerne une commande C, alors toute combinaison valide de (M, G, C) doit exister
• Mais les associations message-groupe et message-commande sont indépendantes

Problème : Cette relation peut contenir des tuples qui ne respectent pas les règles métier si elle n'est pas correctement décomposée.

Transformation en 5NF

Pour mettre en 5NF, nous devons décomposer en relations binaires qui préservent les dépendances de jointure :

MESSAGE_GROUPE(id_message, id_groupe)
MESSAGE_COMMANDE(id_message, id_commande)
GROUPE_COMMANDE(id_groupe, id_commande)

La jointure de ces trois relations recrée exactement la relation originale, garantissant que seules les combinaisons valides selon les règles métier sont présentes.

Note : La 5NF est rarement utilisée en pratique car elle peut créer un grand nombre de relations et complexifier les requêtes. Elle est principalement importante pour comprendre la théorie de la normalisation.

Après application de toutes les formes normales, voici le schéma relationnel final optimisé pour notre système de messagerie e-commerce :

-- Entités principales
UTILISATEUR(
    id_utilisateur PK,
    nom,
    email UNIQUE,
    date_inscription,
    ville
)

GROUPE(
    id_groupe PK,
    nom,
    id_createur FK → UTILISATEUR,
    date_creation
)

COMMANDE(
    id_commande PK,
    date_commande,
    montant_total,
    id_utilisateur FK → UTILISATEUR
)

MESSAGE(
    id_message PK,
    contenu,
    date_envoi,
    lu,
    id_expediteur FK → UTILISATEUR,
    id_commande FK → COMMANDE (nullable)
)

-- Relations d'association
PARTICIPATION(
    id_utilisateur FK → UTILISATEUR,
    id_groupe FK → GROUPE,
    date_ajout,
    PRIMARY KEY (id_utilisateur, id_groupe)
)

RECEPTION(
    id_message FK → MESSAGE,
    id_destinataire FK → UTILISATEUR,
    date_reception,
    PRIMARY KEY (id_message, id_destinataire)
)

MESSAGE_GROUPE(
    id_message FK → MESSAGE,
    id_groupe FK → GROUPE,
    date_ajout,
    PRIMARY KEY (id_message, id_groupe)
)

PRODUIT_COMMANDE(
    id_commande FK → COMMANDE,
    id_produit,
    quantite,
    prix_unitaire,
    PRIMARY KEY (id_commande, id_produit)
)

Ce schéma respecte toutes les formes normales et élimine :

• La redondance des données
• Les anomalies d'insertion, de mise à jour et de suppression
• Les dépendances fonctionnelles et multi-valuées problématiques

Avantages

• Réduction de la redondance : Les données ne sont stockées qu'une seule fois
• Intégrité des données : Moins de risques d'incohérence
• Facilité de maintenance : Les modifications sont localisées
• Économie d'espace : Moins de stockage nécessaire
• Simplification des mises à jour : Moins d'anomalies

Inconvénients

• Performance des requêtes : Les jointures peuvent être coûteuses
• Complexité : Plus de tables à gérer
• Dénormalisation parfois nécessaire : Pour optimiser certaines requêtes fréquentes

En pratique, il est parfois nécessaire de dénormaliser certaines parties de la base de données pour améliorer les performances, tout en conservant la normalisation pour les parties critiques.

À l'issue de ce cours, les étudiants seront capables de :

• Transformer un MCD en MLD en appliquant les règles de transformation
• Identifier les violations des différentes formes normales
• Normaliser une base de données jusqu'à la 3NF et BCNF
• Comprendre les concepts de 4NF et 5NF
• Reconnaître les anomalies causées par le manque de normalisation
• Évaluer les compromis entre normalisation et performance
• Appliquer la normalisation à des cas réels de conception de bases de données

L'évaluation du cours comprend des exercices pratiques où les étudiants doivent :

• Transformer des MCD en MLD
• Identifier et corriger les violations des formes normales
• Normaliser des schémas relationnels existants
• Concevoir des schémas normalisés pour des systèmes réels
• Justifier les choix de normalisation ou de dénormalisation

Les étudiants sont attendus de démontrer leur compréhension des concepts de transformation MCD/MLD et de normalisation à travers des réalisations pratiques et de la documentation.

Les exercices ci-dessous permettent de mettre en pratique la transformation MCD → MLD et les formes normales. Les sujets détaillés sont disponibles dans les documents PDF associés.

Travaux pratiques 1 (TP 1)

📄 Télécharger le sujet du TP 1 (PDF)

📝 Rendre le compte rendu du devoir individuel (TP 1)

D’autres documents d’exercices (TP 2, TP 3, etc.) seront ajoutés ici au fur et à mesure.

Remise des comptes rendus via GitHub Classroom

Les comptes rendus des travaux pratiques doivent être rendus via GitHub Classroom. Cette plateforme permet de gérer les dépôts Git pour chaque exercice et facilite la correction et le suivi des travaux.

🔗 Accéder à GitHub Classroom

Comment se connecter et lier votre compte GitHub

Pour accéder aux exercices et rendre vos travaux, vous devez d'abord vous connecter à GitHub Classroom et lier votre compte GitHub :

1. Créer un compte GitHub : Si vous n'avez pas encore de compte GitHub, créez-en un sur github.com (gratuit pour les étudiants).
2. Accéder à GitHub Classroom : Cliquez sur le lien ci-dessus pour accéder à la classe "Base de données - Licence 1 Informatique".
3. Se connecter avec GitHub : Sur la page GitHub Classroom, cliquez sur "Sign in to GitHub" et autorisez GitHub Classroom à accéder à votre compte.
4. Lier votre compte dans le Classroom Roster :
   • Une fois connecté, vous verrez la liste des étudiants (Classroom Roster)
   • Recherchez votre nom dans la liste
   • Cliquez sur votre nom pour le sélectionner
   • Confirmez que c'est bien votre compte GitHub qui est associé
5. Accepter les exercices : Une fois votre compte lié, vous pourrez accepter les exercices (assignments) qui vous seront proposés. Chaque exercice créera automatiquement un dépôt Git privé dans votre compte GitHub.

Rendre votre travail

Pour chaque exercice :

• Un dépôt Git privé sera créé automatiquement dans votre compte GitHub
• Clonez ce dépôt sur votre machine locale
• Ajoutez vos fichiers de compte rendu (schémas, documentation, etc.)
• Commitez et poussez vos modifications vers GitHub
• Le dépôt sera automatiquement visible par l'enseignant pour correction

Note importante : Assurez-vous de bien lier votre compte GitHub à votre nom dans le Classroom Roster avant de commencer les exercices. Si vous rencontrez des difficultés, contactez l'enseignant.