Database I, année 2022

Lab work is assumed to be done with Linux using most of the time the command-line or Python. If you are lost for technical reasons please ask for help. The goal of the lectures is not to train you with command line scripting.

Getting information

In this lab, I will ask you to fetch information from the official documentation of PostgreSQL. You can found it here..

Be careful to choose adequately the version.

You can check the version using SQL directly:

SELECT version()

Problematic

In the previous TD, we have seen some basic querying. In this one, we are going to define schema. Designing correctly a schema is hard. The first step is to understand what we can define and the appropriate syntax.

A bank schema

Define a schema information to hold a french Bank data:

Note that to define this schema, you may use as many table as possible.

Be careful to add counstraint so that no bank account has negative value and all bank account has at least one owner.

Use references and foreign keys for that!

Improving the schema

Some rules help to define a relational schema correctly. We call those rules normalising the database as they relate as normal form for logical formulas. You can read about relational normal from here.

After reading this wikipedia entry, try to improve your Bank schema.

When using normalization, querying become more annoying as we need to define a lot of join to get some information. To help with that, we can use View. Look in the documentation to integrate view in your schema.

Populate and query your schema.

You can write a small python script to populate your schema with some fake information. For that, you can generate the data using the Python module Faker. Don’t forget to populate with fake transactions as well.

Test your schema on basic querying:


Mastodon