CentraleSupélec — Département informatique
Plateau de Moulon
3 rue Joliot-Curie
F-91192 Gif-sur-Yvette cedex
1CC1000 - Information Systems and Programming - Lab: Manipulating relational databases with Python

Application context

Pistus (short for "Piston Ski") is an event organised by WACS (Winter Association CentraleSupélec) during winter break every year. It's an awesome weeklong mountain trip involving many activities (including skiing, snowboarding, raclette binges, and parties, just to name a few).

When a student wants to register for Pistus, the organizers write to a spreadsheet (i.e., an Excel file) the student personal data, as well as the registration date and the registration fee. Students have the option to pay their dues immediately or later; in any case, the student must pay within 5 days of the registration, otherwise the registration is considered as expired and it is removed. The organizers will send a reminder by email 2 days before the deadline. Once the student pays, the organizers write the payment date to the spreadsheet.

The organizers keep two spreadsheets. One contains the registration data, that include the student personal data (student number, first and last name, gender and email addresses), the registration and payment dates, the registration fee and the year of the event. The other Excel file contains information on the association membership.

In these files, only the associations exist in the real world. The names of the students have been randomly generated from a list of french first and last names. These files do not contain any reference to actual students in the school.


This organization presents a lot of issues:

  1. If students participate to several Pistus editions in different years, their data is rewritten at each registration. This might lead to inconsistencies. For instance, if a student changes her/his email address, the organizers should remove all the references to her/his old email address in the spreadsheets; these manual modifications are unlikely to be accurate.
  2. The organizers must manually check whether a student meets the payment deadline.
  3. The organizers must manually send email reminders to the students.

For this reason, we want to develop a software, called PistusResa, that the organizers can use to manage the registrations in an efficient way.

The architecture of PistusResa is shown in the following Figure.


The architecture of PistusResa

The application has a graphical user interface (GUI) that allows the access all its functionalities. The backend (i.e., the part of the software that is not visible to a user ) consists of five modules, each implementing a specific functionality:

  • The Student module allows an organizer to manage students' personal data.
  • The Registration module allows an organizer to manage the registrations to the Pistus editions.
  • The Authentication module provides the functionalities to restrict the use of PistusResa to authorized users.
  • The Deadline module is a background process that manages the payment deadlines. Its two main functionalities are: sending an email reminder for unpaid registrations two days before the deadline, and removing the expired registrations.

PistusResa uses a relational database to store all the data. Two additional modules are used to create the database and import the data into it.

  • The db module is used to create the database and its tables.
  • The ETL (Extract, Transform, Load) module is used to import into the database the data on the past Pistus editions that are kept in the two spreadsheets mentioned above.

Activities of this lab

  • Design the conceptual model of the PistusResa relational database.
  • Derive the logical model of the PistusResa relational database.
  • Learn how to use the Python module sqlite3.
  • Create the database for PistusResa.

Database modeling is covered in chapter 5 of the course handbook.

For a quick introduction click here.

Database conceptual model

The database will store information about students registering to Pistus.

Students are identified by a number, and have a first and family name, gender and email addresses (a student can have more than one).

A student may belong to one or more associations; each association has a name and a textual description of its activities. Two associations cannot have the same name. Any student has a specific role in an association (e.g., president, secretary, member).

When a student registers to Pistus, the year of the event, the registration and the payment date are stored, as well as the registration fee. The registration and payment date might be different. Importantly, a student can participate to different Pistus editions in different years, but cannot register twice for the same edition.

Only authorized users can use the application PistusResa to access and modify the data. Authorized users have a unique username and a password. Usernames and passwords are also stored in the database.

Conceive a conceptual model of this database. Draw the conceptual model with an entity-relationship (ER) diagram.

Database logical model

From the conceptual model, you can derive a logical model, that includes the tables of the database.

👉 You can learn more about logical models here.


Create the logical model that corresponds to the conceptual model that you conceived. For each table, specify its name, column names (without the types), primary and foreign keys.

The module sqlite3

PistusResa uses SQLite as a relational database management system (RDBMS).

Any application communicates with a DBMS by using functions that are defined in an application programming interface (API). The Python functions used to communicate with any RDBMS are defined in the PEP 249 API.


The PEP 249 API only defines the functions. In other words, the API specifies for each function:

  • its name: it will be used by the applications to invoke the function.
  • its signature: the function parameters and their types, possibly a return value and type.
  • its purpose: what the function is expected to do.

The API does not specify the implementation of the function, that is how the function realizes the expected behaviour.


Each RDBMS offers a different implementation of the PEP 249 API. The implementation of the PEP 249 API for SQLite is the module sqlite3.

Download this notebook and import it into Visual Studio Code. You'll find other exercises and questions in this notebook.

Next lab

In the next lab we will:

  • extract the PistusResa data from CSV files into Pandas dataframes;
  • transform the data using the functions defined in Pandas;
  • load the data from Pandas dataframes into the PistusResa relational database.


Keep your file pistus.db, you'll need it in the next lab!


If you reached the end of this lab, and you still have time, you can start the next lab.