Implémentation SQL - 10% individuel

À partir d'une mise en contexte sommaire, vous devez implémenter une base de données qui assure l'intégrité de l'information stockée. Votre livrable est composé de 2 scripts SQL:

• init.sql mise en place la base de données, insertion de données de départ
  • doit pouvoir être exécuté plusieurs fois, sur une BD arbitraire
• tests.sql démontre le fonctionnement des spécifications demandées par diverses requêtes SQL
  • Chaque fonctionnalité et les commandes SQL de test correspondantes est clairement identifiée

Contexte

Une entreprise utilise actuellement un système de gestion de projet rudimentaire avec MySQL et souhaite améliorer sa méthodologie de travail en plus de migrer vers MariaDB.

Voici le modèle visé par l'entreprise(les entitées sous la ligne pointillée sont à implémenter):

  +-----------------------------------+                    +--------------------------------+
  |              users                |                    |         users_projects         |
  +-----------------------------------+                    +--------------------------------+
  | PK id: int                        | 1                * | PK,FK user_id: int             |
  | firstname: string(50)             +--------------------+ PK,FK project_id: int          |
  | lastname: string(50)              |                    | join_date: date                |
  | fullname: string(101) [GENERATED] |                    +----------------+---------------+
  | email: string(50)                 |                                     |
  | password: string(64) [SHA256]     |                                     |*
  +-------+-------+-------------------+                                     |
          |       |                                                         |1
          | 1     |1                                                        |
          |       |                                            +------------+-------------+
          |       |                                            |         projects         |
          |       |                                            +--------------------------+
          |       |                                            | PK id: int               |
          |       |                                            | name: string(50)         |
          |       |                                            | description: string(300) |
          |       |                                          * | creation_date: date      |
          |       +--------------------------------------------+ FK owner: int            |
          |                                                    |                          |
          |                                                    +------------+-------------+
          |                                                                 |
          |                                                                1|
          |                                                                 |
+------------------------------------------------------------------------------------------------+
          |                                                                 |
          |                          +----------------------+               |
          |                          |         tasks        |               |
          |                          +----------------------+               |
          |                          | PK id: int           |               |
          |                          | name: string(50)     |               |
          |                          | comment: string(300) |               |
          |                          | hours_estimate: int  | *             |
          |                        * | FK project_id: int   +---------------+
          +--------------------------+ FK user_id: int      |
                                     +----------+-----------+
                                                |
                                                | 1
                                                |
                                                | *
                                                |
                                  +-------------+--------------+
                                  |         worktimes          |
                                  +----------------------------+
                                  | PK id: int                 |
                                  | message: string(100)       |
                                  | begin: datetime            |
                                  | end: datetime              |
                                  | duration: int [GENERATED]  |
                                  | FK task_id: int            |
                                  +----------------------------+

Spécifications

Les spécifications requises sont les suivantes:

• users_projects
  • On ne peut pas ajouter un utilisateur en tant que collaborateur s'il est le propriétaire du projet
  • La date d'inscription du collaborateur ne peut pas précéder la date de création du projet
• projects
  • La date de création du projet ne peut pas être dans le futur
  • Le nom ne peut pas être vide, incluant les espaces
• tasks
  • Le nombre d'heures estimé doit être supérieur ou égal à 1
  • On doit pouvoir faire une recherche textuelle avancée(avec opérateurs) sur le nom ET le commentaire
  • l'utilisateur doit faire partie des membres du projet(créateur ou participant)
• worktimes
  • le temps de fin doit être supérieur au temps de début
  • la durée est l'interval de temps entre le début et la fin en heures, arrondie à l'heure supérieure
• Un mécanisme réutilisable doit calculer le temps investi dans un projet par chacun des membres
• Un mécanisme réutilisable doit produire le rapport des projets: champs pertinents du projet, identification explicite du créateur, identification explicite de l'utilisateur le plus investi et du nombre d'heures, liste des membres, nombre total d'heures travaillées sur le projet, nombre total d'heures estimées sur le projet, pourcentage de complétion(heure travaillées/heures estimées)
• La suppression d'une entitée doit entraîner la suppression des enregistrements associés
• Pour chaque spécification, vous devez fournir les requêtes SQL suffisantes pour en démontrer le fonctionnement(dans le fichier tests.sql)

Méthodologie

J'agirai en tant que chargé de projet pour répondre à vos questions dans le but de préciser l'analyse sommaire fournie et orienter la résolution de vos défis techniques.

ATTENTION

• Retirer les mentions du schéma de votre script SQL pour permettre l'exécution sur n'importe quelle base de données.
• Si vous générez un script à partir de Workbench, MariaDB ne supporte pas l'attribut VISIBLE pour les index.

Modalités d’évaluation

Une pénalité de 10% sera appliquée à la note du travail pour chaque jour de retard à la remise.

Instructions de remise

Vous devez remettre via LÉA une archive zip contenant vos fichiers init.sql et tests.sql.

Critères d’évaluation