Whale Tables

1. Whales1.txt

<PRE>

drop table Oceans;

create table Oceans (Ocean varchar2(15), OCode Char(2),

Constraint OC_PK Primary Key (OCode));

insert into Oceans values ('North Atlantic','NA');

insert into Oceans values ('South Atlantic','SA');

insert into Oceans values ('Indian Ocean','IO');

insert into Oceans values ('South Pacific','SP');

insert into Oceans values ('North Pacific','NP');

drop table WaterTemp;

create table WaterTemp (TCode char(2), TClass varchar2(10), AveTempCel NUMBER,

constraint TC_PK Primary key (TCode));

insert into WaterTemp values ('T1','Polar',5);

insert into WaterTemp values ('T2','Temperate',15);

insert into WaterTemp values ('T3','Tropical',25);

drop table WhaleFam;

create table WhaleFam (Kingdom varchar2(10),Phylum varchar2(10), subphylum varchar2(10),

class varchar2(10), subclass varchar2(10), Type varchar2(15), TaxOrder varchar2(10),

family varchar2(20), ID Number, constraint WF_PK Primary Key (ID));

insert into WhaleFam values ('Animalia','Chordata','Vertebrata','Mammalia','Cetacea',

'Baleen Whales','Mysticeti','Balaenidae',1);

insert into WhaleFam values ('Animalia','Chordata','Vertebrata','Mammalia','Cetacea',

'Baleen Whales','Mysticeti','Eschrichtidae',2);

insert into WhaleFam values ('Animalia','Chordata','Vertebrata','Mammalia','Cetacea',

'Baleen Whales','Mysticeti','Balaenopteridae',3);

insert into WhaleFam values ('Animalia','Chordata','Vertebrata','Mammalia','Cetacea',

'Toothed Whales','Odontoceti','Ziphiidae',4);

insert into WhaleFam values ('Animalia','Chordata','Vertebrata','Mammalia','Cetacea',

'Toothed Whales','Odontoceti','Platanistidae',5);

insert into WhaleFam values ('Animalia','Chordata','Vertebrata','Mammalia','Cetacea',

'Toothed Whales','Odontoceti','Monodontidae',6);

insert into WhaleFam values ('Animalia','Chordata','Vertebrata','Mammalia','Cetacea',

'Toothed Whales','Odontoceti','Physeteridae',7);

insert into WhaleFam values ('Animalia','Chordata','Vertebrata','Mammalia','Cetacea',

'Toothed Whales','Odontoceti','Stenidae',8);

insert into WhaleFam values ('Animalia','Chordata','Vertebrata','Mammalia','Cetacea',

'Toothed Whales','Odontoceti','Phocoenidae',9);

insert into WhaleFam values ('Animalia','Chordata','Vertebrata','Mammalia','Cetacea',

'Toothed Whales','Odontoceti','Globicephalidae',10);

insert into WhaleFam values ('Animalia','Chordata','Vertebrata','Mammalia','Cetacea',

'Toothed Whales','Odontoceti','Delphinidae',11);

</PRE>

2. Whales2.txt

<PRE>

drop tables whales;

create table whales (IDNo Number, WFID Number, Genus_species Varchar2(50), CommonName Varchar2(50),

AveLengthM Number, AveWeightKG Number, DivingMin Number, GestationMon Number, LifeYr Number,

VertCervical Number, VertThoracic Number, VertLumbar Number, VerCalidal Number,

Constraint IDNO_PK Primary Key (IDNO),

Constraint WF_FK Foreign Key (WFID) References WhaleFam(ID));

insert into whales values (2,1,'Balaena mysticetus','Bowhead Whale',15,60000,60,10,40,7,13,12,23);

insert into whales values (4,2,'Eschrichtius robustus','Gray Whale',15,27000,18,13,50,7,14,12,23);

insert into whales values (6,3,'Balaenoptera musculus','Blue Whale',24,100000,50,11,60,7,15,15,27);

insert into whales values (10,3,'Megaptera novaeangliae','Humpback Whale',19,48000,30,11,30,7,14,10,22);

insert into whales values (15,4,'Hyperoodon ampullatus','Northern Bottlenose Whale',9,3000,10,12,20,7,9,12,18);

