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
|
CREATE TABLE US_user(
US_ident INT IDENTITY,
US_nom VARCHAR(50) NOT NULL,
US_prenom VARCHAR(50) NOT NULL,
PRIMARY KEY(US_ident)
);
CREATE TABLE TE_test(
TE_ident INT IDENTITY,
TE_code CHAR(4) NOT NULL,
TE_libelle VARCHAR(128) NOT NULL,
PRIMARY KEY(TE_ident),
UNIQUE(TE_code)
);
CREATE TABLE QU_question(
TE_ident INT,
QU_sequ SMALLINT,
QU_libelle VARCHAR(255) NOT NULL,
PRIMARY KEY(TE_ident, QU_sequ),
FOREIGN KEY(TE_ident) REFERENCES TE_test(TE_ident)
);
CREATE TABLE TR_trait(
TR_ident INT IDENTITY,
TR_code CHAR(4) NOT NULL,
TR_libelle VARCHAR(50) NOT NULL,
PRIMARY KEY(TR_ident),
UNIQUE(TR_code)
);
CREATE TABLE CH_choix(
TE_ident INT,
QU_sequ SMALLINT,
CH_sequ TINYINT,
TR_ident INT NOT NULL,
PRIMARY KEY(TE_ident, QU_sequ, CH_sequ),
FOREIGN KEY(TE_ident, QU_sequ) REFERENCES QU_question(TE_ident, QU_sequ),
FOREIGN KEY(TR_ident) REFERENCES TR_trait(TR_ident)
);
CREATE TABLE IN_inscrire(
US_ident INT,
TE_ident INT,
IN_date DATE NOT NULL,
PRIMARY KEY(US_ident, TE_ident),
FOREIGN KEY(US_ident) REFERENCES US_user(US_ident),
FOREIGN KEY(TE_ident) REFERENCES TE_test(TE_ident)
);
CREATE TABLE SL_selectionner(
US_ident INT,
TE_ident INT,
QU_sequ SMALLINT,
CH_sequ TINYINT,
SL_type BIT NOT NULL,
PRIMARY KEY(US_ident, TE_ident, QU_sequ, CH_sequ),
FOREIGN KEY(US_ident) REFERENCES US_user(US_ident),
FOREIGN KEY(TE_ident, QU_sequ, CH_sequ) REFERENCES CH_choix(TE_ident, QU_sequ, CH_sequ)
);
alter table SL_selectionner
add foreign key (US_ident, TE_ident)
references IN_inscrire (US_ident, TE_ident)
; |
Partager