5.1 - Relations

Présentation
Chapitres
Sections Relations Normalisation Multiplicités Clés primaires et étrangères Jointures Base de données de démonstration CROSS JOIN, Combinatoire INNER JOIN, Intersection LEFT JOIN, Union partielle RIGHT JOIN Base de données de démonstration 2

5.1 - Relations

Base de données

Relations et jointures

Plan

  • Relations et multiplicités
  • Jointures

Relations

Relations

Les SGBD relationnels possèdent un mécanisme, clés primaires et clés étrangères, permettant d'assurer l'intégrité des associations qui existent entre 2 tables.

  • Regrouper les enregistrement de même structure dans une table et établir des liens avec les enregistrements d'autres tables
  • Éviter la répétition de l'information, on récupère les données reliées/complémentaires en parcourant les relations
  • La structure des données est séparées en entitées plus facile à comprendre, gérer et organiser

Normalisation

Une étape importante de conception d'une base de données relationnelle organisée est la normalisation

Normalisation - Exemple

Une seule table Recipes

name desc ingr steps prep_time category cook_name cook_email cook_join_date
a ... ... ... ... ... james j@mail.ca YYYY-MM-DD
b ... ... ... ... ... james j@mail.ca YYYY-MM-DD
c ... ... ... ... ... mathieu m@mail.ca YYYY-MM-DD
d ... ... ... ... ... james j@mail.ca YYYY-MM-DD


Devient

Cooks

id name email join_date
1 james j@mail.ca YYYY-MM-DD
2 mathieu m@mail.ca YYYY-MM-DD

Recipes

name desc ingr steps prep_time category cook_id
a ... ... ... ... ... 1
b ... ... ... ... ... 1
c ... ... ... ... ... 2
d ... ... ... ... ... 1

Multiplicités

Relation 1-1

Relation 1-*

Relation *-*

Clés primaires et étrangères

Clés primaires et étrangères

Mécanisme permettant d'assurer l'intégrité relationnelle

  • Les liens, relations, entre les enregistrements sont exprimés explicitement et sont enforcés par le SGBD.
  • 1 seule PRIMARY KEY par table
  • Possibilité de plusieurs FOREIGN KEYS

📚 Primary key

📚 Foreign keys

Clé primaire

Identifie de façon UNIQUE et NOT NULL chaque enregistrement par la valeur d'une ou plusieurs colonnes

CREATE OR REPLACE TABLE teachers (
  -- Raccourci directement à la création de la colonne
  -- PRIMARY est optionnel, mais on précise pour être explicite
  employee_number INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(200) NOT NULL
);
-- DESCRIBE teachers; pour constater le NOT NULL ajouté automatiquement

CREATE OR REPLACE TABLE courses (
  code CHAR(10) NOT NULL,
  name VARCHAR(200) NOT NULL,
  teacher_employee_number INT UNSIGNED, 
  semester CHAR(5) NOT NULL,

  -- Définition dédiée, permet de nommer PLUSIEURS colonnes: CLÉ COMPOSITE
  PRIMARY KEY (code, teacher_employee_number, semester)
);

CléS étrangèreS

Force un lien vers un enregistrement identifié par une PRIMARY KEY

CREATE OR REPLACE TABLE courses (
  code CHAR(10) NOT NULL,
  name VARCHAR(200) NOT NULL,
  semester CHAR(5) NOT NULL,
  teacher_employee_number INT UNSIGNED, -- Interet de mettre NOT NULL ???
  -- Le type des 2 colonnes doit correspondre
  FOREIGN KEY (teacher_employee_number) REFERENCES teachers (employee_number)
);

CREATE OR REPLACE TABLE grades (
  student_da VARCHAR(7),
  grade SMALLINT UNSIGNED NOT NULL,
  course_code CHAR(10) NOT NULL,
  course_teacher INT UNSIGNED NOT NULL,
  course_semester CHAR(5) NOT NULL,
  PRIMARY KEY (student_da, course_code, course_teacher, course_semester),
  FOREIGN KEY (course_code, course_teacher, course_semester) 
    REFERENCES courses (code, teacher_employee_number, semester)
  -- , FOREIGN KEY (student_da) REFERENCES students (da) -- On peut définir plusieurs FK
);

Cascade

On peut préciser le comportement à implémenter lors de la modification de la PRIMARY KEY ou suppression d'un enregistrement parent

