1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140
| DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
CREATE TABLE dossiers(
id SERIAL,
type VARCHAR(2) CHECK (type IN ('A','P','PP','D')),
num VARCHAR(50) NOT NULL,
PRIMARY KEY(id, type),
UNIQUE(num)
);
CREATE TABLE avi(
id SERIAL,
latin VARCHAR(50) NOT NULL,
nom VARCHAR(50) NOT NULL,
PRIMARY KEY(id)
);
CREATE TABLE marchandises(
id_dossiers INTEGER,
type_dossiers VARCHAR(2),
quantite_totale INTEGER NOT NULL,
conditions_transport VARCHAR(50) NOT NULL,
PRIMARY KEY(id_dossiers, type_dossiers),
FOREIGN KEY(id_dossiers, type_dossiers) REFERENCES dossiers(id, type)
);
CREATE TABLE aanoa(
id SERIAL,
catégorie VARCHAR(50),
PRIMARY KEY(id)
);
CREATE TABLE poa(
id SERIAL,
libelle VARCHAR(50),
cs VARCHAR(50),
hc BOOLEAN,
PRIMARY KEY(id)
);
CREATE TABLE vpv(
id SERIAL,
oepp VARCHAR(50) NOT NULL,
latin VARCHAR(50) NOT NULL,
nom VARCHAR(50) NOT NULL,
PRIMARY KEY(id)
);
CREATE TABLE organismes_nuisibles(
id SERIAL,
oepp VARCHAR(10) NOT NULL,
PRIMARY KEY(id)
);
CREATE TABLE lots(
id_dossiers INTEGER,
type_dossiers VARCHAR(2),
id SERIAL,
quantite INTEGER NOT NULL,
PRIMARY KEY(id_dossiers, type_dossiers, id),
FOREIGN KEY(id_dossiers, type_dossiers) REFERENCES marchandises(id_dossiers, type_dossiers)
);
CREATE TABLE lots_a(
id_dossiers INTEGER,
type_dossiers VARCHAR(2),
id_lots INTEGER,
puce VARCHAR(50),
sexe VARCHAR(50),
id_avi INTEGER NOT NULL,
PRIMARY KEY(id_dossiers, type_dossiers, id_lots),
FOREIGN KEY(id_dossiers, type_dossiers, id_lots) REFERENCES lots(id_dossiers, type_dossiers, id),
FOREIGN KEY(id_avi) REFERENCES avi(id)
);
CREATE TABLE lots_p(
id_dossiers INTEGER,
type_dossiers VARCHAR(2),
id_lots INTEGER,
canalisation BOOLEAN,
id_poa INTEGER NOT NULL,
PRIMARY KEY(id_dossiers, type_dossiers, id_lots),
FOREIGN KEY(id_dossiers, type_dossiers, id_lots) REFERENCES lots(id_dossiers, type_dossiers, id),
FOREIGN KEY(id_poa) REFERENCES poa(id)
);
CREATE TABLE lots_d(
id_dossiers INTEGER,
type_dossiers VARCHAR(2),
id_lots INTEGER,
prlvt BOOLEAN,
id_aanoa INTEGER NOT NULL,
PRIMARY KEY(id_dossiers, type_dossiers, id_lots),
FOREIGN KEY(id_dossiers, type_dossiers, id_lots) REFERENCES lots(id_dossiers, type_dossiers, id),
FOREIGN KEY(id_aanoa) REFERENCES aanoa(id)
);
CREATE TABLE lots_pp(
id_dossiers INTEGER,
type_dossiers VARCHAR(2),
id_lots INTEGER,
pp INTEGER,
id_vpv INTEGER NOT NULL,
PRIMARY KEY(id_dossiers, type_dossiers, id_lots),
FOREIGN KEY(id_dossiers, type_dossiers, id_lots) REFERENCES lots(id_dossiers, type_dossiers, id),
FOREIGN KEY(id_vpv) REFERENCES vpv(id)
);
CREATE TABLE vpv_organismes_nuisibles(
id_vpv INTEGER,
id_organismes_nuisibles INTEGER,
PRIMARY KEY(id_vpv, id_organismes_nuisibles),
FOREIGN KEY(id_vpv) REFERENCES vpv(id),
FOREIGN KEY(id_organismes_nuisibles) REFERENCES organismes_nuisibles(id)
);
ALTER TABLE IF EXISTS lots_a
ADD CONSTRAINT lots_a_type_dossiers_check CHECK (type_dossiers = 'A');
ALTER TABLE IF EXISTS lots_d
ADD CONSTRAINT lots_d_type_dossiers_check CHECK (type_dossiers = 'D');
ALTER TABLE IF EXISTS lots_p
ADD CONSTRAINT lots_p_type_dossiers_check CHECK (type_dossiers = 'P');
ALTER TABLE IF EXISTS lots_pp
ADD CONSTRAINT lots_pp_type_dossiers_check CHECK (type_dossiers = 'PP');
INSERT INTO "avi" ("latin", "nom") VALUES ('Gallus Gallus', 'Poulet'),('Equus Caballus', 'Cheval');
INSERT INTO "vpv" ("oepp","latin", "nom") VALUES ('1MABG', 'Malus','Pomme'),('LYPES', 'Solanum lycopersicum','Tomate');
INSERT INTO "poa" ("libelle","cs", "hc") VALUES ('Viande de volaille', 'POU',TRUE),('Viande bovine', 'BOV',TRUE);
INSERT INTO "dossiers" ("type","num") VALUES ('A','2023.0000001'),('P','2023.0000002'),('PP','2023.0000003');
INSERT INTO "marchandises" ("id_dossiers","type_dossiers","quantite_totale","conditions_transport") VALUES (1,'A',1,'Ambiant'),(2,'P',250,'Réfrigéré'),(3,'PP', 800,'Congelé');
INSERT INTO "lots" ("id_dossiers","type_dossiers","quantite") VALUES (1,'A',1),(2,'P',125),(2,'P',125),(3,'PP', 300),(3,'PP', 300),(3,'PP', 200); |