insert into whales values (32,5,'Inia geoffrensis','Amazon River Dolphin',3,90,2,10,20,7,13,5,17);

insert into whales values (34,6,'Monodon monoceros','Narwhal',4.5,1200,5,15,25,7,12,8,27);

insert into whales values (35,6,'Deliphinapterus leucas','Beluga Whale',5,1100,2,14,40,7,12,8,26);

insert into whales values (36,7,'Physeter macrocephalus','Sperm Whale',18,45000,60,16,70,7,11,8,24);

insert into whales (IDNo,WFID,Genus_species, CommonName, AveLengthM, AveWeightKG,

LifeYr, VertCervical, VertThoracic, VertLumbar, VerCalidal) values

(39,8,'Steno bredanensis','Roughtooth Dolphin',2.5,160,20,7,13,16,31);

insert into whales (IDNo,WFID,Genus_species, CommonName, AveLengthM,AveWeightKG,

VertCervical, VertThoracic, VertLumbar, VerCalidal) values

(45,9,'Phocoena dioptrica','Spectacled Porpoise',2,55,7,13,16,32);

insert into whales values (51,10,'Orcinus orca','Killer Whale',9,8000,4,14,25,7,12,10,23);

insert into whales values (75,11,'Delphinus delphis','Common Dolphin',2.2,125,8,10,20,7,14,21,32);

insert into whales values (76,11,'Tursiops truncatus','Bottlenose Dolphin',4,650,10,12,35,7,14,15,29);

</PRE>

3. Whales3.txt

<PRE>

drop tables whaledist;

create table whaledist (IDNo Number, OCode Char(2), TCode Char(2),

Constraint IDNO_FK Foreign Key (IDNo) References whales(IDNo),

Constraint OC_FK Foreign Key (OCode) References Oceans(OCode),

Constraint TC_FK Foreign Key (TCode) References WaterTemp(TCode));

insert into whaledist values (2,'NP','T1');

insert into whaledist values (2,'NA','T1');

insert into whaledist values (4,'NP','T1');

insert into whaledist values (4,'NP','T2');

insert into whaledist values (6,'NA','T1');

insert into whaledist values (6,'SA','T1');

insert into whaledist values (6,'IO','T1');

insert into whaledist values (6,'SP','T1');

insert into whaledist values (6,'NP','T1');

insert into whaledist values (6,'NA','T2');

insert into whaledist values (6,'SA','T2');

insert into whaledist values (6,'IO','T2');

insert into whaledist values (6,'SP','T2');

insert into whaledist values (6,'NP','T2');

insert into whaledist values (6,'NA','T3');

insert into whaledist values (6,'SA','T3');

insert into whaledist values (6,'IO','T3');

insert into whaledist values (6,'SP','T3');

insert into whaledist values (6,'NP','T3');

insert into whaledist values (10,'NA','T1');

insert into whaledist values (10,'SA','T1');

insert into whaledist values (10,'IO','T1');

insert into whaledist values (10,'SP','T1');

insert into whaledist values (10,'NP','T1');

insert into whaledist values (10,'NA','T2');

insert into whaledist values (10,'SA','T2');

insert into whaledist values (10,'IO','T2');

insert into whaledist values (10,'SP','T2');

insert into whaledist values (10,'NP','T2');

insert into whaledist values (10,'NA','T3');

insert into whaledist values (10,'SA','T3');

insert into whaledist values (10,'IO','T3');

insert into whaledist values (10,'SP','T3');

insert into whaledist values (10,'NP','T3');

insert into whaledist values (15,'NA','T1');

insert into whaledist values (15,'NA','T2');

insert into whaledist values (32,'SA','T3');

insert into whaledist values (34,'NA','T1');

insert into whaledist values (35,'NA','T1');

insert into whaledist values (36,'NA','T1');

insert into whaledist values (36,'SA','T1');

insert into whaledist values (36,'IO','T1');

insert into whaledist values (36,'SP','T1');

insert into whaledist values (36,'NP','T1');

insert into whaledist values (36,'NA','T2');

insert into whaledist values (36,'SA','T2');

insert into whaledist values (36,'IO','T2');

insert into whaledist values (36,'SP','T2');

