IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Voir le flux RSS

fsmrel

[Actualité] Join, le tueur des dinosaures

Note : 8 votes pour une moyenne de 4,00.
par , 21/03/2018 à 22h22 (8086 Affichages)
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 - Visualiser dans une fenêtre à part
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 - Visualiser dans une fenêtre à part
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 - Visualiser dans une fenêtre à part
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 - Visualiser dans une fenêtre à part
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 - Visualiser dans une fenêtre à part
    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 - Visualiser dans une fenêtre à part
    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...

Envoyer le billet « Join, le tueur des dinosaures » dans le blog Viadeo Envoyer le billet « Join, le tueur des dinosaures » dans le blog Twitter Envoyer le billet « Join, le tueur des dinosaures » dans le blog Google Envoyer le billet « Join, le tueur des dinosaures » dans le blog Facebook Envoyer le billet « Join, le tueur des dinosaures » dans le blog Digg Envoyer le billet « Join, le tueur des dinosaures » dans le blog Delicious Envoyer le billet « Join, le tueur des dinosaures » dans le blog MySpace Envoyer le billet « Join, le tueur des dinosaures » dans le blog Yahoo

Mis à jour 14/05/2018 à 03h16 par fsmrel

Catégories
Modélisation des données , Merise

Commentaires

  1. Avatar de fsmrel
    • |
    • permalink
    Citation Envoyé par ring0
    Bonjour,

    J'apprécie beaucoup vos articles, sur le fond, sur la forme, les sujets et leur traitement. Continuez! Nous voulons plus, du plus fort. Les Formes normales par exemple.

    Cela dit, NULLIF je ne connais pas, ne serait-ce pas IFNULL ? (par exemple : https://dev.mysql.com/doc/refman/5.7...unction_ifnull ) ?

    Merci encore pour votre travail,

    Dans l'espoir de vous lire.

    -- ring0
    Merci ring0 pour vos appréciations. Je réponds tardivement, mais le comptage des commentaires par DVP laisse à désirer (il est aujourd’hui à 0) et du coup je n'ai pas vu qu'en fait il y avait du courrier...

    Pour les formes normales, c'est ici que ça se passe !

    Quant à NULLIF, je l’ai utilisé avec PostgreSQL, lequel est conforme à la norme SQL, selon laquelle l’expression NULLIF(x,y) est définie comme équivalente à l’expression

    CASE WHEN x = y THEN NULL ELSE x END
    .
  2. Avatar de fsmrel
    • |
    • permalink
    Citation Envoyé par Lino Léum
    Pour ce qui est des SGBD pré-relationnels, z'avez oublié de citer Socrate (devenu Clio ensuite) de Syseca (filiale de Thomson-CSF), qui a permis à de grosses (et moins grosses) entreprises françaises de bâtir des applications stratégiques (EDF, SNCF, la Défense...).
    Je bats volontiers ma coulpe. Cela dit, vous mentionnez la SNCF : il y a environ 25 ans, j’y ai audité non pas le SGBD Socrate, mais une partie du système de réservation du même nom, à l’occasion de l’audit que j’avais fait du cœur du MCD (surréaliste, pour ne pas dire plus) de son partenaire (back-office) Aristote, suite à la mise en production calamiteuse de ce dernier, en l’absence de tout prototypage de performance (entre autres manques). En discutant un peu plus tard de Socrate avec le Professeur S., celui-ci avait fait à juste titre l’éloge du SGBD alors que je pensais avec étonnement qu’il parlait du système de réservation, plutôt catastrophique à l’époque (décidemment...) et qui agaçait le public (tandis que les agents de la SNCF essayaient de se faire leur propre sous-système dans le système). J’ai mis un moment pour me rendre compte du quiproquo...
  3. Avatar de fsmrel
    • |
    • permalink
    Citation Envoyé par Lino Léum
    Ça m'avait effectivement amusé, à l'époque, de voir que la SNCF avait appelé Socrate son calamiteux système de réservation alors que, par ailleurs, elle utilisait encore le SGBD Socrate. La confusion était probable.
    Les bases de Socrate ont été posées au début des '70 par une équipe constituée autour de Jean-Raymond Abrial à l'IMAG, aidée par des travaux de Georges Gardarin et Claude Delobel, entre autres.
    Quant au Professeur S., ça ne m'étonne pas qu'il ait pu faire l'éloge de ce SGBD, dont le système interne de gestion d'espace virtuel était une superbe mécanique, mais qui avait une tare certaine : une taille physique figée (non extensible automatiquement) de la base de données sur le disque ; certes, des outils permettaient de modifier aussi bien le nombre limite d'occurrences de chaque entité (précision obligatoire dans le schéma) et la taille physique du fichier support, mais ça n'avait strictement rien de dynamique.
    Constatant que le modèle relationnel écrasait tout sur son passage, Syseca s'est alors lancée dans le développement du projet MUST, un successeur relationnel à Socrate-Clio. Sauf qu'ils ont eu la très mauvaise idée de cibler OS/2 comme système d'exploitation support du SGBD. On sait ce qu'il est advenu de ce système. Et les coûts de développement de MUST on fait que Thomson-CSF a finalement décidé de jeter l'éponge.

    À part ça, j'ajoute que vos articles sont extrêmement intéressants. Découverts depuis peu avec un vrai plaisir.
    Merci, Lino, pour l’intérêt que vous portez à mes articles, en espérant ne pas vous trop décevoir ici ou là... Vous évoquez J.-R. Abrial, C. Delobel et G. Gardarin, des grands parmi les grands dans le monde des base de données. Les séminaires animés par G. Gardarin m’ont beaucoup apporté, ainsi que son remarquable ouvrage « Bases de données, les systèmes et leurs langages ». Je me sens coupable de ne m’être que très modérément intéressé aux travaux de J.-R. Abrial, mais à ma décharge, je n’ai jamais eu l’occasion d’utiliser Socrate et de modéliser avec Z : pour la peine, j’ai donc rouvert un de mes anciens et néanmoins précieux livres de chevet, « bases de données et systèmes relationnels », de C. Delobel et M. Adiba, et parcouru la partie décrivant le modèle réseau Socrate, sans oublier Z, tout cela avec l’âme du débutant...

    Dans mon article (mal remis en forme par DVP, mais bon...) traitant de la normalisation des bases de données, j’ai eu à m’insurger contre un clerc inconscient, prétendant démontrer (cf. paragraphe 2.8, le 2e exemple) que Delobel et Adiba auraient eu tout faux quand ils prouvèrent que la quatrième forme normale implique la forme normale de Boyce-Codd (cf. paragraphe 4.14) : ça m’a donné l’occasion de citer Flaubert...

    Dommage pour MUST. De son côté, Larry Ellison avait fait le bon pari en implémentant, vampirisant littéralement System R (non brevetable semble-t-il), et en le renommant Oracle... Un papier instructif à ce sujet : Codd almighty! How IBM cracked System R.
  4. Avatar de fsmrel
    • |
    • permalink
    Citation Envoyé par alassanediakite
    Salut
    Avec vous on prend plaisir à lire la science.
    François, quel peut être l'intérêt d'avoir une table "reglement" si la facture ne peut avoir qu'au plus un règlement?
    Si c'est pour éviter le NULL, le champ (la colonne ) "montantreglement" est une "redondance inutile".
    Ah! le temps des dinosaures .
    @+
    Eh bien, Bunny !
    Le MCD initial (ainsi que le MLD et le script SQL qui en sont dérivés) comporte les identifiants « naturels » dont se sert l’utilisateur : codeClient (entité-type CLIENT), numeroFacture (entité-type FACTURE), numeroReglement (entité-type REGLEMENT). Si donc au stade relationnel la relvar (variable relationnelle) REGLEMENT était phagocytée par la relvar FACTURE, il faudrait donc que cette dernière soit dotée d’un attribut numeroReglement, clé candidate, mais pouvant être marquée NULL ! Par ailleurs, il n’y a pas redondance, on ne peut pas faire l’économie de l’attribut montantReglement (pouvant lui aussi être marqué NULL), car le montant d’un règlement peut parfaitement être différent du montant de la facture.

    Laissons les relvars de base comme elles sont, et si tu tiens à n’avoir qu’une relvar, alors celle-ci prendra la forme d’une relvar dérivée, représentée en SQL par la vue FACTURE_REGLEMENT que j’ai proposée.