Bonsoir CAymeric,
Envoyé par
CAymeric
Pour un dossier PP (produit végétaux) avec 3 lots (pommes, patates, tomates) -> référence seulement à l'entité NomenclatureVégétaux mais avec des codes nomenclatures différents contenus dans cette entité pour chaque lot (1MABG(pommes), SOLTU(patates), LYPES(tomates) dans le cas de cet exemple
Si je comprends bien, au stade SQL, la table NomenclatureVegetaux contiendra au moins 3 lignes, une pour 1MABG, une pour SOLTU, une pour LYPES, etc.
Si c’est bien ça, je propose le MCD suivant :
J’ai remplacé la spécialisation par un jeu d’associations entre l’entité-type Marchandise et les nomenclatures (notez l’identification relative 1,1(R)). Les tables LotViande, LotVegetal, LotOrigineAnimale et LotAnimalVivant sont des associations déguisées en tables. J’ai procédé ainsi parce que je ne sens pas la spécialisation d’une entité-type LOT, mais peut-être ai-je tort, il suffira qu’on me le prouve...
J’ai mis en oeuvre une entité-type Famille, pour au moins avoir la certitude qu’une marchandise fasse référence à une famille et une seule. Il n’en demeure pas moins qu’une marchandise M1 puisse être composée de lots hétérogènes, peccamineux, c’est-à-dire faisant référence à plus d’une nomenclature.
C’est ce qui se passe avec l’exemple SQL que je fournis (désolé, c’est du SQL Server...) : la table LotVegetal doit exclusivement contenir des lignes faisant référence à la table NomenclatureVegetal, mais en fait rien n’empêche qu’elle contienne aussi des lignes faisant par exemple référence à la table NomenclatureViande, c’est du travail sans filet... Pour interdire la chose, j’ai prévu un trigger ad-hoc (à enrichir pour que la table LotVegetal ne fasse pas non plus référence aux tables LotOrigineAnimale et LotAnimalVivant. Bien entendu, chacune des 4 tables doit être munie de ses 3 triggers...
Code SQL, création des tables :
CREATE TABLE Famille
(
FamilleId INT
, FamilleCode VARCHAR(2) NOT NULL
, FamilleLibelle VARCHAR(48) NOT NULL
, CONSTRAINT Famille_PK PRIMARY KEY(FamilleId)
, CONSTRAINT FamilleCode_AK UNIQUE(FamilleCode)
, CONSTRAINT FamilleLibelle_AK UNIQUE(FamilleLibelle)
);
CREATE TABLE NomenclatureViande
(
NomenclatureId INT
, NomenclatureLibelle VARCHAR(48) NOT NULL
, CONSTRAINT NomenclatureViande_PK PRIMARY KEY(NomenclatureId)
);
CREATE TABLE NomenclatureVegetal
(
NomenclatureId INT
, NomenclatureLibelle VARCHAR(48) NOT NULL
, CONSTRAINT NomenclatreVegetal_PK PRIMARY KEY(NomenclatureId)
);
CREATE TABLE NomenclatureOrigineAnimale
(
NomenclatureId INT
, NomenclatureLibelle VARCHAR(48) NOT NULL
, CONSTRAINT NomenclatureOrigineAnimale_PK PRIMARY KEY(NomenclatureId)
);
CREATE TABLE Espece
(
EspeceId INT
, EspeceLibelle VARCHAR(48) NOT NULL
, CONSTRAINT Espece_PK PRIMARY KEY(EspeceId)
);
CREATE TABLE Marchandise
(
MarchandiseId INT
, FamilleId INT NOT NULL
, MarchandiseLibelle VARCHAR(48) NOT NULL
, CONSTRAINT Marchandise_PK PRIMARY KEY(MarchandiseId)
, CONSTRAINT Marchandise_Famille_FK
FOREIGN KEY(FamilleId) REFERENCES Famille(FamilleId)
);
CREATE TABLE Dossier
(
DossierId INT
, MarchandiseId INT NOT NULL
, DossierLibelle VARCHAR(24) NOT NULL
, CONSTRAINT Dossier_PK PRIMARY KEY(DossierId)
, CONSTRAINT Dossier_AK UNIQUE(MarchandiseId)
, CONSTRAINT Dossier_Marchandise_FK
FOREIGN KEY(MarchandiseId) REFERENCES Marchandise(MarchandiseId)
);
CREATE TABLE LotViande
(
MarchandiseId INT
, NomenclatureId INT
, LotId INT
, Quantite INT NOT NULL
, CONSTRAINT LotViande_PK PRIMARY KEY(MarchandiseId, NomenclatureId, LotId)
, CONSTRAINT LotViande_NomenclatureViande_FK
FOREIGN KEY(NomenclatureId) REFERENCES NomenclatureViande(NomenclatureId)
, CONSTRAINT LotViande_Marchandise_FK
FOREIGN KEY(MarchandiseId) REFERENCES Marchandise(MarchandiseId)
ON DELETE CASCADE
);
CREATE TABLE LotVegetal
(
MarchandiseId INT
, NomenclatureId INT
, LotId INT
, Quantite INT NOT NULL
, Etat VARCHAR(50) NOT NULL
, CONSTRAINT LotVegetal_PK PRIMARY KEY(MarchandiseId, NomenclatureId, LotId)
, CONSTRAINT LotVegetal_NomenclatreVegetal_FK
FOREIGN KEY(NomenclatureId) REFERENCES NomenclatureVegetal(NomenclatureId)
, CONSTRAINT LotVegetal_Marchandise_FK
FOREIGN KEY(MarchandiseId) REFERENCES Marchandise(MarchandiseId)
ON DELETE CASCADE
);
CREATE TABLE LotOrigineAnimale
(
MarchandiseId INT
, NomenclatureId INT
, LotId INT
, Quantite INT NOT NULL
, CONSTRAINT LotOrigineAnimale_PK PRIMARY KEY(MarchandiseId, NomenclatureId, LotId)
, CONSTRAINT LotOrigineAnimale_NomenclatueOrigineAnimale_FK
FOREIGN KEY(NomenclatureId) REFERENCES NomenclatureOrigineAnimale(NomenclatureId)
, CONSTRAINT LotOrigineAnimale_Marchandise_FK
FOREIGN KEY(MarchandiseId) REFERENCES Marchandise(MarchandiseId)
ON DELETE CASCADE
);
CREATE TABLE LotAnimalVivant
(
MarchandiseId INT
, EspeceId INT
, LotId INT
, Sexe CHAR(4) NOT NULL
, Puce VARCHAR(24) NOT NULL
, CONSTRAINT LotAnimalVivant_PK PRIMARY KEY(MarchandiseId, EspeceId)
, CONSTRAINT LotAnimalVivant_Espece_FK
FOREIGN KEY(EspeceId) REFERENCES Espece(EspeceId)
, CONSTRAINT LotAnimalVivant_Marchandise_FK
FOREIGN KEY(MarchandiseId) REFERENCES Marchandise(MarchandiseId)
ON DELETE CASCADE
) ;
CREATE TRIGGER LotVegetal_insert_trigger ON LotVegetal
AFTER INSERT AS
DECLARE @n as INT ;
DECLARE @Engueulade AS VARCHAR(254)
SET @n =
(
SELECT COUNT(DISTINCT NomenclatureId)
FROM INSERTED
HAVING COUNT(DISTINCT NomenclatureId) > 1
)
;
IF @n > 1
BEGIN
SET @Engueulade = 'Le lot fait référence à plus d''une nomenclature !'
RAISERROR (@Engueulade, 16,1)
ROLLBACK
END ;
INSERT INTO Famille VALUES
(1, 'A', 'Animaux vivants')
, (2, 'P', 'Produits d''origine animale')
, (3, 'PP', 'Végétaux et produits végétaux')
, (4, 'D', 'Produits destinés à l''alimentation animale')
SELECT '' AS Famille, * FROM Famille ;
INSERT INTO NomenclatureViande VALUES
(1, 'V1')
, (2, 'V2')
, (3, 'V3')
SELECT '' AS NomenclatureViande, * FROM NomenclatureViande
INSERT INTO NomenclatureVegetal VALUES
(1, '1MABG')
, (2, 'SOLTU')
, (3, 'LYPES')
SELECT '' AS NomenclatureVegetal, * FROM NomenclatureVegetal
INSERT INTO Marchandise VALUES
(1, 2, 'M1') -- MarchandiseId, FamilleId, libelle
, (2, 2, 'M2') -- marchandise viande
, (3, 2, 'M3') -- marchandise viande
, (4, 3, 'M4') -- marchandise végétale
, (5, 3, 'M5') -- marchandise végétale
SELECT '' AS Marchandise, * FROM Marchandise
INSERT INTO LotViande VALUES
(1, 1, 1, 15) -- MarchandiseId, NomenclatureId, LotId, Quantite
, (1, 1, 2, 25)
, (1, 1 ,3, 36)
SELECT '' AS LotViande, * FROM LotViande
INSERT INTO LotVegetal VALUES
(2, 2, 1, 15, 'pommes') -- MarchandiseId, NomenclatureId, LotId, Quantite
, (2, 2, 2, 25, 'patates')
, (2, 1, 3, 12, 'tomates') -- nomenclature hétérogène !
SELECT '' AS LotVegetal, * FROM LotVegetal
Envoyé par
SQL Server
Msg 50000, Niveau 16, Procédure LotVegetal_insert_trigger
Le lot fait référence à plus d'une nomenclature !
Le trigger a fonctionné. Si vous jugez que ma proposition ne répond pas à votre problème, merci de m’expliquer ce qi ne va pas, on corrigera.
Concernant la contrainte de partition, on en reparlera, mais le document que vous avez eu la gentillesse de nous faire parvenir en traite d’une façon qui relève plus du voeu pieux que de la contrainte effective...
Demande d'aide modélisation héritage
Répondre avec citation
Partager