Voir le flux RSS

fsmrel

Merise : cardinalités 1,1 ---- 1,1 => intégrité référentielle = cautère sur jambe de bois

Note : 3 votes pour une moyenne de 3,67.
par , 19/10/2015 à 00h22 (2237 Affichages)
________________________________________

Une précision à propos de l'intégrité référentielle : bien que correctement mise en oeuvre, l’intégrité référentielle peut être prise en défaut, en présence d’une bijection comme dans l’exemple ci-dessous.

Partons des règles de gestion :

(R1) Une agence a au moins et au plus un directeur ;

(R2) Un directeur dirige au moins et au plus une agence.


MCD merisien correspondant :






MLD dérivé par l’AGL (PowerAMC ici) :






On complète avec les clés alternatives nécessaires (mickey <ak>) :






La dérivation du MCD par l’AGL provoque automatiquement la présence de l’attribut DirecteurId dans l’en-tête de la table AGENCE et la mise en oeuvre d’une clé étrangère {DirecteurId} faisant référence à la clé primaire {DirecteurId} de la table DIRECTEUR.

Comme une association 1,1----1,1 correspond à une bijection, il ne doit donc pas y avoir de jaloux, c’est pourquoi l’AGL ne manque pas de faire figurer un attribut AgenceId dans l’en-tête de la table DIRECTEUR et de mettre en oeuvre une clé étrangère {AgenceId} faisant référence à la clé primaire {AgenceId} de la table AGENCE.


Maintenant, dans le cas des bijections, comme dans cet exemple, les clés étrangères sont aussi efficaces que des cautères sur des jambes de bois. Autrement dit, le filet est troué, ces clés étrangères n’empêchent pas des infractions comme celle-ci :


DIRECTEUR                              AGENCE
+-------------+----------+             +----------+-------------+
| DirecteurId | AgenceId |             | AgenceId | DirecteurId |
|-------------+----------|             |----------+-------------|
|         123 |      314 |             |      314 |         456 |
|         456 |      271 |             |      271 |         123 |
+-------------+----------+             +----------+-------------+ 

Pour éviter cela, d’aucuns recommandent de fusionner les entités-types, mais le défi est justement de n’en rien faire, et préserver leur statut de plein droit d’entités-types : en revanche, rien n’interdit de créer une vue DIRECTION_AGENCE simulant la fusion des entités-types (voir ci-dessous). Quoi qu’il en soit, on remplace les clés étrangères inefficaces par une contrainte.

Exemple en Tutorial D :


    CONSTRAINT DIRECTION_CHK01
        DIRECTEUR {DirecteurId, AgenceId} = AGENCE {DirecteurId, AgenceId} ;

Ce qui se lit : La projection de DIRECTEUR sur les attributs DirecteurId et AgenceId doit être égale à la projection de AGENCE sur ces mêmes attributs.

En vrai relationnel, ceci ne pose aucune difficulté. Par exemple, définissons les variables relationnelles (tables en SQL) :


    VAR DIRECTEUR BASE RELATION 
    {
        DirecteurId        INTEGER
      , DirecteurNom       CHAR
      , AgenceId           INTEGER
      , ...
    }
    KEY {DirecteurId}
    KEY {AgenceId}
    ;

    VAR AGENCE BASE RELATION 
    {
        AgenceId             INTEGER
      , AgenceNom            CHAR
      , DirecteurId          INTEGER
      , ...
    }
    KEY {AgenceId}
    KEY {DirecteurId}
    ;


On définit ensuite la contrainte DIRECTION_CHK01 présentée ci-dessus. A noter que les clés étrangères n’ont pas été définies puisqu’impuissantes à garantir l’intégrité.


Toujours en vrai relationnel, quand il s’agit d’effectuer les ajouts, on procède par affectation multiple :


INSERT DIRECTEUR RELATION {TUPLE {DirecteurId         123,
                                  DirecteurNom        'Raoul',
                                  AgenceId            314,
                                  ... 
                                 }
                        } , 
INSERT AGENCE RELATION {TUPLE {AgenceId           314,
                               AgenceNom          'Agence Volfoni',
                               DirecteurId        123),
                               ... 
                              }
                     } ;  

Les deux inserts font partie de la même instruction, ils y sont simplement séparés par une virgule et la fin de l’instruction est marquée par un point-virgule. Les contraintes d’intégrité ne sont vérifiées qu’à des frontières de points-virgules : en l’occurrence tout se passera donc bien.


Cas de SQL

