Apprendre à manipuler la clause SQL JOIN : du modèle MCD à la requête SQL

Un tutoriel François de Sainte Marie

Le 22 mars 2018, par fsmrel

0PARTAGES

Mais qui a tué les dinosaures ?

Je voudrais montrer ici en quoi le désir de bien faire, doublé d’une connaissance insuffisante de la théorie relationnelle, en particulier de l’opérateur Join, peuvent se révéler dangereux quand il s’agit d’« optimiser » un MLD (modèle logique des données).

(A) En guise d’introduction et de rappel, causons conceptuel, relationnel et SQL

Soit le MCD tout à fait classique suivant :

Aux CIF près (absentes ici), c’est une version actualisée de celui qu’on trouve à la page 200 de l’ouvrage d’Arnold Rochfeld et José Moréjon La Méthode Merise, Tome 3 Gamme opératoire aux Éditions d’organisation, ouvrage paru en 1989.

Pour cette partie de MCD, j’ai utilisé PowerAMC (lequel, pour la petite histoire, naquit à Suresnes, justement en 1989, et a été phagocyté en 2016 par PowerDesigner).

— Une incidente —

En toute rigueur, idClient, idFacture et idReglement sont des attributs invariants et sans signification (en principe invisibles pour les utilisateurs), et ces attributs particuliers (identifiants) devraient donc être accompagnés des identifiants naturels dont la codification est du ressort des utilisateurs : codeClient, numeroFacture, numeroReglement (ces noms parlent d’eux-mêmes).
Cela dit, il y avait encore à l’époque (1989) un dogme selon lequel, au niveau du MCD, une entité-type ne pouvait être dotée que d’un identifiant et un seul. Heureusement, depuis, le dogme est passé à la trappe.

La pédagogie étant l’art de la répétition, je renvoie à cette occasion au billet « De l’invariance des clés primaires », dans lequel je cite un très grand de Merise, à savoir Yves Tabourier.

— fin de l’incidente —

MCD enrichi des identifiants naturels :

MLD (façon PowerAMC) :

Le script SQL de déclaration des tables SQL est le suivant :

Code SQL :

Sélectionner tout

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
CREATE TABLE CLIENT  
( 
   idClient             INT            NOT NULL, 
   codeClient           CHAR(8)        NOT NULL,      
   nomClient            VARCHAR(64)    NOT NULL, 
   CONSTRAINT CLIENT_PK PRIMARY KEY (idClient), 
   CONSTRAINT CLIENT_AK UNIQUE (codeClient) 
) ; 
  
CREATE TABLE FACTURE  
( 
   idFacture            INT            NOT NULL, 
   numeroFacture        CHAR(10)       NOT NULL, 
   idClient             INT            NOT NULL, 
   dateFacture          DATE           NOT NULL, 
   montantFacture       DECIMAL(7,2)   NOT NULL, 
   CONSTRAINT FACTURE_PK PRIMARY KEY (idFacture), 
   CONSTRAINT FACTURE_AK UNIQUE (numeroFacture), 
   CONSTRAINT FACTURE_CLIENT_FK FOREIGN KEY (idClient) 
      REFERENCES CLIENT 
) ; 
  
CREATE TABLE REGLEMENT  
( 
   idReglement          INT            NOT NULL, 
   numeroReglement      CHAR(10)       NOT NULL, 
   idFacture            INT            NOT NULL, 
   dateReglement        DATE           NOT NULL, 
   montantReglement     DECIMAL(7,2)   NOT NULL, 
   CONSTRAINT REGLEMENT_PK PRIMARY KEY (idReglement), 
   CONSTRAINT REGLEMENT_AK UNIQUE (numeroReglement), 
   CONSTRAINT REGLEMENT_AK2 UNIQUE (idFacture), 
   CONSTRAINT REGLEMENT_FACTURE_FK FOREIGN KEY (idFacture) 
      REFERENCES FACTURE 
) ;

Pour savoir par exemple quels clients ont réglé leurs factures avant le 20 mars 2018, on utilise une requête SQL très simple, du genre :

Code SQL :

Sélectionner tout

1
2
3
4
SELECT nomClient 
FROM   CLIENT AS c JOIN FACTURE AS f ON c.idClient = f.idClient 
                   JOIN REGLEMENT AS r ON f.idFacture = r.idFacture 
WHERE  dateReglement < '2018-03-20' ;

Pour connaître les règlements effectués par le client CR-00014 :

Code SQL :

Sélectionner tout

