Énoncé du travail pratique - EBSI - Cours et horaires

Requêtes SQL sur la base de données INSCRIP 1/4
SCI6306 (Automne 2016) Copyright © 2016 Christine DUFOUR
Professeure : Christine Dufour
Université de Montréal, EBSI
SCI6306 – Bases de données documentaires
Automne 2016
BASE DE DONNÉES INSCRIP
REQUÊTES SQL
* TRAVAIL INDIVIDUEL *
Objectif : Le but de ce travail est de vous pratiquer à traduire des besoins en requête SQL et vice-
versa ainsi qu’à manipuler les données des tables par le truchement de requêtes SQL.
Date de remise : 21 octobre 2016, 13h
Pondération : 25%
Notes : Jusqu'à 10% de la note peut être retranché pour la qualité du français.
Vous devez respecter le format du gabarit fourni pour le journal de bord
Contexte
Le travail se divise en deux parties. La première partie vous fera travailler sur des requêtes pour extraire
des informations de la base de données INSCRIP. Dans la deuxième partie, vous travaillerez avec des
requêtes pour manipuler le contenu d’une table de données.
Partie A – Requêtes SELECT [74 points]
Vous aurez 8 besoins d’information sur la base de données INSCRIP à traduire en requêtes SQL. Les
besoins sont classés par niveau de difficulté (le niveau 1 étant le plus facile) et suivent la progression de
la matière présentée en classe. De plus, vous aurez une requête SQL à examiner pour découvrir le
besoin auquel elle répond.
Vous pouvez travailler directement dans phpMyAdmin (http://www.gin-ebsi.umontreal.ca/phpmyadmin) en
important dans votre compte individuel la base de données INSCRIP que vous retrouverez sur le site du
cours (fichier inscrip.sql). Vous pouvez aussi travailler en utilisant l’espace de pratique libre de
l’environnement KeSQiL fait? (http://dufour.ebsi.umontreal.ca/mooc_bd/pratique_libre.php), mais vous
devrez, à un certain moment, passer par phpMyAdmin pour pouvoir y enregistrer vos requêtes.
Vous enregistrerez dans phpMyAdmin les requêtes correspondant à chacun des 8 besoins énoncés ainsi
que préparerez un journal de bord où se retrouveront les informations suivantes pour chacun des
besoins :
• Résumé du besoin
• Décomposition du besoin
o Information(s) à afficher dans la table des résultats
o Table(s) à utiliser pour la requête principale
o Jointure(s) à faire pour la requête principale sous la forme « jointure entre la table x et la
table y sur la base du champ z » (s’il y a lieu)
o Condition(s) à valider pour la requête principale (s’il y a lieu)
o Regroupement pour la requête principale (s’il y a lieu)
o Tri pour la requête principale (s’il y a lieu)
o Informations complémentaires (s’il y a lieu; par ex., le détail d’une sous-requête)
• Requête SQL
• Nom de la requête sauvegardée dans phpMyAdmin (forme : nom_partiea_n1_req1; ne
sauvegardez pas vos requêtes comme des signets visibles aux autres utilisateurs)
• Résultats obtenus (copie d’écran si vous travaillez dans phpMyAdmin, ou copier-coller du
tableau des résultats si vous travaillez dans KeSQiL fait?)
• Commentaires pour justifier ou expliquer certains choix (s’il y a lieu)

Requêtes SQL sur la base de données INSCRIP 2/4
SCI6306 (Automne 2016) Copyright © 2016 Christine DUFOUR
Professeure : Christine Dufour
Il est fortement conseillé, pour chaque besoin, de commencer par remplir le journal de bord avant de
débuter la construction de la requête SQL. La décomposition d’un besoin en ses différentes parties en
amont permet souvent d’éviter de multiples itérations lorsque l’on écrit une requête SQL, comme elle
contribue à mieux le comprendre selon la logique SQL.
Vous retrouverez sur le site du cours un gabarit pour le journal de bord (fichier
sci6306_travail_requetes_SQL_gabarit_journaldebord.doc). Pour les éléments qui ne s’appliquent pas à
une requête, indiquez simplement « ne s’applique pas » ou « N/A ».
Assurez-vous, dans les résultats affichés :
• De toujours inclure les clés primaires des objets représentés. Par exemple, si on demande une
liste d’étudiants, en sus d’y indiquer le nom de l’étudiant, il faut inclure le numéro d’étudiant.
• De penser à inclure les champs pertinents par rapport au besoin énoncé. Par exemple, si on
demande la liste des étudiants ayant obtenu plus de 80% dans un cours spécifique, il faut inclure
dans les résultats, la note obtenue.
• D’éliminer les répétitions. Par exemple, si on demande la liste des étudiants, il ne faut pas y
retrouver deux fois le même étudiant.
• De choisir un ordre de tri logique.
• De faciliter la lecture des tables de résultats.
Finalement, toutes les requêtes doivent respecter le principe d’indépendance c’est-à-dire qu’elles
doivent retourner la bonne réponse peu importe les données que l’on retrouve dans les tables.
Requêtes de niveau 1
8 points par besoin (4 points pour la décomposition du besoin et 4 points pour la requête SQL); total = 24 points
1. Liste des étudiants qui portent le même nom (prénom et nom de famille) qu’un professeur
2. Utilisation des salles de classe par jour de la semaine (en ordre chronologique) c’est-à-dire, avoir,
pour chaque jour de la semaine, la liste des salles occupées (une ligne par salle)
3. Horaire des étudiants en ordre chronologique c’est-à-dire, avoir, pour chaque étudiant, la liste des
cours auxquels il est inscrit en ordre croissant de jour et d’heure
Requêtes de niveau 2
8 points par besoin (4 points pour la décomposition du besoin et 4 points pour la requête SQL); total = 24 points
1. Moyenne des notes par programme
2. Conflits d’horaire dans les inscriptions (c’est-à-dire identification des étudiants inscrits à deux
cours se donnant dans la même plage horaire)
3. Bottin du collège avec le nom des personnes, leur numéro d’identification, leur statut (professeur
ou étudiant), ainsi que le nombre de cours donnés ou suivis (selon le statut)
Requêtes de niveau 3
8 points par besoin (4 points pour la décomposition du besoin et 4 points pour la requête SQL); total = 16 points
1. Liste des étudiants n’ayant pas de cours le mardi
2. Statistiques sur les salles de classe; pour chaque salle : nombre de cours optionnels qui s’y
donnent, nombre de cours obligatoires qui s’y donnent, nombre d'étudiants qui l’occupent, nombre
de professeurs qui y enseignent

Requêtes SQL sur la base de données INSCRIP 3/4
SCI6306 (Automne 2016) Copyright © 2016 Christine DUFOUR
Professeure : Christine Dufour
Compréhension d’une requête SQL
10 points (5 points pour la décomposition du besoin et 5 points pour l’explication du besoin)
Expliquez en vos mots le besoin qui se cache derrière la requête SQL suivante :
(SELECT etud.no_etud, etud.nom, IFNULL((SELECT COUNT(*)*3 FROM etud AS etud2,
suit, cours WHERE etud.no_etud=etud2.no_etud AND etud2.no_etud=suit.no_etud AND
suit.no_cours=cours.no_cours AND cours.statut = 'obligatoire' GROUP BY
etud2.no_etud),0) AS 'Cours obligatoire', IFNULL((SELECT COUNT(*)*3 FROM etud AS
etud2, suit, cours WHERE etud.no_etud=etud2.no_etud AND
etud2.no_etud=suit.no_etud AND suit.no_cours=cours.no_cours AND cours.statut =
'optionnel' GROUP BY etud2.no_etud),0) AS 'Cours optionnel', IFNULL((SELECT
COUNT(*)*3 FROM etud AS etud2, suit, cours WHERE etud.no_etud=etud2.no_etud AND
etud2.no_etud=suit.no_etud AND suit.no_cours=cours.no_cours GROUP BY
etud2.no_etud),0) AS 'Total'
FROM etud)
UNION
(SELECT '', 'TOTAL', COUNT(*)*3, (SELECT COUNT(*)*3 FROM cours, suit WHERE
cours.no_cours=suit.no_cours AND cours.statut='optionnel'), (SELECT COUNT(*)*3
FROM cours, suit WHERE cours.no_cours=suit.no_cours)
FROM cours, suit
WHERE cours.no_cours=suit.no_cours AND cours.statut='obligatoire')
Vous réutiliserez le même tableau que précédemment pour vous aider à décortiquer la requête et en
comprendre le besoin sous-jacent. Faites suivre ce tableau de votre explication du besoin.
Partie B – Requêtes INSERT INTO, UPDATE et DELETE [26 points] 1
Dans un premier temps, vous construirez dans la partie B des requêtes SQL qui vous permettront
d’insérer de nouveaux enregistrements, d’en modifier ainsi que d’en supprimer. Par la suite, vous aurez
trois requêtes SQL à examiner pour découvrir la tâche sous-jacente et, s’il y a lieu, à critiquer.
Vous enregistrerez dans phpMyAdmin les requêtes correspondant à chacune des tâches demandées.
Dans votre journal de bord, consignez les informations suivantes :
• Besoin
• Décomposition du besoin
o Table(s) impliquée(s)
o Condition(s) à valider (s’il y a lieu)
o Informations complémentaires (s’il y a lieu; par exemple, l’information à ajouter ou à
modfier)
• Requête SQL
• Nom de la requête sauvegardée dans phpMyAdmin (forme : nom_partieb_req1; ne sauvegardez
pas vos requêtes comme des signets visibles aux autres utilisateurs)
• Message retourné par phpMyAdmin après l’exécution de la requête (copie d’écran)
• Commentaires pour justifier ou expliquer certains choix (s’il y a lieu)
1 Vous ne pourrez ici que travailler dans phpMyAdmin, dans votre compte individuel. L’espace de pratique libre de l’environnement
KeSQiL fait? ne permet en effet que les requêtes SELECT.

Requêtes SQL sur la base de données INSCRIP 4/4
SCI6306 (Automne 2016) Copyright © 2016 Christine DUFOUR
Professeure : Christine Dufour
Requêtes SQL
total = 14 points (pour chaque requête, la moitié des points est accordée à la décomposition du besoin et l’autre
moitié à la requête SQL)
1. Vous devez mettre à jour INSCRIP pour vous y ajouter comme professeur ainsi que vous
associer à un nouveau cours (remarque : deux requêtes seront nécessaires). (6 points)
2. Vous devez mettre à jour la base de données pour refléter le fait, qu’en raison de rénovations, le
professeur Simard s’installera temporairement dans le bureau du professeur Turmel. (4 points)
3. Il s’avère que l’étudiant Isaac Asimov n’a jamais été inscrit au cours Philosophie 813. Une erreur
de saisie, fort probablement, que vous devez corriger en effaçant cette inscription. (4 points)
Compréhension d’une requête SQL
4 points par requête (2 points pour la décomposition du besoin et 2 points pour l’explication du besoin et la critique de
la requête (s’il y a lieu); total = 12 points)
Expliquez en vos mots le besoin qui se cache derrière les requêtes SQL suivantes. De plus, examinez les
requêtes et faites-en ressortir les faiblesses (s’il y en a) en proposant des solutions.
Requête 1
INSERT INTO SUIT (no_etud, no_cours, note, note_p)
SELECT no_etud, (select no_cours from cours where titre like 'Chimie 110'), 0, 0
FROM etud WHERE nom like 'Wagner, Richard';
Requête 2
UPDATE SUIT
SET NOTE = NOTE-10
WHERE no_cours=20005 AND note_p=1;
Requête 3
DELETE FROM COURS
WHERE (jour = 1 AND heure = '9:00') OR (jour = 5 AND heure = '13:00');
Vous réutiliserez le même tableau que précédemment pour vous aider à décortiquer la requête et en
comprendre le besoin sous-jacent. Faites suivre ce tableau de votre explication du besoin ainsi que, s’il y
a lieu, des améliorations à apporter à la requête.
À remettre sur StudiUM
• Le journal de bord, nommé sci6306_tprequetes_prenom_nom_journaldebord, en format Word ou
RTF ainsi qu’en format PDF
Assurez-vous d’avoir bien sauvegardé vos requêtes sur phpMyAdmin, comme demandé. Une copie des
requêtes sera sauvegardée en local le 21 octobre à 13h (date limite de remise). Les modifications que
vous pourriez apporter par la suite à ces requêtes ne seront pas prises en considération dans la
correction.