Bonsoir mrfof,
J’ai réfléchi à la spécialisation de l’entité-type STOCK, en STOCK_EN_BLOC (articles dans des blocs) et STOCK_HORS_BLOC (articles non présents dans des blocs). Du point de vue conceptuel, c’est très bien. Mais quand on descend au niveau SQL, on a envie d’alléger le code impliqué par cette spécialisation.
De fait, on devra implanter la contrainte d’exclusion et de totalité (XT) entre les deux entités-types spécialisées. Dans le cas de la norme SQL, on peut facilement mettre en oeuvre la contrainte d’exclusion au moyen de l’instruction CREATE ASSERTION, par exemple :
Pour la contrainte d’exclusion :
CREATE ASSERTION CX1 CHECK
(NOT EXISTS
(SELECT idSB
FROM STOCK_EN_BLOC
INTERSECT
SELECT idSHB
FROM STOCK_HORS_BLOC
)AS x
) ;
CONSTRAINT CX1
IS_EMPTY (STOCK_EN_BLOC {idSB} INTERSECT STOCK_HORS_BLOC{idSHB}) ; 
, en conséquence de quoi on doit se résoudre à programmer des triggers (un pour chacune des 2 tables en cause, STOCK_EN_BLOC et STOCK_HORS_BLOC) et ça commence à devenir lourd et franchement pénible.
Et je ne vous parle pas de la programmation de la contrainte de totalité !
Bon. La situation actuelle est la suivante :
image 1
Essayons d’alléger. On peut par exemple chercher à éliminer la spécialisation STOCK_HORS_BLOC dans la mesure où, au stade SQL, un stock hors bloc est un stock qui appartient à la table STOCK, mais pas à la table STOCK_EN_BLOC. Allons-y pour éliminer STOCK_HORS_BLOC :
image 2
Le problème maintenant est qu’on ne sait plus à quel magasin appartient un stock hors bloc.
Dans ces conditions, associons les entités-types MAGASIN et STOCK (on retrouve l’association avoir_3 de votre tout 1er MCD, renommée ici en MAG_STOCK) :
image 3
La contrainte XT peut être conservée, mais ça fait bizarre d’avoir une contrainte d’exclusion entre STOCK_EN_BLOC et rien... On va remplacer cette contrainte par une association, appelons-la ETRE_EN_BLOC . On conserve l’héritage de l’identifiant en utilisant l’identification relative ("1,1,(R)" au lieu de "1,1"). Pour mémoire, pour mettre en oeuvre l’identification relative avec Looping, quand on crée la patte connectant l’entité-type STOCK_EN_BLOC et l’association ETRE_EN_BLOC, on coche la case « Identifiant relatif ». Au résultat :
image 4
Mais on remarquera que se pose un problème : en effet, via le chemin
STOCK_EN_BLOC → ETRE_EN_BLOC → STOCK → MAG_STOCK → MAGASIN
alors pour le stock en bloc S1 on peut atteindre le magasin M1, tandis que pour ce même stock S1, on peut atteindre le magasin M2 via le chemin
STOCK_EN_BLOC → BLOC_STOCK → BLOC → MAG_BLOC → MAGASIN
=>
Contradiction !
Pour résoudre ce problème, en Merise, donc avec Looping, on met en oeuvre une contrainte d’inclusion, traduisant ceci
« Le bloc dans lequel est présent un stock donné doit être un bloc du magasin auquel appartient le stock »
image 5
Dans cette contrainte (I), MAGASIN joue le rôle de « pivot », MAG_STOCK celui de « cible » et BLOC_STOCK celui de « portée ». Reportez-vous à ce sujet à l’ouvrage remarquable de D. Nanci (RIP) et B Espinasse Ingénierie des systèmes d'information : Merise deuxième génération (4e édition, 2001), c’est l’ouvrage de référence. Le chapitre concerné est le chapitre 7 (« Modélisation conceptuelle des données »), à la page 124.
Côté MCD, c’est OK. Problème : les AGL (dont Looping à ce jour) ne traduisent pas cette contrainte au niveau SQL... Au développeur de se débrouiller, donc triggers en vue, c’est-à-dire le cauchemar.
Examinons le code SQL produit par Looping :
CREATE TABLE BLOC
(
idMag INT,
IdBloc INT,
nomBloc VARCHAR(48) NOT NULL,
CONSTRAINT BLOC_PK PRIMARY KEY(idMag, IdBloc),
CONSTRAINT BLOC_MAGASIN_FK FOREIGN KEY(idMag)
REFERENCES MAGASIN(idMag)
);
CREATE TABLE STOCK
(
idStock INT,
idMag INT NOT NULL,
CONSTRAINT STOCK_PK PRIMARY KEY(idStock),
CONSTRAINT STOCK_MAGASIN_FK FOREIGN KEY(idMag)
REFERENCES MAGASIN(idMag)
) ;
CREATE TABLE STOCK_EN_BLOC
(
idStock INT,
IdBloc INT NOT NULL,
CONSTRAINT STOCK_EN_BLOC_PK PRIMARY KEY(idStock),
CONSTRAINT STOCK_EN_BLOC_STOCK_FK FOREIGN KEY(idStock)
REFERENCES STOCK(idStock),
CONSTRAINT STOCK_EN_BLOC_BLOC_FK FOREIGN KEY(IdBloc)
REFERENCES BLOC(IdBloc)
);
A la réflexion, BLOC n’est jamais qu’une propriété multivaluée de MAGASIN, on dit que c’est une entité-type « faible » car un bloc sans magasin ça n’a aucun sens. Dans ces conditions, dans le MCD on va utiliser l’identification relative pour l’association MAG_BLOC :
[MAGASIN]---0,n---(MAG_BLOC)---1,1(R)---[BLOC]
Impact au stade SQL :
CREATE TABLE BLOC
(
idMag INT,
IdBloc INT,
nomBloc VARCHAR(48) NOT NULL,
CONSTRAINT BLOC_PK PRIMARY KEY(idMag, IdBloc),
CONSTRAINT BLOC_MAGASIN_FK FOREIGN KEY(idMag)
REFERENCES MAGASIN(idMag)
);
CREATE TABLE STOCK
(
idStock INT,
idMag INT NOT NULL,
CONSTRAINT STOCK_PK PRIMARY KEY(idStock),
CONSTRAINT STOCK_MAGASIN_FK FOREIGN KEY(idMag)
REFERENCES MAGASIN(idMag)
) ;
CREATE TABLE STOCK_EN_BLOC
(
idStock INT,
idMag INT NOT NULL,
IdBloc INT NOT NULL,
CONSTRAINT STOCK_EN_BLOC_PK PRIMARY KEY(idStock),
CONSTRAINT STOCK_EN_BLOC_STOCK_FK FOREIGN KEY(idStock)
REFERENCES STOCK(idStock),
CONSTRAINT STOCK_EN_BLOC_BLOC_FK FOREIGN KEY(idMag, IdBloc)
REFERENCES BLOC(idMag, IdBloc)
);
Aux grands maux, les grands remèdes ! on va dire qu’à son tour, STOCK n’est jamais qu’une propriété multivaluée de MAGASIN, donc une entité-type faible. Dans ces conditions, dans le MCD on va utiliser l’identification relative pour l’association MAG_STOCK :
[MAGASIN]---0,n---(MAG_STOCK)---1,1(R)---[STOCK]
Le MCD devient :
image 6
Conséquences sur le code SQL :
CREATE TABLE BLOC
(
idMag INT,
IdBloc INT,
nomBloc VARCHAR(48) NOT NULL,
CONSTRAINT BLOC_PK PRIMARY KEY(idMag, IdBloc),
CONSTRAINT BLOC_MAGASIN_FK FOREIGN KEY(idMag)
REFERENCES MAGASIN(idMag)
);
CREATE TABLE STOCK
(
idMag INT,
idStock INT,
CONSTRAINT STOCK_PK PRIMARY KEY(idMag, idStock),
CONSTRAINT STOCK_MAGASIN_FK FOREIGN KEY(idMag) REFERENCES MAGASIN(idMag)
) ;
CREATE TABLE STOCK_EN_BLOC
(
idMag INT,
idStock INT,
idMag_1 INT NOT NULL,
IdBloc INT NOT NULL,
CONSTRAINT STOCK_EN_BLOC_PK PRIMARY KEY(idMag, idStock),
CONSTRAINT STOCK_EN_BLOC_STOCK_FK FOREIGN KEY(idMag, idStock)
REFERENCES STOCK(idMag, idStock),
CONSTRAINT STOCK_EN_BLOC_BLOC_FK FOREIGN KEY(idMag_1, IdBloc)
REFERENCES BLOC(idMag, IdBloc)
);
(1) colonne idMag héritée du chemin
STOCK_EN_BLOC → ETRE_EN_BLOC → STOCK → MAG_STOCK → MAGASIN
(2^) colonne idMag_1 héritée du chemin
STOCK_EN_BLOC → BLOC_STOCK → BLOC → MAG_BLOC → MAGASIN
Pour empêcher la contradiction évoquée précédemment et mettre en oeuvre la contrainte d’inclusion, dans STOCK_EN_BLOC il suffit de forcer idMag_1 à prendre les mêmes valeurs que idMag, ce qui revient tout simplement à ne faire qu’une seule colonne de ces deux colonnes, autrement dit supprimer idMag_1 et la remplacer par idMag dans la contrainte STOCK_EN_BLOC_BLOC_FK :
CREATE TABLE STOCK_EN_BLOC
(
idMag INT,
idStock INT,
IdBloc INT NOT NULL,
CONSTRAINT STOCK_EN_BLOC_PK PRIMARY KEY(idMag, idStock),
CONSTRAINT STOCK_EN_BLOC_STOCK_FK FOREIGN KEY(idMag, idStock)
REFERENCES STOCK(idMag, idStock),
CONSTRAINT STOCK_EN_BLOC_BLOC_FK FOREIGN KEY(idMag, IdBloc)
REFERENCES BLOC(idMag, IdBloc)
);
Tant qu’à faire, histoire de se simplifier la vie dans les requêtes à venir (par exemple déterminer le compte d’expédition pour un compte de destination donné), on va pousser jusqu’à identifier MAGASIN relativement à COMPTE.
image 7
code SQL (toujours avec absorption de idMag_1 par idMag) :
CREATE TABLE COMPTE
(
idCpte INT,
nomCpte VARCHAR(48) NOT NULL,
CONSTRAINT COMPTE_PK PRIMARY KEY(idCpte),
CONSTRAINT COMPTE_AK UNIQUE(nomCpte)
);
CREATE TABLE MAGASIN
(
idCpte INT,
idMag INT,
nomMag VARCHAR(48) NOT NULL,
CONSTRAINT MAGASIN_PK PRIMARY KEY(idCpte, idMag),
CONSTRAINT MAGASIN_AK UNIQUE(nomMag),
CONSTRAINT MAGASIN_COMPTE_FK FOREIGN KEY(idCpte)
REFERENCES COMPTE(idCpte)
);
CREATE TABLE BLOC
(
idCpte INT,
idMag INT,
IdBloc INT,
nomBloc VARCHAR(48) NOT NULL,
CONSTRAINT BLOC_PK PRIMARY KEY(idCpte, idMag, IdBloc),
CONSTRAINT BLOC_MAGASIN_FK FOREIGN KEY(idCpte, idMag)
REFERENCES MAGASIN(idCpte, idMag)
);
CREATE TABLE STOCK
(
idCpte INT,
idMag INT,
idStock INT,
CONSTRAINT STOCK_PK PRIMARY KEY(idCpte, idMag, idStock),
CONSTRAINT STOCK_MAGASIN_FK FOREIGN KEY(idCpte, idMag)
REFERENCES MAGASIN(idCpte, idMag)
);
CREATE TABLE STOCK_EN_BLOC
(
idCpte INT NOT NULL,
idMag INT NOT NULL,
IdBloc INT NOT NULL,
idStock INT,
CONSTRAINT STOCK_EN_BLOC_PK PRIMARY KEY(idCpte, idMag, idStock),
CONSTRAINT STOCK_EN_BLOC_STOCK_FK FOREIGN KEY(idCpte, idMag, idStock)
REFERENCES STOCK(idCpte, idMag, idStock),
CONSTRAINT STOCK_EN_BLOC_BLOC_FK FOREIGN KEY(idCpte, idMag, IdBloc)
REFERENCES BLOC(idCpte, idMag, IdBloc)
);
A suivre...
Correction MCD gestion de transport des marchandises
Répondre avec citation
Envoyé par mrfof
Partager