James Hoffman

Techniques de l'Informatique - Cégep de Shawinigan

Menu

Base de données 1

5.2- Exercices

6.1- Modèle relationnel

6.2- Jointures

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.

  • 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

Et d'organiser l'information

  • 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

📚 Primary key

📚 Foreign keys

Clé primaire

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

sql
copier
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

sql
copier
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, -- NOT NULL ???

-- le type des 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

  • 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

sql
copier
FOREIGN KEY (col[, ...])
REFERENCES table_name (col[, ...])
[ON DELETE { RESTRICT | CASCADE | SET NULL }]
[ON UPDATE { RESTRICT | CASCADE | SET NULL }]

Normalisation

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

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 2 tables

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

1 - 1

Ou 0 - 1 ?

1 - *

Ou 0 - * ?

* - *

Devient

Ou ... - ... ?