MCD Sujet Gestion d’un site de location de vacances - Modéli

Ce document didactique est conçu pour accompagner les étudiants universitaires dans leur apprentissage des bases de données relationnelles. Il propose une série d'exercices pratiques, suivis de leurs corrections détaillées, couvrant des aspects essentiels de la gestion de bases de données.

Il couvre les notions suivantes :

• La modélisation conceptuelle de données (MCD) et la dérivation de schémas relationnels.
• La manipulation de données via SQL, incluant la création, la suppression et la mise à jour de tables.
• L'interrogation de bases de données avec des requêtes SQL complexes (jointures, agrégations).

Modélisation Merise : MCD Sujet Gestion d’un site de location de vacances

Télécharger PDF

Correction :

Télécharger PDF

Modélisation et Requêtes SQL pour la Gestion de Logements de Vacances

Cet article propose une exploration structurée de la conception d'une base de données pour un site de logements de vacances, avec un accent sur la modélisation conceptuelle, le schéma relationnel, et des requêtes SQL fondamentales pour la manipulation des données.

Partie 1 : Modèle Conceptuel de Données (MCD) et Schéma Relationnel

Question 1.1 : Entités pour le site de logements de vacances de Ticarol

Pour un site de logements de vacances comme Ticarol, les entités clés à identifier dans le Modèle Conceptuel de Données (MCD) sont :

• Opérateur : Gère les logements et leurs offres.
• Semaine : Représente les périodes calendaires de location.
• Logement : L'unité de base à louer, avec des informations générales.
• MobilHome : Un type spécifique de Logement, avec des caractéristiques propres.
• Chalet : Un autre type spécifique de Logement, avec ses propres attributs.
• Planning : Permet d'associer un logement à une semaine et un opérateur, et d'indiquer sa disponibilité.

Question 1.2 : Rédaction du MCD du site de logements de vacances de Ticarol

Le Modèle Conceptuel de Données (MCD) se traduit par l'identification des entités, de leurs attributs et des relations. Il est souvent représenté graphiquement, mais pour cet exercice, sa traduction logique est le schéma relationnel présenté ci-dessous, qui décrit la structure des tables.

Question 1.3 : Rédaction du schéma relationnel correspondant

Le schéma relationnel est la traduction du MCD en structures de tables de base de données, incluant les clés primaires et les attributs. Voici la proposition pour le site Ticarol :

Opérateur(NumOpérateur, NomOpérateur, SiteOpérateur)
Semaine(NumSemaine, NomSemaine)
Logement(NumLogement, Lieu, Capacité, TypeLogement)
Planning(NumLogement, NumSemaine, NumOpérateur, Dispo)
MobilHome(NumLogement, NombreDeChambres)
Chalet(NumLogement, NombreEtoiles, JardinPrivatif)

Compléments d'information sur les conventions et concepts

• Les identifiants préfixés par un « # » dans certaines notations de MCD ou de schéma relationnel (comme dans l'énoncé d'origine) indiquent des clés étrangères qui participent également à la clé primaire d'une table, notamment dans les tables d'association comme Planning. Dans le schéma relationnel ci-dessus, ces attributs sont listés sans le `#`, leur rôle de clé étrangère et primaire étant défini ultérieurement dans les requêtes CREATE TABLE.
• Le champ TypeLogement dans la table Logement est un champ discriminant. Il permet de déterminer rapidement, sans jointure coûteuse, si un logement est de type MobilHome ou Chalet, facilitant la gestion des spécialisations de logements.
• Le concept d'exclusivité et totalité, souvent symbolisé par un « + » (équivalent à XT) dans les MCD, correspond à un opérateur OU exclusif (XOR). Cela signifie qu'un logement est nécessairement soit un mobil-home, soit un chalet, mais ne peut pas être les deux à la fois. En logique booléenne, l'opérateur « + » peut désigner un « OU » (addition booléenne).

Partie 2 : Requêtes SQL Fondamentales

Question 2.1 : Rédaction des requêtes de création des tables (CREATE TABLE)

