Discussion :

[fsmrel]

Salve,

Dans la série « Contraintes d’inclusion », prenons le cas du MCD ci-dessous, reprenant celui de la figure 7.37 de l’ouvrage de D. Nanci et B. Espinasse Ingénierie des systèmes d'information - Merise deuxième génération (paragraphe II-D-3-s-i. Contraintes d'inclusion de relations sur d'autres relations) :
 

Ce MCD contient une contrainte d’inclusion permettant de représenter graphiquement la contrainte CX suivante :

(CX) Si un article entre dans la composition d’une commande et que cette commande concerne un dépôt, alors cet article est obligatoirement stocké dans ce dépôt.

LDD produit par Looping :
 

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
CREATE TABLE Depot(
   DepotNo SMALLINT,
   DepotNom VARCHAR(50) NOT NULL,
   CONSTRAINT Depot_PK PRIMARY KEY(DepotNo)
);
CREATE TABLE Article(
   ArtNo SMALLINT,
   ArtNom VARCHAR(50) NOT NULL,
   CONSTRAINT Article_PK PRIMARY KEY(ArtNo)
);
CREATE TABLE Stocker(
   DepotNo SMALLINT,
   ArtNo SMALLINT,
   CONSTRAINT Stocker_PK PRIMARY KEY(DepotNo, ArtNo),
   CONSTRAINT Stocker_Depot_FK FOREIGN KEY(DepotNo) REFERENCES Depot(DepotNo),
   CONSTRAINT Stocker_Article_FK FOREIGN KEY(ArtNo) REFERENCES Article(ArtNo) ON DELETE CASCADE
);
CREATE TABLE Commande(
   CdeNo SMALLINT,
   CdeDate DATE NOT NULL,
   DepotNo SMALLINT NOT NULL,
   CONSTRAINT Commande_PK PRIMARY KEY(CdeNo),
   CONSTRAINT Commande_Depot_FK FOREIGN KEY(DepotNo) REFERENCES Depot(DepotNo)
);
CREATE TABLE Composer(
   ArtNo SMALLINT,
   CdeNo SMALLINT,
   CONSTRAINT Composer_PK PRIMARY KEY(ArtNo, CdeNo),
   CONSTRAINT Composer_Article_FK FOREIGN KEY(ArtNo) REFERENCES Article(ArtNo),
   CONSTRAINT Composer_Commande_FK FOREIGN KEY(CdeNo) REFERENCES Commande(CdeNo) ON DELETE CASCADE
);

Etant donné qu’on ne peut pas traduire la contrainte d’inclusion à partir de clés étrangères, on en sera réduit à mettre en oeuvre un trigger.

----------------------------

Passons au LDD selon Tutorial D.

Définition des variables relationnelles :

Code Tutorial D :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
VAR Depot BASE RELATION {DepotNo INT, DepotNom CHAR} KEY{DepotNo} ;
VAR Article BASE RELATION  {ArtNo INT, ArtNom CHAR} KEY{ArtNo} ;
VAR Stocker BASE RELATION {DepotNo INT, ArtNo INT} KEY{DepotNo, ArtNo} ;
VAR Commande BASE RELATION {CdeNo INT, DepotNo INT, CdeDate CHAR} KEY{CdeNo} ;
VAR Composer BASE RELATION {ArtNo INT, CdeNo INT} KEY{ArtNo, CdeNo} ;

Les contraintes d’intégrité référentielle :

Code Tutorial D :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
CONSTRAINT Stocker_Depot_FK Stocker {DepotNo} ⊆ Depot{DepotNo} ;
CONSTRAINT Stocker_Article_FK Stocker {ArtNo} ⊆ Article{ArtNo} ;
CONSTRAINT Commande_Depot_FK Commande{DepotNo} ⊆ Depot{DepotNo} ;
CONSTRAINT Composer_Article_FK Composer{ArtNo} ⊆ Article{ArtNo} ;
CONSTRAINT Composer_Commande_FK Composer{CdeNo} ⊆ Commande{CdeNo} ;

