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 universitaires souhaitant approfondir leurs compétences en modélisation et en manipulation de données.
À travers deux exercices pratiques, ce corrigé couvre les notions suivantes :
- Les extensions du modèle Merise 2 (modèle conceptuel de données, spécialisations, contraintes d'intégrité) ;
- La traduction en schéma relationnel ;
- L'élaboration de requêtes SQL complexes.
Modélisation Merise : Devoir n°1 Merise 2 et SQL corrigé
Télécharger PDFConception de Bases de Données : Exercice Corrigé Merise 2 et SQL
Ce document présente la correction d'un exercice pratique axé sur la conception de bases de données, en utilisant les extensions du modèle Merise 2 et les requêtes SQL. Il aborde des concepts fondamentaux pour les ingénieurs en étude et développement, ainsi que pour toute personne souhaitant maîtriser la modélisation des données.
Exercice 1 : Modèle Conceptuel de Données (MCD)
Cet exercice se concentre sur la création d'un Modèle Conceptuel de Données (MCD) pour une société de services informatiques, PRASORIASTE, afin d'améliorer le suivi de ses projets.
Contexte
L’agence orléanaise de la société de services informatiques PRASORIASTE dispose de deux plateaux de développement constitués d’ingénieurs étude et développement. Le premier plateau, orienté « télécommunication », s'occupe de projets d’envergure à destination d’opérateurs de télécommunication et d’organismes financiers. Le second plateau s’occupe des autres projets, souvent également d’envergure. Les ingénieurs travaillant sur ces deux plateaux, très largement issus de cursus Bac+5 de l’Université d’Orléans et de Polytech Tours, sont regroupés sous forme d’équipes projet.
Afin d’améliorer le suivi de ses projets, l’agence orléanaise de PRASORIASTE souhaite, à titre expérimental, mettre en place un outil de gestion de projet développé sur mesure. Pour ce faire, elle a confié le développement de la première version du logiciel à des stagiaires dans le cadre de leur stage de fin d’études. En tant que stagiaire, vous avez à charge la modélisation des données du logiciel à concevoir. Ce modèle de données devra prendre en compte les attentes de PRASORIASTE en matière de gestion de projet.
Fonctionnement Attendu du Logiciel
Pour l’agence orléanaise, l’objectif général du logiciel est double :
- Permettre à PRASORIASTE d’établir des plannings hebdomadaires (par semaine) : par salarié, par projet, etc. Ces plannings ont vocation à lui permettre d’adapter ses effectifs à la charge de travail.
- Permettre à PRASORIASTE de suivre le coût de ses projets, la rentabilité de ces derniers et l’efficacité de ses ingénieurs.
Le logiciel stockera les salariés ingénieurs étude et développement intervenant de manière habituelle sur ses plateaux de développement. En pratique, l’appartenance à un plateau de développement n’a aucune importance. Lorsque la charge de travail devient trop importante, PRASORIASTE fait appel à des ingénieurs en provenance d’autres agences. Ces salariés occasionnels seront également stockés en base de données. Il conviendra de pouvoir identifier les salariés qui sont habituels de ceux qui ne le sont pas. On notera qu’un salarié occasionnel finit parfois par devenir un salarié habituel en cas de mutation. Et il convient de savoir quand la mutation a eu lieu.
PRASORIASTE, selon le cas, souhaite pouvoir évaluer l’amélioration de l’efficacité de ses salariés à compter de leur date d’embauche ou à compter de leur mutation. Quoi qu’il en soit, tous les salariés sont identifiés par un matricule, ont un prénom, un nom et un salaire brut.
Le logiciel permettra de suivre des projets, ayant entre autres un code, un libellé, une date de début et une date fin prévisionnelles, une date de début et une date fin effectives, un prix de vente. Un projet est divisé en tâches ayant une référence faisant elle-même référence au projet (exemple : projet « ALPHABRAVO », la tâche de référence « #10 » s’écrit « ALPHABRAVO#10 »). Chaque tâche a par ailleurs un libellé, une description, une durée prévisionnelle et une durée effective exprimée en jours homme. Une tâche a finalement une donnée état (exemple : « En attente », « En cours », « Terminée ») pouvant varier dans le temps.
Chaque salarié affecté au projet a un ou plusieurs rôles sur ce projet (exemple : « chef de projet », « développeur », « testeur », etc.). D’ailleurs, plusieurs salariés peuvent avoir le même rôle sur ce projet. Et à chaque rôle correspond un tarif journalier, exprimé en €. Les salariés affectés au projet peuvent ensuite être affectés à une tâche pour une période donnée.
Question : Proposer un MCD
Proposer un MCD permettant de répondre au besoin décrit ci-avant. Vous justifierez succinctement vos choix (extensions Merise 2 et cardinalités) sur votre copie.
Justifications des Choix du MCD
- Contrainte d’inclusion entre les associations « planning », « affectation » et « diviser » : Cette contrainte permet de modéliser la règle de gestion qui consiste à faire en sorte qu’un salarié ne puisse être affecté à une tâche d’un projet que si ce salarié est affecté à ce projet. Elle assure la cohérence des données et des affectations.
- Identifiant relatif (1,1) entre Tâche et Projet : Une tâche est relative à un projet, c'est-à-dire qu’elle n’a de sens que pour un projet donné. Elle en est dépendante. Si l’on supprime le projet, les tâches du projet n’ont plus lieu d’être conservées. D’où la nécessité de recourir à un identifiant relatif, qui intègre l'identifiant du projet dans celui de la tâche.
- Historisation de l'état d'une tâche (H) : Une tâche a un seul état à un instant donné, d’où la cardinalité (1,1). Cependant, l’état varie au travers du temps. L’on peut dès lors souhaiter conserver l’historique des états que la tâche a pris au travers du temps. On modélise cette historisation grâce au symbole (H), indiquant que toutes les modifications de cet attribut doivent être tracées.
- Spécialisation des salariés : On distingue deux types de salariés (habituel et occasionnel) ayant des propriétés distinctes, ce qui justifie le recours à une spécialisation. On utilise un héritage de type « T » (totalité) car un salarié est nécessairement de l’un de ces deux types. Toutefois, un salarié occasionnel peut devenir salarié habituel. Les deux types ne s’excluent donc pas (spécialisation non disjointe).
- Association ternaire « affectation » : Elle permet de respecter le fait qu’un salarié peut participer à plusieurs projets, qu’il peut avoir plusieurs rôles sur un même projet et que plusieurs salariés peuvent avoir le même rôle sur un même projet, tout en associant un tarif journalier à ce rôle spécifique sur ce projet.
- Association ternaire « planning » : Une tâche peut être commune à plusieurs salariés et un salarié peut avoir plusieurs tâches figurant dans son planning. Toutefois, il est convenu que la tâche est attribuée au salarié pour une période donnée. Ceci ajoute une dimension temporelle à cette relation. En notation Merise, cela se traduit par la nécessité d’ajouter une troisième entité, représentant la dimension temporelle. Finalement, on obtient une ternaire.
Barème de l'Exercice 1
- Bonnes entités (N.B. : entités cohérentes) : 1 point
- Bonnes propriétés (N.B. : présence des informations nécessaires au logiciel, clés primaires soulignées) : 1 point
- Bonnes cardinalités : 1 point
- Spécialisation : 1,5 point
- Spécialisation présente (0,5 point)
- Type de spécialisation (1 point si « T » ; 0,5 si « XT » ; 0 sinon)
- Ternaire « affectation » : 1 point
- Ternaire « planning » : 1 point (Alternative : représentation approximative au moyen de deux propriétés portées, 0,5 point)
- Contrainte d’inclusion : 1 point
- Identifiant relatif : 1 point
- Historisation : 0,5 point
Commentaire : Toute solution cohérente et justifiée était acceptée.
Exercice 2 : Modèle Logique et Requêtes SQL
Ce deuxième exercice aborde la transformation d'un modèle conceptuel en schéma relationnel et la formulation de requêtes SQL pour interroger les données.
Sujet
Basé sur le schéma relationnel fourni (représentant une partie d'un modèle de données de gestion de commandes, excluant certaines entités et associations pour cet exercice), répondez aux questions suivantes.
Questions
1. Schéma Relationnel
Rédiger le schéma relationnel correspondant à toutes les entités et associations, sauf :
- Associations : « réservation », « montable sur »
- Entités : « CLIENT », « DEPOT » et « MATERIEL »
Solution :
- Article(Code, Libelle, PrixUnitaire, Type)
- ArticleRemplacement(#Original, #Remplacant)
- Clé étrangère : Original en référence à Article(Code)
- Clé étrangère : Remplacant en référence à Article(Code)
- PieceDatachee(#CodeArticle, Fonction, Fabriquant)
- Clé étrangère : CodeArticle en référence à Article(Code)
- Consommable(#CodeArticle, Categorie, Conditionnement)
- Clé étrangère : CodeArticle en référence à Article(Code)
- Stock(#SigleDepot, #CodeArticle, QteDisponible)
- Clé étrangère : SigleDepot en référence à Depot(Sigle)
- Clé étrangère : CodeArticle en référence à Article(Code)
- Commande(Num, Creation, Livraison, #NumClient, #NumFacturation, #SigleDepot)
- Clé étrangère : NumClient en référence à Client(Num)
- Clé étrangère : NumFacturation en référence à Client(Num)
- Clé étrangère : SigleDepot en référence à Depot(Sigle)
- LigneCommande(#NumCommande, Num, #CodeArticle, QteCommandee)
- Clé étrangère : NumCommande en référence à Commande(Num)
- Clé étrangère : CodeArticle en référence à Article(Code)
Barème :
- Bonnes clés primaires : 1,5 point (-0,5 point par clé erronée)
- Bonnes clés étrangères : 2,5 points (-0,5 point par clé manquante ou erronée).
2. Contraintes d’Associations
Quelles sont les contraintes d’associations ? Et quelle est la signification de chacune d’entre elles ?
Solution :
Il y a deux contraintes d’associations :
- Une contrainte d’exclusion entre les associations « passé par » et « facturé à autre » : Cette contrainte signifie que, si la commande passée par un client X est facturée à un client Y, on doit impérativement avoir X ≠ Y. Elle empêche un client d'être à la fois l'initiateur et le "facturé à autre" de la même commande.
- Une contrainte d’inclusion entre les associations « réservation » et « stocké dans » : Cette contrainte signifie qu’il ne doit être possible de réserver un article que s’il figure dans le stock d’un dépôt. Une réservation est donc subordonnée à l'existence de l'article en stock.
Barème : 2 points, un par contrainte d’association (dénomination + signification).
3. Requête SQL de consultation du stock global
Établir la requête SQL permettant de consulter la liste de tous les articles (code et libellé) avec son stock global (quel que soit le dépôt).
Solution :
SELECT Code, Libelle, SUM(QteDisponible)
FROM Article INNER JOIN Stock ON Article.Code = Stock.CodeArticle
GROUP BY Code, Libelle;
Barème : 3 points, un point par ligne juste.
4. Requête SQL des articles les plus commandés
Établir la requête SQL permettant de connaître par année les articles (code et libellé) commandés plus de 100 fois.
Solution :
SELECT Article.Code, Article.Libelle, YEAR(Commande.Creation)
FROM Article INNER JOIN LigneCommande ON Article.Code = LigneCommande.CodeArticle
INNER JOIN Commande ON LigneCommande.NumCommande = Commande.Num
GROUP BY Article.Code, Article.Libelle, YEAR(Commande.Creation)
HAVING SUM(QteCommandee) > 100;
Barème : 2 points. Toute réponse cohérente est acceptée.
Foire Aux Questions (FAQ)
Qu'est-ce qu'un identifiant relatif en Merise et pourquoi l'utiliser ?
Un identifiant relatif en Merise est un identifiant d'entité qui incorpore l'identifiant d'une autre entité dont elle est fortement dépendante. Il est utilisé pour modéliser une dépendance existentielle forte, où l'entité "enfant" n'a de sens que dans le contexte de l'entité "parent". Par exemple, une tâche qui dépend intrinsèquement d'un projet sera identifiée par une combinaison de son propre identifiant et de l'identifiant du projet, ce qui implique que la suppression du projet entraîne la suppression des tâches associées.
Comment modélise-t-on l'historisation d'un attribut en Merise ?
L'historisation d'un attribut en Merise est indiquée par le symbole (H) à côté de l'attribut concerné dans le Modèle Conceptuel de Données (MCD). Cela signifie que toutes les valeurs successives prises par cet attribut au cours du temps doivent être conservées. En Modèle Logique de Données (MLD), cela peut se traduire par la création d'une nouvelle entité d'historique qui stockera l'identifiant de l'entité principale, la valeur de l'attribut à un instant T, et la période de validité de cette valeur.
À quoi sert une contrainte d'inclusion entre associations ?
Une contrainte d'inclusion spécifie qu'un ensemble de faits issus d'une association doit être inclus dans un ensemble de faits issus d'une autre association. Elle garantit la cohérence des données en imposant qu'une action ou un état ne puisse exister que si une condition préalable (représentée par l'autre association) est remplie. Par exemple, un salarié ne peut être affecté à une tâche d'un projet que s'il est déjà affecté au projet global, assurant ainsi la logique des opérations.