1
2
3
4
SELECT r.numeroReglement, f.numeroFacture, r.dateReglement, r.montantReglement 
FROM   CLIENT AS c JOIN FACTURE AS f ON c.idClient = f.idClient 
                   JOIN REGLEMENT AS r ON f.idFacture = r.idFacture 
WHERE  c.codeClient = 'CR-00014' ;

Rien que de très classique, la codification de ces requêtes est évidemment immédiate.

Pour le confort du développement des applications, on peut déclarer une table virtuelle, c'est-à-dire une vue, par exemple celle-ci :

Code SQL :

Sélectionner tout

1
2
3
4
5
6
7
8
9
10
CREATE VIEW FACTURE_REGLEMENT  
    (codeClient, numeroFacture, dateFacture, montantFacture, numeroReglement, montantReglement, dateReglement) 
AS 
    SELECT c.codeClient, f.numeroFacture, f.dateFacture, f.montantFacture 
         , COALESCE(NULLIF(numeroReglement, ''), 'non règlement') 
         , COALESCE(NULLIF(CAST(r.montantReglement AS VARCHAR), ''), 'non règlement') 
	 , COALESCE(NULLIF(CAST(r.dateReglement AS VARCHAR), ''), 'non règlement') 
   FROM   FACTURE AS f LEFT JOIN REGLEMENT AS r ON f.idFacture = r.idFacture 
                       JOIN CLIENT AS c ON c.idClient = f.idClient 
;

Précision supplémentaire : on peut modéliser REGLEMENT comme étant une entité-type faible, une propriété monovaluée de FACTURE. L’attribut idReglement peut être éliminé de REGLEMENT.

MCD

MLD

idFacture remplace très avantageusement idReglement, et le DBA qui est dans la soute sera bien content, car déjà il y aura un index en moins, et l’effet cluster pourra jouer plein pot lors des jointures mettant en jeu les deux tables FACTURE et REGLEMENT.

Bien sûr, si les requêtes font jouer simultanément les trois tables CLIENT, FACTURE et REGLEMENT, l’effet cluster peut être moins efficace, mais approfondir ce genre de point nous ferait descendre un bon moment dans la soute et nous faire changer de sujet.

Une autre façon de concevoir la relation entre FACTURE et REGLEMENT est de considérer cette dernière entité-type comme une spécialisation de FACTURE (renommons-la FACTURE_REGLEE) :

MCD

Le MLD est identique à celui précède.

So far, so good, mais on verra que les choses vont se gâter.

(B) Pour mieux comprendre le point (C), causons brièvement mais opportunément du prérelationnel, du navigationnel

Faisons un retour aux temps héroïques. À l’époque de la parution de l’ouvrage, en 1989, les dinosaures, c'est-à-dire les SGBD prérelationnels vivaient leurs derniers instants. Ils étaient essentiellement catalogués hiérarchiques (IMS/DL1) ou navigationnels (IDS2, IDMS, TOTAL), voire listes inverses (Datacom/DB). En France, IDS2 régnait dans les administrations, car installé sur des ordinateurs Bull (français) pour faire court. Ce SGBD étant navigationnel, pour obtenir les factures du client Raoul, donc pour poser la question : « Quelles sont les factures de Raoul ? » cela passait par le parcours d’une chaîne de pointeurs (dits NEXT), chacun d’eux accédant à l’information cible. Dans l’autre sens : « À quel client appartient la facture 2015-F0145 ? » un pointeur (dit OWNER) permettait de répondre à la question. Mais il fallait s’adresser à des développeurs spécialisés pour traiter ces questions simples, et le temps de programmer puis de tester leur prenait quand même quelques heures.

Toutes choses égales (mutatis mutandis comme dit mon lettré de voisin), le principe de navigation vaut pour FACTURE et REGLEMENT quand on pose la question « Quel est le règlement de la facture 2015-F0145 ? », ou cette autre « Quelle est la facture du règlement 2015-R0304 ? ».

Mais voilà qu’avec le modèle relationnel de données de Codd, voyez son article (qui avait déjà 20 ans en 1989...) Derivability, Redundancy and Consistency of Relations Stored in Large Data Banks, suivi en 1970 du fameux A Relational Model of Data for Large Shared Data Banks, on dispose de l’opérateur JOIN, lequel est à l’origine de la disparition des dinosaures, c’est l’opérateur par excellence des langages relationnels comme QUEL ou SQL, car il permet de simplifier radicalement la façon de poser une question, au lieu de bâtir un programme, on rédige en deux minutes les requêtes SQL ci-dessus.

On peut considérer JOIN comme étant le « dinosaur killer »...

(C) Arrivons enfin au point important, à savoir la confusion des genres