Contrainte déclenchée s'il existe au moins une commande faisant référence à un dépôt qui n'est pas celui des articles référencés par cette commande.

Code Tutorial D :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
CONSTRAINT CX IS_EMPTY  
           ((Commande JOIN Composer) {ArtNo, DepotNo} 
           MINUS 
           JOIN {Commande{CdeNo, DepotNo}, Composer{CdeNo, ArtNo}, Stocker{DepotNo, ArtNo}} {ArtNo, DepotNo}) ;

Un jeu d’essai

Code Tutorial D :

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
36
37
DELETE Depot,
INSERT Depot RELATION {  TUPLE {DepotNo 1, DepotNom "depot01"}
                       , TUPLE {DepotNo 2, DepotNom "depot02"}
                       , TUPLE {DepotNo 3, DepotNom "depot03"}} ;

DELETE Article,
INSERT Article RELATION {  TUPLE {ArtNo 1, ArtNom "art01"}
                         , TUPLE {ArtNo 2, ArtNom "art02"}
                         , TUPLE {ArtNo 3, ArtNom "art03"}
                         , TUPLE {ArtNo 4, ArtNom "art04"}
                         , TUPLE {ArtNo 5, ArtNom "art05"}} ;

DELETE Stocker,
INSERT Stocker RELATION {  TUPLE {DepotNo 1, ArtNo 2}
                         , TUPLE {DepotNo 1, ArtNo 4}
                         , TUPLE {DepotNo 2, ArtNo 1}
                         , TUPLE {DepotNo 2, ArtNo 4}
                         , TUPLE {DepotNo 3, ArtNo 1}
                         , TUPLE {DepotNo 3, ArtNo 2}
                         , TUPLE {DepotNo 3, ArtNo 3}
                         , TUPLE {DepotNo 3, ArtNo 4}} ;

DELETE Commande,
INSERT Commande RELATION {  TUPLE {CdeNo 1, DepotNo 1, CdeDate "d01"}
                          , TUPLE {CdeNo 2, DepotNo 1, CdeDate "d01"}
                          , TUPLE {CdeNo 3, DepotNo 3, CdeDate "d03"}
                          , TUPLE {CdeNo 4, DepotNo 2, CdeDate "d03"}
                          , TUPLE {CdeNo 5, DepotNo 1, CdeDate "d05"}
                         } ;
DELETE Composer,
INSERT Composer RELATION {  TUPLE {CdeNo 1, ArtNo 4}  // respecte la contrainte
                          , TUPLE {CdeNo 2, ArtNo 3}  // viole la contrainte 
                          , TUPLE {CdeNo 2, ArtNo 2}  // respecte la contrainte
                          , TUPLE {CdeNo 3, ArtNo 3}  // respecte la contrainte
                          , TUPLE {CdeNo 4, ArtNo 1}  // respecte la contrainte
                          , TUPLE {CdeNo 5, ArtNo 4}  // respecte la contrainte
                         } ;

La variable relationnelle Composer contient le n-uplet délinquant {CdeNo 2, ArtNo 3}, en conséquence de quoi le système déclenchera une erreur.

A cette occasion, je fais observer qu’en relationnel pur (Tutorial D), les triggers sont totalement inconnus et n’ont aucunement lieu d’être.

[fsmrel]

Salve,

Passage à SQL (SQL/SERVER)

Les déclarations des variables relationnelles donnent lieu à des CREATE TABLE :

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 Depot(
   DepotNo SMALLINT,
   DepotNom VARCHAR(50) NOT NULL,
   CONSTRAINT Depot_PK PRIMARY KEY(DepotNo)
);
 
CREATE TABLE Article(
   ArtNo SMALLINT,
   ArtNom VARCHAR(50) NOT NULL,
   CONSTRAINT Article_PK PRIMARY KEY(ArtNo)
);
 
