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
| CREATE TABLE MA_marque(
MA_ident INT IDENTITY,
MA_code CHAR(6) NOT NULL,
MA_nom VARCHAR(128) NOT NULL,
PRIMARY KEY(MA_ident),
UNIQUE(MA_code)
);
CREATE TABLE PE_personne(
PE_ident INT IDENTITY,
PE_nom VARCHAR(50) NOT NULL,
PE_prenom VARCHAR(50) NOT NULL,
PRIMARY KEY(PE_ident)
);
CREATE TABLE MD_modele(
MD_ident INT IDENTITY,
MD_nom VARCHAR(50) NOT NULL,
MA_ident INT NOT NULL,
PRIMARY KEY(MD_ident),
FOREIGN KEY(MA_ident) REFERENCES MA_marque(MA_ident)
);
CREATE TABLE FI_finition(
MD_ident INT,
FI_seqn SMALLINT,
FI_nom VARCHAR(50) NOT NULL,
PRIMARY KEY(MD_ident, FI_seqn),
FOREIGN KEY(MD_ident) REFERENCES MD_modele(MD_ident)
);
CREATE TABLE VH_vehicule(
VH_ident INT IDENTITY,
VH_immat CHAR(10),
PE_ident INT NOT NULL,
MD_ident INT NOT NULL,
FI_seqn SMALLINT NOT NULL,
MD_ident_1 INT NOT NULL,
PRIMARY KEY(VH_ident),
UNIQUE(VH_immat),
FOREIGN KEY(PE_ident) REFERENCES PE_personne(PE_ident),
FOREIGN KEY(MD_ident, FI_seqn) REFERENCES FI_finition(MD_ident, FI_seqn),
FOREIGN KEY(MD_ident_1) REFERENCES MD_modele(MD_ident)
);
CREATE TABLE IN_interesser(
VH_ident INT,
PE_ident INT,
PRIMARY KEY(VH_ident, PE_ident),
FOREIGN KEY(VH_ident) REFERENCES VH_vehicule(VH_ident),
FOREIGN KEY(PE_ident) REFERENCES PE_personne(PE_ident)
);
alter table VH_vehicule
add constraint VHKF01MD
foreign key (MD_ident, FI_seqn)
references FI_finition(MD_ident, FI_seqn)
; |
Partager