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

Activities of this lab:

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

Database conceptual model

Click here.

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.

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)

Database physical model

The physical model is obtained from the logical model by:

  • specifying the type of each column;
  • imposing any integrity constraint on the tables (NOT NULL, CHECK...).

👉 You can learn more about physical models here.


If you used the VSCode bigER extension to create the conceptual model, you can automatically generate the SQL code that creates the physical database model. You can use that code as a starting point, but be careful on the following points:

  • The code generates a new table for each one-to-many relationship. This is not consistent with the rules for translating a conceptual model into a logical model.
  • The bigER extension does not support attributes on relationships. As a result, you may need to manually add some columns to the generated tables.
  • FOREIGN KEY constraints are created with only the option ON DELETE CASCADE. You should also specify the option ON UPDATE.


From your logical model, create the physical model of the PistusResa database. We use SQLite as a relational database management system (RDBMS).

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
)

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.

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.


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.