IdentifiantMot de passe
Loading...
Mot de passe oubli� ?Je m'inscris ! (gratuit)
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 (4069 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...