Travaux pratiques

Les TP sont faits sous le SGBD PostgreSQL, sur le serveur du département informatique où chaque étudiante et étudiant dispose d’un compte et d’une base de données. Les noms d’utilisateur et de base de données sont le login ; le mot de passe est communiqué via TOMUSS.

Pour les interactions d’exploration ou d’administration de la base, il conviendra d’utiliser une connexion psql en ligne de commande. Pour la rédaction de vos compte-rendus de TP et l’exécution des requêtes, on utilisera la plateforme jupyter de l’université (se connecter au “serveur L3”).

Pour les étudiants qui souhaitent utiliser leur propre ordinateur, il est conseillé d’utiliser un environnement Python avec les modules du fichier suivant : ./requirements.txt

Les commandes psql

Memo sur la syntaxe de PostgreSQL

La documentation officielle de PostgreSQL en français et très complète.

Selon votre groupe, les semaines de TP votre séance est soit le mardi matin soit le jeudi après-midi (séances de 3h)

• 17 ou 19 septembre
  • Objectifs : instructions élémentaires de création d’une BD avec clés et clés étrangères, requêtes SQL
  • Sujet : Exercices 1 et 2. Pour vous aider à savoir si vos requêtes sont correctes.
• 8 octobre ou 10 octobre
  • Objectifs : requêtes agrégatives et fenêtrages, même sujet que le TP 1, questions 3, 4 et 5.
• 15 octobre ou 17 octobre
  • Objectifs : Implémentation d’un schéma en SQL, vérification différée de contraintes, triggers et fonctions.
    • Sujet
    • Pour gagner du temps : Script de création des relations
    • Voir la documentation sur les triggers
• 22 octobre ou 24 octobre
  • Objectif : rétro-conception d’un jeu de données dénormalisé
    • Sujet
• 3 décembre ou 5 décembre
  • Suite
• 10 décembre :
  • Objectifs : se familiariser avec la compréhension des plans d’exécution, observer l’effet des index et de la forme des requêtes, réaliser un partitionnement.
  • Sujet

Pour vous connecter à votre compte à partir d’un notebook Jupyter, vous pouvez créer dans votre arborescence un fichier “connexion_BD.ini”. Dans ce fichier, vous recopiez les éléments suivants (en précisant votre login et mdp).

[pg]
drivername = postgresql
username = [login étudiant]
password = [fourni dans TOMUSS]
host = bd-pedago.univ-lyon1.fr
port = 5432
database = [login étudiant]

Ensuite, la première cellule de votre notebook contiendra :

# charge l'extension jupysql de Jupyter, permettant des cellules et commandes "magiques" SQL
%reload_ext sql 
#charge l'extension MOCODO de Jupyter, permettant des cellules "magiques" MOCODO
%reload_ext mocodo 
# réference à votre fichier avec les informations de connexion
%config SqlMagic.dsn_filename = "connexion_BD.ini"
#initialise la connection en utilisant la connexion [pg] définie dans le fichier
%sql --section pg
# limite dans le nombre de tuples du résultat
%config SqlMagic.displaylimit = 30 

Vous pourrez alors créer des cellules magiques SQL en les commençant par “%%sql”. Elles contiendront du code SQL uniquement qui sera envoyé au serveur à l’exécution des cellules.