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
72
73
74
75
76
77
78
79
|
spool createTables
DROP TABLE LesMaladies;
DROP TABLE LesAnimaux;
DROP TABLE LesGardiens;
DROP TABLE LesCages;
DROP TABLE LesResponsable;
DROP TABLE LesEmployes;
CREATE TABLE LesEmployes
(nomE VARCHAR2(30),
Adresse VARCHAR2(80),
PRIMARY KEY (nomE) ENABLE
);
CREATE TABLE LesResponsable
(noAllee NUMBER(5,0),
nomE VARCHAR2(30),
PRIMARY KEY (noAllee) ENABLE,
FOREIGN KEY (nomE)
REFERENCES LesEmployes (nomE) ENABLE,
CONSTRAINT noAlleeInfResponsable CHECK (noAllee > 0)
);
CREATE TABLE LesCages
(noCage NUMBER(5,0) ,
fonction VARCHAR2(30),
noAllee NUMBER(5,0),
PRIMARY KEY (noCage) ENABLE,
FOREIGN KEY (noAllee)
REFERENCES LesResponsable (noAllee) ENABLE,
CONSTRAINT noCageInfCages CHECK (noCage > 0),
CONSTRAINT noAlleeInfCages CHECK (noAllee > 0)
);
CREATE TABLE LesGardiens
(noCage NUMBER(5,0),
nomE VARCHAR2(30),
PRIMARY KEY (noCage, NomE) ENABLE,
FOREIGN KEY (noCage)
REFERENCES LesCages (noCage) ENABLE,
FOREIGN KEY (nomE)
REFERENCES LesEmployes (nomE) ENABLE,
CONSTRAINT noCageInfGardiens CHECK (noCage > 0)
);
CREATE TABLE LesAnimaux
(nomA VARCHAR2(30),
sexe VARCHAR2(20),
type VARCHAR2(80),
pays VARCHAR2(80),
anNais NUMBER(4,0),
noCage NUMBER(5,0),
PRIMARY KEY (nomA) ENABLE,
FOREIGN KEY (noCage)
REFERENCES LesCages (noCage) ENABLE,
CONSTRAINT annee CHECK ( anNais > 1900 ),
CONSTRAINT noCageInf CHECK (noCage > 0)
);
CREATE TABLE LesMaladies
(nomA VARCHAR2(30),
nomM VARCHAR2(30),
PRIMARY KEY (nomA, nomM) ENABLE,
FOREIGN KEY (nomA)
REFERENCES LesAnimaux(nomA) ENABLE
);
insert into LesEmployes select nomE, Adresse from fauvetm.LesEmployes;
insert into LesResponsable select noAllee, nomE from fauvetm.LesResponsables;
insert into LesCages select noCage, fonction, noAllee from fauvetm.LesCages;
insert into LesGardiens select noCage, nomE from fauvetm.LesGardiens;
insert into LesAnimaux select nomA, sexe, type, pays, anNais, noCage from fauvetm.LesAnimaux;
insert into LesMaladies select nomA, nomM from fauvetm.LesMaladies;
spool off |
