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.

Let's start with the database module!