Correction examen conception - Modélisation Merise

Ce document pédagogique est destiné aux étudiants universitaires des filières d'informatique de gestion et des domaines connexes. Il a pour objectif de consolider les connaissances fondamentales en matière de conception et de manipulation des bases de données relationnelles. À travers une série d'exercices pratiques et leurs solutions détaillées, il permet aux étudiants de maîtriser les concepts essentiels.

Il couvre les notions suivantes :

La modélisation conceptuelle des données (MCD)
La dérivation vers le modèle logique des données (MLD)
L'élaboration de requêtes en langage SQL pour l'interrogation et la gestion des données.

Modélisation Merise : Correction examen conception

Télécharger PDF

Informatique de gestion : Conception de Données et Langage SQL

Partie 1 : Conception des données

Cette section aborde la conception de bases de données à travers des exercices pratiques, en commençant par l'élaboration de Modèles Conceptuels de Données (MCD) et leur transformation en Modèles Logiques de Données (MLD).

Élaboration d'un Modèle Conceptuel de Données (MCD)

Le Modèle Conceptuel de Données (MCD) est une représentation graphique et formelle des données à gérer dans un système d'information. Il décrit les entités, leurs propriétés (attributs) et les relations qui les unissent, ainsi que leurs cardinalités. L'objectif est de modéliser le monde réel sans se soucier des contraintes techniques de mise en œuvre.

Contexte de l'entreprise :

Une entreprise de distribution d'ouvrages scolaires gère des dépôts répartis dans différentes régions. Ces dépôts stockent des ouvrages provenant de divers éditeurs.

Précisions du gestionnaire :

Un livre est identifié par son numéro ISBN, son titre et son thème.
Un même livre (identifié par son ISBN, titre et thème) peut être édité par plusieurs éditeurs.
Un livre peut être écrit par plusieurs écrivains.
Un livre peut être stocké dans plusieurs dépôts. Pour chaque dépôt, la quantité en stock doit être connue.
Chaque dépôt est situé dans une région spécifique.
Un livre ne peut être édité qu'une seule fois par un même éditeur, mais il peut être édité par différents éditeurs.

Analyse pour le MCD :

À partir de ces informations, nous pouvons identifier les entités et les relations principales :

Entités : Livre (ISBN, Titre, Thème), Éditeur (ID_Éditeur, Nom_Éditeur), Écrivain (ID_Écrivain, Nom_Écrivain, Prénom_Écrivain), Dépôt (ID_Dépôt, Nom_Dépôt, Adresse_Dépôt), Région (ID_Région, Nom_Région).
Relations :
- Un Livre est Édité par un ou plusieurs Éditeurs. Un Éditeur peut Éditer un ou plusieurs Livres. La contrainte "Un livre ne peut être édité qu'une seule fois chez le même éditeur" suggère une cardinalité (1,1) pour l'éditeur sur la relation "Éditer" avec un livre spécifique si on considère une édition, mais (0,N) pour l'éditeur avec l'entité livre conceptuelle. La cardinalité serait (0,N) pour Livre vers Éditeur et (0,N) pour Éditeur vers Livre, formant une association pour gérer quelle édition ou quelle association existe.
- Un Livre est Écrit par un ou plusieurs Écrivains. Un Écrivain peut Écrire un ou plusieurs Livres (relation N-N).
- Un Livre est Stocké dans un ou plusieurs Dépôts avec une Quantité associée. Un Dépôt peut Stocker un ou plusieurs Livres (relation N-N avec attribut 'Quantité').
- Un Dépôt est Situé dans une et une seule Région. Une Région peut contenir un ou plusieurs Dépôts (relation 1-N).

Conversion d'un Modèle Conceptuel de Données (MCD) en Modèle Logique de Données (MLD)

Le Modèle Logique de Données (MLD) est une représentation structurée des données prête à être implémentée dans une base de données relationnelle. Il se compose d'un ensemble de tables (ou relations), de leurs attributs (colonnes), de clés primaires et étrangères pour gérer les liens entre les tables.

MCD donné pour un centre médical :

Ce MCD représente les visites dans un centre médical, impliquant des médecins, des patients, des consultations et des médicaments prescrits.

MLD correspondant au MCD ci-dessus :

La conversion du MCD en MLD aboutit aux tables et relations suivantes :

Medecin (matricule, nom, prenom)

