Énoncé du travail pratique - EBSI - Cours et horaires
publicité
Requêtes SQL sur la base de données INSCRIP 1/4 Université de Montréal, EBSI SCI6306 – Bases de données documentaires Automne 2016 BASE DE DONNÉES INSCRIP REQUÊTES SQL * TRAVAIL INDIVIDUEL * Objectif : Date de remise : Pondération : Notes : Le but de ce travail est de vous pratiquer à traduire des besoins en requête SQL et viceversa ainsi qu’à manipuler les données des tables par le truchement de requêtes SQL. 21 octobre 2016, 13h 25% 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) SCI6306 (Automne 2016) Professeure : Christine Dufour Copyright © 2016 Christine DUFOUR Requêtes SQL sur la base de données INSCRIP 2/4 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 SCI6306 (Automne 2016) Professeure : Christine Dufour Copyright © 2016 Christine DUFOUR Requêtes SQL sur la base de données INSCRIP 3/4 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. SCI6306 (Automne 2016) Professeure : Christine Dufour Copyright © 2016 Christine DUFOUR Requêtes SQL sur la base de données INSCRIP 4/4 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. SCI6306 (Automne 2016) Professeure : Christine Dufour Copyright © 2016 Christine DUFOUR
