Bases de Données Relationnelles et SQL

Slides
Author

Ludovic Deneuville

Objectives

What is a database?

  • Organized collection of data
  • Stored electronically
  • Structured to facilitate access and management
  • Designed to minimize redundancy
  • Can be used by applications
  • Can be queried and modified by users

Relational databases

  • Organizes data into Tables
  • Interconnected tables
  • Structured tables
  • Table = spreadsheet

DBMS

A Database Management System (DBMS) is software that allows you to:

  • store
  • organize
  • manage data in a structured way

Examples of DBMS:

  • relational: PostgreSQL, MySQL, Oracle Database
  • NoSQL: MongoDB, Cassandra
  • DBMS: main interface with data
  • For us, PostgreSQL, free, open source, installed on a VM
  • NoSQL: Not Only SQL

What is it?

  • PostgreSQL: DBMS
  • DBeaver: client tool that allows connection to a DBMS
  • SQL: programming language designed to manage and manipulate relational databases.

SQL will be discussed afterwards

Data types

There are many different data types that can be stored, the main ones are:

  • Character strings
  • Numeric values
  • Boolean values
  • Date and time
  • Geometric data

More details in the PostgreSQL documentation.

  • Date and time -> many functions
  • Geometric -> geographic boundaries

Types - example

CREATE TABLE personne (
    id_personne      SERIAL PRIMARY KEY,
    nom              VARCHAR(100) NOT NULL,
    prenom           VARCHAR(100) NOT NULL,
    date_naissance   DATE,
    email            VARCHAR(255) UNIQUE,
    nb_enfants       INT CHECK (nb_enfants >= 0),
    taille_m         DECIMAL(3, 2)
    est_actif        BOOLEAN,
    last_updated     TIMESTAMPTZ DEFAULT NOW() 
);

  • SERIAL: sequence

  • NOT NULL: mandatory

  • UNIQUE: distinct values, but does not prevent NULL values

  • DECIMAL(3, 2): from -9.99 to 9.99

    • 3 significant digits including 2 decimal places

Well-aligned code, keywords in uppercase

Table

A Table is composed of rows and columns:

  • a row represents a specific record

  • a column represents a particular attribute of these records

  • Primary key (PK): a column or set of columns that uniquely identifies each record in a table

    • allows a row to be identified unambiguously

PK:

  • mandatory (not null)
  • unique

Table - example

Physical Data Model (UML)

classDiagram
  class Joueuse {
    id_joueuse: INT PK
    nom: VARCHAR
    prenom: VARCHAR
    date_naissance: DATE
    pays: VARCHAR
  }

Joueuse
id_joueuse (PK) nom prenom date_naissance pays
1 Sebag Marie 1986-10-15 France
2 Polgar Judit 1976-07-23 Hungary
3 Hou Yifan 1994-02-27 China
4 Kosteniuk Alexandra 1984-04-23 Switzerland
5 Ju Wenjun 1991-01-31 China

Relationships between tables

Foreign key

A foreign key (FK):

  • establishes a link between 2 tables
  • is a column of table A
  • corresponds to the primary key of table B

Types of relationships

  • 1..1: A Person has a Passport and a Passport belongs to a single Person

    • foreign key in one of the 2 tables

  • 1..*: A Player plays for a single Team. A Team is composed of several Players

    • foreign key in the Player table

  • *..*: A Student attends several Courses and a Course is attended by several Students

    • association table between Student and Course

SQL

  • SQL: Structured Query Language
  • Invented in 1970 by Edgar F. Codd
  • Programming language
  • Used to manage and manipulate relational databases
  • Very, very, very widely used
  • All data-related programming languages can embed SQL

CRUD operations

SQL allows CRUD operations:

  • SELECT: retrieve data from a table
  • INSERT: insert new data into a table
  • UPDATE: update existing data
  • DELETE: delete data from a table
NoteCRUD

Create, Read, Update, Delete

Actions on a table

Create a Table

-- Creation of the personne table (this line is a comment)

CREATE TABLE personne (
    id_personne      INT         PRIMARY KEY,
    nom              VARCHAR(30) NOT NULL,
    prenom           VARCHAR(40),
    date_naissance   DATE,
    adresse          TEXT
);

The table is created but empty.

id_personne nom prenom date_naissance adresse

A good practice is to store tables in a schema to properly organize your database.

CREATE SCHEMA ran;

CREATE TABLE ran.personne (
    id_personne      INT         PRIMARY KEY,
    ...
);