Voici les requêtes SQL pour créer les tables de la base de données de Ticarol, incluant les définitions des clés primaires, des clés étrangères et des contraintes d'intégrité référentielle.

CREATE TABLE Operateur (
 NumOperateur INT AUTO_INCREMENT PRIMARY KEY,
 NomOperateur VARCHAR(50),
 SiteOperateur VARCHAR(50)
);

CREATE TABLE Semaine (
 NumSemaine SMALLINT PRIMARY KEY,
 NomSemaine VARCHAR(30)
);

CREATE TABLE Logement (
 NumLogement INT PRIMARY KEY,
 Lieu VARCHAR(50),
 Capacite SMALLINT,
 TypeLogement VARCHAR(15)
);

CREATE TABLE Planning (
 NumSemaine SMALLINT REFERENCES Semaine(NumSemaine) ON UPDATE CASCADE ON DELETE CASCADE,
 NumLogement INT REFERENCES Logement(NumLogement) ON UPDATE CASCADE ON DELETE CASCADE,
 NumOperateur INT REFERENCES Operateur(NumOperateur) ON UPDATE CASCADE ON DELETE CASCADE,
 Dispo BOOLEAN,
 PRIMARY KEY (NumSemaine, NumLogement)
);

CREATE TABLE MobilHome (
 NumLogement INT PRIMARY KEY REFERENCES Logement(NumLogement) ON UPDATE CASCADE ON DELETE CASCADE,
 NombreDeChambres SMALLINT
);

CREATE TABLE Chalet (
 NumLogement INT PRIMARY KEY REFERENCES Logement(NumLogement) ON UPDATE CASCADE ON DELETE CASCADE,
 NombreEtoiles SMALLINT,
 JardinPrivatif BOOLEAN
);

Compléments techniques sur les types de données et contraintes

