Projet de conception de base de données

Énoncé

On souhaite créer une base de données qui sera utilisée dans une application de gestion de projet.

Une entreprise qui propose du développement commercial à ses clients souhaite informatiser la gestion de ses projets. L’entreprise gère ses projets en interne en suivant la méthode de conception Merise et en utilisant des méthodes de développement Agile et une organisation de type Kanban.

Les projets sont gérés par un ou plusieurs chefs de projet qui sont responsables de la planification, de l’organisation, du suivi et de la facturation des projets. Chaque projet est caractérisé par un identifiant, un nom, une description, une date de début, une date de fin, un budget, un chef de projet et une liste de membres qui peuvent être des employés ou des prestataires de service. Les projets peuvent être internes ou externes. Les projets internes sont des projets réalisés pour l’entreprise elle-même et les projets externes sont des projets réalisés pour des clients. Les projets peuvent être liés à des entreprises qui sont des clients de l’entreprise. Les projets peuvent être liés à des fichiers qui sont des documents de travail.

Chaque membre est caractérisé par un identifiant, un nom, un prénom, une date de naissance, une adresse, un numéro de téléphone et un email et peut être rattaché à des entreprises ou des prestataires de service s’ils ne font pas partie de l’entreprise. Les membres ont un rôle principal comme par exemple développeur, testeur, analyste, chef de projet, commercial, prestataire, etc. mais peuvent aussi être rattachés à des projets avec des rôles différents. Les membres peuvent être affectés à des projets et peuvent être responsables de tâches. Les membres peuvent être liés à des fichiers qui sont des documents de travail. Chaque entreprise est caractérisée par un identifiant, un nom, l’adresse de son siège, un numéro de téléphone et une adresse email générale.

Les projets sont organisés en listes de tâches. Les tâches sont caractérisées par un identifiant, un nom, une description, une date de début, une date de fin, un coût estimé, un coût réel et un statut (terminée ou pas) . Les tâches peuvent être liées à d’autres tâches et peuvent être assignées aux différents acteurs rattachés au projet. Elles peuvent être liées à des fichiers qui sont caractérisés par un identifiant, un nom, une description, une date de création, une date de modification, une taille, un type, un nom de fichier ainsi qu’un chemin d’accès. On peut créer des labels pour les tâches qui sont caractérisés par un identifiant, un nom, une couleur et une description. Tous les membres d’un projet n’ont pas les mêmes droits sur l’administration du projet. Certains membres peuvent créer de nouvelles listes et nouvelles tâches, d’autres peuvent uniquement consulter les tâches et les listes existantes, etc.

L’application devra pouvoir afficher la liste des projets auxquels un membre a accès, puis afficher les projets en détails, les membres affectés à un projet, les tâches affectées à un projet, les tâches affectées à un membre, les tâches affectées à un fichier, les tâches affectées à un label pour un projet donnée, les tâches affectées à une tâche, les membres qui sont assignés à tous les projets, les membres qui ne sont assignés à aucune tâche, les membres qui ont terminé toutes leurs tâches, etc. Le client souhaite pouvoir créer des rapports sur les projets, autrement dit on souhaite pouvoir faire un regroupement des informations sur les projets pour générer les rapports. À tout moment, on souhaite pouvoir savoir si des projets dépassent le budget initial en fonction du coût réel des tâches déjà réalisées.

On souhaite aussi pouvoir suivre l’avancement des projets en fonction des tâches réalisées et des tâches restantes et générer des plannings pour les projets (par tâches, par membres, par projet, etc.).

Demande générale

1. Vous devez proposer une étude conceptuelle et logique qui réponde au mieux à cet énoncé.
2. Vous devez prendre des décisions et justifier vos choix dans votre rapport pour proposer une solution logique, en 3ème forme normale, permettant de répondre aux différentes contraintes et requêtes demandées par la suite.
3. Vous devez produire une base de données MySQL ou PostgreSQL avec des données factices qualitatives pour tester votre base de données et les requêtes demandées.
4. Vous devez rendre un rapport au format MarkDown, les différents fichiers nécessaires pour la création de la base de données (DBML, SVG, DDL, DML, PHP, etc.) le tout dans un dépôt Github privé.
5. Le projet sera noté sur 40 points, 5 points étant attribués à la gestion du projet (dépôt Github, gestion de projet, qualité des rendus, etc.), 10 points à la conception, 10 points au modèle logique de données, 10 points aux contraintes et procédures stockées et 5 points aux requêtes DQL.

Conseil

