CentraleSupélec — Département informatique
Plateau de Moulon
3 rue Joliot-Curie
F-91192 Gif-sur-Yvette cedex
1CC1000 - Systèmes d'information et programmation - TD: Manipulation de bases de données relationnelles avec Python

Activités de ce TD :

  • Création du modèle conceptuel de la base de données relationnelle PistusResa.
  • Dériver le modèle logique et physique de la base de données relationnelle PistusResa.
  • Apprendre à utiliser le module Python sqlite3.
  • Créer la base de données pour PistusResa.

Modèle conceptuel de la base de données

Cliquez ici.

Modèle logique de la base de données

A partir du modèle conceptuel, vous pouvez dériver un modèle logique, c'est-à-dire les tables de la base de données.

👉 Pour en savoir plus sur les modèles logiques cliquez ici.


Créez le modèle logique correspondant au modèle conceptuel que vous avez conçu. Pour chaque table, indiquez son nom, les noms des colonnes (sans les types), les clés primaires et étrangères.

ANSWER ELEMENTS

Le modèle conceptuel que nous utilisons comme référence est montré dans cette figure. 

 student (stud_number, first_name, last_name, gender)
 emailAddress (email, stud_number)
 association (asso_name, asso_desc)
 membership (stud_number, asso_name, stud_role)
 pistusEdition (year, registration_fee)
 registration (stud_number, year, registration_date, payment_date)
 login (username, password)

Clés étrangères

  • emailAddress(stud_number) references student(stud_number)
  • membership(stud_number) references student(stud_number)
  • membership(asso_name) references association(asso_name)
  • registration(stud_number) references student(stud_number),
  • registration(year) references pistusEdition(year)

Modèle physique de la base de données

Le modèle physique est obtenu à partir du modèle logique :

  • en spécifiant le type de chaque colonne ;
  • en imposant toute contrainte d'intégrité sur les tables (NOT NULL, CHECK...).

👉 Pour en savoir plus sur les modèles physiques cliquez ici.


Si vous avez utilisé l'extension VSCode bigER pour créer le modèle conceptuel, vous pouvez générer automatiquement le code SQL qui crée le modèle physique de la base de données. Vous pouvez utiliser ce code comme point de départ, mais faites attention aux points suivants :

  • Le code génère une nouvelle table pour chaque association de type "un à plusieurs". Ceci n'est pas cohérent avec les règles de traduction d'un modèle conceptuel en un modèle logique.
  • L'extension bigER ne supporte pas les attributs sur les associations. Par conséquent, vous devrez peut-être ajouter manuellement certaines colonnes aux tables générées.
  • Les contraintes de FOREIGN KEY sont créées uniquement avec l'option ON DELETE CASCADE. Vous devez également spécifier l'option ON UPDATE.


À partir de votre modèle logique, créez le modèle physique de la base de données de PistusResa. Nous utilisons SQLite comme système de gestion de base de données relationnelle (SGBDR).

ANSWER ELEMENTS 

student (
   stud_number INTEGER PRIMARY KEY, 
   first_name TEXT, 
   last_name TEXT, 
   gender TEXT CHECK (gender='M' or gender='F')
)

emailAddress (
   email TEXT PRIMARY KEY, 
   stud_number INTEGER NOT NULL,
   FOREIGN KEY(stud_number) REFERENCES student(stud_number)
)

association (
   asso_name TEXT PRIMARY KEY, 
   asso_desc TEXT
)

membership (
   stud_number INTEGER, 
   asso_name TEXT, 
   stud_role TEXT,
   PRIMARY KEY(stud_number, asso_name),
   FOREIGN KEY(stud_number) REFERENCES student(stud_number),
   FOREIGN KEY(asso_name) REFERENCES association(asso_name)
)

pistusEdition (
   year INTEGER PRIMARY KEY, 
   registration_fee REAL NOT NULL CHECK (registration_fee > 0)
) 

registration (
   stud_number INTEGER, 
   year INTEGER, 
   registration_date TEXT NOT NULL, 
   payment_date TEXT,
   PRIMARY KEY(stud_number, year),
   FOREIGN KEY(stud_number) REFERENCES student(stud_number),
   FOREIGN KEY(year) REFERENCES pistusEdition(year)
)

login (
   username TEXT PRIMARY KEY, 
   password TEXT
)

Le module sqlite3

PistusResa utilise SQLite comme système de gestion de base de données relationnelle (SGBDR).

Toute application communique avec un SGBD en utilisant des fonctions définies dans une interface de programmation d'application (en anglais, Application Programming Interface, ou API). Les fonctions Python utilisées pour communiquer avec n'importe quel SGBDR sont définies dans l'API PEP 249 API.


L'API PEP 249 ne fait que définir les fonctions. En d'autres termes, l'API spécifie pour chaque fonction :

  • son nom : il sera utilisé par les applications pour invoquer la fonction.
  • sa signature : les paramètres de la fonction et leurs types, éventuellement une valeur de retour et son type.
  • son but : ce que la fonction est censée faire.

L'API ne spécifie pas l'implémentation de la fonction, c'est-à-dire la manière dont la fonction réalise le comportement attendu.


Chaque SGBDR offre une implémentation différente de l'API PEP 249. L'implémentation de l'API PEP 249 pour SQLite est le module sqlite3.


Téléchargez le notebook disponible à ce lien et importez-le dans Visual Studio Code. Vous y trouverez la suite du TD.

ANSWER ELEMENTS

Le notebook avec les solutions est disponible à ce lien.

Prochain TD

Dans le prochain TD, nous allons :

  • extraire les données PistusResa des fichiers CSV pour les mettre dans des dataframes Pandas ;
  • transformer les données en utilisant les fonctions définies dans Pandas ;
  • charger les données des dataframes Pandas dans la base de données relationnelle de PistusResa.


Conservez votre fichier pistus.db, vous en aurez besoin dans le prochain TD !


👉 Si vous avez atteint la fin de ce TD, et qu'il vous reste du temps, vous pouvez commencer le prochain TD.