• Accueil|
• Feuille de route|
• Manuel du cours

Le langage SQL

Réaliser le travail pratique 2

Présentation

Cette activité est consacrée à la réalisation du travail pratique 2 qui compte pour 25 % de la note finale. Le travail consiste à utiliser le langage SQL pour créer un schéma de base de données, pour y insérer des tuples et pour faire des recherches dans la base. Pour réaliser ce travail, vous devez utiliser le logiciel MySQL, que vous installerez dans votre ordinateur en suivant les consignes du guide d’installation présenté ci-dessous.

Vous devez rédiger un court rapport (en format « pdf » ou « Word »), que vous déposerez en utilisant l’outil de dépôt des travaux notés.

Quand on vous demande de produire une requête SQL, vous devez non seulement fournir la requête mais aussi une brève explication ainsi que le résultat de votre requête. Si vos réponses ne sont pas complètes, vous pouvez perdre une partie ou la totalité des points.

Vous devez commencer par installer MySQL

On vous invite à installer MySQL sur votre machine dès maintenant si ce n’est pas déjà fait.

• Installation de MySQL 5.7 (ou ultérieur) sur un PC Windows
• Installation de MySQL 5.7 (ou ultérieur) sur un Mac

Documentation MySQL

La syntaxe du SGBD MySQL est disponible en ligne et en français.

Consignes supplémentaires

• Dans tous les cas, incluez avec votre solution le résultat de vos requêtes.
• Vous ne devez pas utiliser de procédures stockées ou de déclencheur. Il n’est pas nécessaire de vous préoccuper des transactions même si elles pourraient être souhaitables dans la pratique.
• Vous pouvez utiliser des requêtes GROUP BY, mais celles-ci doivent comporter un agrégat (MAX, MIN, AVG, COUNT, SUM). Par exemple, cette requête n’est pas acceptable dans ce cours bien qu’elle puisse être acceptée par MySQL : SELECT x,y FROM table GROUP BY x.
• Il est permis d’utiliser un SGBD autre que MySQL (par ex. Oracle ou sqlite), mais vous assumez alors la responsabilité de tous les problèmes techniques que vous rencontrez.

Contenu du travail

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éfinies 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é.

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 jegere.Projet (
        idProjet INT NOT NULL ,
        idClient INT NOT NULL ,
        nomProjetVARCHAR(45) NOT NULL ,
        dateDebut DATE ,
        dateFin DATE ,
        idResponsableINT NOT NULL ,
        PRIMARY KEY (idProjet) ,
        FOREIGN KEY (idClient ) REFERENCES jegere.Client (idClient ),
        FOREIGN KEY (idResponsable ) REFERENCES jegere.Employe (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.

  Client
  idClient	nomClient	adresse	telephone	adresseCourriel
  321	Financière Quebec	1234 Rue La Montagne, Trois Rivières, QC	819 3765244	info@fquebec.qc.ca
  345	Services Comptables Garneau	8721 Rue St Laurent, Montreal, QC	514 3217896	services@comptablegarneau.ca

  Employe
  idEmploye	nomEmploye	adresse	Telephone	adresseCourriel
  1876	Martin Rey	3345 Avenue Poirier, Montreal, QC	514 9871245	martin.rey@jegere.ca
  2231	Jean Pierre Bordeau	2309 Boulevard Pie XII, Quebec, QC	418 6573298	jean.bordeau@jegere.ca
  4354	Louise Gagnon	2101 Blvd Bois Franc, Trois Rivières, QC	819 6574028	louise.gagnon@jegere.ca
  1212	Marie St-Jerome	1111 Avenue Jean François, Montreal, QC	514 4932876	marie.stjerome@jegere.ca

  Projet
  idProjet	idClient	nomProjet	dateDebut	dateFin	idResponsable
  1	321	Développement du site web	01/08/2011		1876
  2	321	Maintenance du système de ressources humaines	01/05/2012	23/07/2012	2231
  3	345	Développement du système de gestion de fournisseurs	01/11/2011		2231

  RessourcesProjet
  idProjet	idEmploye	nbrHeure	PrixHeure
  1	1876	500	65
  1	4354	2000	31
  2	2231	250	55
  3	2231	500	65
  3	1212	3000	35
  3	1876	2000	35

  Etape
  idEtape	nomEtape	Livrable
  1	Démarrage	Définition du base de projet (objectifs, chef du projet)
  2	Prévision	Planification du projet (périmètre, activités, ressources requis, coûts)
  3	Réalisation	Exécution du plan du projet
  4	Surveillance et Maîtrise	Rapport de performance
  5	Clôture	Document de clôture du projet

  EtapexProjet
  idEtape	idProjet	dateDebut	dateFin
  1	1	01/07/2011	01/09/2011
  2	1	02/09/2011	30/11/2011
  3	1	01/12/2011	07/07/2012
  4	1	08/07/2012
  1	2	01/05/2012	10/05/2012
  2	2	11/05/2012	01/06/2012
  3	2	02/06/2012	01/07/2012
  4	2	01/07/2012	21/07/2012
  5	2	22/07/2012	23/07/2012
  1	3	01/11/2011	20/01/2012
  2	3	21/01/2012	01/04/2012
  3	3	02/04/2012

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. Précision : écrivez une requête qui permet de vérifier automatiquement 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.