TP #4 : Étudiants en mobilité internationale
Objectifs
Ce TP a pour objectif de comparer l'utilisation de tableurs aux bases de données relationnelles. Vous devez prendre note des avantages, difficultés et différences des approches (e.g. stockage des données, facilité d'assurer la cohérence, facilité d'utilisation, travailler avec des grandes quantités de données, ...).

Introduction

Nous vous fournissons deux copies des données : une dans un fichier .ods que vous pouvez ouvrir avec Libreoffice Calc, et la deuxième copie dans des fichiers .sql que vous devez utiliser pour créer la base de données. Notez par exemple la structuration des données dans les deux approches.

\textbf{On vous demande de répondre deux fois à chaque question, une fois avec le tableur et l’autre avec une requête SQL. Faites une question à la fois, en passant d’une technologie à l’autre. Vous êtes libre d’utiliser des fonctions avancés de Calc (fonctions, formules, filtres, …).

Initialisation de la partie tableur

Récupérer le fichier BD_excel.ods qui contient les données “Étudiants partants à l’étranger” avec la commande :

curl http://www.dequidt.me/uploads/gba/BD_excel.ods -o BD_excel.ods

Initialisation de la partie SQL

La définition des relations et des données “Étudiants partants à l’étranger” se trouvent respectivement dans les fichiers BD_relationsEtudiants.sql et BD_donneesEtudiants.sql. Exécutez les commandes suivantes en remplaçant votreLogin par votre compte postgres.

Pour copier les fichiers dans le répertoire courant, utilisez la commande cp :

curl http://www.dequidt.me/uploads/gba/BD_relationsEtudiants.sql -o BD_relationsEtudiants.sql
curl http://www.dequidt.me/uploads/gba/BD_donneesEtudiants.sql -o BD_donneesEtudiants.sql

Positionnez le variable d’environnement qui spécifie le serveurpostgres à utiliser :

export PGHOST=serveur-etu.polytech-lille.fr

Configurez l’affichage de requêtes danspostgres pour permettre de visualiser beaucoup de données :

export PAGER='less -SFMX'

Créez la base avec votre login Polytech Lille:

createdb -U votreLogin votreLoginEtudiants
Password: postgres

Ensuite connectez vous à votre base de données etudiants sur le serveur de base de données:

psql -U votreLogin votreLoginEtudiants
Password: postgres

Vous pouvez maintenant créer les relations (c.a.d. les tables) et insérez les données en utilisant nos fichiers sql préalablement copiés:

\i BD_relationsEtudiants.sql
\i BD_donneesEtudiants.sql

Finalement vous pouvez lister les relations avec la commande :

\d

Requêtes SQL

Votre base de données est prête, maintenant vous pouvez interagir avec vos données. Par exemple, pour lister tous les tuples de la table ETUDIANT, utilisez la requête suivante :

SELECT * FROM etudiant ;

Ensuite, pour vous donner une idée du contenu de la base essayez une requête plus complexe (vous pouvez copier/coller les requêtes suivantes). Attention aux conditions de jointure, aux clés primaires composés de plus d’un attribut, et aux attributs de même nom qui demandent d’enlever les ambiguïtés (e.g. l’attribut dept se trouve dans deux relations différents). Notez bien quelles sont les tables listés dans chaque requête. Naviguez avec les flèches.

SELECT  *
FROM    etudiant, formation, effectue, stage, pays, entreprise
WHERE   stage.etudiant=numEt  AND entreprise=numEn AND pays=numPa
  AND   stage.formation=numFo AND stage.dept=formation.dept
  AND   effectue.stage=numSt  AND effectue.etudiant=numEt ;

Vous pouvez utiliser la requête suivante qui reproduit la feuille Excel (jointure de 8 des 9 tables SQL). Regardez quelles sont les colonnes choisies, les calculs de durée et l’ordonnancement des résultats par numéro d’étudiant.

SELECT  stage.dept, numfo, CONCAT(anneedeb || '-' || anneefin) AS annee, numEt,
        Etudiant.civilite, nomEt, prenomEt, datedeb, datefin,
        (datefin::timestamp - datedeb::timestamp) AS duree_en_jours,
        job, fax AS fax_signe_le, convention AS convention_signee_le,
        nomPa, nomEn, Contact.civilite, Contact.fonction, ville,
        sujet, remuneration
FROM    etudiant, formation, effectue, stage, pays, entreprise, suit, contact
WHERE   stage.etudiant     = numEt
AND     stage.entreprise   = numEn
AND     pays               = numPa
AND     stage.formation    = numFo
AND     stage.dept         = formation.dept
AND     effectue.stage     = numSt
AND     effectue.etudiant  = numEt
AND     formation.numFo    = suit.formation
AND     formation.dept     = suit.dept
AND     suit.etudiant      = numEt
AND     contact.entreprise = numEn
AND     contact.stage      = stage.numSt
ORDER BY numet ;

Exercices

Répondez aux questions suivantes en utilisant Libreoffice Calc et en écrivant des requêtes SQL:

  1. Quels étudiants sont partis à l’étranger pour leur stage découverte en entreprise ?
  2. Combien d’étudiants sont partis en Chine en 2012 ?
  3. Quel est le nom et le prénom des étudiants partis plusieurs fois à l’étranger ?
  4. Combien d’étudiants sont partis plusieurs fois à l’étranger ?
  5. Où a été Remi Carton ?
  6. Quels sont les organismes américains qui ont reçu des étudiants de Polytech ?
  7. Quel est le nom et le pays des entreprises qui ont reçu plusieurs étudiants ?
  8. Quelle est la rémunération moyenne des étudiants ?
  9. Quels sont les étudiants qui sont partis en pays anglophones (c’est-à-dire États Unis, Australie, Irlande, Royaume Uni, Canada).
  10. Quel est le département qui a envoyé le plus d’étudiants à l’étranger ?
  11. Quel est le sujet de stage Charlotte Capelle ?
  12. Quels sont les contacts de l’INRS ?
  13. Sur quelle période, Justine Vanneste a-t-elle effectué son stage ?
  14. Quel a été le lieu et le sujet de stage des étudiants partis en Pologne ?
  15. Quel étudiant a été le plus rémuné en stage à l’étranger ?