COURS MCD MLDR JOINTURES
Cours MCD, MLDR et Jointures pour BTS SIO
option SLAM (1ère et 2ème année)
Table des matières
1. Introduction
2. Modèle Conceptuel de Données (MCD)
3. Modèle Logique de Données Relationnel (MLDR)
4. Jointures SQL
5. Exercices pratiques et corrigés
6. Conclusion et ressources supplémentaires
Introduction
Ce cours couvre les concepts essentiels de la modélisation de données et des jointures SQL,
indispensables pour les étudiants en BTS SIO option SLAM. Nous aborderons la conception de
bases de données depuis la phase conceptuelle jusqu'à l'interrogation des données à l'aide de
jointures.
Modèle Conceptuel de Données (MCD)
Définition et objectifs du MCD
Le Modèle Conceptuel de Données (MCD) est une représentation graphique et structurée des
données d'un système d'information. Il permet de décrire de façon formelle les données qui
seront utilisées par le système d'information. Son but est de représenter ces données de
manière indépendante des contraintes techniques.
Éléments du MCD
1. Entités : Représentent les objets du monde réel (ex: Client, Produit).
2. Attributs : Caractéristiques des entités (ex: Nom, Prénom pour Client).
3. Identifiants : Attributs uniques permettant d'identifier une occurrence d'entité.
4. Associations : Relations entre les entités.
5. Cardinalités : Contraintes de participation aux associations.
Règles de construction du MCD
1. Identifier les entités pertinentes.
2. Définir les attributs de chaque entité.
3. Déterminer les identifiants.
4. Établir les associations entre les entités.
5. Définir les cardinalités des associations.
Exemple de MCD
Prenons l'exemple d'une petite librairie :
[CLIENT] [COMMANDE]
- ID_Client (PK) 1,n -------- 1,1 - ID_Commande (PK)
- Nom - Date
- Prénom - Total
- Email
[LIGNE_COMMANDE]
[LIVRE] 1,n ----------------- 0,n [COMMANDE]
- ISBN (PK) - ID_Commande (FK)
- Titre - Quantité
- Auteur
- Prix
Exercice 1 : Création d'un MCD
Énoncé : Créez un MCD pour un système de gestion d'une école. Incluez les entités Étudiant,
Cours, Professeur et Inscription.
Corrigé :
[ETUDIANT] [INSCRIPTION] [COURS]
- ID_Etudiant (PK) 1,n ------------ 0,n - ID_Cours (PK)
- Nom - Date - Nom_Cours
- Prénom - Note - Description
- Date_Naissance
[ENSEIGNE]
[PROFESSEUR] 1,n ------------ 0,n [COURS]
- ID_Professeur (PK) - Année_Scolaire
- Nom
- Prénom
- Spécialité
Modèle Logique de Données Relationnel (MLDR)
Définition et objectifs du MLDR
Le Modèle Logique de Données Relationnel (MLDR) est une représentation des données qui
se rapproche de la façon dont elles seront stockées dans une base de données relationnelle. Il
découle directement du MCD et prépare l'implémentation physique de la base de données.
Passage du MCD au MLDR
1. Entités → Tables
2. Attributs → Colonnes
3. Identifiants → Clés primaires
4. Associations :
1,1 - 1,1 : Fusion des tables ou clé étrangère
1,1 - 1,n : Clé étrangère dans la table côté 1,n
1,n - 1,n : Création d'une table d'association
Exemple de transformation MCD → MLDR
Reprenons l'exemple de la librairie :
CLIENT (ID_Client, Nom, Prénom, Email)
LIVRE (ISBN, Titre, Auteur, Prix)
COMMANDE (ID_Commande, Date, Total, #ID_Client)
LIGNE_COMMANDE (ID_Ligne, #ID_Commande, #ISBN, Quantité)
Exercice 2 : Transformation MCD vers MLDR
Énoncé : Transformez le MCD de l'école (Exercice 1) en MLDR.
Corrigé :
ETUDIANT (ID_Etudiant, Nom, Prénom, Date_Naissance)
PROFESSEUR (ID_Professeur, Nom, Prénom, Spécialité)
COURS (ID_Cours, Nom_Cours, Description)
INSCRIPTION (ID_Inscription, #ID_Etudiant, #ID_Cours, Date, Note)
ENSEIGNE (#ID_Professeur, #ID_Cours, Année_Scolaire)
Jointures SQL
Définition et types de jointures
Les jointures SQL permettent de combiner des lignes de deux ou plusieurs tables en se basant
sur une condition de jointure. Il existe plusieurs types de jointures :
1. INNER JOIN
2. LEFT (OUTER) JOIN
3. RIGHT (OUTER) JOIN
4. FULL (OUTER) JOIN
Syntaxe des jointures
SELECT colonne1, colonne2, ...
FROM table1
[INNER | LEFT | RIGHT | FULL] JOIN table2
ON [Link] = [Link];
Exemples de jointures
Utilisons le MLDR de la librairie pour illustrer les jointures :
1. INNER JOIN : Lister tous les livres commandés avec leurs quantités
SELECT [Link], [Link]é
FROM LIVRE l
INNER JOIN LIGNE_COMMANDE lc ON [Link] = [Link];
2. LEFT JOIN : Lister tous les clients et leurs commandes (y compris ceux qui n'ont pas
commandé)
SELECT [Link], [Link]énom, co.ID_Commande
FROM CLIENT c
LEFT JOIN COMMANDE co ON c.ID_Client = co.ID_Client;
3. RIGHT JOIN : Lister toutes les commandes avec les informations des clients (y compris
les commandes sans client)
SELECT co.ID_Commande, [Link], [Link]énom
FROM CLIENT c
RIGHT JOIN COMMANDE co ON c.ID_Client = co.ID_Client;
Exercice 3 : Écriture de requêtes avec jointures
Énoncé : En utilisant le MLDR de l'école (Exercice 2), écrivez les requêtes SQL suivantes :
a) Lister tous les étudiants et leurs cours (s'ils en ont).
b) Afficher tous les cours avec les noms des professeurs qui les enseignent.
c) Trouver tous les étudiants qui n'ont pas encore de note pour leurs cours.
Corrigé :
a)
SELECT [Link], [Link]énom, c.Nom_Cours
FROM ETUDIANT e
LEFT JOIN INSCRIPTION i ON e.ID_Etudiant = i.ID_Etudiant
LEFT JOIN COURS c ON i.ID_Cours = c.ID_Cours;
b)
SELECT c.Nom_Cours, [Link], [Link]énom
FROM COURS c
INNER JOIN ENSEIGNE e ON c.ID_Cours = e.ID_Cours
INNER JOIN PROFESSEUR p ON e.ID_Professeur = p.ID_Professeur;
c)
SELECT [Link], [Link]énom, c.Nom_Cours
FROM ETUDIANT e
INNER JOIN INSCRIPTION i ON e.ID_Etudiant = i.ID_Etudiant
INNER JOIN COURS c ON i.ID_Cours = c.ID_Cours
WHERE [Link] IS NULL;
Exercices pratiques et corrigés
Exercice 4 : Conception complète (MCD, MLDR, Requêtes)
Énoncé : Vous êtes chargé de concevoir un système de gestion pour une bibliothèque
municipale. Le système doit gérer les livres, les adhérents, les emprunts et les auteurs.
1. Créez un MCD pour ce système.
2. Transformez ce MCD en MLDR.
3. Écrivez les requêtes SQL pour :
a) Lister tous les livres empruntés avec les noms des adhérents.
b) Trouver les auteurs qui ont le plus de livres dans la bibliothèque.
c) Afficher les adhérents qui ont des emprunts en retard.
Corrigé :
1. MCD :
[LIVRE] [EMPRUNT] [ADHERENT]
- ISBN (PK) 1,n ------------ 0,n - ID_Adherent (PK)
- Titre - Date_Emprunt - Nom
- Année - Date_Retour - Prénom
- #ID_Auteur - Adresse
[AUTEUR] 1,n
- ID_Auteur (PK) |
- Nom |
- Prénom |
[ECRIT]
| 0,n
[LIVRE]
2. MLDR :
LIVRE (ISBN, Titre, Année, #ID_Auteur)
AUTEUR (ID_Auteur, Nom, Prénom)
ADHERENT (ID_Adherent, Nom, Prénom, Adresse)
EMPRUNT (ID_Emprunt, #ISBN, #ID_Adherent, Date_Emprunt, Date_Retour)
3. Requêtes SQL :
a) Lister tous les livres empruntés avec les noms des adhérents :
SELECT [Link], [Link], [Link]énom, e.Date_Emprunt
FROM LIVRE l
INNER JOIN EMPRUNT e ON [Link] = [Link]
INNER JOIN ADHERENT a ON e.ID_Adherent = a.ID_Adherent
WHERE e.Date_Retour IS NULL;
b) Trouver les auteurs qui ont le plus de livres dans la bibliothèque :
SELECT [Link], [Link]énom, COUNT(*) as Nombre_Livres
FROM AUTEUR a
INNER JOIN LIVRE l ON a.ID_Auteur = l.ID_Auteur
GROUP BY a.ID_Auteur
ORDER BY Nombre_Livres DESC
LIMIT 5;
c) Afficher les adhérents qui ont des emprunts en retard :
SELECT [Link], [Link]énom, [Link], e.Date_Emprunt, e.Date_Retour
FROM ADHERENT a
INNER JOIN EMPRUNT e ON a.ID_Adherent = e.ID_Adherent
INNER JOIN LIVRE l ON [Link] = [Link]
WHERE e.Date_Retour < CURDATE() OR (e.Date_Retour IS NULL AND e.Date_Emprunt
< DATE_SUB(CURDATE(), INTERVAL 14 DAY));
Conclusion et ressources supplémentaires
Ce cours vous a fourni une base solide sur les concepts de MCD, MLDR et jointures SQL,
essentiels pour le BTS SIO option SLAM. Pour approfondir vos connaissances, voici quelques
ressources supplémentaires :
1. Livres :
"Conception et architecture des bases de données" de Jean-Luc Hainaut
"SQL: The Complete Reference" de James R. Groff et Paul N. Weinberg
2. Sites web :
W3Schools SQL Tutorial: [Link]
SQL Zoo: [Link]
3. Outils de modélisation :
MySQL Workbench
Lucidchart (pour les diagrammes MCD)
N'oubliez pas que la pratique régulière est la clé pour maîtriser ces concepts. Continuez à créer
des modèles et à écrire des requêtes SQL pour divers scénarios afin de renforcer vos
compétences.