• SMALLINT et INT sont des types d'entiers (entiers courts et entiers standards), choisis pour leur adéquation aux données représentées (ex: numéro de semaine, capacité, nombre de chambres).
• BOOLEAN (ou BIT dans certains SGBD) est un type booléen, utilisé pour représenter des valeurs vrai/faux (par exemple, la disponibilité d'un logement).
• AUTO_INCREMENT est une propriété de colonne qui génère automatiquement une nouvelle valeur numérique unique à chaque insertion d'une ligne. Il est souvent utilisé pour les clés primaires afin d'assurer leur unicité (par exemple, NumOperateur).
• Les instructions ON UPDATE CASCADE ON DELETE CASCADE, associées aux clés étrangères, garantissent l'intégrité référentielle. Elles signifient que si une clé primaire référencée est mise à jour ou supprimée, les clés étrangères correspondantes dans les tables dépendantes seront automatiquement mises à jour ou supprimées. Cela évite ainsi les liens "cassés" dans la base de données et assure une meilleure cohérence des données.

Question 2.2 : Rédaction de la requête de suppression d'un mobil-home et du logement correspondant

Pour supprimer le mobil-home n°4, puis le logement associé, l'ordre des opérations est crucial pour respecter les contraintes d'intégrité si les clauses CASCADE ne sont pas configurées ou si la table enfant doit être supprimée en premier.

DELETE FROM MobilHome WHERE NumLogement = 4;
DELETE FROM Logement WHERE NumLogement = 4;

Question 2.3 : Quel problème pourrait occasionner la suppression du logement avant le mobil-home ? Justifier.

Si l'on tente de supprimer d'abord le logement n°4 de la table Logement, en l'absence de la clause ON DELETE CASCADE sur la clé étrangère de MobilHome vers Logement, une erreur se produira. En effet, une contrainte d'intégrité référentielle serait violée : il existerait toujours un tuple (une ligne) dans la table MobilHome pointant sur un Logement n°4 qui n'existerait plus. La base de données refuserait cette opération pour préserver la cohérence des données, car la clé étrangère de MobilHome ne peut pas référencer un enregistrement inexistant dans Logement. La table MobilHome deviendrait "orpheline".

Question 2.4 : Rédaction de la requête pour rendre un logement indisponible

Pour indiquer qu'un logement mis en location est désormais indisponible, il faut mettre à jour le champ Dispo dans la table Planning pour la semaine et le logement concernés.

UPDATE Planning
SET Dispo = FALSE /* ou Dispo = 0, si 'FALSE' n'est pas directement supporté ou si '1' est la convention pour indisponible */
WHERE NumLogement = [Numéro du logement]
AND NumSemaine = [Numéro de la semaine];

Question 2.5 : Rédaction de la requête pour afficher la liste des opérateurs

Cette requête simple permet de lister tous les opérateurs enregistrés dans la base de données.

SELECT * FROM Operateur;

Question 2.6 : Rédaction de la requête pour afficher la liste des logements du lieu « Les Peupliers »

Pour obtenir la liste détaillée des logements situés au lieu « Les Peupliers », en distinguant les caractéristiques des mobil-homes et des chalets, une union de requêtes est nécessaire.

SELECT L.NumLogement, L.TypeLogement, M.NombreDeChambres, NULL AS NombreEtoiles, NULL AS JardinPrivatif
FROM Logement AS L
INNER JOIN MobilHome AS M ON L.NumLogement = M.NumLogement
WHERE L.Lieu = 'Les Peupliers'

UNION

SELECT L.NumLogement, L.TypeLogement, NULL AS NombreDeChambres, C.NombreEtoiles, C.JardinPrivatif
FROM Logement AS L
INNER JOIN Chalet AS C ON L.NumLogement = C.NumLogement
WHERE L.Lieu = 'Les Peupliers';

Une version alternative utilisant des jointures implicites dans la clause WHERE est également possible, bien que la syntaxe INNER JOIN explicite soit généralement préférée pour sa clarté et sa conformité aux standards SQL modernes :

SELECT L.NumLogement, L.TypeLogement, M.NombreDeChambres, NULL AS NombreEtoiles, NULL AS JardinPrivatif
FROM Logement AS L, MobilHome AS M
WHERE L.Lieu = 'Les Peupliers'
AND L.NumLogement = M.NumLogement

UNION

SELECT L.NumLogement, L.TypeLogement, NULL AS NombreDeChambres, C.NombreEtoiles, C.JardinPrivatif
FROM Logement AS L, Chalet AS C
WHERE L.Lieu = 'Les Peupliers'
AND L.NumLogement = C.NumLogement;

Question 2.7 : Rédaction de la requête pour afficher la liste des logements disponibles la semaine 14

Pour lister les logements disponibles pour une semaine spécifique, il faut joindre les tables Planning, Logement et Operateur, puis filtrer par semaine et par statut de disponibilité.

SELECT L.NumLogement, L.TypeLogement, L.Lieu, O.NomOperateur, P.Dispo
FROM Planning AS P
INNER JOIN Logement AS L ON P.NumLogement = L.NumLogement
INNER JOIN Operateur AS O ON P.NumOperateur = O.NumOperateur
WHERE P.NumSemaine = 14 AND P.Dispo = TRUE; /* Ou P.Dispo = 'O' si 'O' représente disponible */

Une version alternative utilisant des jointures implicites :

SELECT L.NumLogement, L.TypeLogement, L.Lieu, O.NomOperateur, P.Dispo
FROM Planning AS P, Logement AS L, Operateur AS O
WHERE P.NumSemaine = 14 AND P.Dispo = TRUE /* Ou P.Dispo = 'O' si 'O' représente disponible */
AND P.NumLogement = L.NumLogement
AND P.NumOperateur = O.NumOperateur;

Question 2.8 : Rédaction de la requête pour connaître la quantité de logements disponibles par opérateur entre la semaine 14 et la semaine 52

Cette requête permet d'agréger le nombre de logements disponibles par opérateur sur une période donnée en utilisant les fonctions d'agrégation COUNT(*) et la clause GROUP BY.

SELECT O.NomOperateur, COUNT(*) AS 'Logements disponibles'
FROM Planning AS P
INNER JOIN Operateur AS O ON P.NumOperateur = O.NumOperateur
WHERE P.NumSemaine BETWEEN 14 AND 52 AND P.Dispo = TRUE /* Filtrer uniquement les logements disponibles */
GROUP BY O.NomOperateur;

Une version alternative avec jointure implicite :

SELECT O.NomOperateur, COUNT(*) AS 'Logements disponibles'
FROM Planning AS P, Operateur AS O
WHERE P.NumSemaine BETWEEN 14 AND 52 AND P.Dispo = TRUE /* Filtrer uniquement les logements disponibles */
AND P.NumOperateur = O.NumOperateur
GROUP BY O.NomOperateur;

Foire Aux Questions (FAQ)

Qu'est-ce qu'un Modèle Conceptuel de Données (MCD) ?

Le MCD est une représentation abstraite et graphique des données d'un système d'information, indépendante de toute implémentation technique. Il identifie les entités (objets importants), leurs propriétés (attributs) et les relations qui les unissent, ainsi que leurs cardinalités. Il est une étape essentielle dans la conception d'une base de données, permettant de structurer l'information de manière logique et compréhensible par tous les acteurs d'un projet.

Quelle est la différence entre une clé primaire et une clé étrangère ?

Une clé primaire est un attribut (ou un ensemble d'attributs) qui identifie de manière unique chaque enregistrement dans une table. Elle garantit l'intégrité de l'entité et ne peut pas contenir de valeurs nulles. Une clé étrangère est un attribut (ou un ensemble d'attributs) dans une table qui fait référence à la clé primaire d'une autre table. Elle établit une relation entre les tables et assure l'intégrité référentielle en garantissant que les valeurs de la clé étrangère correspondent toujours à une clé primaire existante dans la table référencée, évitant ainsi les liens brisés.

Pourquoi utiliser les clauses ON UPDATE CASCADE et ON DELETE CASCADE ?

Les clauses ON UPDATE CASCADE et ON DELETE CASCADE sont des options de gestion des contraintes d'intégrité référentielle appliquées aux clés étrangères. Elles permettent de maintenir automatiquement la cohérence des données entre les tables liées. Si un enregistrement de la table parente est mis à jour (pour ON UPDATE CASCADE) ou supprimé (pour ON DELETE CASCADE), les enregistrements correspondants dans la table enfant sont automatiquement mis à jour ou supprimés. Cela évite d'avoir des "orphelins" (enregistrements dans la table enfant qui référencent des données inexistantes ou modifiées dans la table parente), simplifiant la gestion des données tout en garantissant leur intégrité.

Cela peut vous intéresser :

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 d...

Devoir n°1 Merise 2 et SQL corrigé - Modélisation Merise

Ce document universitaire présente la correction détaillée du Devoir n°1, axé sur la conception de bases de données. Il est destiné aux étudiants univ...

Sujet 1 mcd Gestion d'un pressing - Modélisation Merise

Ce document est destiné aux étudiants universitaires en informatique, spécifiquement ceux abordant la conception de bases de données. Il contient deux...

Solution Examen 2 en informatique de gestion - Modélisation

Ce document présente la solution détaillée d'un examen en informatique de gestion, spécifiquement conçu pour les étudiants universitaires. Il s'adress...

Solution de l’examen 1 en informatique de gestion - Modélisa

Ce document propose la solution complète de l'examen en informatique de gestion, spécifiquement conçu pour les étudiants du Parcours Gestion en semest...

Rattrapage Avril2018 Solution - Modélisation Merise

Ce document pédagogique est spécifiquement conçu pour les étudiants universitaires, notamment ceux inscrits en Parcours Gestion. Il présente un examen...

Examen en Informatique de gestion - Modélisation Merise

Ce document est un examen d'informatique de gestion conçu pour les étudiants universitaires du parcours Gestion (niveau S5). Il a pour objectif d'éval...

Fiche de révisions MCD et schéma relationnel - Modélisation

Ce document est une fiche de révisions complète destinée aux étudiants universitaires, en particulier ceux suivant une formation en gestion des systèm...

Voir tous →