Insert data

INSERT INTO personne (id_personne, nom, prenom, date_naissance, adresse)
VALUES
    (1, 'Gatore' , 'Ali'  , '1990-05-15', 'Amiens'),
    (2, 'Dure'   , 'Laure', '1985-09-22', 'Auxerre'),
    (3, 'Erateur', 'Maud' , '1995-03-10', 'Lille');
id_personne nom prenom date_naissance adresse
1 Gatore Ali 1990-05-15 Amiens
2 Dure Laure 1985-09-22 Auxerre
3 Erateur Maud 1995-03-10 Lille

Delete a table

DROP TABLE personne;

If you then try:

SELECT *
  FROM personne;

ERROR: relation “personne” does not exist

Actions on rows

Select all

To display the entire contents of a table.

SELECT *
  FROM personne;
  • A query ends with a ;
  • It is possible to limit the number of rows displayed
    • LIMIT 5

Filter rows

SELECT *
  FROM personne
 WHERE adresse LIKE 'A%'
   AND prenom = 'Laure'
   AND adresse IN ('Amiens', 'Auxerre')
   AND adresse IS NOT NULL
   AND adresse != 'Lille'
   AND id_personne BETWEEN 2 AND 3;
Tip

For better readability, align your code!

id_personne prenom nom date_naissance adresse
2 Laure Dure 1985-09-22 Auxerre

The LIKE clause is used to search for specific text in a text column.

  • % represents zero, one, or more characters
  • _ represents a single character

Update rows

UPDATE personne
   SET adresse = 'Amiens'
 WHERE id_personne = 2;

If you run SELECT * FROM personne; again:

id_personne prenom nom date_naissance adresse
1 Ali Gatore 1990-05-15 Amiens
2 Laure Dure 1985-09-22 Amiens
3 Maud Erateur 1995-03-10 Lille
  • single quotes in SQL
  • not double quotes

Delete rows

DELETE FROM personne
 WHERE prenom = 'Ali';


To bring Ali back:

INSERT INTO personne (id_personne, nom, prenom, date_naissance, adresse)
VALUES (1, 'Gatore', 'Ali', '1990-05-15', 'Amiens');
id_personne prenom nom date_naissance adresse
2 Laure Dure 1985-09-22 Amiens
3 Maud Erateur 1995-03-10 Lille

Actions on columns

Select columns

SELECT nom,
       prenom
  FROM personne;
nom prenom
Gatore Ali
Dure Laure
Erateur Maud

Rename a column

ALTER TABLE personne
RENAME COLUMN date_naissance TO dnais;
id_personne nom prenom dnais adresse
1 Gatore Ali 1990-05-15 Amiens
2 Dure Laure 1985-09-22 Amiens
3 Erateur Maud 1995-03-10 Lille

Add an attribute

ALTER TABLE personne
ADD joue_echecs BOOLEAN;


You can add a default value.

ALTER TABLE personne
ADD joue_echecs BOOLEAN DEFAULT true;
id_personne nom prenom dnais adresse joue_echecs
1 Gatore Ali 1990-05-15 Amiens true
2 Dure Laure 1985-09-22 Amiens true
3 Erateur Maud 1995-03-10 Lille true

Delete a column

ALTER TABLE personne
DROP COLUMN joue_echecs;
id_personne nom prenom dnais adresse
1 Gatore Ali 1990-05-15 Amiens
2 Dure Laure 1985-09-22 Amiens
3 Erateur Maud 1995-03-10 Lille

Rename when displaying

The AS keyword allows you to rename a column when displaying it.

⚠️ It does not change the name of the column.

SELECT prenom,
       adresse AS Ville
  FROM personne;
prenom Ville
Maud Lille
Ali Amiens
Laure Amiens

Joins

Alias

Until now, we only had one table.

We therefore knew that the nom field came from the personne table.

What should we do if we join with a table that also has a column named nom?

SELECT p.nom       -- 2. p.nom: nom attribute of the table aliased as p, i.e. personne
  FROM personne p  -- 1. we declare p as an alias for the personne table

Full join

SELECT p.prenom,
       c.produit,
       c.quantite
  FROM personne p
  JOIN commande c ON p.id_personne = c.id_personne
 WHERE prenom = 'Laure';
SELECT p.prenom,
       c.produit,
       c.quantite
  FROM personne p
  JOIN commande c USING(id_personne);
TipUSING

