Contrainte Catégorie/Produit/Pays

**Oberown** · 17/03/2014, 11h21

Bonjour,

J'ai un petit problème de contrainte:
Dans ma BD, j'ai des produits qui sont rattaché à un ou plusieurs pays.
J'ai des catégories qui est attaché à un pays.
Et je souhaite rattacher mon produit a plusieurs catégories. Comment faire pour que mon produit soit attaché à une catégorie où les deux appartiennent au même pays ?

Dois-je utiliser un CIF ? Comment le mettre en place sur SQL Server ?

Merci beaucoup pour votre aide

**fsmrel** · 17/03/2014, 19h49

Bonsoir Oberown,

Je n’ai pas WinDesign, mais plutôt qu’une CIF, la contrainte à envisager est plus une contrainte d’inclusion. Au niveau conceptuel j’en resterai là.

Au niveau logique, le MLD dérivé de votre MCD ressemble au suivant :

Le code SQL correspondant est le suivant :

TABLE PAYS

Code SQL :

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

1
2
3
4
5
CREATE TABLE PAYS (
   PaysId               INT                  not null,
   PaysNom              VARCHAR(64)          not null,
   CONSTRAINT PK_PAYS PRIMARY KEY (PaysId)
) ;

TABLE CATEGORIE

Code SQL :

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

1
2
3
4
5
6
7
8
CREATE TABLE CATEGORIE (
   CatId                INT                  not null,
   PaysId               INT                  not null,
   CatNom               VARCHAR(64)          not null,
   CONSTRAINT PK_CATEGORIE PRIMARY KEY (CatId),
   CONSTRAINT FK_CATEGORI_REL_1_PAYS FOREIGN KEY (PaysId)
      REFERENCES PAYS (PaysId)
) ;

TABLE PRODUIT

Code SQL :

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

1
2
3
4
5
CREATE TABLE PRODUIT (
   ProduitId            INT                  not null,
   ProduitNom           VARCHAR(64)          not null,
   CONSTRAINT PK_PRODUIT PRIMARY KEY (ProduitId)
) ;

TABLE REL_2

Code SQL :

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

1
2
3
4
5
6
7
8
9
CREATE TABLE REL_2 (
   ProduitId            INT                  not null,
   PaysId               INT                  not null,
   CONSTRAINT PK_REL_2 PRIMARY KEY (ProduitId, PaysId),
   CONSTRAINT FK_REL_2_REL_2_PRODUIT FOREIGN KEY (ProduitId)
      REFERENCES PRODUIT,
   CONSTRAINT FK_REL_2_REL_5_PAYS FOREIGN KEY (PaysId)
      REFERENCES PAYS 
) ;

TABLE REL_3

Code SQL :

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

1
2
3
4
5
6
7
8
9
CREATE TABLE REL_3 (
   CatId                INT                  not null,
   ProduitId            INT                  not null,
   CONSTRAINT PK_REL_3 PRIMARY KEY (CatId, ProduitId),
   CONSTRAINT FK_REL_3_REL_3_CATEGORI FOREIGN KEY (CatId)
      REFERENCES CATEGORIE,
   CONSTRAINT FK_REL_3_REL_4_PRODUIT FOREIGN KEY (ProduitId)
      REFERENCES PRODUIT
) ;

Supposons que vous créiez systématiquement en même temps les occurrences pour les associations REL_2 et REL_3. Selon ce scénario, pour garantir la contrainte selon laquelle :

Le pays du produit (REL_2) doit être le pays de la catégorie du produit (REL_3) ;

Alors dans le cas de SQL on peut s’interdire la mise à jour directe des tables REL_2 et REL_3, mais en revanche mettre à jour une vue avec laquelle les données seront ventilées dans ces deux tables (au moyen d’un trigger si le SGBD ne sait pas le faire).

Déclaration de la vue :

Code SQL :

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

1
2
3
4
CREATE VIEW PROD_CAT_PAYS (ProduitId, PaysId, CatId)
 AS 
     SELECT x.ProduitId, x.PaysId, y.CatId
     FROM   REL_2 AS x JOIN REL_3 AS y ON x.Produitid = y.Produitid ;

Exemple avec MS SQL Server, lequel refuse de mettre à jour lui-même les vues de jointure :

Code SQL :

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

1
2
3
4
5
6
7
8
9
CREATE TRIGGER PROD_CAT_PAYS_INSERT ON PROD_CAT_PAYS INSTEAD OF INSERT AS
 
    INSERT INTO REL_2 (ProduitId, PaysId) 
        SELECT ProduitId, PaysId 
        FROM   INSERTED ;
 
    INSERT INTO REL_3 (ProduitId, CatId) 
        SELECT ProduitId, CatId 
        FROM INSERTED ;

Il faudra évidemment étudier la programmation des triggers concernant les opérations d’UPDATE et de DELETE.

