Ce document constitue le deuxième Travaux Dirigés (TD N°2) de l'unité "Conception de BD", élaboré pour les étudiants universitaires en informatique ou gestion. Il s'inscrit dans la continuité de l'étude du modèle MERISE, en se concentrant spécifiquement sur ses extensions et révisions (MERISE 2), éléments cruciaux pour la modélisation des systèmes d'information.
Il propose une série d'exercices pratiques invitant à :
- Mettre en application les concepts avancés de MERISE 2.
- Rédiger des requêtes SQL complexes pour la gestion de données.
- Maîtriser les jointures, agrégats, sous-requêtes et opérateurs UNION en SQL.
Modélisation Merise : TD N°2 les extensions Merise 2 corrigé
Télécharger PDFLes Extensions Merise 2 : Révisions et Requêtes SQL pour la Gestion du Personnel
Ce document explore les extensions de la méthode MERISE 2, une approche fondamentale dans la conception de bases de données. Nous réviserons les principes de MERISE et les appliquerons à travers un exercice pratique de gestion du personnel, en nous concentrant sur l'élaboration de requêtes SQL.
Qu'est-ce que MERISE 2 ?
MERISE 2 est une évolution de la méthode MERISE (Méthode de Conception Informatique pour les Systèmes d'Entreprise). Elle introduit des concepts additionnels pour modéliser des systèmes d'information plus complexes, notamment en améliorant la gestion des types et sous-types ainsi que la modélisation orientée objet. Cette version permet une conception plus précise des Modèles Conceptuels de Données (MCD) et des Modèles Logiques de Données (MLD).
Exercice : Gestion du personnel (SQL)
Cet exercice pratique s'appuie sur un Modèle Logique de Données (MLD) et/ou un Modèle Conceptuel de Données (MCD) précédemment établis lors de la première partie du TD N°2. L'objectif est de rédiger plusieurs requêtes SQL pour interroger une base de données de gestion du personnel. Nous utiliserons le MLD dans lequel les sous-types ont été générés, permettant une manipulation directe des tables correspondantes.
Sujet de l'Exercice
En vous servant du MLD et/ou du MCD de la première partie du TD N°2, rédigez les requêtes SQL ci-après décrites. On utilisera le MLD dans lequel les sous-types ont été générés.
Requêtes SQL et Solutions
1. Vendeur ayant réalisé le plus grand CA cumulé.
SELECT * FROM Vendeur Ve INNER JOIN Salarie Sa ON Ve.MatriculeS = Sa.MatriculeS WHERE Ve.CumulCA >= (SELECT MAX(CumulCA) FROM Vendeur);
Note : Pour trouver le vendeur avec le chiffre d'affaires cumulé le plus élevé, il est nécessaire d'utiliser la fonction d'agrégation MAX() dans la sous-requête afin d'obtenir une seule valeur de comparaison. La requête originale était syntaxiquement imprécise pour cette opération.
2. CA cumulé des vendeurs par secteur.
SELECT Se.CodeRegion, Se.NumSecteur, Se.NomSecteur, SUM(Ve.CumulCA) AS CASecteur FROM Vendeur Ve INNER JOIN Salarie Sa ON Ve.MatriculeS = Sa.MatriculeS INNER JOIN Secteur Se ON Se.NumSecteur = Ve.NumSecteur GROUP BY Se.CodeRegion, Se.NumSecteur, Se.NomSecteur;
Note : Si le champ Se.NomSecteur est unique pour chaque combinaison (CodeRegion, NumSecteur), il pourrait potentiellement être retiré de la clause GROUP BY. Cependant, la règle stricte est que tous les champs non agrégés figurant dans la clause SELECT doivent également figurer dans la clause GROUP BY.
3. Liste de tout le personnel, vendeurs et employés inclus (indice : UNION).
SELECT Sa.MatriculeS, Sa.NomS, Sa.PrenomS, Sa.DateEmbS, 'Vendeur' AS TypePersonnel FROM Salarie Sa INNER JOIN Vendeur Ve ON Sa.MatriculeS = Ve.MatriculeS UNION ALL SELECT Sa.MatriculeS, Sa.NomS, Sa.PrenomS, Sa.DateEmbS, 'Employé' AS TypePersonnel FROM Salarie Sa INNER JOIN Employe Em ON Sa.MatriculeS = Em.MatriculeS;
Note : L'indice mentionne UNION. Pour obtenir une liste combinée de tous les vendeurs et employés sous une structure uniforme, l'opérateur UNION ALL est le plus adapté. La requête originale utilisant des LEFT JOIN aurait eu un comportement différent en joignant les types de personnel à la table Salarie plutôt qu'en les listant séparément. Chaque requête SELECT de l'UNION ALL doit retourner le même nombre de colonnes avec des types de données compatibles. Nous avons ajouté une colonne TypePersonnel pour identifier le rôle de chaque individu.
4. CA moyen des secteurs d’une même région pour chaque région.
SELECT Re.CodeRegion, Re.NomRegion, AVG(Req.CASecteur) AS CAMoyen, SUM(Req.CASecteur) AS CATOTAL
FROM ( /* Requête 2. */
SELECT Se.CodeRegion, Se.NumSecteur, Se.NomSecteur, SUM(Ve.CumulCA) AS CASecteur
FROM Vendeur Ve
INNER JOIN Salarie Sa ON Ve.MatriculeS = Sa.MatriculeS
INNER JOIN Secteur Se ON Se.NumSecteur = Ve.NumSecteur
GROUP BY Se.CodeRegion, Se.NumSecteur, Se.NomSecteur
) AS Req
INNER JOIN RegionGeographie Re ON Re.CodeRegion = Req.CodeRegion
GROUP BY Re.CodeRegion, Re.NomRegion;
Note : Cette requête utilise une sous-requête (aliassée Req) pour d'abord calculer le chiffre d'affaires cumulé par secteur (similaire à la requête 2). Ensuite, elle agrège ces résultats par région pour déterminer le CA moyen et total par région. Pour respecter la règle du GROUP BY, Re.NomRegion doit être inclus s'il est présent dans la clause SELECT et n'est pas fonctionnellement dépendant de Re.CodeRegion.
5. Nombre d’employés embauchés par an.
SELECT YEAR(DateEmbS) AS Annee, COUNT(*) AS NombreEmbauches FROM Salarie GROUP BY YEAR(DateEmbS);
Note : Cette requête extrait l'année de la colonne DateEmbS (date d'embauche) et regroupe les résultats par année pour compter le nombre total d'embauches pour chaque année.
Foire Aux Questions (FAQ) sur MERISE 2 et SQL
Qu'est-ce que la méthode MERISE et pourquoi MERISE 2 est-elle nécessaire ?
MERISE est une méthode française de conception de systèmes d'information qui structure le processus de développement en phases (conceptuelle, logique, physique). MERISE 2 est une évolution qui permet de gérer plus efficacement la complexité des systèmes modernes. Elle introduit des concepts de modélisation avancés, comme une meilleure prise en charge des sous-types et une intégration des paradigmes orientés objet, rendant la modélisation plus riche, plus précise et plus adaptée aux exigences actuelles.
Comment les sous-types sont-ils gérés dans un MLD et quel est leur impact sur les requêtes SQL ?
Dans un Modèle Logique de Données (MLD), les sous-types peuvent être modélisés de différentes manières. Une approche courante est de créer des tables séparées pour chaque sous-type, chacune héritant des attributs de la table parent, souvent via une clé primaire-étrangère partagée. Une autre méthode consiste à ajouter toutes les colonnes spécifiques aux sous-types dans la table parent, en utilisant des colonnes de discriminant pour identifier le type d'entité. La méthode choisie pour la gestion des sous-types dans le MLD influence directement la structure des jointures et des conditions de filtrage dans les requêtes SQL, comme illustré avec les tables Vendeur et Employe qui sont des spécialisations de Salarie.
Quelle est la différence entre INNER JOIN, LEFT JOIN et UNION ALL en SQL ?
INNER JOIN (ou jointure interne) retourne uniquement les lignes où il y a une correspondance dans toutes les tables jointes. LEFT JOIN (ou jointure gauche) retourne toutes les lignes de la première table (à gauche de la jointure) et les lignes correspondantes de la deuxième table (à droite) ; si aucune correspondance n'est trouvée pour une ligne de la table de gauche, des valeurs NULL sont retournées pour les colonnes de la table de droite. UNION ALL combine les jeux de résultats de deux ou plusieurs requêtes SELECT distinctes en un seul, sans supprimer les doublons. Chacun de ces opérateurs est utilisé pour des scénarios d'interrogation de données spécifiques en fonction des relations entre les tables et des résultats souhaités.