De leur côté, les auteurs de l’ouvrage ont écrit à propos du MLD qu’il fallait « optimiser » ((américanisme une fois de plus, mais bon...), je cite :

« par l’introduction dans FACTURE de la clé étrangère #Règlement »

(Le terme « #Règlement » est synonyme de celui que j’ai utilisé, « idReglement »).

Pourquoi un tel aménagement du MLD ? Par référence aux requêtes SQL qui précèdent, cette « optimisation » n’apporte bien entendu strictement rien ! Les auteurs ont manifestement raisonné « prérelationnel ». En effet, voici la raison invoquée pour cette prétendue « optimisation », c’est la perle du jour, car il s’agit de :

« faciliter la communication entre FACTURE et REGLEMENT » (sic !)

Phrase dans laquelle on peut comprendre que « communication » est synonyme de « navigation ».

En introduisant une clé étrangère dans FACTURE, faisant référence à REGLEMENT, faciliterait-on vraiment la « communication » ? On se pince !

Demandons à PowerAMC la génération du MLD dans lequel l’injection de l’attribut idReglement (#Reglement) dans la table FACTURE serait « indispensable », déterminante si on suit les auteurs.

Cette prétendue « optimisation » n’apporte strictement rien, les requêtes SQL ci-dessus le prouvent. Les auteurs auraient dû le savoir, car en 1989, le modèle relationnel de données avait 20 ans, on utilisait déjà pas mal SQL et QUEL. Non seulement elle n’apporte rien, mais au contraire elle fiche la zoubia...

Elle autorise la présence du bonhomme Null, car pour les factures non réglées, l’attribut idReglement de l’en-tête de la table FACTURE devra pouvoir être marqué NULL.
La présence de l’attribut idReglement nécessite de déclarer {idReglement} comme clé alternative de FACTURE, sinon la règle de gestion des données qui veut qu’à un règlement ne corresponde qu’une seule facture peut sans problème être prise en défaut.
Rien n’interdit que via le chemin FACTURE - REGLEMENT, la facture F1 fasse référence au règlement R1, tandis que, toujours pour cette facture F1, l’attribut idReglement de FACTURE prenne la valeur R2, bien présente du côté REGLEMENT, mais faisant référence à une facture F2...
Les opérations d’INSERT déclenchent le problème de l’œuf et de la poule ! Supposons que le SGBD soit PostgreSQL (né seulement en 1995, quand son grand frère INGRES avait huit ans en 1989, et que leur papa, le prototype avait alors une quinzaine d’années). Pour créer une facture, il faut que la clé étrangère {idReglement} ne soit déclarée pour la table FACTURE qu’après déclaration de la table REGLEMENT (ce qui est logique !) et que la contrainte soit déclarée différée :
Code SQL : Sélectionner tout
```
1
2
ALTER TABLE FACTURE ADD CONSTRAINT FACTURE_REGLEMENT_FK FOREIGN KEY (idReglement) 
    REFERENCES REGLEMENT INITIALLY DEFERRED ;
```
La contrainte ne pouvant être rétablie qu’une fois créés les factures et leurs règlements :
Code SQL : Sélectionner tout
```
SET CONSTRAINTS FACTURE_REGLEMENT_FK IMMEDIATE ;
```
Dans la légèreté et la facilité, on fait mieux !
...

Les auteurs n’ont manifestement pas vu que la structure {idFacture, idClient, dateFacture, montantFacture, idReglement} peut être obtenue au moyen d’une vue de jointure (cf. cf. la vue FACTURE_REGLEMENT fournie plus haut), et cette fois-ci on ne peut que réellement « faciliter la communication ».

Bref, oubliant l’objet et la puissance de l’opérateur Join, ils ont confondu relationnel et navigationnel, et n’ont pas réfléchi aux conséquences redoutables de leur trouvaille...
■

Une erreur dans cette actualité ? Signalez-nous-la !

Votre nom : Votre e-mail :

Décrivez l'erreur que vous souhaitez porter à notre connaissance :

Identifiez-vous

Créer un compte

Apprendre à manipuler la clause SQL JOIN : du modèle MCD à la requête SQL

Un tutoriel François de Sainte Marie

Identifiant
Mot de passe

Mot de passe oublié ?

Identifiez-vous

Créer un compte

Apprendre à manipuler la clause SQL JOIN : du modèle MCD à la requête SQL Un tutoriel François de Sainte Marie

Apprendre à manipuler la clause SQL JOIN : du modèle MCD à la requête SQL

Un tutoriel François de Sainte Marie