Discussion :

[Oberown]

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]

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]

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]

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]

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]

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...