If and only if the 2 columns used to perform the join have the same name.

↪️ You can then use this syntax with USING.

id_personne nom prenom dnais adresse
1 Gatore Ali 1990-05-15 Amiens
2 Dure Laure 1985-09-22 Amiens
3 Erateur Maud 1995-03-10 Lille 
CREATE TABLE commande (
    id_commande INT PRIMARY KEY,
    produit VARCHAR(50),
    quantite INT,
    prix_unitaire DECIMAL(10, 2),
    id_personne INT,
    FOREIGN KEY (id_personne) REFERENCES personne(id_personne)
);

INSERT INTO commande (id_commande, produit, quantite, prix_unitaire, id_personne) VALUES
(1, 'livre', 1, 10, 2),
(2, 'pain', 3, 2, 3),
(3, 'pomme', 10, 0.5, 2);
id_commande produit quantite prix_unitaire id_personne
1 livre 1 10 2
2 pain 3 2 3
3 pomme 10 0.5 2
prenom produit quantite
Laure livre 1
Laure pomme 10
Maud pain 3

Performing a join is like creating a large merged table.

Types of joins

In the previous join:

  • Laure appears twice because she has 2 orders
  • Maud appears once
  • Ali does not appear

How can we include Ali in the table even though he has no order?

  • inner join (INNER JOIN)

    • the value of id_personne must be present in both tables
    • otherwise, if id_personne exists in only one table, nothing is displayed for it

Outer joins

SELECT p.prenom,
       c.produit,
       c.quantite
  FROM personne p
  LEFT JOIN commande c USING(id_personne);
  • LEFT JOIN means that we keep all the content from the previous table
  • and complete it with the content from the following table
  • RIGHT JOIN does the opposite
prenom produit quantite
Laure livre 1
Laure pomme 10
Maud pain 3
Ali

The ****outer join**** performed using the LEFT JOIN keyword indicates that we display:

  • all data from the ****people**** table

  • supplemented with data from the ****orders**** table

    • for rows where the relationship is established

Aggregation

GROUP BY

  • Used to group results based on one or more columns
  • Allows the use of aggregate functions such as COUNT, SUM, AVG…
SELECT adresse,
       COUNT(1)
  FROM personne
 GROUP BY adresse;
adresse count
Amiens 2
Lille 1

HAVING

To filter after a GROUP BY

SELECT adresse,
       COUNT(1)
  FROM personne
 GROUP BY adresse
HAVING COUNT(1) > 1;
adresse count
Amiens 2
Warning
  • do not confuse it with WHERE
  • HAVING is used to filter after a GROUP BY

Sort rows

SELECT *
  FROM personne
 ORDER BY dnais DESC

  • To sort the results

    • Optionally add DESC for descending order
id_personne nom prenom dnais adresse
3 Erateur Maud 1995-03-10 Lille
1 Gatore Ali 1990-05-15 Amiens
2 Dure Laure 1985-09-22 Amiens

It is possible to sort by several columns.

Normal forms

First normal form

A relation is in first normal form (1NF) if

  • it has at least one key
  • and all its attributes are atomic
NoteAtomic attribute
  • Contains only one value for a given tuple
  • Therefore, it does not group several values together
id nom
1 Ali Gator
2 Laure Dure
3 Maud Erateur

This table does not comply with 1NF because its nom attribute is not atomic.

Other normal forms

  • 2NF: 1NF + every non-key attribute depends on the key
  • 3NF: 2NF + a non-key attribute cannot depend on another non-key attribute

Schemas

A good practice (not applied here) is to organize our tables into different schemas.

In the same way that you organize files into folders, you will find your way around more easily by organizing tables into schemas.

If you do not specify a schema when creating a table, it is placed in the public schema.

Schemas - example

Create a schema for the programming project

CREATE schema projet;


When creating the table, store it in the schema

CREATE table projet.joueuse(
  ...
);


Specify the schema in queries

SELECT *
  FROM projet.joueuse;

SQL without a table

SELECT CURRENT_DATE;
SELECT 1 + 2;
SELECT 1 > 2;
SELECT 'Hello';

Other concepts not covered

  • ACID, COMMIT, ROLLBACK
  • views, snapshot
  • indexes
  • privileges: grant / revoke
  • WITH (Common Table Expressions)
  • UNION, INTERSECT, EXCEPT
  • DISTINCT
  • EXISTS
  • SEQUENCE

Bibliography

Exercise

exercise