Pensez à bien lire et étudier les requêtes et triggers demandés avant de démarrer votre analyse conceptuelle.

Partie 1 - Conception (MCD) - 10 points

1. Lisez l’ensemble du sujet du projet au moins 2 fois. Lors de votre seconde lecture, commencez à compléter un tableau Taiga (ou autre outil de gestion de projet) avec les différentes tâches à effectuer (Créer une liste par partie du projet par exemple, puis créez des tâches adéquates au déroulement du projet, etc).

2. Extrayez les propositions pertinentes de l’énoncé, les transformer en propositions unaires et binaires, commencer à identifier les contraintes potentielles

3. Proposez, sous forme de textes courts, les entités, attributs, associations et cardinalités

4. Identifiez les contraintes statiques et dynamiques en fonction de l’énoncé et des requêtes et triggers demandés

5. Identifiez les dépendances fonctionnelles

6. Réalisez un schéma entité-association à l’aide du langage DBML :

   1. Commentez les attributs, relations et associations
   2. Ajoutez les contraintes d’intégrité référentielle et statiques (si possible)
   3. Définissez les types de données et les contraintes de vérification
   4. Définissez les comportements de la cascade pour les suppressions et les mises à jour

7. Effectuez une passe de normalisation pour vérifier que le schéma est en 3NF (3ème forme normale) et qu’il n’y a pas de redondance ou d’anomalie. Le rapport de normalisation doit être inclus dans votre rapport.

8. Effectuez une passe de vérifications et de corrections en fonction des requêtes et contraintes demandées en parties 3 et 4 pouz vous assurez que votre conception permettra d’y répondre.

9. Créez un dépôt privé sur Github puis y ajouter les fichiers suivants :

   1. • 00-gestion-rapport.md contenant votre décomposition et analyse, ainsi que les informations sur votre projet (Noms, Prénoms, URL du Github, URL de l’outil de gestion de projet, etc)
   2. • 01-gestion-mcd.dbml contenant votre schéma au format DBML
   3. • 02-gestion-erd.svg contenant votre schéma au format SVG

10. M’ajouter à votre dépôt Github (pierre.blarre@univ-grenoble-alpes.fr) et votre outil de gestion de projet (Taiga, Trello, etc).

11. Envoyez-moi un email avec les noms et prénoms des membres de votre groupe, le lien vers votre dépôt Github et votre outil de gestion de projet.

Rendu

Vous devez me rendre votre conception sur Moodle le dimanche 2 mars 2025 à 23h59 au plus tard.

Partie 2 - Modèle Logique de données - 10 points

2.1 - Génération du DDL

1. Produisez le code SQL de création de la base de données (DDL) à partir du fichier 01-gestion-mcd.dbml et l’outil dbml2sql et sauvegardez le fichier sous le nom 03-gestion-ddl.sql
   • Attention à bien générer le code pour MySQL ou PostgreSQL en fonction de votre choix.
2. Testez l’importation de votre base de données dans un SGBD MySQL. L’importation doit se faire sans erreurs.
3. Installez Dotenv dans votre projet PHP pour gérer les variables d’environnement et les informations de connexion à la base de données.

Vous pouvez vous inspirer du Github d’exemple de la conception de la bdd boutique pour vous aider, ainsi que de cet exemple de fichier .env :

DB_DRIVER=mysql
DB_HOST=localhost
DB_PORT=3306
DB_NAME=[YOUR_DB_NAME]
DB_USER=[YOUR_DB_USERNAME]
DB_PASS=[YOUR_DB_PASSWORD]

DBML_FILE=01-gestion-mcd.dbml
SVG_FILE=02-gestion-erd.svg
DDL_FILE=03-gestion-ddl.sql
FAKER_FILE=04-gestion-faker.php
DML_FILE=05-gestion-dml.sql

4. Créez un fichier generate.php et y ajouter les commandes nécessaires pour :

   • Générez le fichier 02-gestion-erd.svg à partir du fichier 01-gestion-mcd.dbml
   • Générez le fichier 03-gestion-ddl.sql à partir du fichier 01-gestion-mcd.dbml
   • Vous pouvez vous inspirer de l’exemple de la boutique

5. Effectuez un commit Git pour sauvegarder vos modifications.

Aide