FOREIGN KEY (col[, ...])
  REFERENCES table_name (col[, ...])
  [ON DELETE { RESTRICT | CASCADE | SET NULL }]
  [ON UPDATE { RESTRICT | CASCADE | SET NULL }]
  • RESTRICT Comportement par défaut, l'action est interdite et génère une erreur SQL
  • CASCADE L'action est propagée. Les enfants sont supprimés, la mise à jour de PRIMARY KEY est répercutée
  • SET NULL La valeur NULL est assignée à la FOREIGN KEY

Jointures

Jointures

Permet de récupérer des données de plusieurs tables en exploitant les relations établies entres elles

SELECT select_expr [, select_expr ...]
FROM table_ref [, table_ref ...]

table_ref:
    table_factor
  | CROSS JOIN
  | INNER JOIN table_factor ON conditional_expr
  | { LEFT | RIGHT } JOIN table_factor ON conditional_expr

Démo!

👉 Démarche

Base de données de démonstration

-- Reference: https://fr.wikipedia.org/wiki/Alphabet_grec

DROP DATABASE IF EXISTS alphabet;
CREATE DATABASE alphabet;

-- Different de CREATE OR REPLACE DATABASE qui est en ordre alphabetique
-- Sinon DROP TABLES IF EXISTS alphabet.greek, alphabet.english;

CREATE TABLE alphabet.english (
  id INT UNSIGNED PRIMARY KEY,
  letter VARCHAR(1)
);

CREATE TABLE alphabet.greek (
  id INT UNSIGNED PRIMARY KEY,
  name VARCHAR(10),
  english_id INT UNSIGNED,
  FOREIGN KEY (english_id) REFERENCES english(id) ON DELETE CASCADE
);

INSERT INTO alphabet.english VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd'), (5, 'e'), (6, 'f'), (7, 'g'), (8, 'h'), (9, 'i'), (10, 'j'), (11, 'k'), (12, 'l'), (13, 'm'), (14, 'n'), (15, 'o'), (16, 'p'), (17, 'q'), (18, 'r'), (19, 's'), (20, 't'), (21, 'u'), (22, 'v'), (23, 'w'), (24, 'x'), (25, 'y'), (26, 'z'); 
INSERT INTO alphabet.greek VALUES (1, 'alpha', 1), (2, 'beta', 2), (3, 'gamma', 7), (4, 'delta', 4), (5, 'epsilon', 5), (6, 'zeta', 26), (7, 'eta', NULL), (8, ' theta', NULL), (9, 'iota', 9), (10, 'kappa', 11), (11, 'lambda', 12), (12, 'mu', 13), (13, 'nu', 14), (14, 'xi', 24), (15, 'omnicron', 15), (16, 'pi', 16), (17, 'rho', 18), (18, 'sigma', 19), (19, 'tau', 20), (20, 'upsilon', 21), (21, 'phi', NULL), (22, 'chi', NULL), (23, 'psi', NULL), (24, 'omega', NULL);


english   +----+------+
          | id | name |
          +----+------+
          |  1 | a    |
          |  2 | b    |
          |  3 | c    |
          ...
          +----+------+

greek   +-----+-------------+------------+
        | id  |    name     | english_id |
        +-----+-------------+------------+
        |  1  | alpha       |          1 |
        |  2  | beta        |          2 |
        ...
        |  24 | omega       |       NULL |
        +-----+-------------+------------+

CROSS JOIN, Combinatoire

SELECT * FROM alphabet.english
CROSS JOIN alphabet.greek;

+----+--------+----+----------+------------+
| id | letter | id | name     | english_id |
+----+--------+----+----------+------------+
|  1 | a      |  1 | alpha    |          1 |
|  1 | a      |  2 | beta     |          2 |
|  1 | a      |  3 | gamma    |          7 |
|  1 | a      |  4 | delta    |          4 |
...
| 26 | z      | 19 | tau      |         20 |
| 26 | z      | 20 | upsilon  |         21 |
| 26 | z      | 21 | phi      |       NULL |
| 26 | z      | 22 | chi      |       NULL |
| 26 | z      | 23 | psi      |       NULL |
| 26 | z      | 24 | omega    |       NULL |
+----+--------+----+----------+------------+

CROSS JOIN, Combinatoire

INNER JOIN, Intersection