Patient (numSS, nom, prenom)

Medicament (codeMedicament, nomCommercial)

Consultation (numConsultation, date_heure, #matricule, #numSS)

Prescrire (#numConsultation, #codeMedicament, posologie)

Explications :

matricule dans Medecin, numSS dans Patient et codeMedicament dans Medicament sont les clés primaires de leurs tables respectives.
Consultation est une entité qui a une clé primaire numConsultation. Elle a des clés étrangères #matricule (référence Medecin.matricule) et #numSS (référence Patient.numSS) pour lier une consultation à un médecin et un patient.
Prescrire est une table d'association (résultat d'une relation N-N entre Consultation et Medicament). Sa clé primaire est la combinaison des clés étrangères #numConsultation (référence Consultation.numConsultation) et #codeMedicament (référence Medicament.codeMedicament), et elle contient l'attribut spécifique de la relation, posologie.

Partie 2 : Le langage SQL

SQL (Structured Query Language) est le langage standard pour la gestion et la manipulation des bases de données relationnelles. Cette section présente des requêtes SQL pour interroger et extraire des informations d'un schéma relationnel donné.

Schéma relationnel :

Voici les tables et leurs attributs, avec les clés primaires soulignées :

Produit (Référence, désignation, prix, quantitéStock)

Client (CIN, Nom, adresse, téléphone)

Commande (Num, Date, #CIN)

Liste (#Num, #Référence, quantité)

Notes :

#CIN dans Commande est une clé étrangère référençant Client.CIN.
#Num dans Liste est une clé étrangère référençant Commande.Num.
#Référence dans Liste est une clé étrangère référençant Produit.Référence.
La table Liste gère les produits inclus dans chaque commande, avec la quantité commandée pour chaque produit.

Questions et requêtes SQL :

1. Donnez la référence et la désignation des produits dont le prix est supérieur à 100.

SELECT Référence, désignation
FROM Produit
WHERE prix > 100;

2. Donnez le nom du client ayant passé une commande à la date "15/06/2015".

SELECT C.Nom
FROM Client C, Commande Co
WHERE C.CIN = Co.CIN AND Co.Date = '2015-06-15';

Note : Le format de date a été ajusté à 'YYYY-MM-DD' pour une meilleure compatibilité SQL.

3. Donnez le nombre de produits pour chaque commande.

SELECT Num, COUNT(*) AS NombreDeProduits
FROM Liste
GROUP BY Num;

4. Donnez le nom et le téléphone du client ayant commandé des produits avec une quantité supérieure à 100.

SELECT C.Nom, C.téléphone
FROM Client C
JOIN Commande Co ON C.CIN = Co.CIN
JOIN Liste L ON Co.Num = L.Num
WHERE L.quantité > 100;

Note : La requête originale a été corrigée pour utiliser les jointures appropriées entre les tables Client, Commande et Liste via leurs clés primaires et étrangères.

FAQ (Foire Aux Questions)

Q1 : Quelle est la différence fondamentale entre un MCD et un MLD ?

R1 : Le MCD (Modèle Conceptuel de Données) décrit les données de manière abstraite et indépendante de toute contrainte technique, en se concentrant sur le "quoi". Le MLD (Modèle Logique de Données) est une traduction du MCD dans un modèle spécifique (relationnel, hiérarchique, etc.), en ajoutant les détails d'implémentation comme les tables, clés primaires et étrangères, se concentrant sur le "comment".

Q2 : À quoi sert la clause GROUP BY en SQL ?

R2 : La clause GROUP BY est utilisée en SQL pour regrouper les lignes qui ont les mêmes valeurs dans une ou plusieurs colonnes spécifiées en une seule ligne de résumé. Elle est souvent utilisée avec des fonctions d'agrégation (comme COUNT(), SUM(), AVG(), MAX(), MIN()) pour effectuer des calculs sur chaque groupe de lignes, comme compter le nombre de produits par commande.

Q3 : Pourquoi est-il important de bien définir les cardinalités dans un MCD ?

R3 : La bonne définition des cardinalités (ex: (1,1), (0,N), (1,N)) est cruciale car elles expriment les contraintes d'intégrité et la nature des liens entre les entités. Elles déterminent comment les relations seront transformées en clés étrangères et en tables d'association dans le MLD, garantissant ainsi la cohérence et l'exactitude des données de la base de données.