CREATE TABLE Stocker(
   DepotNo SMALLINT,
   ArtNo SMALLINT,
   CONSTRAINT Stocker_PK PRIMARY KEY(DepotNo, ArtNo),
   CONSTRAINT Stocker_Depot_FK FOREIGN KEY(DepotNo) REFERENCES Depot(DepotNo),
   CONSTRAINT Stocker_Article_FK FOREIGN KEY(ArtNo) REFERENCES Article(ArtNo) ON DELETE CASCADE
);
 
CREATE TABLE Commande(
   CdeNo SMALLINT,
   CdeDate DATE NOT NULL,
   DepotNo SMALLINT NOT NULL,
   CONSTRAINT Commande_PK PRIMARY KEY(CdeNo),
   CONSTRAINT Commande_Depot_FK FOREIGN KEY(DepotNo) REFERENCES Depot(DepotNo)
);
 
CREATE TABLE Composer(
   ArtNo SMALLINT,
   CdeNo SMALLINT,
   CONSTRAINT Composer_PK PRIMARY KEY(ArtNo, CdeNo),
   CONSTRAINT Composer_Article_FK FOREIGN KEY(ArtNo) REFERENCES Article(ArtNo),
   CONSTRAINT Composer_Commande_FK FOREIGN KEY(CdeNo) REFERENCES Commande(CdeNo) ON DELETE CASCADE
);

La contrainte CX donne lieu à un trigger affecté à la table Composer :

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
CREATE TRIGGER CX
ON Composer AFTER INSERT, UPDATE AS
BEGIN
  DECLARE @count SMALLINT ;
  SET @count = 
    (SELECT COUNT(*)
     FROM
     (SELECT comp.ArtNo, cde.DepotNo
      FROM Commande as cde JOIN Composer as comp on cde.CdeNo = comp.CdeNo
      EXCEPT
      SELECT sto.ArtNo, sto.DepotNo
      FROM Commande as cde 
      JOIN Composer AS comp ON cde.CdeNo = comp.CdeNo
      JOIN Article AS art ON comp.ArtNo = art.ArtNo
      JOIN Stocker AS sto ON art.ArtNo = sto.ArtNo
     ) AS j
    ) ;
  IF @count > 0 
      BEGIN 
        ROLLBACK TRANSACTION ;
        THROW 314115, 'Contrainte CX : y a de l''eau dans le gaz !', 16
      END ;
  END ;

Etant entendu qu’il faudra surveiller par des triggers additionnels en cas par exemple de delete/update affectant les tables Depot et Stoker...

-----------------
Un jeu d’essai

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
INSERT INTO Depot (DepotNo, DepotNom) values
    (1,'depot01'), (2,'depot02'), (3,'depot03') ;
INSERT INTO Article (ArtNo, ArtNom) values
    (1, 'art01'),(2, 'art02'),(3, 'art03'),(4, 'art04'),(5, 'art05') ;
INSERT INTO Stocker (DepotNo, ArtNo) values
    (1, 2), (1, 4), (2, 1), (2, 4), (3, 1), (3, 2), (3, 3), (3, 4) ;
INSERT INTO Commande (CdeNo, DepotNo, CdeDate) values
    (1, 1, '2045-01-01'), (2, 1, '2045-01-01'), (3, 3, '2055-01-01'), (4, 2, '2055-01-01'), (5, 1, '2065-01-01')  ;
INSERT INTO Composer (CdeNo, ArtNo) values
    (1, 4)
  , (2, 3) -- viole la contrainte     
  , (2, 2), (3, 3), (4, 1), (5, 4) ;

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT '' AS 'Depot' , * FROM Depot ;
SELECT '' AS 'Article' , * FROM Article ;
SELECT '' AS 'Stocker' , * FROM Stocker ;
SELECT '' AS 'Commande' , * FROM Commande ;
SELECT '' AS 'Composer' , CdeNo, ArtNo FROM Composer ORDER BY CdeNo, ArtNo ;

A plus tard !