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
| CREATE TABLE SO_soldat(
SO_ident INT,
SO_nom VARCHAR(50) NOT NULL,
PRIMARY KEY(SO_ident)
);
CREATE TABLE BA_bataille(
BA_ident INT,
BA_nom VARCHAR(50) NOT NULL,
PRIMARY KEY(BA_ident)
);
CREATE TABLE YB_type_blessure(
YB_ident INT,
YB_description VARCHAR(50) NOT NULL,
PRIMARY KEY(YB_ident)
);
CREATE TABLE BL_blessure(
SO_ident INT,
BL_ident INT,
BL_severite SMALLINT,
BL_date DATE NOT NULL,
YB_ident INT NOT NULL,
PRIMARY KEY(SO_ident, BL_ident),
FOREIGN KEY(SO_ident) REFERENCES SO_soldat(SO_ident),
FOREIGN KEY(YB_ident) REFERENCES YB_type_blessure(YB_ident)
);
CREATE TABLE PA_participer(
SO_ident INT,
BA_ident INT,
CA_date DATE,
CA_date_1 DATE,
PRIMARY KEY(SO_ident, BA_ident, CA_date, CA_date_1),
FOREIGN KEY(SO_ident) REFERENCES SO_soldat(SO_ident),
FOREIGN KEY(BA_ident) REFERENCES BA_bataille(BA_ident)
);
CREATE TABLE IN_infliger(
BA_ident INT,
SO_ident INT,
BL_ident INT,
PRIMARY KEY(BA_ident, SO_ident, BL_ident),
FOREIGN KEY(BA_ident) REFERENCES BA_bataille(BA_ident),
FOREIGN KEY(SO_ident, BL_ident) REFERENCES BL_blessure(SO_ident, BL_ident)
);
alter table PA_participer
add constraint PA_CK01_DATES (CA_date_1 >= CA_date)
Create TRIGGER... |
