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.

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 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.

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.

ANSWER ELEMENTS

The conceptual model that we use as a reference is shown in this 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)

Foreign keys

• 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)

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.

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

ANSWER ELEMENTS

The notebook with the solutions is available at this link.

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.