La contrainte DIRECTION_CHK01 fera l’objet d’une assertion ou plutôt d’un trigger car, à ce jour, les SGBD SQL ne proposent pas l’instruction CREATE ASSERTION (laquelle n'offre du reste pas toutes les garanties voulues). Orientons-nous donc vers la mise en oeuvre de triggers, mais en observant soigneusement que SQL ne permet pas l’affectation multiple, ce qui veut dire qu’en soumettant un 1er INSERT, disons dans la table DIRECTEUR, l’opération sera rejetée par le trigger, au motif du viol de la contrainte : on est confronté au problème de l’œuf et de la poule...
On s’en sort en appliquant les opérations de mise à jour à une vue de jointure, d’où le plus souvent la nécessité d’utiliser aussi un trigger associé à cette vue (exception faite de MySQL qui, encore aujourd’hui, refuse les triggers portant sur des vues, entre autres restrictions gênantes ).

Exemple :

Tables de base :


CREATE TABLE DIRECTEUR 
(
   DirecteurId            INT                  NOT NULL,
   DirecteurNom           VARCHAR(32)          NOT NULL,
   AgenceId               INT                  NOT NULL,
CONSTRAINT DIRECTEUR_PK PRIMARY KEY (DirecteurId),
CONSTRAINT DIRECTEUR_AK UNIQUE (AgenceId)
) ; 
 
CREATE TABLE AGENCE 
(
   AgenceId               INT                  NOT NULL,
   AgenceNom              VARCHAR(32)          NOT NULL,
   DirecteurId            INT                  NOT NULL,
CONSTRAINT AGENCE_PK PRIMARY KEY (AgenceId),
CONSTRAINT AGENCE_AK UNIQUE (DirecteurId)
) ;


Vue de jointure :


CREATE VIEW DIRECTION_AGENCE (DirecteurId, AgenceId, DirecteurNom, AgenceNom)
AS 
    SELECT x.DirecteurId, x.AgenceId, x.DirecteurNom, y.AgenceNom
    FROM   DIRECTEUR AS x JOIN AGENCE AS y ON x.DirecteurId = y.DirecteurId ;


Trigger pour insert (SQL Server) :


CREATE TRIGGER DIRECTION_AGENCE_INSERT_TR ON DIRECTION_AGENCE INSTEAD OF INSERT AS
 
    INSERT INTO DIRECTEUR (DirecteurId, AgenceId, DirecteurNom)
           SELECT DirecteurId, AgenceId, DirecteurNom
           FROM   INSERTED ;
 
    INSERT INTO AGENCE (DirecteurId, AgenceId, AgenceNom)
           SELECT DirecteurId, AgenceId, AgenceNom
           FROM   INSERTED ;


Un bout de jeu d’essai :


INSERT INTO DIRECTION_AGENCE (DirecteurId, AgenceId, DirecteurNom, AgenceNom) VALUES 
    (123, 314, 'Raoul', 'Agence Volfoni'), (456, 271, 'Fernand', 'Agence Naudin') ; 
 
SELECT '' AS 'DIRECTEUR', * FROM DIRECTEUR ;
SELECT '' AS 'AGENCE', * FROM AGENCE ;

=>


DIRECTEUR    DirecteurId    DirecteurNom    AgenceId
             123            Raoul           314
             456            Fernand         271

AGENCE       AgenceId    AgenceNom        DirecteurId
             271         Agence Naudin    456
             314         Agence Volfoni   123



A propos de DB-MAIN


DB-MAIN génère le code SQL suivant (conforme à la norme SQL) :


CREATE TABLE DIRECTEUR 
(
     DirecteurId      NUMERIC(5)      NOT NULL,
     DirecteurNom     VARCHAR(32)     NOT NULL,
     CONSTRAINT ID_DIRECTEUR_ID PRIMARY KEY (DirecteurId)
);

CREATE TABLE AGENCE 
(
     AgenceId         NUMERIC(5)      NOT NULL,
     DirecteurId      NUMERIC(5)      NOT NULL,
     AgenceNom        VARCHAR(32)     NOT NULL,
     CONSTRAINT ID_AGENCE PRIMARY KEY (AgenceId),
     CONSTRAINT FKDIRIGER_ID UNIQUE (DirecteurId)
);

ALTER TABLE DIRECTEUR ADD CONSTRAINT ID_DIRECTEUR_CHK
     CHECK(EXISTS(SELECT * FROM AGENCE
                  WHERE AGENCE.DirecteurId = DirecteurId)) ; 

ALTER TABLE AGENCE ADD CONSTRAINT FKDIRIGER_FK
     FOREIGN KEY (DirecteurId) REFERENCES DIRECTEUR ;


Mais j’ai comme le sentiment qu’il y a application une fois de plus d’un cautère sur une jambe de bois. Et vous ?

____________________________________________

Envoyer le billet « Merise : cardinalités 1,1 ---- 1,1  =>  intégrité référentielle = cautère sur jambe de bois » dans le blog Viadeo Envoyer le billet « Merise : cardinalités 1,1 ---- 1,1  =>  intégrité référentielle = cautère sur jambe de bois » dans le blog Twitter Envoyer le billet « Merise : cardinalités 1,1 ---- 1,1  =>  intégrité référentielle = cautère sur jambe de bois » dans le blog Google Envoyer le billet « Merise : cardinalités 1,1 ---- 1,1  =>  intégrité référentielle = cautère sur jambe de bois » dans le blog Facebook Envoyer le billet « Merise : cardinalités 1,1 ---- 1,1  =>  intégrité référentielle = cautère sur jambe de bois » dans le blog Digg Envoyer le billet « Merise : cardinalités 1,1 ---- 1,1  =>  intégrité référentielle = cautère sur jambe de bois » dans le blog Delicious Envoyer le billet « Merise : cardinalités 1,1 ---- 1,1  =>  intégrité référentielle = cautère sur jambe de bois » dans le blog MySpace Envoyer le billet « Merise : cardinalités 1,1 ---- 1,1  =>  intégrité référentielle = cautère sur jambe de bois » dans le blog Yahoo

Commentaires

  1. Avatar de fsmrel
    • |
    • permalink
    Ave Philippe,

    Jusqu’ici je n’avais pas vu ton commentaire, car dans la page d’accueil de mes billets, le compteur des commentaires est à 0...

    Comme je manque d’entraînement en MySQL et SQL Server et que je me remets actuellement un peu à PostgreSQL, je passe à celui-ci.

    La norme SQL (depuis SQL/92) permet que n’importe quelle contrainte puisse être dans l’état soit immédiat, soit différé. PostgreSQL l’a pris en compte, d’où la possibilité de différer le contrôle de l’intégrité référentielle (INITIALLY DEFERRED), et je déclare donc les clés étrangères par ALTER TABLE, à la suite des CREATE TABLE initiaux (et en plus je code ON UPDATE CASCADE, ça sera utile lors de l’exécution des instructions UPDATE, remplacement par exemple de Raoul par Paul à la tête de l’agence Volfoni :

    ALTER TABLE DIRECTEUR ADD CONSTRAINT DIRECTEUR_AGENCE_FK FOREIGN KEY (AgenceId)
           REFERENCES AGENCE (AgenceId) ON UPDATE CASCADE 
           INITIALLY DEFERRED ;
    ;
    
    ALTER TABLE AGENCE ADD CONSTRAINT AGENCE_DIRECTEUR_FK FOREIGN KEY (DirecteurId)
           REFERENCES DIRECTEUR (DirecteurId) ON UPDATE CASCADE 
           INITIALLY DEFERRED ;
    ; 
    
    Ceci fait, comme le contrôle de l’intégrité référentielle est dans l’état différé, j’effectue les INSERT sans problème, directement dans les tables :

    INSERT INTO AGENCE (AgenceId, DirecteurId, AgenceNom)
           VALUES (314, 456, 'Agence Volfoni') ;
    
    INSERT INTO AGENCE (AgenceId, DirecteurId, AgenceNom)
           VALUES (271, 123, 'Agence Naudin') ;
    
    INSERT INTO DIRECTEUR (DirecteurId, AgenceId, DirecteurNom)
           VALUES (456, 314, 'Raoul') ;
    
    INSERT INTO DIRECTEUR (DirecteurId, AgenceId, DirecteurNom)
           VALUES (123, 271, 'Fernand') ;
    
    Suite à quoi, j’active sans différer (sic !) le contrôle de l’intégrité référentielle :

    SET CONSTRAINTS AGENCE_DIRECTEUR_FK IMMEDIATE ;
    SET CONSTRAINTS DIRECTEUR_AGENCE_FK IMMEDIATE ;
    
    Et si l’intégrité référentielle a été violée lors des INSERT qui précèdent, la patrouille nous rattrapera...

    Je te prie de noter que l’enchaînement :

    ALTER TABLE

    INSERT

    SET CONSTRAINTS

    est un mélange ad-hoc de DDL et de DML, ce qui est laid et bien lourd comparativement à la solution de l’affectation multiple exposée dans le billet, et ne doit pas concourir à la performance ; quant aux conséquences sur les tâches concurrentes, croisons les doigts pour que le SGBD garantisse la règle d’isolation (voir les propriétés ACID) tant que l’intégrité référentielle est débranchée.

    En tout cas, si le coup de l’oeuf et de la poule est ici résolu, en l’état rien n’empêche une fois de plus que Raoul dirige l’agence Naudin et l’on intuite qu’il va falloir encore des triggers pour l’en empêcher (sinon gare au bourre pif...), alors que la solution en Tutorial D est concise, élégante, et ne mélange pas les genres. Je rappelle :

        CONSTRAINT DIRECTION_CHK01
            DIRECTEUR {DirecteurId, AgenceId} = AGENCE {DirecteurId, AgenceId} ;
    
    Certes, coder une instruction SQL CREATE ASSERTION permettrait de se simplifier la vie, d’éviter la mise en oeuvre de triggers, car contrôler les règles de gestion des données n’est pas leur vocation, c’est bien celle des assertions, mais les SGBD mettront-ils un jour cette instruction à notre disposition ?

    Pour en venir au remplacement de Raoul par Paul, pas de problème, grâce à l’action de compensation CASCADE retenue lors de la déclaration des clés étrangères :

     
    UPDATE DIRECTEUR SET DirecteurId = 789, Directeurnom = 'Paul' WHERE DirecteurId = 456 ;
    
    
    PostgreSQL met à jour la table AGENCE, dans laquelle on vérifie donc désormais DirecteurId = 789 (je m’en suis assuré).

    En me focalisant sur PostgreSQL, je n’ai répondu qu’en partie à ta question, il faudrait voir l’impact de l’action de compensation CASCADE dans le cas de SQL Server (SQLpro a certainement la réponse ), quant à MySQL, I give my tongue to the cat...