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
| CREATE TABLE SG_siege(
SG_ident INT IDENTITY,
SG_reference CHAR(8) NOT NULL,
PRIMARY KEY(SG_ident),
UNIQUE(SG_reference)
);
CREATE TABLE MO_modele(
MO_ident INT IDENTITY,
MO_reference CHAR(10) NOT NULL,
MO_nom_commercial VARCHAR(50) NOT NULL,
PRIMARY KEY(MO_ident),
UNIQUE(MO_reference)
);
CREATE TABLE XS_exemplaire_siege(
SG_ident INT,
XS_ident INT IDENTITY,
XS_numserie SMALLINT NOT NULL,
XS_date_fab DATE NOT NULL,
PRIMARY KEY(SG_ident, XS_ident),
UNIQUE(XS_numserie),
FOREIGN KEY(SG_ident) REFERENCES SG_siege(SG_ident)
);
CREATE TABLE MG_magasin(
MG_ident INT IDENTITY,
MG_ref CHAR(12) NOT NULL,
MG_libelle VARCHAR(50) NOT NULL,
PRIMARY KEY(MG_ident),
UNIQUE(MG_ref)
);
CREATE TABLE MM_modele_millesime(
MO_ident INT,
MM_ident INT IDENTITY,
MM_dtdeb VARCHAR(50) NOT NULL,
PRIMARY KEY(MO_ident, MM_ident),
FOREIGN KEY(MO_ident) REFERENCES MO_modele(MO_ident)
);
CREATE TABLE VE_vehicule(
MO_ident INT,
MM_ident INT,
VE_ident INT IDENTITY,
VE_num_chassis CHAR(15) NOT NULL,
VE_date_fab DATE NOT NULL,
PRIMARY KEY(MO_ident, MM_ident, VE_ident),
UNIQUE(VE_num_chassis),
FOREIGN KEY(MO_ident, MM_ident) REFERENCES MM_modele_millesime(MO_ident, MM_ident)
);
CREATE TABLE EM_emplacement(
MO_ident INT,
MM_ident INT,
EM_ident INT IDENTITY,
EM_code CHAR(4) NOT NULL,
EM_libelle VARCHAR(50) NOT NULL,
PRIMARY KEY(MO_ident, MM_ident, EM_ident),
UNIQUE(EM_code),
FOREIGN KEY(MO_ident, MM_ident) REFERENCES MM_modele_millesime(MO_ident, MM_ident)
);
CREATE TABLE AF_affectation(
AF_ident INT IDENTITY,
MG_ident INT,
MO_ident INT,
MM_ident INT,
VE_ident INT,
PRIMARY KEY(AF_ident),
FOREIGN KEY(MG_ident) REFERENCES MG_magasin(MG_ident),
FOREIGN KEY(MO_ident, MM_ident, VE_ident) REFERENCES VE_vehicule(MO_ident, MM_ident, VE_ident)
);
CREATE TABLE SV_adapter(
SG_ident INT,
MO_ident INT,
MM_ident INT,
EM_ident INT,
PRIMARY KEY(SG_ident, MO_ident, MM_ident, EM_ident),
FOREIGN KEY(SG_ident) REFERENCES SG_siege(SG_ident),
FOREIGN KEY(MO_ident, MM_ident, EM_ident) REFERENCES EM_emplacement(MO_ident, MM_ident, EM_ident)
);
CREATE TABLE XF_affecter(
SG_ident INT,
XS_ident INT,
CA_date DATE,
AF_ident INT NOT NULL,
PRIMARY KEY(SG_ident, XS_ident, CA_date),
FOREIGN KEY(SG_ident, XS_ident) REFERENCES XS_exemplaire_siege(SG_ident, XS_ident),
FOREIGN KEY(AF_ident) REFERENCES AF_affectation(AF_ident)
); |
Partager