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
| CREATE TABLE GEN_genre(
GEN_ident INT IDENTITY,
GEN_code CHAR(4) NOT NULL,
GEN_libelle VARCHAR(50) NOT NULL,
PRIMARY KEY(GEN_ident),
UNIQUE(GEN_code)
);
CREATE TABLE PRS_personne(
PRS_ident INT IDENTITY,
PRS_nom VARCHAR(50) NOT NULL,
PRS_prenom VARCHAR(50) NOT NULL,
PRIMARY KEY(PRS_ident)
);
CREATE TABLE CLI_client(
PRS_ident INT,
PRIMARY KEY(PRS_ident),
FOREIGN KEY(PRS_ident) REFERENCES PRS_personne(PRS_ident)
);
CREATE TABLE EMP_employe(
PRS_ident INT,
EMP_matricule CHAR(5) NOT NULL,
EMP_dtemb DATE,
PRIMARY KEY(PRS_ident),
UNIQUE(EMP_matricule),
FOREIGN KEY(PRS_ident) REFERENCES PRS_personne(PRS_ident)
);
CREATE TABLE CAR_caracteristique(
CAR_ident INT IDENTITY,
CAR_code CHAR(6) NOT NULL,
CAR_libelle VARCHAR(50) NOT NULL,
PRIMARY KEY(CAR_ident),
UNIQUE(CAR_code)
);
CREATE TABLE ANI_animal(
GEN_ident INT,
ANI_seq SMALLINT,
ANI_nom VARCHAR(50) NOT NULL,
ANI_ddn DATE NOT NULL,
PRS_ident INT NOT NULL,
PRIMARY KEY(GEN_ident, ANI_seq),
FOREIGN KEY(GEN_ident) REFERENCES GEN_genre(GEN_ident),
FOREIGN KEY(PRS_ident) REFERENCES CLI_client(PRS_ident)
);
CREATE TABLE CGN_carac_genre(
GEN_ident INT,
CAR_ident INT,
PRIMARY KEY(GEN_ident, CAR_ident),
FOREIGN KEY(GEN_ident) REFERENCES GEN_genre(GEN_ident),
FOREIGN KEY(CAR_ident) REFERENCES CAR_caracteristique(CAR_ident)
);
CREATE TABLE CAN_carac_ani(
GEN_ident INT,
ANI_seq SMALLINT,
CAL_date DATE,
CAR_ident INT NOT NULL,
PRIMARY KEY(GEN_ident, ANI_seq, CAL_date),
FOREIGN KEY(GEN_ident, ANI_seq) REFERENCES ANI_animal(GEN_ident, ANI_seq),
FOREIGN KEY(CAR_ident) REFERENCES CAR_caracteristique(CAR_ident)
);
ALTER TABLE CAN_carac_ani
ADD CONSTRAINT CAN_FK_001
FOREIGN KEY(GEN_ident, CAR_ident)
REFERENCES CGN_carac_genre(GEN_ident, CAR_ident); |