SELECT *
FROM alphabet.english
INNER JOIN alphabet.greek ON alphabet.english.id = alphabet.greek.english_id
ORDER BY letter;

+----+--------+----+----------+------------+
| id | letter | id | name     | english_id |
+----+--------+----+----------+------------+
|  1 | a      |  1 | alpha    |          1 |
|  2 | b      |  2 | beta     |          2 |
'c' est absent, pas de correspondance grec
|  4 | d      |  4 | delta    |          4 |
|  5 | e      |  5 | epsilon  |          5 |
'f' est absent, pas de correspondance grec
|  7 | g      |  3 | gamma    |          7 |
...
| 26 | z      |  6 | zeta     |         26 |
+----+--------+----+----------+------------+

INNER JOIN, Intersection

LEFT JOIN, Union partielle

SELECT *
FROM alphabet.english
LEFT JOIN alphabet.greek ON alphabet.english.id = alphabet.greek.english_id;

+----+--------+------+----------+------------+
| id | letter | id   | name     | english_id |
+----+--------+------+----------+------------+
|  1 | a      |    1 | alpha    |          1 |
|  2 | b      |    2 | beta     |          2 |
|  3 | c      | NULL | NULL     |       NULL |
|  4 | d      |    4 | delta    |          4 |
|  5 | e      |    5 | epsilon  |          5 |
|  6 | f      | NULL | NULL     |       NULL |
|  7 | g      |    3 | gamma    |          7 |
...
| 24 | x      |   14 | xi       |         24 |
| 25 | y      | NULL | NULL     |       NULL |
| 26 | z      |    6 | zeta     |         26 |
+----+--------+------+----------+------------+

LEFT JOIN, Exclusif

SELECT *
FROM alphabet.english
LEFT JOIN alphabet.greek ON alphabet.english.id = alphabet.greek.english_id
WHERE alphabet.greek.id IS NULL; -- <== EXCLUSIF à english

+----+--------+------+------+------------+
| id | letter | id   | name | english_id |
+----+--------+------+------+------------+
|  3 | c      | NULL | NULL |       NULL |
|  6 | f      | NULL | NULL |       NULL |
|  8 | h      | NULL | NULL |       NULL |
| 10 | j      | NULL | NULL |       NULL |
| 17 | q      | NULL | NULL |       NULL |
| 22 | v      | NULL | NULL |       NULL |
| 23 | w      | NULL | NULL |       NULL |
| 25 | y      | NULL | NULL |       NULL |
+----+--------+------+------+------------+

RIGHT JOIN

SELECT *
FROM alphabet.english
RIGHT JOIN alphabet.greek 
ON alphabet.english.id = alphabet.greek.english_id
ORDER BY alphabet.greek.id;

+------+--------+----+----------+------------+
| id   | letter | id | name     | english_id |
+------+--------+----+----------+------------+
|    1 | a      |  1 | alpha    |          1 |
|    2 | b      |  2 | beta     |          2 |
...
| NULL | NULL   | 23 | psi      |       NULL |
| NULL | NULL   | 24 | omega    |       NULL |
+------+--------+----+----------+------------+

 -- EXCLUSIF à grec
SELECT *
FROM alphabet.english
RIGHT JOIN alphabet.greek 
ON alphabet.english.id = alphabet.greek.english_id
WHERE alphabet.english.id IS NULL;

+------+--------+----+--------+------------+
| id   | letter | id | name   | english_id |
+------+--------+----+--------+------------+
| NULL | NULL   |  7 | eta    |       NULL |
| NULL | NULL   |  8 | theta  |       NULL |
| NULL | NULL   | 21 | phi    |       NULL |
| NULL | NULL   | 22 | chi    |       NULL |
| NULL | NULL   | 23 | psi    |       NULL |
| NULL | NULL   | 24 | omega  |       NULL |
+------+--------+----+--------+------------+

Base de données de démonstration 2

-- CREATE OR REPLACE ok car courses < teachers

CREATE OR REPLACE DATABASE school;
USE school;

CREATE TABLE teachers (
  employee_number INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(200) NOT NULL
);

CREATE TABLE courses (
  code CHAR(10) NOT NULL,
  teacher_employee_number INT UNSIGNED NOT NULL, 
  semester CHAR(5) NOT NULL,

  PRIMARY KEY (code, teacher_employee_number, semester),
  FOREIGN KEY (teacher_employee_number) REFERENCES teachers (employee_number)
);

