Résolution de problematique

L’entreprise JeGère fait le développement de logiciel pour des entreprises au Québec. Pour faire la gestion des projets elle utilise un système informatique qui permet de faire le suivi de chaque projet, ses étapes, ses responsables, ses participants, entre autres. À chaque projet on donne un identificateur unique (un numéro entre 0 et 4000) et un responsable qui est un employé de l’entreprise. De plus, l’entreprise garde le nom, la date de début et de fin du chaque projet. Un projet est divisé par des étapes. Chaque étape a un livrable, une date de début et une date de fin. Cependant, l’entreprise a déjà quelques étapes définis qui peuvent être utilisées dans plusieurs projets. Finalement un projet a des ressources humaines qui travaillent un nombre d’heures dans le projet et qui ont un prix par heure déterminé. (Annexe 1) Exercice 1 - Définition du schéma (Question 1 à 3) À l’aide du langage SQL de MySQL, faites et exécutez les commandes SQL suivantes pour : Créez le schéma ’jegere’ (indice : CREATE DATABASE jegere ;) puis sélectionnez-le (indice : USE jegere ; ). Créez les tables de la Figure 1, en incluant les contraintes d’unicité de clé, référentielles, de domaine et de non nullité. (Indice : CREATE TABLE) Par exemple, vous aurez CREATE TABLE IF NOT EXISTS [login to view URL] ( idProjet INT NOT NULL , idClient INT NOT NULL , nomProjetVARCHAR(45) NOT NULL , dateDebut DATE NOT NULL , dateFin DATE NOT NULL , idResponsableINT NOT NULL , PRIMARY KEY (idProjet) , FOREIGN KEY (idClient ) REFERENCES [login to view URL] (idClient ), FOREIGN KEY (idResponsable ) REFERENCES [login to view URL] (idEmploye ) ) ; Générez les commandes pour insérer l’information suivante. Il est permis de reformater les données, par exemple lors de l’insertion des dates. (Fichier annexe 2) Exercice 2 - Recherches de données Écrivez avec le langage SQL les requêtes pour : Trouver les employés qui ont travaillé sur au moins 2 projets. Donner le nom et le livrable de l’étape actuelle de chaque projet de l’entreprise. (L’étape actuelle est la dernière étape pour laquelle nous avons des données.) Indices : Vous pouvez utiliser la syntaxe SELECT x,y FROM ... WHERE y = (SELECT MAX(z) FROM ... WHERE ...). Vous ne devez pas utiliser un GROUP BY. Étant donné un identifiant de projet (idProjet), vous voulez trouver la valeur maximale de l’identifiant d’étape (idEtape). Vérifier que les étapes de chaque projet ont été faites entre les dates de début et de fin du projet. (Écrivez une requête qui permette à un humain de savoir si c’est le cas. On ne vous demande pas d’ajouter cette contrainte à la base de données.) Donner le nombre d’heures total travaillés et la somme totale gagnée par employé dans tous les projets, en incluant le nom de l’employé et l’adresseCourriel. (Résultat attendu : idemploye, nomEmploye, adresseCourriel, nbrHeures, somme gagnée) Vous devez exécuter les requêtes et inclure la réponse du SGBD. Expliquez votre solution. Exercice 3 - Mises à jour de données Le projet 3 a eu des modifications importantes, donnez les commandes pour modifier l’information dans la base de données : Le responsable du projet a changé et il est maintenant Mme St-Jerome. L’étape 3 vient de finir, donc l’étape 4 a été ajoutée au projet. (Vous pouvez utiliser deux requêtes SQL pour résoudre ce problème.) Le taux horaire de Mme St-Jerome a augmenté de 12$. Le projet 2 doit être supprimé complètement, donnez les commandes et l’ordre d’exécution de ces commandes. Vous devez exécuter les requêtes et inclure la réponse du SGBD. Expliquez votre solution.