• On génère le code SQL à partir du DMBL avec l’outil dbml2sql en ligne de commande.
• On génère le schéma entité-association au format SVG à partir du fichier DBML avec l’outil dbml-renderer en ligne de commande.
• On peut importer le fichier SQL dans un SGBD MySQL avec l’outil mysql en ligne de commande ou bien récupérer le code SQL et l’exécuter avec une instance HPP de PDO
• On peut exécuter des commandes système avec PHP en utilisant la fonction shell_exec ou exec. Vous pouvez vous référer au Github d’exemple de la conception de la bdd boutique pour vous aider.

2.2 - Génération de données factices

1. En utilisant PHP Faker, créer un scénario de génération de données factices qualitatives pour votre base de données. On souhaitera à minima :

Scénario de données factices minimal

On souhaite générer le scénario suivant :

• 10 entreprises
• 50 membres
• 10 rôles
• 5 projets
• 20 listes
• 500 tâches
• 100 fichiers
• 10 labels
• 100 affectations de membres à des tâches
• 100 affectations de fichiers à des tâches
• 100 affectations de fichiers à des membres
• 100 affectations de membres à des projets avec des rôles différents
• 20 affectations de fichiers à des projets
• 100 affectations de labels à des tâches
• 100 affectations de tâches à des tâches
• 100 affectations de tâches à des projets
• 100 affectations de tâches à des membres
• 100 affectations de tâches à des fichiers
• 100 affectations de tâches à des labels
• Un membre qui est assigné à tous les projets
• Un membre qui n’est assigné à aucune tâche
• Un membre pour qui toutes ses tâches sont terminées
• Un membre qui a tous les rôles
• Un projet qui a dépassé son budget

Vous pouvez ajouter d’autres cas de figure si vous le souhaitez.

2. Sauvegardez le code PHP dans un fichier 04-gestion-faker.php et effectuez un commit Git.

3. Générez un jeu de données SQL satisfaisant, sauvegarder les données SQL sous le nom 05-gestion-dml.sql puis effectuez un commit Git.

4. Mettez à jour le fichier generate.php pour injecter les données SQL à partir du fichier 05-gestion-dml.sql, et en option relancer la génération du scénario de données factices puis effectuez un commit Git.

Partie 3 - Contraintes et procédures stockées - 10 points

Créer un fichier 06-gestion-constraints.sql contenant :

1. Toutes les contraintes supplémentaires et nécessaires pour garantir l’intégrité des données dans la base de données

2. Les triggers suivants :

• Une date de fin de projet ou de tâche ne peut pas être antérieure à sa date de début
• Un membre ne peut pas être assigné à une tâche s’il n’est pas assigné au projet
• Un membre ne peut pas créer de tâche s’il n’a pas le rôle adéquat
• La somme des coûts estimés des tâches d’un projet devrait déclencher une alerte si le coût estimé du projet est dépassé

3. les procédures suivantes :

• Affecter un membre à toutes les tâches d’une liste de tâche donnée

4. Mettez à jour vos fichiers .env et generate.php en conséquence, puis effectuez un commit Git pour sauvegarder vos modifications.

Partie 4 - Requêtes - DQL - 5 points

Créer un fichier 07-gestion-dql.sql contenant les requêtes ou vues SQL suivantes :

1. Liste des projets avec le coût estimé et le coût réel actuel
2. Liste des membres affectés à un projet ainsi que le nom des entreprises auxquelles ils sont rattachés
3. Liste des tâches affectées à un projet
4. Liste des tâches affectées à un membre
5. Liste des tâches affectées à un fichier
6. Liste des tâches affectées à un label pour un projet donnée
7. Liste des tâches affectées à une tâche
8. Liste des membres qui sont assignés à tous les projets
9. Liste des membres qui ne sont assignés à aucune tâche
10. Liste des membres qui ont terminé toutes leurs tâches

Mettez à jour vos fichiers .env et generate.php en conséquence, puis effectuez un commit Git pour sauvegarder vos modifications.

Partie 5 - Rendu final - 5 points

1. Vérifiez que votre dépôt Github est complet, que tous les fichiers nécessaires sont présents et que je suis bien ajouté
2. Vérifiez que votre outil de gestion de projet est à jour et que je suis bien ajouté
3. Vérifiez que votre fichier generate.php est fonctionnel et que vous pouvez générer votre base de données et vos données factices en une seule commande
4. Réalisez une nouvelle installation de votre projet depuis votre dépôt Github pour vérifier que tout fonctionne correctement : Le fichier README.md doit contenir les instructions pour installer et générer la base de données
5. Vérifiez et testez vos archives ZIP avant de les déposer sur Moodle
6. Déposez vos archives ZIP correctement nommées sur Moodle avant les dates limites