INSERT INTO teachers (employee_number, name)
VALUES (1, 'James'), (2, 'Mathieu'), (3, 'Stevens'), (4, 'Marco'), (5, 'Lyne'), (6, 'Nicolas');
INSERT INTO courses
VALUES  ('420-0Q4-SW', 1, 'A2021'), ('420-0Q4-SW', 1, 'A2020'), ('420-0Q4-SW', 1, 'A2019'), ('420-0Q7-SW', 1, 'A2021'), ('420-2SS-SW', 1, 'A2021'), ('420-2SS-SW', 1, 'A2020'), ('420-0Q7-SW', 2, 'A2021'), ('420-2SS-SW', 2, 'A2020'), ('420-0Q4-SW', 2, 'A2019'), ('420-0SU-SW', 2, 'A2021'), ('420-2SU-SW', 2, 'A2021'), ('420-1SY-SW', 2, 'A2021'), ('420-0Q4-SW', 3, 'A2021'), ('420-0SV-SW', 3, 'A2021'), ('420-1SX-SW', 3, 'A2021'), ('420-2SS-SW', 3, 'A2020'), ('420-0Q4-SW', 4, 'A2018'), ('420-0Q4-SW', 4, 'A2017');


CROSS JOIN

SELECT * 
FROM school.teachers
CROSS JOIN school.courses
ORDER BY name, code, semester;

+-----------------+---------+------------+-------------------------+----------+
| employee_number | name    | code       | teacher_employee_number | semester |
+-----------------+---------+------------+-------------------------+----------+
|               1 | James   | 420-0Q4-SW |                       4 | A2017    |
|               1 | James   | 420-0Q4-SW |                       4 | A2018    |
|               1 | James   | 420-0Q4-SW |                       1 | A2019    |
|               1 | James   | 420-0Q4-SW |                       2 | A2019    |
|               1 | James   | 420-0Q4-SW |                       1 | A2020    |
|               1 | James   | 420-0Q4-SW |                       3 | A2021    |
|               1 | James   | 420-0Q4-SW |                       1 | A2021    |
|               1 | James   | 420-0Q7-SW |                       2 | A2021    |
|               1 | James   | 420-0Q7-SW |                       1 | A2021    |
...
|               3 | Stevens | 420-2SS-SW |                       1 | A2021    |
|               3 | Stevens | 420-2SU-SW |                       2 | A2021    |
+-----------------+---------+------------+-------------------------+----------+


CROSS JOIN, sous-requête

SELECT name, code 
FROM school.teachers
CROSS JOIN (SELECT DISTINCT code FROM school.courses) AS codes
ORDER BY name;

+---------+------------+
| name    | code       |
+---------+------------+
| James   | 420-2SU-SW |
| James   | 420-2SS-SW |
| James   | 420-1SY-SW |
| James   | 420-1SX-SW |
| James   | 420-0SV-SW |
| James   | 420-0SU-SW |
| James   | 420-0Q7-SW |
| James   | 420-0Q4-SW |
...
| Stevens | 420-0Q4-SW |
| Stevens | 420-2SU-SW |
| Stevens | 420-2SS-SW |
| Stevens | 420-1SY-SW |
+---------+------------+


INNER JOIN

SELECT *
FROM school.teachers
INNER JOIN school.courses ON employee_number = teacher_employee_number
ORDER BY name, code, semester;

+-----------------+---------+------------+-------------------------+----------+
| employee_number | name    | code       | teacher_employee_number | semester |
+-----------------+---------+------------+-------------------------+----------+
|               1 | James   | 420-0Q4-SW |                       1 | A2019    |
|               1 | James   | 420-0Q4-SW |                       1 | A2020    |
|               1 | James   | 420-0Q4-SW |                       1 | A2021    |
|               1 | James   | 420-0Q7-SW |                       1 | A2021    |
|               1 | James   | 420-2SS-SW |                       1 | A2020    |
|               1 | James   | 420-2SS-SW |                       1 | A2021    |
|               4 | Marco   | 420-0Q4-SW |                       4 | A2017    |
|               4 | Marco   | 420-0Q4-SW |                       4 | A2018    |
...
|               3 | Stevens | 420-0Q4-SW |                       3 | A2021    |
|               3 | Stevens | 420-0SV-SW |                       3 | A2021    |
|               3 | Stevens | 420-1SX-SW |                       3 | A2021    |
|               3 | Stevens | 420-2SS-SW |                       3 | A2020    |
+-----------------+---------+------------+-------------------------+----------+

