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:

• people information (identifiant, surname, name, address, email, etc…)
• account information (account identifiant, money on the account interest rate, account type, account holder)
• transactions log: (each transaction from accounts to accounts, possibly some account are externals, with date and amount informations)

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.

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:

• Get all the account of one person.
• Get the count of people in one city.
• Check all transaction done in the last month in one given account.

Compiled the: mer. 04 sept. 2024 12:49:43 CEST