Jeu d’essai (INSERT) :

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
INSERT INTO PAYS (PaysId,PaysNom) VALUES (1, 'BE') ;
INSERT INTO PAYS (PaysId,PaysNom) VALUES (2, 'FR') ;
INSERT INTO PAYS (PaysId,PaysNom) VALUES (3, 'SP') ;
INSERT INTO PAYS (PaysId,PaysNom) VALUES (4, 'CH') ;
 
INSERT INTO PRODUIT (ProduitId, ProduitNom) VALUES (1, 'Boulon') ;
INSERT INTO PRODUIT (ProduitId, ProduitNom) VALUES (2, 'Came') ;
INSERT INTO PRODUIT (ProduitId, ProduitNom) VALUES (3, 'Vis') ;
 
INSERT INTO CATEGORIE (CatId, PaysId, CatNom) VALUES (1, 3, 'CAT1') ;
INSERT INTO CATEGORIE (CatId, PaysId, CatNom) VALUES (2, 1, 'CAT2') ;
INSERT INTO CATEGORIE (CatId, PaysId, CatNom) VALUES (3, 2, 'CAT3') ;
INSERT INTO CATEGORIE (CatId, PaysId, CatNom) VALUES (4, 3, 'CAT4') ;
INSERT INTO CATEGORIE (CatId, PaysId, CatNom) VALUES (5, 4, 'CAT5') ;

Mise à jour de la vue :

Code SQL :

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

1
2
3
INSERT INTO PROD_CAT_PAYS (ProduitId, PaysId, CatId) VALUES (3, 1, 2) ;
INSERT INTO PROD_CAT_PAYS (ProduitId, PaysId, CatId) VALUES (3, 2, 3) ; 
INSERT INTO PROD_CAT_PAYS (ProduitId, PaysId, CatId) VALUES (3, 3, 4) ;

=>

Code :

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

1
2
3
4
5
6
PROD_CAT_PAYS
ProduitNom    CatNom    PaysNom
----------    ------    -------
Vis           CAT2      BE
Vis           CAT3      FR
Vis           CAT4      SP

Maintenant, si le scénario de mise à jour des tables REL_2 et Rel_3 (simultanéité des mises à jour) n’est pas le bon on peut revoir ça...

**Oberown** · 18/03/2014, 12h37

Merci pour la réponse. Mais le scénario de mise à jour des tables REL_2 et Rel_3 n'est pas simultané.
Un produit n'est pas forcément dans une catégorie. D'ailleurs les produits peuvent changer de catégorie

**fsmrel** · 18/03/2014, 14h13

Bonjour Oberown,

On va essayer par la face sud. Peut-on partir du principe que les ajouts dans REL_3 ne peuvent être effectués que lorsque REL_2 est elle-même à jour ?

**Oberown** · 18/03/2014, 14h45

Envoyé par fsmrel

Bonjour Oberown,

On va essayer par la face sud. Peut-on partir du principe que les ajouts dans REL_3 ne peuvent être effectués que lorsque REL_2 est elle-même à jour ?

Oui.

**fsmrel** · 18/03/2014, 17h19

Bonsoir Oberown,

On peut donc attaquer par la face sud. On laisse tomber le trigger PROD_CAT_PAYS_INSERT. En revanche on déclare un trigger portant uniquement sur la table REL_3 (le contexte est encore SQL Server) :

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
CREATE TRIGGER REL_3_INSERT ON REL_3 INSTEAD OF INSERT AS
       DECLARE @N AS INT
 
SET @N = (SELECT COUNT(*)
          FROM   INSERTED as u 
          WHERE NOT EXISTS (SELECT '' 
                            FROM   CATEGORIE AS y 
                                           JOIN REL_2 AS z ON y.PaysId = z.PaysId 
                                           JOIN PRODUIT AS t ON z.Produitid = t.ProduitId 
                            WHERE  u.ProduitId = t.ProduitId and u.CatId = y.CatId)) ;  
 
IF @N > 0 
    BEGIN 
        RAISERROR ('REL_3 : il existe au moins une ligne telle que le pays de la catégorie ne correspond pas à celui du produit...', 15,1)
        RETURN
    END                       
 
INSERT INTO REL_3 (ProduitId, CatId) 
    SELECT ProduitId, CatId 
    FROM INSERTED ;

Jeu d’essai :

PAYS

Code SQL :

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

1
2
3
4
INSERT INTO PAYS (PaysId, PaysNom) VALUES (1, 'BE') ;
INSERT INTO PAYS (PaysId, PaysNom) VALUES (2, 'FR') ;
INSERT INTO PAYS (PaysId, PaysNom) VALUES (3, 'SP') ;
INSERT INTO PAYS (PaysId, PaysNom) VALUES (4, 'CH') ;

PRODUIT

Code SQL :

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

