Ce document constitue le quatrième Travaux Dirigés (TD) de l'enseignement sur la conception de bases de données, destiné aux étudiants universitaires. Il propose une série d'exercices pratiques visant à consolider les compétences fondamentales en modélisation des données (Merise) et en manipulation de bases de données relationnelles (SQL).
Ce TD couvre les notions suivantes :
- La rédaction de requêtes SQL variées, incluant des jointures, des regroupements et des sous-requêtes ;
- L'élaboration de Modèles Conceptuels et Logiques de Données (MCD, MLD) ;
- L'application de contraintes d'intégrité et l'utilisation de triggers pour la cohérence des données.
Modélisation Merise : TD N°4 modéliser, requêter, modéliser, requêter corrigé
Télécharger PDFTD N°4 : Merise 2 + SQL
Modéliser, requêter, modéliser, requêter... Révisions MERISE
Exercice 1 : Requêtes SQL Fondamentales
Sujet : Schéma Relationnel
Soit le schéma relationnel suivant :
ARTICLES (NOART, LIBELLE, STOCK, PRIXINVENT)
FOURNISSEURS (NOFOUR, NOMFOUR, ADRFOUR, VILLEFOUR)
ACHETER (NOFOUR#, NOART#, PRIXACHAT, DELAI)
Il vous est demandé de rédiger les requêtes SQL correspondant aux questions qui suivent. Leur difficulté est grosso modo croissante.
Questions :
-
Numéros et libellés des articles dont le stock est inférieur à 10 ?
SELECT NOART, LIBELLE FROM ARTICLES WHERE STOCK<10;
-
Liste des articles dont le prix d'inventaire est compris entre 100 et 300 ?
SELECT * FROM ARTICLES WHERE PRIXINVENT BETWEEN 100 AND 300;
-
Liste des fournisseurs dont on ne connaît pas l'adresse ?
SELECT * FROM FOURNISSEURS WHERE ADRFOUR IS NULL;
-
Liste des fournisseurs dont le nom commence par "STE" ?
SELECT * FROM FOURNISSEURS WHERE NOMFOUR LIKE 'STE%';
-
Noms et adresses des fournisseurs qui proposent des articles pour lesquels le délai d'approvisionnement est supérieur à 20 jours ?
SELECT DISTINCT NOMFOUR, ADRFOUR, VILLEFOUR FROM FOURNISSEURS AS F INNER JOIN ACHETER AS A ON F.NOFOUR=A.NOFOUR WHERE DELAI>20;
-
Nombre d'articles référencés ?
SELECT COUNT(*) AS NbArticles FROM ARTICLES;
-
Valeur du stock ?
SELECT SUM(STOCK*PRIXINVENT) AS ValeurStock FROM ARTICLES;
-
Numéros et libellés des articles triés dans l'ordre décroissant des stocks ?
SELECT NOART, LIBELLE, STOCK FROM ARTICLES ORDER BY STOCK DESC;
-
Liste pour chaque article (numéro et libellé) du prix d'achat maximum, minimum et moyen ?
SELECT A.NOART, LIBELLE, MAX(PRIXACHAT) AS PMAX, MIN(PRIXACHAT) AS PMIN, AVG(PRIXACHAT) AS PMOY FROM ACHETER AS A INNER JOIN ARTICLES B ON A.NOART = B.NOART GROUP BY A.NOART, LIBELLE;
-
Délai moyen pour chaque fournisseur proposant au moins 2 articles ?
SELECT A.NOFOUR, NOMFOUR, AVG(DELAI) AS DelaiMoyen FROM ACHETER AS A INNER JOIN FOURNISSEURS AS F ON A.NOFOUR = F.NOFOUR GROUP BY A.NOFOUR, NOMFOUR HAVING COUNT(*) >=2;
-
Prix minimum de chaque article avec le fournisseur correspondant
SELECT A.NOART, A.LIBELLE, A1.PRIXACHAT, F.NOMFOUR FROM ACHETER AS A1 INNER JOIN ARTICLES AS A ON A1.NOART = A.NOART INNER JOIN FOURNISSEURS AS F ON A1.NOFOUR = F.NOFOUR WHERE A1.PRIXACHAT = ( SELECT MIN(A2.PRIXACHAT) FROM ACHETER AS A2 WHERE A2.NOART = A.NOART );
-
Listes des articles pouvant être achetés chez plusieurs fournisseurs, avec le prix d'achat et le délai correspondants
SELECT AR.NOART, AR.LIBELLE FROM ARTICLES AS AR INNER JOIN ACHETER AS AC ON AR.NOART = AC.NOART GROUP BY AR.NOART, AR.LIBELLE HAVING COUNT(DISTINCT AC.NOFOUR) > 1;
Note : La requête originale ne spécifiait pas le fournisseur ni le délai dans le SELECT. Pour lister les articles avec le prix d'achat et le délai pour chaque fournisseur si plusieurs sont associés, une jointure supplémentaire sur ACHETER serait nécessaire ou une approche différente en fonction de l'interprétation exacte de "avec le prix d'achat et le délai correspondants". La version corrigée compte les fournisseurs distincts.
-
Quels sont le ou les fournisseurs qui fournissent le plus de produits ?
SELECT NOFOUR, NOMFOUR, COUNT(*) AS nombre_produits FROM FOURNISSEURS AS F, ACHETER AS A WHERE F.NOFOUR = A.NOFOUR GROUP BY NOFOUR, NOMFOUR HAVING COUNT(*)=( SELECT MAX(nb_prod) FROM ( SELECT COUNT(*) AS nb_prod FROM ACHETER GROUP BY NOFOUR ) AS B );
Exercice 2 : Modélisation d'un Parc Immobilier
Sujet : Description du Parc Immobilier
Le parc immobilier de la SCI EasyLocation est constitué d’appartements faisant partie d’immeubles situés à une adresse (numéro, voie, code postal, ville).
- L’immeuble dispose ou non de la fibre optique, il dispose ou non d’un parking privatif.
- L’appartement a une valeur locative (loyer mensuel), il a une superficie totale, il a une description, il possède ou non une terrasse, il a une classe de consommation d’énergie (« A », « B », « C », ...), il est chauffé à l’électricité (« E ») ou au gaz (« G »), il a ou n’a pas de place de parking. S’il a une place de parking, son prix est indiqué.
- L’appartement est constitué d’une à plusieurs pièces ayant une superficie et une fonction (exemple : « salle d’eau », « cuisine », etc.).
- À l’appartement peuvent être attachées des photos.
Questions :
-
Établir le MCD correspondant à la situation décrite.
Le MCD (Modèle Conceptuel de Données) n'est pas fourni sous forme graphique, mais le MLD ci-après permet de le déduire.
-
Établir le MLD correspondant à la situation décrite.
Voici le MLD (Modèle Logique de Données) proposé :
Immeuble(id, adrNum, adrVoie, adrCodePostal, adrVille, fibreOptique, parkingPrivatif)
Clé primaire : id
Appartement(#immeuble, num, description, loyer, superficie, terrasse, classeConso, chauffage, placeParking, prixParking)
Clé primaire : immeuble, num
Clé étrangère : immeuble en référence à Immeuble(id)
Piece(#(immeuble, appartement), num, superficie, fonction)
Clé primaire : immeuble, appartement, num
Clés étrangères : (immeuble, appartement) en référence à Appartement(immeuble, num)
Photo(num, titre, description, uri, #(immeuble, appartement))
Clé primaire : num
Clé étrangère : (immeuble, appartement) en référence à Appartement(immeuble, num)
-
Préciser les contraintes qui ne figurent pas sur le MCD ?
Les contraintes suivantes ne figurent pas explicitement sur le MCD :
- Le prix de la place de parking d’un appartement peut et doit être NULL si l’appartement ne possède pas de place de parking.
- Un appartement ne peut avoir de place de parking si l’immeuble n’a pas de parking privatif.
- La superficie totale d’un appartement doit être égale à la somme de la superficie de chacune de ses pièces.
-
Rédiger le script SQL de création des tables correspondant à votre MCD et/ou MLD.
CREATE TABLE Immeuble( Id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, adrNum VARCHAR(7) NOT NULL, adrVoie VARCHAR(100) NOT NULL, adrCodePostal VARCHAR(5) NOT NULL, adrVille VARCHAR(30) NOT NULL, fibreOptique TINYINT NOT NULL, parkingPrivatif TINYINT NOT NULL ); CREATE TABLE Appartement( immeuble INT(11), num INT(3) NOT NULL, description LONGTEXT, loyer DECIMAL(5,2) NOT NULL, superficie DECIMAL(3,2) NOT NULL, terrasse TINYINT NOT NULL, classeConso CHAR(1) NOT NULL, chauffage CHAR(1) NOT NULL, placeParking TINYINT NOT NULL, prixParking DECIMAL(3,2), CONSTRAINT pk_appartement PRIMARY KEY (immeuble, num), CONSTRAINT fk_immeuble FOREIGN KEY (immeuble) REFERENCES Immeuble(id) ); CREATE TABLE Photo( immeuble INT(11), appartement INT(3), reference INT(11) NOT NULL, titre VARCHAR(75), description VARCHAR(255), uri VARCHAR(120) NOT NULL, CONSTRAINT pk_photo PRIMARY KEY (immeuble, appartement, reference), CONSTRAINT fk_appartement_photo FOREIGN KEY (immeuble, appartement) REFERENCES Appartement(immeuble, num) ); CREATE TABLE Piece( immeuble INT(11), appartement INT(3), num INT(2) NOT NULL, superficie DECIMAL(3, 1), fonction VARCHAR(30), CONSTRAINT pk_piece PRIMARY KEY (immeuble, appartement, num), CONSTRAINT fk_appartement_piece FOREIGN KEY (immeuble, appartement) REFERENCES Appartement(immeuble, num) );
-
Rédiger la requête SQL qui calcule la superficie totale de chacun des appartements à partir de celle de ses pièces.
SELECT immeuble, appartement, SUM(superficie) FROM Piece GROUP BY immeuble, appartement;
-
Quelle notion évoquée en cours vous permettrait de vérifier les contraintes pour le moment non vérifiées ? Quelles seraient les opérations de vérification à effectuer et à quelle(s) moment(s) ces vérifications interviendraient-elles ?
Un trigger nous permettrait de vérifier les contraintes pour le moment non vérifiées, à savoir celles listées en réponse à la question 3.
Les vérifications à effectuer sont les suivantes :
- À l’insertion d’un appartement n’ayant aucune place de parking (placeParking vaut 0), il faut vérifier que le prix du parking est bien nul (prixParking vaut NULL). Si le prix n’est pas nul, il faut empêcher l’insertion.
- À la mise à jour d’un appartement, s’il n’y a plus de place de parking (placeParking passe à 0), il faut forcer le passage du prix du parking à nul.
- Toujours à la mise à jour d’un appartement, on doit empêcher le passage du prix à nul si l’appartement possède une place de parking.
- Finalement, il importe de vérifier que la superficie totale de l’appartement soit bien égale à la somme de celle de ses pièces. À l’insertion comme à la mise à jour d’une pièce, on doit recalculer et mettre à jour la superficie totale de l’appartement. À la mise à jour, on vérifiera que la superficie totale de l’appartement est bien égale à la somme de celle de ses pièces. Si tel est le cas tout va bien. Dans le cas contraire, il faut empêcher la mise à jour.
- Lors de l’insertion d’un appartement, ce dernier ne comporte encore aucune pièce. De ce fait, sa superficie doit pour le moment être égale à 0. Il convient de forcer cette valeur, c'est-à-dire de passer automatiquement la superficie à 0.
Exercice 3 : Archivage de Mails et Requêtes
Sujet : Description du Système de Webmail
- Une webmail (alias une messagerie web ou encore boîte de messagerie) permet de conserver les mails reçus et envoyés. Elle est propre à un utilisateur.
- À un mail, il est possible d’affecter un ou plusieurs libellés.
- Les libellés peuvent être hiérarchisés.
- Les anciens mails de la webmail finissent par être archivés (exemple : une fois par an).
Questions :
-
Proposer un MCD représentant la situation décrite.
Le MCD (Modèle Conceptuel de Données) n'est pas fourni sous forme graphique, mais le MLD ci-après permet de le déduire.
-
Établir le MLD correspondant à votre MCD.
Mail(mail)
Clé primaire : mail
Utilisateur(id, motDePasse, grainDeSel, #mail)
Clé primaire : id
Clé étrangère : mail en référence à Mail
Message(#utilisateur, num, creation, objet, corps, #emetteur)
Clé primaire : utilisateur, num
Clé étrangère : utilisateur en référence à Utilisateur
MessageDestinataire(#(utilisateur, num), #destinataire)
Clé primaire : utilisateur, num, destinataire
Clés étrangères : (utilisateur, num) en référence à Message, destinataire en référence à Mail
MessageLibelle(#(utilisateur, num), #libelle)
Clé primaire : utilisateur, num, libelle
Clés étrangères : (utilisateur, num) en référence à Message, libelle en référence à Libelle
Libelle(libelle, #parent)
Clé primaire : libelle
Clé étrangère : parent en référence à Libelle
MessageArchive(#utilisateur, num, creation, objet, corps, #emetteur)
Clé primaire : utilisateur, num
Clé étrangère : utilisateur en référence à Utilisateur
N.B. : On notera qu’en pratique on se passera très volontiers de la table Mail.
-
Rédiger la requête SQL qui réponde à la question suivante : combien de mails ont été envoyés par mois, sur l’année 2016, par l’utilisateur dont l’adresse mail est john.doe@gmal.com ?
SELECT MONTH(creation) AS 'Mois', COUNT(*) AS 'Nombre de mails' FROM Message AS M INNER JOIN Utilisateur AS U ON M.utilisateur = U.id WHERE U.mail = 'john.doe@gmal.com' AND YEAR(creation) = 2016 GROUP BY MONTH(creation);
Exercice 4 : Gestion des Prix de Produits Historisés
Sujet : Description de la Société Wissenschaft
- La société Wissenschaft est spécialisée dans la formation en langue en ligne. Elle commercialise ses produits au moyen d’un site e-commerce/e-learning. Ces produits sont des biens matériels (livres) ou des biens immatériels, à savoir des prestations (formations en ligne).
- Tous les produits ont un prix mais seuls les biens matériels ont un poids et font l’objet d’une livraison.
- Une commande auprès de Wissenschaft porte soit sur une prestation soit sur un ou plusieurs biens matériels. On notera que le prix des produits de la société varie au fil du temps.
- Une commande peut ou non avoir un mode de livraison. De même, la commande intègre ou non des frais de port.
Questions :
-
À votre avis, dans quel cas la commande n’a ni mode de livraison ni frais de port ?
La délivrance de prestations de formation ne requiert pas de livraison physique. De ce fait, une commande de formation ne nécessite ni mode de livraison ni frais de port.
-
Les prix figurant sur une commande varient-ils dans le temps ?
Si le prix d’un produit est susceptible de varier dans le temps, une commande et le bon de commande associé sont des éléments commerciaux établis une fois pour toutes. Pour chaque produit commandé (c'est-à-dire pour chaque ligne de commande), le prix appliqué est celui valable au jour de la commande.
En conséquence, une fois une commande validée, les prix sont figés et ne varient pas dans le temps.
-
Proposer un MCD représentant la situation décrite.
Le MCD (Modèle Conceptuel de Données) n'est pas fourni sous forme graphique, mais les justifications ci-après décrivent les choix de modélisation.
Justifications :
- On a bien précisé que le prix des produits est historisé : (H). Par conséquent, pour retrouver simplement et efficacement le prix du produit valable lors d’une commande déjà passée, on a utilisé les propriétés portées « prix unitaire ».
- Pour retrouver simplement et efficacement les informations tarifaires propres à une commande, on a choisi de recourir aux champs calculés « prix total ». Ils ne sont pas nécessaires mais permettent de retrouver les données tarifaires sans passer par des agrégats, les agrégats devant être calculés à la création (insert) et recalculés lors d’une éventuelle modification (update).
- Un produit est soit une formation, soit un livre. Dès lors, la spécialisation est bien de type « XT » : exclusivité + totalité.
- On commande soit une formation soit un ou plusieurs livres, d’où la contrainte d’exclusion entre les associations « ligne de commande ».
- Si on commande une formation, il n’y a pas de frais de transport, d’où la seconde contrainte d’exclusion.
- On a choisi que les frais de transport soient relatifs à un mode de livraison de sorte qu’on connaît le mode de livraison d’une commande par l’intermédiaire de ses frais de port.
- On a très naturellement choisi que les frais de transport dépendent du poids total de la commande. Ainsi, une fois les produits et les quantités commandées choisis, on peut calculer le poids de la commande. Puis, une fois le mode de livraison choisi, on peut appliquer les frais de transport, à savoir les frais de port du mode de livraison choisi tels que `poids min ≤ poids commande < poids max`.
-
Justifier la présence d’éventuelles contraintes d’associations ? Pour chacune d’entre elles, expliquer quelles sont les vérifications à effectuer afin de préserver l’intégrité des données ? Préciser à quel(s) moment(s) ces vérifications doivent être effectuées.
Les justifications détaillées des contraintes d'associations et les vérifications à effectuer sont expliquées dans la réponse à la question 3, notamment concernant la gestion des prix historisés, la spécialisation des produits, les contraintes d'exclusion sur les lignes de commande et les frais de port, ainsi que la dépendance des frais de transport au mode de livraison et au poids de la commande. Ces vérifications interviendraient généralement au moment de l'insertion ou de la mise à jour des données pertinentes dans la base.
Exercice 5 : Location de Véhicules et Permis
Sujet : Description du Système de Location de Véhicules
- Un véhicule est loué pour une période donnée.
- La location est effectuée par une personne disposant d’un permis (exemple : permis poids-lourd).
- Le véhicule requiert lui-même un permis particulier.
Questions :
-
Proposer un MCD représentant la situation décrite.
Le MCD (Modèle Conceptuel de Données) n'est pas fourni sous forme graphique.
-
Justifier la présence d’une éventuelle contrainte d’association ? De quel type de contrainte d’association s’agit-il ?
Une contrainte d’inclusion est nécessaire pour modéliser la contrainte d’intégrité suivante : un conducteur ne peut louer un véhicule que s’il est titulaire du permis requis pour ce véhicule.
-
Quelles sont les opérations à effectuer afin que la contrainte d’association soit vérifiée ? À quel(s) moment(s) ces vérifications doivent-elles intervenir ?
Il convient de mettre en place un trigger :
- À l’insertion et à la modification d’une Location (INSERT ou UPDATE sur la table « Location »).
- Qui vérifiera que le conducteur spécifié correspond à un conducteur ayant parmi ses permis celui requis pour conduire le véhicule.
- Autrement dit, le résultat retourné par la requête suivante doit être égal à 1 :
SELECT COUNT(*) FROM Location INNER JOIN Titulaire ON Inserted.Conducteur = Titulaire.Conducteur INNER JOIN Véhicule ON Inserted.Vehicule = Vehicule.Numero WHERE Titulaire.Permis = Vehicule.Permis;
N.B. : où
Insertedcorrespond à la ligne insérée ou mise à jour.- Si le résultat de la requête n’est pas égal à 1 (c'est-à-dire si la vérification a échoué), l’insertion doit être annulée (rollback).
Foire Aux Questions (FAQ)
1. Qu'est-ce que Merise et à quoi sert-il ?
Merise (Méthode d'étude et de réalisation informatique pour les systèmes d'entreprise) est une méthodologie de conception de systèmes d'information, très utilisée en France. Elle permet de modéliser les données et les traitements d'un système informatique de manière structurée, facilitant ainsi la communication entre les utilisateurs et les développeurs, et assurant la cohérence de la base de données.
2. Quelle est la différence entre un MCD et un MLD ?
Le MCD (Modèle Conceptuel de Données) est une représentation des données qui se concentre sur la sémantique et les relations entre les entités métier, sans prendre en compte les contraintes techniques de la base de données. Le MLD (Modèle Logique de Données), quant à lui, est une traduction du MCD dans un modèle adapté à un type de base de données (par exemple, relationnel). Il spécifie les tables, les colonnes, les clés primaires et étrangères, et leurs types de données.
3. À quoi sert un "trigger" en SQL ?
Un "trigger" (ou déclencheur en français) est un bloc de code SQL qui est exécuté automatiquement en réponse à certains événements sur une table ou une vue de la base de données, tels qu'une insertion (INSERT), une mise à jour (UPDATE) ou une suppression (DELETE) de données. Les triggers sont souvent utilisés pour maintenir l'intégrité des données, appliquer des règles métier complexes, ou pour l'audit des modifications.