fsmrel

________________________________________

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 ?

____________________________________________