1
2
3
INSERT INTO PRODUIT (ProduitId, ProduitNom) VALUES (1, 'Boulon') ;
INSERT INTO PRODUIT (ProduitId, ProduitNom) VALUES (2, 'Écrou') ;
INSERT INTO PRODUIT (ProduitId, ProduitNom) VALUES (3, 'Vis') ;

CATEGORIE

Code SQL :

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

1
2
3
4
5
INSERT INTO CATEGORIE (CatId, PaysId, CatNom) VALUES (1, 3, 'CAT1') ;
INSERT INTO CATEGORIE (CatId, PaysId, CatNom) VALUES (2, 1, 'CAT2') ;
INSERT INTO CATEGORIE (CatId, PaysId, CatNom) VALUES (3, 2, 'CAT3') ;
INSERT INTO CATEGORIE (CatId, PaysId, CatNom) VALUES (4, 3, 'CAT4') ;
INSERT INTO CATEGORIE (CatId, PaysId, CatNom) VALUES (5, 4, 'CAT5') ;

REL_2

Code SQL :

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

1
2
3
4
5
6
INSERT INTO REL_2 (ProduitId, PaysId) VALUES (3, 2) ; -- vis, FR
INSERT INTO REL_2 (ProduitId, PaysId) VALUES (3, 1) ; -- vis, BE
INSERT INTO REL_2 (ProduitId, PaysId) VALUES (1, 4) ; -- boulon, CH
INSERT INTO REL_2 (ProduitId, PaysId) VALUES (1, 2) ; -- boulon, FR
INSERT INTO REL_2 (ProduitId, PaysId) VALUES (2, 4) ; -- écrou, CH
INSERT INTO REL_2 (ProduitId, PaysId) VALUES (2, 3) ; -- écrou, SP

REL_3

Code SQL :

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

1
2
3
4
5
6
INSERT INTO REL_3 (CatId, ProduitId) VALUES (2, 3) ; --> pays cible = BE, OK
INSERT INTO REL_3 (CatId, ProduitId) VALUES (3, 3) ; --> pays cible = FR, OK
INSERT INTO REL_3 (CatId, ProduitId) VALUES (1, 3) ; --> pays cible = SP qui n'appartient pas à {FR, BE}
INSERT INTO REL_3 (CatId, ProduitId) VALUES (2, 1) ; --> pays cible = BE qui n'appartient pas à {CH, FR}
INSERT INTO REL_3 (CatId, ProduitId) VALUES (3, 2) ; --> pays cible = FR qui n'appartient pas à {CH, SP}
INSERT INTO REL_3 (CatId, ProduitId) VALUES (1, 1) ; --> pays cible = SP qui n'appartient pas à {CH, FR}

Les 4 dernières lignes sont rejetées par le trigger.

Si cela a pu vous rendre service, n’hésitez pas à voter...

**fsmrel** · 18/03/2014, 19h16

Bonsoir Oberown,

D’un point de vue relationnel, la contrainte faisant l’objet du trigger s’exprime ainsi au moyen de l’algèbre relationnelle :

Code D :

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

1
2
CONSTRAINT CX_REL_2
    (REL_1 JOIN REL_3) {ProduitId, PaysId} ⊆ Rel_2 {ProduitId, PaysId} ;

Ce qui se lit :

La projection sur les attributs ProduitId, PaysId de la jointure naturelle de REL_1 et REL_3 doit être incluse dans la projection de REL_2 sur les attributs ProduitId, PaysId.

Pour en revenir au MCD, la rétro-conception de cette contrainte ne donnerait-elle pas lieu graphiquement à la contrainte d’inclusion suivante (les pointillés en bleu représentant les pivots de la contrainte) ?

**Oberown** · 21/03/2014, 10h52

Et comment faisons-nous pour le mettre sur sql server ?

On est bien d'accord que lors d'inclusion dans la BD est REL2, REL1, puis REL3 ?

**fsmrel** · 21/03/2014, 13h25

Le trigger REL_3_INSERT contrôlant les INSERT a été fourni ici : post #6.

Pour revenir sur la contrainte CX_REL_2 : dans la mesure où l’association REL_1 est absorbée au niveau relationnel par la variable relationnelle CATEGORIE, cette contrainte peut être réécrite ainsi :

Code :

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

1
2
CONSTRAINT CX_REL_2
    (CATEGORIE JOIN REL_3) {ProduitId, PaysId} ⊆ Rel_2  {ProduitId, PaysId} ;

**fsmrel** · 21/03/2014, 13h32

Envoyé par Oberown

On est bien d'accord que lors d'inclusion dans la BD est REL2, REL1, puis REL3 ?

Quel sens donnez-vous à cette phrase ?

**Oberown** · 25/03/2014, 11h04

Que je fais des "INSERT" dans cette ordre dans les tables: REL2, REL1, puis REL3

Contrainte Catégorie/Produit/Pays

Schéma

Discussions similaires

Partager

Partager