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 |
Partager