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
) ;
Un premier trigger de contrôle de l’homogénéité des lots (table LotVegetal) :
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
=>