insert into whaledist values (36,'NP','T2');

insert into whaledist values (36,'NA','T3');

insert into whaledist values (36,'SA','T3');

insert into whaledist values (36,'IO','T3');

insert into whaledist values (36,'SP','T3');

insert into whaledist values (36,'NP','T3');

insert into whaledist values (39,'NA','T3');

insert into whaledist values (39,'SA','T3');

insert into whaledist values (39,'IO','T3');

insert into whaledist values (39,'SP','T3');

insert into whaledist values (39,'NP','T3');

insert into whaledist values (45,'SA','T1');

insert into whaledist values (51,'NA','T1');

insert into whaledist values (51,'SA','T1');

insert into whaledist values (51,'IO','T1');

insert into whaledist values (51,'SP','T1');

insert into whaledist values (51,'NP','T1');

insert into whaledist values (51,'NA','T2');

insert into whaledist values (51,'SA','T2');

insert into whaledist values (51,'IO','T2');

insert into whaledist values (51,'SP','T2');

insert into whaledist values (51,'NP','T2');

insert into whaledist values (51,'NA','T3');

insert into whaledist values (51,'SA','T3');

insert into whaledist values (51,'IO','T3');

insert into whaledist values (51,'SP','T3');

insert into whaledist values (51,'NP','T3');

insert into whaledist values (75,'NA','T2');

insert into whaledist values (75,'SA','T2');

insert into whaledist values (75,'IO','T2');

insert into whaledist values (75,'SP','T2');

insert into whaledist values (75,'NP','T2');

insert into whaledist values (75,'NA','T3');

insert into whaledist values (75,'SA','T3');

insert into whaledist values (75,'IO','T3');

insert into whaledist values (75,'SP','T3');

insert into whaledist values (75,'NP','T3');

insert into whaledist values (76,'NA','T2');

insert into whaledist values (76,'SA','T2');

insert into whaledist values (76,'IO','T2');

insert into whaledist values (76,'SP','T2');

insert into whaledist values (76,'NP','T2');

insert into whaledist values (76,'NA','T3');

insert into whaledist values (76,'SA','T3');

insert into whaledist values (76,'IO','T3');

insert into whaledist values (76,'SP','T3');

insert into whaledist values (76,'NP','T3');

</PRE>

4. Whales4.txt

<PRE>

drop sequence indwh;

create sequence indwh start with 1000;

drop sequence sight;

create sequence sight start with 10000;

drop table whalesightings;

drop table indivwhales;

create table indivwhales (WhID number, Name varchar2(15),

DOB date, IDNo number, constraint wh_pk Primary Key (WhID),

constraint wid_fk foreign key (IDno) references whales(IDNo));

create table whalesightings (WhID number, SID number, Sight_Date date,

LengthM number, WeightKG number, DivingMin number,

constraint sid_pk primary key (SID),

constraint ind_fk foreign key (WhId) references indivwhales(WhID));

insert into indivwhales values (indwh.nextval,'Orbit','28-JUN-76',10);

insert into indivwhales values (indwh.nextval,'Moby','14-JUL-84',36);

insert into indivwhales values (indwh.nextval,'Little Moby','22-SEP-92',36);

insert into indivwhales values (indwh.nextval,'Estrella','30-APR-81',51);

insert into indivwhales values (indwh.nextval,'Keiko','04-JUL-87',10);

insert into indivwhales values (indwh.nextval,'Babe','26-MAR-78',6);

insert into indivwhales values (indwh.nextval,'Algol','11-JUL-89',4);

insert into whalesightings values (1000,sight.nextval,'23-JUN-82',15,40000,20);

insert into whalesightings values (1000,sight.nextval,'31-MAY-85',17,44000,28);

insert into whalesightings values (1001,sight.nextval,'22-OCT-85',17,40000,40);

insert into whalesightings values (1002,sight.nextval,'15-DEC-94',14,37000,30);

insert into whalesightings values (1003,sight.nextval,'16-FEB-82',8,6000,2);

insert into whalesightings values (1003,sight.nextval,'22-AUG-88',9,7000,3);

insert into whalesightings values (1006,sight.nextval,'07-JAN-93',16,28000,21);

</PRE>