INNER JOIN, group by

SELECT 
    employee_number,
    name,
    COUNT(DISTINCT code) AS `Nb cours`,
    COUNT(semester) AS `Nb sessions`
FROM school.teachers
INNER JOIN school.courses ON teachers.employee_number = courses.teacher_employee_number
GROUP BY name;

+-----------------+---------+----------+-------------+
| employee_number | name    | Nb cours | Nb sessions |
+-----------------+---------+----------+-------------+
|               1 | James   |        3 |           6 |
|               4 | Marco   |        1 |           2 |
|               2 | Mathieu |        6 |           6 |
|               3 | Stevens |        4 |           4 |
+-----------------+---------+----------+-------------+

INNER JOIN, group by 2

SELECT 
    employee_number,
    name,
    code,
    COUNT(DISTINCT code) AS `Nb cours (inutile)`,
    COUNT(semester) AS `Nb sessions`
FROM school.teachers
INNER JOIN school.courses ON employee_number = teacher_employee_number
GROUP BY name, code; -- 2 niveaux name ET code

+-----------------+---------+------------+--------------------+-------------+
| employee_number | name    | code       | Nb cours (inutile) | Nb sessions |
+-----------------+---------+------------+--------------------+-------------+
|               1 | James   | 420-0Q4-SW |                  1 |           3 |
|               1 | James   | 420-0Q7-SW |                  1 |           1 |
|               1 | James   | 420-2SS-SW |                  1 |           2 |
|               4 | Marco   | 420-0Q4-SW |                  1 |           2 |
|               2 | Mathieu | 420-0Q4-SW |                  1 |           1 |
...
+-----------------+---------+------------+--------------------+-------------+

LEFT JOIN

SELECT *
FROM school.teachers
LEFT JOIN school.courses ON employee_number = teacher_employee_number;

+-----------------+---------+------------+-------------------------+----------+
| employee_number | name    | code       | teacher_employee_number | semester |
+-----------------+---------+------------+-------------------------+----------+
|               1 | James   | 420-0Q4-SW |                       1 | A2019    |
|               1 | James   | 420-0Q4-SW |                       1 | A2020    |
|               1 | James   | 420-0Q4-SW |                       1 | A2021    |
|               1 | James   | 420-0Q7-SW |                       1 | A2021    |
...
|               4 | Marco   | 420-0Q4-SW |                       4 | A2017    |
|               4 | Marco   | 420-0Q4-SW |                       4 | A2018    |
|               5 | Lyne    | NULL       |                    NULL | NULL     |
|               6 | Nicolas | NULL       |                    NULL | NULL     |
+-----------------+---------+------------+-------------------------+----------+

LEFT JOIN exclusif

SELECT *
FROM school.teachers
LEFT JOIN school.courses ON employee_number = teacher_employee_number
WHERE teacher_employee_number IS NULL;

+-----------------+---------+------+-------------------------+----------+
| employee_number | name    | code | teacher_employee_number | semester |
+-----------------+---------+------+-------------------------+----------+
|               5 | Lyne    | NULL |                    NULL | NULL     |
|               6 | Nicolas | NULL |                    NULL | NULL     |
+-----------------+---------+------+-------------------------+----------+

LEFT JOIN, group by

SELECT employee_number, name, code, COUNT(semester) AS `Nb sessions`
FROM school.teachers
LEFT JOIN school.courses ON teachers.employee_number = courses.teacher_employee_number
GROUP BY name, code
ORDER BY employee_number;

+-----------------+---------+------------+-------------+
| employee_number | name    | code       | Nb sessions |
+-----------------+---------+------------+-------------+
|               1 | James   | 420-0Q4-SW |           3 |
|               1 | James   | 420-0Q7-SW |           1 |
|               1 | James   | 420-2SS-SW |           2 |
|               2 | Mathieu | 420-2SU-SW |           1 |
|               2 | Mathieu | 420-0Q4-SW |           1 |
...
|               3 | Stevens | 420-0SV-SW |           1 |
|               4 | Marco   | 420-0Q4-SW |           2 |
|               5 | Lyne    | NULL       |           0 |
|               6 | Nicolas | NULL       |           0 |
+-----------------+---------+------------+-------------+