Activities of this lab:
- 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 logical model
You already created a conceptual model for the PistusResa
database (cf. homework).
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.
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).
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.