Entity Relationship Diagram et schématisation
Dans cette leçon, nous schématiserons nos tables et les relations entre-elles.
Comme nous l’avons vu, les relations entre les tables sont réalisées par les relations clés primaires/clés étrangères. Une relation est créée dès lors que deux tables ont des attributs en commun.
Le schéma ci-dessous est un schéma physique d’une base de données qu’on appelle aussi Modèle Physique des Données ou MPD.
NB : Il existe plusieurs schématisations : les modèles conceptuels ou Entity Relationship Diagram, les modèles logiques et physiques. Nous n’allons pas entrer dans les détails de chacun de ces modèles, focalisons-nous sur le modèle physique qui est la schématisation finale de notre base de données, ce qui permet d’aller droit au but en comprenant tous les concepts.
Pour faire simple, le modèle physique reprend le même concept que les autres modèles en incluant des informations sur les colonnes (le type, le format) et sur les clés primaires et étrangères. Pour le reste, disons que ce qui change sont les termes employés, le vocabulaire. Comme vous l’avez remarqué, j’ai utilisé différents termes pour désigner la même chose soit :
-entités = tables = classes
-attributs = colonnes = variables
-lignes = observations = tuples = objets
Simplifions le vocabulaire, appelons ces concepts ‘tables’, ‘colonnes’, ‘lignes’.
- Imaginons une base de données avec 3 tables : clients, produits, achats.
Nous allons modéliser la phrase « les clients achètent des produits ».
-La table ‘clients’ contient des informations sur les clients comme l’identifiant (id), le nom (nom), le prénom (prenom), le courriel (email), le téléphone (tel).
-La table ‘produits’ contient des informations sur les produits comme l’identifiant du produit (id), le nom du produit (produit), la date de mise en vente (debut_vente), la date de fin de vente (fin_vente).
-La table ‘achats’ contient des informations sur les achats de produits par les clients comme la date d’achat (date_achat), l’identifiant du client (id_client), l’identifiant du produit (id_produit).
Définissons les colonnes, les types et les formats
-Table ‘clients’
Identifiant client : id, serial, primary key
Nom : nom, varchar, 100 caractères, not null
Prénom : prenom, varchar, 100 caractères, not null
Courriel : email, varchar, 100 caractères
Téléphone : tel, char, 10 caractères, not null
-Table ‘produits’
Identifiant produit : id, serial, primary key
Nom du produit : produit, varchar, 100 caractères, not null
Date de début de vente : debut_vente, date, not null
Date de fin de vente : fin_vente, date, not null
-Table ‘achats’
Identifiant client : id_client, integer, primary key, foreign key (client -> id)
Identifiant produit : id_produit, integer, primary key, foreign key (produit -> id)
Date d’achat : date_achat, date, not null
NB : ici, pour faire la relation entre les clients et les produits, il faut créer une table ‘achats’. Par convention, chaque table doit avoir une clé primaire (une colonne ou un groupe de colonnes). La clé primaire de la table ‘achats’ est l’association de l’identifiant ‘id’ de la table ‘produits’ et l’identifiant ‘id’ de la table ‘clients’. Finalement, la clé primaire de la table ‘achats’ est l’association de deux clés étrangères.
Création du schéma physique
Une fois que vous avez décrit la structure de vos tables et leurs relations, il est possible avec pgAdmin de créer des schémas physiques.
Pour accéder à l’outil :
-Cliquer sur ‘Tools > New ERD Project (Beta)’.
Créons nos trois tables :
-Cliquer sur ‘Add table’.
Dans la fenêtre qui s’ouvre ‘New table’, créons les trois tables ‘clients’, ‘produits’ et ‘achats’ et incluons les colonnes que nous avons décrites plus haut.
Créons les relations entre clés primaires et clés étrangères
Une fois que nous avons créé nos tables et leurs colonnes, créons les relations entre-elles.
Voici nos trois tables, leurs colonnes, leurs types et leurs formats sans les relations.
Pour créer les relations, créons des liens avec la cardinalité One-to-Many : un même client peut acheter plusieurs produits.
Une fois la fenêtre ouverte, remplissez les cases avec les informations nécessaires. La colonne ‘id_client’ de la table ‘achats’ fait référence à la colonne ‘id’ de la table ‘clients’.
La colonne ‘id_produit’ de la table ‘achats’ fait référence à la colonne ‘id’ de la table ‘produits’.
Finalement, le couple des colonnes ‘id_client’ et ‘id_produit’ de la table ‘achats’ forme la clé primaire et chacune de ces colonnes est une clé étrangère qui fait référence aux tables ‘produits’ et ‘clients’.
Dans cet exemple, nous avons donc un cas où les clés primaires sont aussi des clés étrangères.
Générer le code SQL à partir d’un schéma
Vous pouvez générer le code SQL qui permet de créer les tables ainsi que leurs relations exactement comme nous les avons créées.
Code SQL généré
-- This script was generated by a beta version of the ERD tool in pgAdmin 4.
-- Please log an issue at https://www.postgresql.org/account/auth/4/?d=2BTbAL2XW2t-Sy-qXK3cLA==$EK0VnhtmFaGXzye2_AH3rsZ9CIpdZIuWhJa5umJK5G_Iaf43YDiMNC2mpNmGLuw6ngwLrYVPMzhH0eNL8FGcm5nhUN-lS7kj838GUzIZgmwLRxwg8xezjNat6wFFZ8Yp if you find any bugs, including reproduction steps.
BEGIN;
CREATE TABLE public.clients
(
id serial NOT NULL,
nom character varying(100) NOT NULL,
prenom character varying(100) NOT NULL,
email character varying(100),
tel character(10) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE public.produits
(
id serial NOT NULL,
produit character varying(100) NOT NULL,
debut_vente date NOT NULL,
fin_vente date,
PRIMARY KEY (id)
);
CREATE TABLE public.achats
(
id_client integer NOT NULL,
id_produit integer NOT NULL,
date_achat date NOT NULL,
PRIMARY KEY (id_client, id_produit)
);
ALTER TABLE public.achats
ADD FOREIGN KEY (id_client)
REFERENCES public.clients (id)
NOT VALID;
ALTER TABLE public.achats
ADD FOREIGN KEY (id_produit)
REFERENCES public.produits (id)
NOT VALID;
END;
Une fois que le code s’ouvre, vous pouvez lancer la requête comme une requête classique avec le bouton ‘Execute’ (ou F5).
Générer un schéma à partir d’un code SQL
Voici l’équivalent du code que nous aurions pu écrire :
CREATE TABLE clients (
id SERIAL PRIMARY KEY,
nom VARCHAR(100) NOT NULL,
prenom VARCHAR(100) NOT NULL,
email VARCHAR(100),
tel CHAR(10) NOT NULL
);
CREATE TABLE produits (
id SERIAL PRIMARY KEY,
produit VARCHAR(100) NOT NULL,
debut_vente DATE NOT NULL,
fin_vente DATE
);
CREATE TABLE achats (
id_client INT NOT NULL,
id_produit INT NOT NULL,
date_achat DATE NOT NULL,
CONSTRAINT pk_achat PRIMARY KEY (id_client, id_produit),
CONSTRAINT fk_client FOREIGN KEY (id_client) REFERENCES clients (id),
CONSTRAINT fk_produits FOREIGN KEY (id_produit) REFERENCES produits (id)
);
Pour générer le schéma relationnel de vos tables à partir de vos tables :
Cliquer droit sur la base de données et cliquer sur ‘Generate ERD (Beta)’.
pgAdmin crée ensuite une schéma exactement comme vous l’avons créé précédemment, ou presque.
NB : Vous remarquerez que le type des clés primaires ‘id’ des tables ‘clients’ et ‘produits’ sont des integers, contrairement à ce que nous avons écrit dans notre requête (serial). En fait, postgreSQL a attribué le type ‘integer’ aux ‘id’ mais a créé en parallèle un autre objet : une séquence auto-incrémentée, comme doit le faire le type ‘serial’. La finalité est donc la même.
Vérifions nos nouvelles tables
Nos trois tables ont bien été créées comme nous pouvons le voir.
Je vous invite aussi à regarder les structures de vos tables en cliquant sur ‘Properties…’ pour vérifier que les clés ont été convenablement prises en compte.