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)
; |
