--------------------------------------------------------------------------------------------- --Animal Tables CREATE AND INSERT -- PERSON CREATE AND INSERT ARE AFTER THE ANIMAL DROP TABLE species CASCADE CONSTRAINTS; DROP TABLE animal CASCADE CONSTRAINTS; DROP TABLE exhibit CASCADE CONSTRAINTS; DROP TABLE an_home CASCADE CONSTRAINTS; DROP TABLE habitat CASCADE CONSTRAINTS; DROP TABLE habitat_exhibit CASCADE CONSTRAINTS; DROP TABLE species_habitat CASCADE CONSTRAINTS; DROP TABLE supplr CASCADE CONSTRAINTS; DROP TABLE species_supplier CASCADE CONSTRAINTS; DROP TABLE shipment CASCADE CONSTRAINTS; DROP TABLE food_inv CASCADE CONSTRAINTS; DROP TABLE feeding_sched CASCADE CONSTRAINTS; DROP TABLE problem CASCADE CONSTRAINTS; DROP TABLE animal_solution CASCADE CONSTRAINTS; DROP TABLE animal_problem CASCADE CONSTRAINTS; DROP TABLE exhibit_solution CASCADE CONSTRAINTS; DROP TABLE maintask CASCADE CONSTRAINTS; DROP TABLE employee CASCADE CONSTRAINTS; DROP TABLE eme CASCADE CONSTRAINTS; DROP TABLE exhibit_problem CASCADE CONSTRAINTS; DROP SEQUENCE species_id_sequence; DROP SEQUENCE animal_id_sequence; DROP SEQUENCE ex_id_sequence; DROP SEQUENCE move_id_sequence; DROP SEQUENCE habitat_id_sequence; DROP SEQUENCE supplr_id_sequence; DROP SEQUENCE shipment_id_sequence; DROP SEQUENCE food_id_sequence; DROP SEQUENCE feed_id_sequence; DROP SEQUENCE prob_id_sequence; DROP SEQUENCE anSol_id_sequence; DROP SEQUENCE anProb_id_sequence; DROP SEQUENCE exSol_id_sequence; DROP SEQUENCE exProb_id_sequence; DROP SEQUENCE main_id_sequence; DROP SEQUENCE emp_id_sequence; DROP SEQUENCE eme_id_sequence; --Create Species Table CREATE TABLE species ( species_id NUMBER (5) NOT NULL, latin_name VARCHAR2 (50) NOT NULL, common_name VARCHAR2 (50) NOT NULL, quantity NUMBER (5) NOT NULL, CONSTRAINT species_species_id_pk PRIMARY KEY (species_id)); --Create sequence for species ID CREATE SEQUENCE species_id_sequence START WITH 1000 INCREMENT BY 1 NOCACHE; --Create Animal Table CREATE TABLE animal( animal_id NUMBER(5) NOT NULL, animal_name VARCHAR2(25) NOT NULL, DOB DATE NOT NULL, species_id NUMBER(5) NOT NULL, CONSTRAINT animal_animal_id_PK PRIMARY KEY (animal_id), CONSTRAINT animal_species_id_fk FOREIGN KEY (species_id) REFERENCES species(species_id)); --Create sequence for Animal ID CREATE SEQUENCE animal_id_sequence START WITH 2000 INCREMENT BY 1 NOCACHE; --Create Supplier Table CREATE TABLE supplr ( supplr_id NUMBER (5) NOT NULL, supplrname VARCHAR2 (50) NOT NULL, supplradrs VARCHAR2 (50) NOT NULL, supplrcity VARCHAR2 (50) NOT NULL, supplrstat CHAR (2) NOT NULL, supplrctry VARCHAR (20) NOT NULL, CONSTRAINT supplr_supplr_id_pk PRIMARY KEY (supplr_id)); CREATE SEQUENCE supplr_id_sequence START WITH 3000 INCREMENT BY 1 NOCACHE; --Create Exhibit Table CREATE TABLE exhibit ( ex_id NUMBER(5) NOT NULL, ex_name VARCHAR2(50) NOT NULL, ex_terrain VARCHAR2(50) NOT NULL, ex_description VARCHAR2(75), CONSTRAINT exhibit_ex_id_pk PRIMARY KEY (ex_id)); --Create sequence for exhibit ID*/ CREATE SEQUENCE ex_id_sequence START WITH 4000 INCREMENT BY 1 NOCACHE; --Create Animal Home Table CREATE TABLE an_home ( move_id NUMBER(5) NOT NULL, move_qty NUMBER(3) NOT NULL, date_entered DATE NOT NULL, date_left DATE NOT NULL, animal_id NUMBER (5) NOT NULL, ex_id NUMBER (5) NOT NULL, CONSTRAINT an_home_move_id_pk PRIMARY KEY (move_id), CONSTRAINT an_home_animal_id_fk FOREIGN KEY (animal_id) REFERENCES animal(animal_id), CONSTRAINT an_home_ex_id_fk FOREIGN KEY (ex_id) REFERENCES exhibit(ex_id)); /*Create sequence for animal move ID*/ CREATE SEQUENCE move_id_sequence START WITH 5000 INCREMENT BY 1 NOCACHE; --Create Habitat Table CREATE TABLE habitat ( habitat_id NUMBER(5) NOT NULL, habitat_climate VARCHAR2(50) NOT NULL, habitat_avetemp NUMBER(3) NOT NULL, CONSTRAINT habitat_habitat_id_pk PRIMARY KEY (habitat_id)); --Create sequence for Habitat ID CREATE SEQUENCE habitat_id_sequence START WITH 6000 INCREMENT BY 1 NOCACHE; --Create Habitat-Exhibit Table CREATE TABLE habitat_exhibit ( habitat_id NUMBER (5) NOT NULL, ex_id NUMBER (5) NOT NULL, CONSTRAINT habitat_exhibit_habitat_id_pk PRIMARY KEY (habitat_id, ex_id), CONSTRAINT habitat_exhibit_habitat_id_fk FOREIGN KEY (habitat_id) REFERENCES habitat(habitat_id), CONSTRAINT habitat_exhibit_ex_id_fk FOREIGN KEY (ex_id) REFERENCES exhibit(ex_id)); --Create Species - Habitat Table CREATE TABLE species_habitat ( species_id NUMBER (5) NOT NULL, habitat_id NUMBER (5) NOT NULL, CONSTRAINT species_habitat_id_pk PRIMARY KEY (habitat_id, species_id), CONSTRAINT species_habitat_id_fk FOREIGN KEY (habitat_id) REFERENCES habitat (habitat_id), CONSTRAINT species_habitat_species_id_fk FOREIGN KEY (species_id) REFERENCES species(species_id)); --Create Species-Supplier Table CREATE TABLE species_supplier ( supplr_id NUMBER(5) NOT NULL, species_id NUMBER(7) NOT NULL, CONSTRAINT species_supplier_supplr_id_pk PRIMARY KEY (species_id, supplr_id), CONSTRAINT species_supplier_supplr_id_fk FOREIGN KEY (supplr_id) REFERENCES supplr (supplr_id), CONSTRAINT species_supplier_species_id_fk FOREIGN KEY (species_id) REFERENCES species (species_id)); --Create Shipment Table CREATE TABLE shipment ( shipment_id NUMBER(5) NOT NULL, ship_date DATE NOT NULL, QTY NUMBER(5) NOT NULL, supplr_id NUMBER(5) NOT NULL, animal_id NUMBER(5) NOT NULL, CONSTRAINT shipment_shipment_id_pk PRIMARY KEY (shipment_id), CONSTRAINT shipment_supplr_id_fk FOREIGN KEY (supplr_id) REFERENCES supplr (supplr_id), CONSTRAINT shipment_animal_id_fk FOREIGN KEY (animal_id) REFERENCES animal (animal_id)); --Create sequence for shipment ID CREATE SEQUENCE shipment_id_sequence START WITH 7000 INCREMENT BY 1 NOCACHE; --Create Food Table CREATE TABLE food_inv ( food_id NUMBER(5) NOT NULL, food_name VARCHAR2(50) NOT NULL, food_rop NUMBER (6) NOT NULL, supplr_id NUMBER (5) NOT NULL, CONSTRAINT food_inv_food_id_pk PRIMARY KEY (food_id), CONSTRAINT food_inv_supplr_id_fk FOREIGN KEY (supplr_id) REFERENCES supplr (supplr_id)); --Create sequence for food ID CREATE SEQUENCE food_id_sequence START WITH 8000 INCREMENT BY 1 NOCACHE; --Create Feeding Schedule Table CREATE TABLE feeding_sched ( feed_id NUMBER(5) NOT NULL, Time_Interval NUMBER(1) NOT NULL, food_source VARCHAR2(75) NOT NULL, feed_amount VARCHAR2(75) NOT NULL, food_id NUMBER (5) NOT NULL, animal_id NUMBER (5) NOT NULL, CONSTRAINT feeding_sched_feeding_id_pk PRIMARY KEY (feed_id), CONSTRAINT feeding_sched_food_id_fk FOREIGN KEY (food_id) REFERENCES food_inv (food_id), CONSTRAINT feeding_sched_animal_id_fk FOREIGN KEY (animal_id) REFERENCES animal (animal_id)); --Create sequence for feed ID CREATE SEQUENCE feed_id_sequence START WITH 9000 INCREMENT BY 1 NOCACHE; --Create Problems Table CREATE TABLE problem ( prob_id NUMBER(5) NOT NULL, prob_type VARCHAR2(50) NOT NULL, CONSTRAINT problem_prob_id_pk PRIMARY KEY (prob_id)); --Create sequence for problem ID CREATE SEQUENCE prob_id_sequence START WITH 10000 INCREMENT BY 1 NOCACHE; --Create Animal Solution Table CREATE TABLE animal_solution ( anSol_id NUMBER(5) NOT NULL, anSol_type VARCHAR2(50) NOT NULL, CONSTRAINT animal_solution_anSol_id_pk PRIMARY KEY (anSol_id)); --Create sequence for animal solution ID CREATE SEQUENCE anSol_id_sequence START WITH 11000 INCREMENT BY 1 NOCACHE; --Create Animal Problem Table CREATE TABLE animal_problem ( anProb_id NUMBER(5) NOT NULL, anProb_date DATE NOT NULL, animal_id NUMBER(5) NOT NULL, prob_id NUMBER(5) NOT NULL, anSol_id NUMBER(5) NOT NULL, CONSTRAINT animal_problem_anProb_id_pk PRIMARY KEY (anProb_id), CONSTRAINT animal_problem_ex_id_fk FOREIGN KEY (animal_id) REFERENCES animal (animal_id), CONSTRAINT animal_problem_prob_id_fk FOREIGN KEY (prob_id) REFERENCES problem (prob_id), CONSTRAINT animal_problem_anSol_id_fk FOREIGN KEY (anSol_id) REFERENCES animal_solution (anSOL_id)); --Create sequence for animal problem ID CREATE SEQUENCE anProb_id_sequence START WITH 12000 INCREMENT BY 1 NOCACHE; --Create Exhibit Solution Table CREATE TABLE exhibit_solution ( exSol_id NUMBER(5) NOT NULL, exSol_type VARCHAR2(50) NOT NULL, CONSTRAINT exhibit_solution_exSol_id_pk PRIMARY KEY (exSol_id)); --Create sequence for animal problem ID CREATE SEQUENCE exSol_id_sequence START WITH 13000 INCREMENT BY 1 NOCACHE; --Create Exhibit Problem Table CREATE TABLE exhibit_problem ( exProb_id NUMBER(5) NOT NULL, exprob_date DATE NOT NULL, prob_id NUMBER(5) NOT NULL, ex_id NUMBER (5), exSol_id NUMBER(5) NOT NULL, CONSTRAINT exhibit_problem_exProb_id_pk PRIMARY KEY (exProb_id), CONSTRAINT exhibit_problem_prob_id_fk FOREIGN KEY (prob_id) REFERENCES problem (prob_id), CONSTRAINT exhibit_problem_ex_id_fk FOREIGN KEY (ex_id) REFERENCES exhibit (ex_id), CONSTRAINT exhibit_problem_exSol_id_fk FOREIGN KEY (exSOL_id) REFERENCES exhibit_solution (exSOL_id)); --Create sequence for exhibit problem ID CREATE SEQUENCE exProb_id_sequence START WITH 13000 INCREMENT BY 1 NOCACHE; --Create Maintenance Task Table CREATE TABLE maintask ( main_id NUMBER (5) NOT NULL, taskname VARCHAR2 (50) NOT NULL, maindesc VARCHAR2 (50) NOT NULL, CONSTRAINT maintask_main_id_pk PRIMARY KEY (main_id)); /*Create sequence for Maintenance Task ID*/ CREATE SEQUENCE main_id_sequence START WITH 14000 INCREMENT BY 1 NOCACHE; --Create Employee Table CREATE TABLE employee( emp_id NUMBER(5), emp_fName VARCHAR2(25), emp_lName VARCHAR2(25), CONSTRAINT employee_emp_id_PK PRIMARY KEY (emp_id)); /*Create sequence for employee ID*/ CREATE SEQUENCE emp_id_sequence START WITH 15000 INCREMENT BY 1 NOCACHE; --Create Employee Maintaning Exhibit Table CREATE TABLE eme ( EME_id NUMBER (5) NOT NULL, emp_id NUMBER (5) NOT NULL, ex_id NUMBER (5) NOT NULL, main_id NUMBER (5) NOT NULL, day_maint VARCHAR2 (20) NOT NULL, CONSTRAINT eme_eme_pk PRIMARY KEY (EME_id), CONSTRAINT eme_employida_fk FOREIGN KEY (emp_id) REFERENCES employee (emp_id), CONSTRAINT eme_exhibitida_fk FOREIGN KEY (ex_id) REFERENCES exhibit (ex_id), CONSTRAINT eme_maintida_fk FOREIGN KEY (main_id) REFERENCES maintask (main_id)); /*Create sequence for employee maintaining exhibit ID*/ CREATE SEQUENCE eme_id_sequence START WITH 16000 INCREMENT BY 1 NOCACHE; ---------------------------------------------------------------------------------------------- /*CREATES INSERTS FOR SPECIES TABLED*/ INSERT INTO species VALUES (species_id_sequence.NEXTVAL, 'Pan Troglodytes', 'Common Chimpanzee', 12); INSERT INTO species VALUES (species_id_sequence.NEXTVAL, 'Panthera Tigris Tigris', 'Bengal Tiger', 6); INSERT INTO species VALUES (species_id_sequence.NEXTVAL, 'Panthera Leo', 'Lion', 9); INSERT INTO species VALUES (species_id_sequence.NEXTVAL, 'Ursus Maritimus', 'Polar Bear', 7); INSERT INTO species VALUES (species_id_sequence.NEXTVAL, 'Loxodonta Africana', 'African Bush Elephant', 3); INSERT INTO species VALUES (species_id_sequence.NEXTVAL, 'Tursiops truncatus', 'Bottlenosed Dolphin', 2); INSERT INTO species VALUES (species_id_sequence.NEXTVAL, 'Alligator Mississippiensis', 'American Alligator', 4); INSERT INTO species VALUES (species_id_sequence.NEXTVAL, 'Salamandra', 'Salamander', 10); INSERT INTO species VALUES (species_id_sequence.NEXTVAL, 'Lontra Canadensis', 'American River Otter', 15); INSERT INTO species VALUES (species_id_sequence.NEXTVAL, 'Macropus Giganteus ', 'Gray Kangaroo', 6); INSERT INTO species VALUES (species_id_sequence.NEXTVAL, 'Canis lupus', 'Mexican Grey Wolf', 5); INSERT INTO species VALUES (species_id_sequence.NEXTVAL, 'Addax Nasomaculatus', 'Addax', 8); -- Populates Animal table INSERT INTO animal VALUES(animal_id_sequence.NEXTVAL, 'Chimpy', TO_DATE('01/15/2001', 'MM/DD/YYYY'), 1000); INSERT INTO animal VALUES(animal_id_sequence.NEXTVAL, 'Frisky Tiger', TO_DATE('03/25/2002', 'MM/DD/YYYY'), 1001); INSERT INTO animal VALUES(animal_id_sequence.NEXTVAL, 'Lame Lion', TO_DATE('11/25/2004', 'MM/DD/YYYY'), 1002); INSERT INTO animal VALUES(animal_id_sequence.NEXTVAL, 'Polorizer', TO_DATE('03/13/2010', 'MM/DD/YYYY'), 1003); INSERT INTO animal VALUES(animal_id_sequence.NEXTVAL, 'Egor the Elephant', TO_DATE('10/02/2004', 'MM/DD/YYYY'), 1004); INSERT INTO animal VALUES(animal_id_sequence.NEXTVAL, 'Dolphy the Dolphin', TO_DATE('03/24/2005', 'MM/DD/YYYY'), 1005); INSERT INTO animal VALUES(animal_id_sequence.NEXTVAL, 'Hater Gator', TO_DATE('02/09/2012', 'MM/DD/YYYY'), 1006); INSERT INTO animal VALUES(animal_id_sequence.NEXTVAL, 'Salam', TO_DATE('12/19/2008', 'MM/DD/YYYY'), 1007); INSERT INTO animal VALUES(animal_id_sequence.NEXTVAL, 'Obo the Otter', TO_DATE('04/04/2010', 'MM/DD/YYYY'), 1008); INSERT INTO animal VALUES(animal_id_sequence.NEXTVAL, 'Roo the Kangaroo', TO_DATE('02/09/2007', 'MM/DD/YYYY'), 1009); INSERT INTO animal VALUES(animal_id_sequence.NEXTVAL, 'Wolfy the Wolf', TO_DATE('06/2/2009', 'MM/DD/YYYY'), 1010); INSERT INTO animal VALUES(animal_id_sequence.NEXTVAL, 'Arp the Addax', TO_DATE('01/01/2001', 'MM/DD/YYYY'), 1011); /*Populates supplr Table*/ INSERT INTO supplr VALUES(supplr_id_sequence.NEXTVAL, 'WallyWorld', '4972 Richison Drive','Milwaukee', 'MT', 'United States'); INSERT INTO supplr VALUES(supplr_id_sequence.NEXTVAL, 'Animal Food and Stuff', '4780 Berkley Street', 'Philadelphia', 'PA', 'United States'); INSERT INTO supplr VALUES(supplr_id_sequence.NEXTVAL, 'Random Supplier Person', '4827 Wilkinson Court', 'North Naples', 'FL','United States'); INSERT INTO supplr VALUES(supplr_id_sequence.NEXTVAL, 'Perez Inc.', '4561 Frank Avenue', 'Springfield', 'MA','United States'); INSERT INTO supplr VALUES(supplr_id_sequence.NEXTVAL, 'Joyce Rides', '523 Boring Lane', 'San Francisco', 'CA','United States'); INSERT INTO supplr VALUES(supplr_id_sequence.NEXTVAL, 'Marvin''s Gardens', '1277 Filbert Street', 'Philadelphia', 'PA','United States'); INSERT INTO supplr VALUES(supplr_id_sequence.NEXTVAL, 'Riley Office Supplies', '3062 Calvin Street','Balitmore', 'MD','United States'); INSERT INTO supplr VALUES(supplr_id_sequence.NEXTVAL, 'LoLo Animals', '262 Lavin Street','St.Pual', 'MN','United States'); INSERT INTO supplr VALUES(supplr_id_sequence.NEXTVAL, 'Nance Pet Supplier', '233 South Street','Akron', 'OH','United States'); INSERT INTO supplr VALUES(supplr_id_sequence.NEXTVAL, 'Petco', '362 Ninth Street','Eau Claire', 'WI','United States'); INSERT INTO supplr VALUES(supplr_id_sequence.NEXTVAL, 'Animart', '909 Center Street','San Antonio', 'TX','United States'); INSERT INTO supplr VALUES(supplr_id_sequence.NEXTVAL, 'Tropic Waters', '6232 Robin Road','Dallas', 'TX','United States'); /*POPULATES EXHIBIT TABLE*/ INSERT INTO exhibit VALUES (ex_id_sequence.NEXTVAL, 'Regenstein Wolf Woods', 'Forest', 'Wooded Forest'); INSERT INTO exhibit VALUES (ex_id_sequence.NEXTVAL, 'The Swamp', 'Swamp', 'low land swamp'); INSERT INTO exhibit VALUES (ex_id_sequence.NEXTVAL,'Australia House', 'Field', 'Flat Land Plains'); INSERT INTO exhibit VALUES (ex_id_sequence.NEXTVAL,'Hoofed Animals', 'Field', 'Flat Land Plains'); INSERT INTO exhibit VALUES (ex_id_sequence.NEXTVAL,'Salt Creek Wilderness', 'Wooded with Salt Water', 'Some wood area with a salt water pond'); INSERT INTO exhibit VALUES (ex_id_sequence.NEXTVAL,'Pachyderm House', 'Indoor or Field', 'Come in during the winter when its cold and in the field during the summer'); INSERT INTO exhibit VALUES (ex_id_sequence.NEXTVAL,'Seven Seas', 'Salt Water', 'Excludes fresh water fish'); INSERT INTO exhibit VALUES (ex_id_sequence.NEXTVAL,'Pinniped Point', 'Salt Water', 'Body of water for finned animals'); INSERT INTO exhibit VALUES (ex_id_sequence.NEXTVAL,'Fragile Hunters', 'Combonation', 'Plenty of places to hide for hunters'); INSERT INTO exhibit VALUES (ex_id_sequence.NEXTVAL,'African Savahanna', 'Flat plain', 'Lot of room for graze'); INSERT INTO exhibit VALUES (ex_id_sequence.NEXTVAL,'African Forest', 'Forest', 'Trees vary from normal forest'); INSERT INTO exhibit VALUES (ex_id_sequence.NEXTVAL,'Fragile Desert', 'Desert', 'Plenty of heat and sand'); /* Populate animal home table*/ INSERT INTO an_home Values(move_id_sequence.NEXTVAL, 12, TO_DATE('2005-01-29', 'YYYY/MM/DD'), TO_DATE('2008-03-19', 'YYYY/MM/DD'), 2000, 4000); INSERT INTO an_home Values(move_id_sequence.NEXTVAL, 55, TO_DATE('2002-11-04', 'YYYY/MM/DD'), TO_DATE('2003-06-09', 'YYYY/MM/DD'), 2001, 4001); INSERT INTO an_home Values(move_id_sequence.NEXTVAL, 8, TO_DATE('2005-12-12', 'YYYY/MM/DD'), TO_DATE('2007-08-02', 'YYYY/MM/DD'), 2002, 4002 ); INSERT INTO an_home Values(move_id_sequence.NEXTVAL, 72, TO_DATE('2000-06-17', 'YYYY/MM/DD'), TO_DATE('2012-12-21', 'YYYY/MM/DD'), 2003, 4003); INSERT INTO an_home Values(move_id_sequence.NEXTVAL, 99, TO_DATE('2007-05-22', 'YYYY/MM/DD'), TO_DATE('2008-10-18', 'YYYY/MM/DD'), 2004, 4004); INSERT INTO an_home Values(move_id_sequence.NEXTVAL, 28, TO_DATE('1999-09-15', 'YYYY/MM/DD'), TO_DATE('2002-02-04', 'YYYY/MM/DD'), 2005, 4005); INSERT INTO an_home Values(move_id_sequence.NEXTVAL, 5, TO_DATE('2002-12-31', 'YYYY/MM/DD'), TO_DATE('2009-03-05', 'YYYY/MM/DD'), 2005, 4005); INSERT INTO an_home Values(move_id_sequence.NEXTVAL, 9, TO_DATE('2004-10-21', 'YYYY/MM/DD'), TO_DATE('2005-03-05', 'YYYY/MM/DD'), 2006, 4006); INSERT INTO an_home Values(move_id_sequence.NEXTVAL, 34, TO_DATE('2006-12-31', 'YYYY/MM/DD'), TO_DATE('2007-03-05', 'YYYY/MM/DD'), 2007, 4007); INSERT INTO an_home Values(move_id_sequence.NEXTVAL, 35, TO_DATE('2008-01-30', 'YYYY/MM/DD'), TO_DATE('2009-04-15', 'YYYY/MM/DD'), 2008, 4008); INSERT INTO an_home Values(move_id_sequence.NEXTVAL, 19, TO_DATE('2010-02-11', 'YYYY/MM/DD'), TO_DATE('2010-03-09', 'YYYY/MM/DD'), 2009, 4009); INSERT INTO an_home Values(move_id_sequence.NEXTVAL, 2, TO_DATE('2007-12-20', 'YYYY/MM/DD'), TO_DATE('2007-12-21', 'YYYY/MM/DD'), 2010, 4010); /*Populates HABITAT table*/ INSERT INTO habitat VALUES (habitat_id_sequence.NEXTVAL, 'Aquatic', '88'); INSERT INTO habitat VALUES (habitat_id_sequence.NEXTVAL, 'Wetland', '65'); INSERT INTO habitat VALUES (habitat_id_sequence.NEXTVAL, 'Swamp', '60'); INSERT INTO habitat VALUES (habitat_id_sequence.NEXTVAL, 'Tropical', '90'); INSERT INTO habitat VALUES (habitat_id_sequence.NEXTVAL, 'Desert', '99'); INSERT INTO habitat VALUES (habitat_id_sequence.NEXTVAL, 'Pine Forest', '50'); INSERT INTO habitat VALUES (habitat_id_sequence.NEXTVAL, 'Decidiuous Forest', '55'); INSERT INTO habitat VALUES (habitat_id_sequence.NEXTVAL, 'Fresh Aquatic', '80'); INSERT INTO habitat VALUES (habitat_id_sequence.NEXTVAL, 'Rainforest', '95'); INSERT INTO habitat VALUES (habitat_id_sequence.NEXTVAL, 'Polor', '10'); INSERT INTO habitat VALUES (habitat_id_sequence.NEXTVAL, 'Seasonal', '50'); INSERT INTO habitat VALUES (habitat_id_sequence.NEXTVAL, 'Low Desert', '93'); /*Populate habitat exibit table*/ INSERT INTO habitat_exhibit VALUES(6000, 4000); INSERT INTO habitat_exhibit VALUES(6001, 4001); INSERT INTO habitat_exhibit VALUES(6002, 4002); INSERT INTO habitat_exhibit VALUES(6003, 4003); INSERT INTO habitat_exhibit VALUES(6004, 4004); INSERT INTO habitat_exhibit VALUES(6005, 4005); INSERT INTO habitat_exhibit VALUES(6006, 4006); INSERT INTO habitat_exhibit VALUES(6007, 4007); INSERT INTO habitat_exhibit VALUES(6008, 4008); INSERT INTO habitat_exhibit VALUES(6009, 4009); INSERT INTO habitat_exhibit VALUES(6010, 4010); INSERT INTO habitat_exhibit VALUES(6011, 4011); /*Populates Species Habitat Table*/ INSERT INTO species_habitat VALUES (1000, 6000); INSERT INTO species_habitat VALUES (1001, 6001); INSERT INTO species_habitat VALUES (1002, 6002); INSERT INTO species_habitat VALUES (1003, 6003); INSERT INTO species_habitat VALUES (1004, 6004); INSERT INTO species_habitat VALUES (1005, 6005); INSERT INTO species_habitat VALUES (1006, 6006); INSERT INTO species_habitat VALUES (1007, 6007); INSERT INTO species_habitat VALUES (1008, 6008); INSERT INTO species_habitat VALUES (1009, 6009); INSERT INTO species_habitat VALUES (1010, 6010); INSERT INTO species_habitat VALUES (1011, 6011); /*Populate species supplier table*/ INSERT INTO species_supplier VALUES(3000, 1000); INSERT INTO species_supplier VALUES(3001, 1001); INSERT INTO species_supplier VALUES(3002, 1002); INSERT INTO species_supplier VALUES(3003, 1003); INSERT INTO species_supplier VALUES(3004, 1004); INSERT INTO species_supplier VALUES(3005, 1005); INSERT INTO species_supplier VALUES(3006, 1006); INSERT INTO species_supplier VALUES(3007, 1007); INSERT INTO species_supplier VALUES(3008, 1008); INSERT INTO species_supplier VALUES(3009, 1009); INSERT INTO species_supplier VALUES(3010, 1010); INSERT INTO species_supplier VALUES(3011, 1011); /*Populates Shipment Table*/ INSERT INTO shipment VALUES(shipment_id_sequence.NEXTVAL, TO_DATE('2010-04-29', 'YYYY/MM/DD'), 2300, 3000, 2000); INSERT INTO shipment VALUES(shipment_id_sequence.NEXTVAL, TO_DATE('2010-04-14', 'YYYY/MM/DD'), 2715, 3001, 2001); INSERT INTO shipment VALUES(shipment_id_sequence.NEXTVAL, TO_DATE('2010-05-13', 'YYYY/MM/DD'), 1550, 3002, 2002); INSERT INTO shipment VALUES(shipment_id_sequence.NEXTVAL, TO_DATE('2010-05-28', 'YYYY/MM/DD'), 2120, 3003, 2003); INSERT INTO shipment VALUES(shipment_id_sequence.NEXTVAL, TO_DATE('2010-06-12', 'YYYY/MM/DD'), 1350, 3004, 2004); INSERT INTO shipment VALUES(shipment_id_sequence.NEXTVAL, TO_DATE('2010-06-27', 'YYYY/MM/DD'), 9670, 3005, 2005); INSERT INTO shipment VALUES(shipment_id_sequence.NEXTVAL, TO_DATE('2010-09-15', 'YYYY/MM/DD'), 1680, 3006, 2006); INSERT INTO shipment VALUES(shipment_id_sequence.NEXTVAL, TO_DATE('2010-10-16', 'YYYY/MM/DD'), 1780, 3007, 2007); INSERT INTO shipment VALUES(shipment_id_sequence.NEXTVAL, TO_DATE('2010-11-17', 'YYYY/MM/DD'), 1460, 3008, 2008); INSERT INTO shipment VALUES(shipment_id_sequence.NEXTVAL, TO_DATE('2010-12-18', 'YYYY/MM/DD'), 1830, 3009, 2009); INSERT INTO shipment VALUES(shipment_id_sequence.NEXTVAL, TO_DATE('2010-01-20', 'YYYY/MM/DD'), 1380, 3010, 2010); INSERT INTO shipment VALUES(shipment_id_sequence.NEXTVAL, TO_DATE('2010-02-25', 'YYYY/MM/DD'), 1970, 3011, 2011); /*Populates Food Inventory Table*/ INSERT INTO food_inv VALUES (food_id_sequence.NEXTVAL, 'oats', '8000', '3000'); INSERT INTO food_inv VALUES (food_id_sequence.NEXTVAL, 'peanuts', '6000', '3001'); INSERT INTO food_inv VALUES (food_id_sequence.NEXTVAL, 'bananas', '6000', '3002'); INSERT INTO food_inv VALUES (food_id_sequence.NEXTVAL, 'carrots', '3000', '3002'); INSERT INTO food_inv VALUES (food_id_sequence.NEXTVAL, 'herring', '750', '3003'); INSERT INTO food_inv VALUES (food_id_sequence.NEXTVAL, 'squid', '750', '3003'); INSERT INTO food_inv VALUES (food_id_sequence.NEXTVAL, 'hay', '4000', '3000'); INSERT INTO food_inv VALUES (food_id_sequence.NEXTVAL, 'apples', '7000', '3004'); INSERT INTO food_inv VALUES (food_id_sequence.NEXTVAL, 'meat', '5000', '3005'); INSERT INTO food_inv VALUES (food_id_sequence.NEXTVAL, 'eggs', '1500', '3006'); INSERT INTO food_inv VALUES (food_id_sequence.NEXTVAL, 'lettuce', '2500', '3007'); INSERT INTO food_inv VALUES (food_id_sequence.NEXTVAL, 'grapes', '10000', '3008'); /*Populates Feeding Schedule Table*/ INSERT INTO feeding_sched VALUES (feed_id_sequence.NEXTVAL, '3' , 'Warehouse 2', '3 pounds', '8000', '2000'); INSERT INTO feeding_sched VALUES (feed_id_sequence.NEXTVAL, '5' , 'Warehouse 5', '10', '8001', '2001'); INSERT INTO feeding_sched VALUES (feed_id_sequence.NEXTVAL, '1' , 'Warehouse 2', '15', '8002', '2002'); INSERT INTO feeding_sched VALUES (feed_id_sequence.NEXTVAL, '4' , 'Warehouse 3', '2 pounds', '8003', '2003'); INSERT INTO feeding_sched VALUES (feed_id_sequence.NEXTVAL, '1' , 'Warehouse 1', '25', '8001', '2004'); INSERT INTO feeding_sched VALUES (feed_id_sequence.NEXTVAL, '2' , 'Warehouse 2', '4 pounds', '8005', '2005'); INSERT INTO feeding_sched VALUES (feed_id_sequence.NEXTVAL, '6' , 'Warehouse 2', '1 bale', '8006', '2005'); INSERT INTO feeding_sched VALUES (feed_id_sequence.NEXTVAL, '6' , 'Warehouse 2', '5', '8007', '2005'); INSERT INTO feeding_sched VALUES (feed_id_sequence.NEXTVAL, '3' , 'Warehouse 2', '4 pounds', '8008', '2005'); INSERT INTO feeding_sched VALUES (feed_id_sequence.NEXTVAL, '4' , 'Warehouse 2', '2 dozen', '8009', '2005'); INSERT INTO feeding_sched VALUES (feed_id_sequence.NEXTVAL, '5' , 'Warehouse 2', '10 heads', '8010', '2005'); INSERT INTO feeding_sched VALUES (feed_id_sequence.NEXTVAL, '2' , 'Warehouse 2', '3 bunches', '8011', '2005'); /*Populates Problem Table*/ INSERT INTO problem VALUES (prob_id_sequence.NEXTVAL, 'Medical'); INSERT INTO problem VALUES (prob_id_sequence.NEXTVAL, 'Maintenance'); INSERT INTO problem VALUES (prob_id_sequence.NEXTVAL, 'Animal Behavior'); INSERT INTO problem VALUES (prob_id_sequence.NEXTVAL, 'Medical'); INSERT INTO problem VALUES (prob_id_sequence.NEXTVAL, 'Medical'); INSERT INTO problem VALUES (prob_id_sequence.NEXTVAL, 'Maintenance'); INSERT INTO problem VALUES (prob_id_sequence.NEXTVAL, 'Animal Behavior'); INSERT INTO problem VALUES (prob_id_sequence.NEXTVAL, 'Medical'); INSERT INTO problem VALUES (prob_id_sequence.NEXTVAL, 'Maintenance'); INSERT INTO problem VALUES (prob_id_sequence.NEXTVAL, 'Animal Behavior'); INSERT INTO problem VALUES (prob_id_sequence.NEXTVAL, 'Maintenance'); INSERT INTO problem VALUES (prob_id_sequence.NEXTVAL, 'Animal Behavior'); /*Populates Animal Solution Table*/ INSERT INTO animal_solution VALUES(anSol_id_sequence.NEXTVAL, 'One week Antibiotics'); INSERT INTO animal_solution VALUES(anSol_id_sequence.NEXTVAL, 'Isolation'); INSERT INTO animal_solution VALUES(anSol_id_sequence.NEXTVAL, '1 Month Antibiotics'); INSERT INTO animal_solution VALUES(anSol_id_sequence.NEXTVAL, 'Isolation'); INSERT INTO animal_solution VALUES(anSol_id_sequence.NEXTVAL, 'One week Antibiotics'); INSERT INTO animal_solution VALUES(anSol_id_sequence.NEXTVAL, 'Change Feed'); INSERT INTO animal_solution VALUES(anSol_id_sequence.NEXTVAL, 'Remove Tooth'); INSERT INTO animal_solution VALUES(anSol_id_sequence.NEXTVAL, 'Eye Patch for 2 weeks'); INSERT INTO animal_solution VALUES(anSol_id_sequence.NEXTVAL, 'Trim fur'); INSERT INTO animal_solution VALUES(anSol_id_sequence.NEXTVAL, 'Remove thorn'); INSERT INTO animal_solution VALUES(anSol_id_sequence.NEXTVAL, 'Seperate Mother from others'); INSERT INTO animal_solution VALUES(anSol_id_sequence.NEXTVAL, 'Change diet'); /*Populates Animal Problem Table*/ INSERT INTO animal_problem VALUES(anProb_id_sequence.NEXTVAL, TO_DATE ('12/05/2010', 'MM/DD/YYYY'), 2000, 10000, 11000); INSERT INTO animal_problem VALUES(anProb_id_sequence.NEXTVAL, TO_DATE ('10/10/2009', 'MM/DD/YYYY'), 2001, 10001, 11001); INSERT INTO animal_problem VALUES(anProb_id_sequence.NEXTVAL, TO_DATE ('12/05/2010', 'MM/DD/YYYY'), 2002, 10002, 11002); INSERT INTO animal_problem VALUES(anProb_id_sequence.NEXTVAL, TO_DATE ('6/15/2008', 'MM/DD/YYYY'), 2003, 10003, 11003); INSERT INTO animal_problem VALUES(anProb_id_sequence.NEXTVAL, TO_DATE ('8/01/2009', 'MM/DD/YYYY'), 2004, 10004, 11004); INSERT INTO animal_problem VALUES(anProb_id_sequence.NEXTVAL, TO_DATE ('1/07/2008', 'MM/DD/YYYY'), 2005, 10005, 11005); INSERT INTO animal_problem VALUES(anProb_id_sequence.NEXTVAL, TO_DATE ('3/12/2009', 'MM/DD/YYYY'), 2006, 10006, 11006); INSERT INTO animal_problem VALUES(anProb_id_sequence.NEXTVAL, TO_DATE ('4/16/2009', 'MM/DD/YYYY'), 2007, 10007, 11007); INSERT INTO animal_problem VALUES(anProb_id_sequence.NEXTVAL, TO_DATE ('1/24/2009', 'MM/DD/YYYY'), 2008, 10008, 11008); INSERT INTO animal_problem VALUES(anProb_id_sequence.NEXTVAL, TO_DATE ('3/31/2009', 'MM/DD/YYYY'), 2009, 10009, 11009); INSERT INTO animal_problem VALUES(anProb_id_sequence.NEXTVAL, TO_DATE ('5/13/2009', 'MM/DD/YYYY'), 2010, 10010, 11010); INSERT INTO animal_problem VALUES(anProb_id_sequence.NEXTVAL, TO_DATE ('6/08/2009', 'MM/DD/YYYY'), 2011, 10011, 11011); /*Populates Exhibit Solution Table*/ INSERT INTO exhibit_solution VALUES(exSol_id_sequence.NEXTVAL, 'Fill Holes'); INSERT INTO exhibit_solution VALUES(exSol_id_sequence.NEXTVAL, 'Patch Wall'); INSERT INTO exhibit_solution VALUES(exSol_id_sequence.NEXTVAL, 'Fixed Fence'); INSERT INTO exhibit_solution VALUES(exSol_id_sequence.NEXTVAL, 'Patch Wall'); INSERT INTO exhibit_solution VALUES(exSol_id_sequence.NEXTVAL, 'Repair Lights'); INSERT INTO exhibit_solution VALUES(exSol_id_sequence.NEXTVAL, 'Fix water source'); INSERT INTO exhibit_solution VALUES(exSol_id_sequence.NEXTVAL, 'Install higher fence'); INSERT INTO exhibit_solution VALUES(exSol_id_sequence.NEXTVAL, 'Fix railings'); INSERT INTO exhibit_solution VALUES(exSol_id_sequence.NEXTVAL, 'Hire landscape company to fix habitat floor'); INSERT INTO exhibit_solution VALUES(exSol_id_sequence.NEXTVAL, 'Repair wall'); INSERT INTO exhibit_solution VALUES(exSol_id_sequence.NEXTVAL, 'Patch Hole'); INSERT INTO exhibit_solution VALUES(exSol_id_sequence.NEXTVAL, 'Add lighting'); /*Populates Exhibit Problem Table*/ INSERT INTO exhibit_problem VALUES(exProb_id_sequence.NEXTVAL, TO_DATE ('1/05/2010', 'MM/DD/YYYY'), 10000, 4000, 13000); INSERT INTO exhibit_problem VALUES(exProb_id_sequence.NEXTVAL, TO_DATE ('10/01/2009', 'MM/DD/YYYY'), 10001, 4001, 13001); INSERT INTO exhibit_problem VALUES(exProb_id_sequence.NEXTVAL, TO_DATE ('2/05/2010', 'MM/DD/YYYY'), 10002, 4002, 13002); INSERT INTO exhibit_problem VALUES(exProb_id_sequence.NEXTVAL, TO_DATE ('6/01/2008', 'MM/DD/YYYY'),10003, 4003, 13003); INSERT INTO exhibit_problem VALUES(exProb_id_sequence.NEXTVAL, TO_DATE ('8/01/2009', 'MM/DD/YYYY'), 10004, 4004, 13004); INSERT INTO exhibit_problem VALUES(exProb_id_sequence.NEXTVAL, TO_DATE ('10/07/2008', 'MM/DD/YYYY'), 10005, 4005, 13005); INSERT INTO exhibit_problem VALUES(exProb_id_sequence.NEXTVAL, TO_DATE ('1/22/2009', 'MM/DD/YYYY'), 10006, 4006, 13011); INSERT INTO exhibit_problem VALUES(exProb_id_sequence.NEXTVAL, TO_DATE ('2/05/2009', 'MM/DD/YYYY'), 10007, 4007, 13010); INSERT INTO exhibit_problem VALUES(exProb_id_sequence.NEXTVAL, TO_DATE ('5/16/2009', 'MM/DD/YYYY'), 10008, 4008, 13009); INSERT INTO exhibit_problem VALUES(exProb_id_sequence.NEXTVAL, TO_DATE ('7/26/2009', 'MM/DD/YYYY'), 10009, 4009, 13008); INSERT INTO exhibit_problem VALUES(exProb_id_sequence.NEXTVAL, TO_DATE ('11/15/2009', 'MM/DD/YYYY'), 10010, 4010, 13007); INSERT INTO exhibit_problem VALUES(exProb_id_sequence.NEXTVAL, TO_DATE ('12/01/2009', 'MM/DD/YYYY'), 10011, 4011, 13006); /*Populates Maintainence Task Table*/ INSERT INTO maintask VALUES (main_id_sequence.NEXTVAL, 'Waste Pickup', 'Discard animal waste'); INSERT INTO maintask VALUES (main_id_sequence.NEXTVAL, 'Water Fill', 'Fill animal containters'); INSERT INTO maintask VALUES (main_id_sequence.NEXTVAL, 'Welfare Check', 'Check all animals'); INSERT INTO maintask VALUES (main_id_sequence.NEXTVAL, 'Lighting', 'Replace Bulbs'); INSERT INTO maintask VALUES (main_id_sequence.NEXTVAL, 'Disinfect', 'Wipe all exhibits'); INSERT INTO maintask VALUES (main_id_sequence.NEXTVAL, 'Clean', 'Wash Out animal feeding areas'); INSERT INTO maintask VALUES (main_id_sequence.NEXTVAL, 'Test', 'Test water levels'); INSERT INTO maintask VALUES (main_id_sequence.NEXTVAL, 'Clean Filters', 'Remove obstructions in water features'); INSERT INTO maintask VALUES (main_id_sequence.NEXTVAL, 'Rake', 'Rake leaves from areas surrounding the exhibit'); INSERT INTO maintask VALUES (main_id_sequence.NEXTVAL, 'Shovel', 'Shovel path around exhibit'); INSERT INTO maintask VALUES (main_id_sequence.NEXTVAL, 'Change Water', 'Change animal drinking water'); INSERT INTO maintask VALUES (main_id_sequence.NEXTVAL, 'Fill Hole', 'Repair holes in walking paths in exhibit'); /*Populates Employee table*/ INSERT INTO employee VALUES(emp_id_sequence.NEXTVAL, 'Norma', 'Deville'); INSERT INTO employee VALUES(emp_id_sequence.NEXTVAL, 'Gerardo', 'Chamorro'); INSERT INTO employee VALUES(emp_id_sequence.NEXTVAL, 'Margaret', 'Myers'); INSERT INTO employee VALUES(emp_id_sequence.NEXTVAL, 'Robert', 'Holman'); INSERT INTO employee VALUES(emp_id_sequence.NEXTVAL, 'Marisa', 'Ashley'); INSERT INTO employee VALUES(emp_id_sequence.NEXTVAL, 'Nathan', 'Ferguson'); INSERT INTO employee VALUES(emp_id_sequence.NEXTVAL, 'Fredrick', 'Khan'); INSERT INTO employee VALUES(emp_id_sequence.NEXTVAL, 'Betty', 'Radcliffe'); INSERT INTO employee VALUES(emp_id_sequence.NEXTVAL, 'Patrick', 'Santamaria'); INSERT INTO employee VALUES(emp_id_sequence.NEXTVAL, 'Daniel', 'Blythe'); INSERT INTO employee VALUES(emp_id_sequence.NEXTVAL, 'Kai', 'Eveleigh'); INSERT INTO employee VALUES(emp_id_sequence.NEXTVAL, 'Toria', 'Papworth'); /*Populates Employee Maintaining Exhibit Table*/ INSERT INTO eme VALUES (EME_id_sequence.NEXTVAL, 15000, 4000, 14000, TO_DATE ('10/10/2010', 'MM/DD/YYYY')); INSERT INTO eme VALUES (EME_id_sequence.NEXTVAL, 15001, 4001, 14001 , TO_DATE ('1/05/2009', 'MM/DD/YYYY')); INSERT INTO eme VALUES (EME_id_sequence.NEXTVAL, 15002, 4002, 14002, TO_DATE ('12/25/2009', 'MM/DD/YYYY')); INSERT INTO eme VALUES (EME_id_sequence.NEXTVAL, 15003, 4003, 14003, TO_DATE ('04/09/2010', 'MM/DD/YYYY')); INSERT INTO eme VALUES (EME_id_sequence.NEXTVAL, 15004, 4004, 14004, TO_DATE ('05/26/2010', 'MM/DD/YYYY')); INSERT INTO eme VALUES (EME_id_sequence.NEXTVAL, 15005, 4005, 14005, TO_DATE ('10/05/2010', 'MM/DD/YYYY')); INSERT INTO eme VALUES (EME_id_sequence.NEXTVAL, 15006, 4006, 14006, TO_DATE ('9/25/2010', 'MM/DD/YYYY')); INSERT INTO eme VALUES (EME_id_sequence.NEXTVAL, 15007, 4007, 14007, TO_DATE ('3/12/2010', 'MM/DD/YYYY')); INSERT INTO eme VALUES (EME_id_sequence.NEXTVAL, 15008, 4008, 14008, TO_DATE ('12/14/2010', 'MM/DD/YYYY')); INSERT INTO eme VALUES (EME_id_sequence.NEXTVAL, 15009, 4009, 14009, TO_DATE ('1/24/2010', 'MM/DD/YYYY')); INSERT INTO eme VALUES (EME_id_sequence.NEXTVAL, 15010, 4010, 14010, TO_DATE ('2/13/2010', 'MM/DD/YYYY')); INSERT INTO eme VALUES (EME_id_sequence.NEXTVAL, 15011, 4011, 14011, TO_DATE ('4/19/2010', 'MM/DD/YYYY')); COMMIT; SELECT * FROM species; SELECT * FROM animal; SELECT * FROM exhibit; SELECT * FROM an_home; SELECT * FROM habitat; SELECT * FROM habitat_exhibit; SELECT * FROM species_habitat; SELECT * FROM supplr; SELECT * FROM species_supplier; SELECT * FROM shipment; SELECT * FROM food_inv; SELECT * FROM feeding_sched; SELECT * FROM problem; SELECT * FROM animal_solution; SELECT * FROM animal_problem; SELECT * FROM exhibit_solution; SELECT * FROM maintask; SELECT * FROM employee; SELECT * FROM eme; SELECT * FROM exhibit_problem; -- THIS IS WHERE THE PERSON CREATES AND INSERTS START --To delete the tables (and associated constraint objects). DROP TABLE person CASCADE CONSTRAINTS; DROP TABLE donate CASCADE CONSTRAINTS; DROP TABLE contact CASCADE CONSTRAINTS; DROP TABLE wrksch CASCADE CONSTRAINTS; DROP TABLE avail CASCADE CONSTRAINTS; DROP TABLE degree CASCADE CONSTRAINTS; DROP TABLE empler CASCADE CONSTRAINTS; DROP TABLE jobs CASCADE CONSTRAINTS; DROP TABLE skills CASCADE CONSTRAINTS; DROP TABLE CRIME CASCADE CONSTRAINTS; DROP TABLE jobskills CASCADE CONSTRAINTS; DROP TABLE skillposs CASCADE CONSTRAINTS; DROP TABLE employed CASCADE CONSTRAINTS; DROP TABLE crimecomm CASCADE CONSTRAINTS; DROP TABLE degreeobt CASCADE CONSTRAINTS; DROP TABLE zipcode Cascade CONSTRAINTS; DROP TABLE vol_info Cascade CONSTRAINTS; DROP TABLE emer CASCADE CONSTRAINTS; DROP TABLE joblocate CASCADE CONSTRAINTS; DROP TABLE program CASCADE CONSTRAINTS; DROP TABLE prgrattd CASCADE CONSTRAINTS; DROP SEQUENCE availid_sequence; DROP SEQUENCE donate_id_sequence; DROP SEQUENCE contact_id_sequence; --------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------- --Building the single-entity table "Zipcode" database. CREATE TABLE zipcode ( zip_code VARCHAR2 (10) NOT NULL, city VARCHAR2 (50) NOT NULL, state CHAR(2) DEFAULT 'IL' NOT NULL, CONSTRAINT zip_code_pk PRIMARY KEY (zip_code)); --Populating the zipcode table. INSERT INTO Zipcode VALUES ('54734','Downing','WI'); INSERT INTO Zipcode VALUES ('24453','Chicago','IL'); INSERT INTO Zipcode VALUES ('60513','Brookfield','IL'); INSERT INTO Zipcode VALUES ('77355','Minneapolis','MN'); INSERT INTO Zipcode VALUES ('55098','Platt','WI'); INSERT INTO Zipcode VALUES ('25434','Phoenix','AZ'); INSERT INTO Zipcode VALUES ('54701','Eau Claire','WI'); INSERT INTO Zipcode VALUES ('23513','Philadelphia','PA'); INSERT INTO Zipcode VALUES ('99355','San Fransicso','CA'); INSERT INTO Zipcode VALUES ('43098','Seattle','WA'); INSERT INTO Zipcode VALUES ('11434','Dallas','TX'); INSERT INTO Zipcode VALUES ('28701','Springfield','IL'); COMMIT; ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --Building the single-entity table "person" database. CREATE TABLE person ( persid NUMBER (10) NOT NULL, persfrst VARCHAR2 (50) NOT NULL, perslast VARCHAR2 (50) NOT NULL, persadrs VARCHAR2 (50) NOT NULL, persdob DATE NOT NULL, persemal VARCHAR2 (50) NOT NULL, persmemb CHAR (1) NOT NULL, zip_code VARCHAR2 (10) NOT NULL, CONSTRAINT person_persid_pk PRIMARY KEY (persid), CONSTRAINT person_persmemb_cc CHECK (persmemb IN ('Y', 'N')), CONSTRAINT fk_person_zipcode FOREIGN KEY (zip_code) REFERENCES zipcode (zip_code)); --Populating the person table. INSERT INTO person VALUES ('1435767','George', 'Foreman', '224 7th Str',TO_DATE('01/24/1974','MM/DD/YYYY'), 'formagt@uewc.edu', 'Y','54734'); INSERT INTO person VALUES ('1435777','Thomas', 'Foreman', '244 1st Str',TO_DATE('01/14/1979','MM/DD/YYYY'), 'faultyhn@uwrf.edu', 'Y','54701'); INSERT INTO person VALUES ('1435793','Henry', 'Smith', '24 Main Str',TO_DATE('02/20/1964','MM/DD/YYYY'), 'morgmagt@uewc.edu', 'N','24453'); INSERT INTO person VALUES ('1433457','Rachel', 'Beets', '371 Oak Ave Apt 3',TO_DATE('09/04/1974','MM/DD/YYYY'), 'geoliagt@uewc.com', 'N','77355'); INSERT INTO person VALUES ('1435722','John', 'Alberts', '872 Pennsylvania Ave',TO_DATE('11/04/1988','MM/DD/YYYY'), 'tomamagt@uwms.edu', 'Y','55098'); INSERT INTO person VALUES ('1435799','Amy', 'O''Reilly', '6667 London Str',TO_DATE('01/30/1999','MM/DD/YYYY'), 'forme@ccsc.edu', 'N','23513'); INSERT INTO person VALUES ('1445767','Norma', 'Deville', '2245 Apple Str',TO_DATE('12/24/1984','MM/DD/YYYY'), 'devilt@uewc.edu', 'Y','99355'); INSERT INTO person VALUES ('1445777','Gerardo', 'Chamorro', '1244 Pear Str',TO_DATE('01/14/1990','MM/DD/YYYY'), 'choma@uwrf.edu', 'Y','43098'); INSERT INTO person VALUES ('1445793','Margaret', 'Myers', '24 Orange Str',TO_DATE('12/20/1972','MM/DD/YYYY'), 'myersat@gmail.edu', 'N','11434'); INSERT INTO person VALUES ('1443457','Robert', 'Holman', '125 Oak Ave Apt 3',TO_DATE('09/23/1984','MM/DD/YYYY'), 'holmagt@uill.com', 'N','28701'); INSERT INTO person VALUES ('1445722','Marisa', 'Ferguson', '332 Texas Ave',TO_DATE('11/04/1998','MM/DD/YYYY'), 'tomerg@uwms.com', 'Y','55098'); INSERT INTO person VALUES ('1445799','Mildred', 'Salas', '7787 Fruit Str',TO_DATE('01/12/1989','MM/DD/YYYY'), 'salsa@ccsc.edu', 'N','25434'); COMMIT; --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --Building the single-entity table "Contact INFO" database. CREATE TABLE contact ( contact_id NUMBER (10) NOT NULL, contact_num NUMBER (11) NOT NULL, contact_type VARCHAR2 (50) NOT NULL, best_contact CHAR (2) NOT NULL, persid NUMBER(10) NOT NULL, CONSTRAINT contact_contactid_pk PRIMARY KEY (contact_id), CONSTRAINT contact_bestcontact_cc CHECK (best_contact IN ('Y','N')), CONSTRAINT fk_contact_persid FOREIGN KEY (persid) REFERENCES person (persid)); --Create sequence for contact ID CREATE SEQUENCE contact_id_sequence START WITH 5000 INCREMENT BY 1 NOCACHE; --Populating the Contact INFO table. INSERT INTO contact VALUES (contact_id_sequence.NEXTVAL,'8890985564', 'Primary', 'Y','1435767'); INSERT INTO contact VALUES (contact_id_sequence.NEXTVAL,'1234567890', 'Secondary', 'N','1435767'); INSERT INTO contact VALUES (contact_id_sequence.NEXTVAL,'4543231234', 'Primary', 'Y','1435799'); INSERT INTO contact VALUES (contact_id_sequence.NEXTVAL,'8789890987', 'Primary', 'Y','1433457'); INSERT INTO contact VALUES (contact_id_sequence.NEXTVAL,'3456789876', 'Primary', 'Y','1435722'); INSERT INTO contact VALUES (contact_id_sequence.NEXTVAL,'3456785454', 'Secondary', 'N','1435722'); INSERT INTO contact VALUES (contact_id_sequence.NEXTVAL,'7150985564', 'Primary', 'Y','1445777'); INSERT INTO contact VALUES (contact_id_sequence.NEXTVAL,'6234567890', 'Secondary', 'N','1445777'); INSERT INTO contact VALUES (contact_id_sequence.NEXTVAL,'4567231234', 'Primary', 'Y','1445767'); INSERT INTO contact VALUES (contact_id_sequence.NEXTVAL,'8789746987', 'Primary', 'Y','1443457'); INSERT INTO contact VALUES (contact_id_sequence.NEXTVAL,'6126789876', 'Primary', 'Y','1445799'); INSERT INTO contact VALUES (contact_id_sequence.NEXTVAL,'3456785454', 'Secondary', 'N','1445799'); COMMIT; ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --Building the single-entity table "AVAILABILITY" database. CREATE TABLE avail( availid NUMBER (10) NOT NULL, availstrt DATE NOT NULL, availend DATE NOT NULL, availday VARCHAR2(3) NOT NULL, persid NUMBER (10) NOT NULL, CONSTRAINT avail_availid_pk PRIMARY KEY (availid), CONSTRAINT fk_person_persidc FOREIGN KEY (persid) REFERENCES person (persid), CONSTRAINT avail_availday_cc CHECK (availday IN ('MON', 'TUE','WED', 'THU','FRI', 'SAT','SUN'))); --The following block of code will add the id for the availability table sequentially. CREATE SEQUENCE availid_sequence START WITH 1000 INCREMENT BY 10 NOCACHE; --Populating the AVAILABILITY table. INSERT INTO avail VALUES (availid_sequence.NEXTVAL, TO_DATE('09:00', 'HH24:MI'), TO_DATE('22:30', 'HH24:MI'), 'TUE','1435767'); INSERT INTO avail VALUES (availid_sequence.NEXTVAL, TO_DATE('10:00', 'HH24:MI'), TO_DATE('20:00', 'HH24:MI'), 'TUE','1435777'); INSERT INTO avail VALUES (availid_sequence.NEXTVAL, TO_DATE('11:00', 'HH24:MI'), TO_DATE('17:30', 'HH24:MI'), 'WED','1435793'); INSERT INTO avail VALUES (availid_sequence.NEXTVAL, TO_DATE('12:00', 'HH24:MI'), TO_DATE('18:30', 'HH24:MI'), 'THU','1433457'); INSERT INTO avail VALUES (availid_sequence.NEXTVAL, TO_DATE('13:00', 'HH24:MI'), TO_DATE('19:30', 'HH24:MI'), 'FRI','1435767'); INSERT INTO avail VALUES (availid_sequence.NEXTVAL, TO_DATE('14:00', 'HH24:MI'), TO_DATE('20:30', 'HH24:MI'), 'TUE','1435799'); INSERT INTO avail VALUES (availid_sequence.NEXTVAL, TO_DATE('09:00', 'HH24:MI'), TO_DATE('22:30', 'HH24:MI'), 'MON','1445767'); INSERT INTO avail VALUES (availid_sequence.NEXTVAL, TO_DATE('10:00', 'HH24:MI'), TO_DATE('20:00', 'HH24:MI'), 'THU','1445777'); INSERT INTO avail VALUES (availid_sequence.NEXTVAL, TO_DATE('11:00', 'HH24:MI'), TO_DATE('17:30', 'HH24:MI'), 'SAT','1435793'); INSERT INTO avail VALUES (availid_sequence.NEXTVAL, TO_DATE('12:00', 'HH24:MI'), TO_DATE('18:30', 'HH24:MI'), 'THU','1443457'); INSERT INTO avail VALUES (availid_sequence.NEXTVAL, TO_DATE('13:00', 'HH24:MI'), TO_DATE('19:30', 'HH24:MI'), 'FRI','1445767'); INSERT INTO avail VALUES (availid_sequence.NEXTVAL, TO_DATE('14:00', 'HH24:MI'), TO_DATE('20:30', 'HH24:MI'), 'WED','1445799'); COMMIT; ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --Building the single-entity table "DONATION" database. CREATE TABLE donate ( donate_id NUMBER (10) NOT NULL, amount NUMBER (6) NOT NULL, donate_date DATE NOT NULL, persid NUMBER (10) NOT NULL, CONSTRAINT donate_donateid_pk PRIMARY KEY (donate_id), CONSTRAINT fk_person_persid FOREIGN KEY (persid) REFERENCES person (persid)); --The following block of code will add the id for the donation table sequentially. CREATE SEQUENCE donate_id_sequence START WITH 2000 INCREMENT BY 10 NOCACHE; --Populating the donation table. INSERT INTO donate VALUES (donate_id_sequence.NEXTVAL,'23000', TO_DATE('01/24/2010','MM/DD/YYYY'),'1435767'); INSERT INTO donate VALUES (donate_id_sequence.NEXTVAL,'14000', TO_DATE('01/24/2008','MM/DD/YYYY'),'1435777'); INSERT INTO donate VALUES (donate_id_sequence.NEXTVAL,'5000', TO_DATE('10/24/2000','MM/DD/YYYY'),'1435793'); INSERT INTO donate VALUES (donate_id_sequence.NEXTVAL,'3400', TO_DATE('09/04/2010','MM/DD/YYYY'),'1433457'); INSERT INTO donate VALUES (donate_id_sequence.NEXTVAL,'500', TO_DATE('07/11/2009','MM/DD/YYYY'),'1435722'); INSERT INTO donate VALUES (donate_id_sequence.NEXTVAL,'40000', TO_DATE('03/14/2010','MM/DD/YYYY'),'1435799'); INSERT INTO donate VALUES (donate_id_sequence.NEXTVAL,'230', TO_DATE('01/24/2006','MM/DD/YYYY'),'1445767'); INSERT INTO donate VALUES (donate_id_sequence.NEXTVAL,'1400', TO_DATE('01/24/2005','MM/DD/YYYY'),'1445777'); INSERT INTO donate VALUES (donate_id_sequence.NEXTVAL,'500', TO_DATE('10/24/2004','MM/DD/YYYY'),'1435793'); INSERT INTO donate VALUES (donate_id_sequence.NEXTVAL,'34500', TO_DATE('09/04/2000','MM/DD/YYYY'),'1443457'); INSERT INTO donate VALUES (donate_id_sequence.NEXTVAL,'5000', TO_DATE('07/11/2002','MM/DD/YYYY'),'1445722'); INSERT INTO donate VALUES (donate_id_sequence.NEXTVAL,'4000', TO_DATE('03/14/2001','MM/DD/YYYY'),'1445799'); COMMIT; ------------------------------------------------------------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --Building the single-entity table "Degree" database. CREATE TABLE degree( degreeid NUMBER (5) NOT NULL, degreetitle VARCHAR2(50) NOT NULL, degreearea VARCHAR2 (50) NOT NULL, CONSTRAINT degree_degreeid_pk PRIMARY KEY (degreeid)); --Populating the degree table. INSERT INTO degree VALUES ('3000','BBA','Information Systems'); INSERT INTO degree VALUES ('3010','MBA','Ecology'); INSERT INTO degree VALUES ('3020','BSS','Biology'); INSERT INTO degree VALUES ('3030','BS','Computer Science'); INSERT INTO degree VALUES ('3040','MSA','Scientology'); INSERT INTO degree VALUES ('3050','BS','Geology'); INSERT INTO degree VALUES ('3060','MBA','Information Systems'); INSERT INTO degree VALUES ('3070','MBA','Accounting'); INSERT INTO degree VALUES ('3080','BSS','Kinesiology'); INSERT INTO degree VALUES ('3090','BS','Chemistry'); INSERT INTO degree VALUES ('4000','MSA','Chemistry'); INSERT INTO degree VALUES ('4010','BS','Theology'); COMMIT; ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --Building the single-entity table "Employer" database. CREATE TABLE empler( emplerid NUMBER (10) NOT NULL, emplername VARCHAR2(50) NOT NULL, empleraddr VARCHAR2 (50) NOT NULL, zip_code VARCHAR2 (10) NOT NULL, CONSTRAINT empler_emplerid_pk PRIMARY KEY (emplerid), CONSTRAINT fk_empler_zipcode FOREIGN KEY (zip_code) REFERENCES zipcode (zip_code)); --Populating the employer table. INSERT INTO empler VALUES ('4000','McDonalds','345 Main St','60513'); INSERT INTO empler VALUES ('4010','Best Buy','4637 1st St','24453'); INSERT INTO empler VALUES ('4020','Downing Cafe','343 Main St','54734'); INSERT INTO empler VALUES ('4030','Weeds N More','8445 Clairemont Ave','54701'); INSERT INTO empler VALUES ('4040','1st Avenue','2332 1st Ave','77355'); INSERT INTO empler VALUES ('4050','Cheesecake Factory','Camelback Rd','25434'); INSERT INTO empler VALUES ('4060','Burger King','345 Apple St','25434'); INSERT INTO empler VALUES ('4070','Home Store','355 3rd St','77355'); INSERT INTO empler VALUES ('4080','Glenwood Cafe','343 South St','54701'); INSERT INTO empler VALUES ('4090','Fudge N More','822 Clairemont Ave','54734'); INSERT INTO empler VALUES ('5000','Willy Billy','255 2nd Ave','24453'); INSERT INTO empler VALUES ('5010','DJ Dirt','233 Mountain Rd','60513'); COMMIT; ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --Building the single-entity table "Jobs" database. CREATE TABLE jobs( jobid NUMBER (5) NOT NULL, jobname VARCHAR2 (20) NOT NULL, jobminage NUMBER (2) NOT NULL, CONSTRAINT jobs_jobid_pk PRIMARY KEY (jobid)); --Populating the jobs table. INSERT INTO jobs VALUES ('33242','Lead tours','18'); INSERT INTO jobs VALUES ('33241','Clean exhibits', '18'); INSERT INTO jobs VALUES ('33243','Kid supervision', '18'); INSERT INTO jobs VALUES ('33244','Run Consessions', '18'); INSERT INTO jobs VALUES ('33245','Career ladder', '14'); INSERT INTO jobs VALUES ('33246','Train Animals','18'); INSERT INTO jobs VALUES ('33247','Clean Bathrooms', '18'); INSERT INTO jobs VALUES ('33248','Supervise Tours', '18'); INSERT INTO jobs VALUES ('33249','Manage exhibits', '18'); INSERT INTO jobs VALUES ('33250','Manage Habitats', '18'); INSERT INTO jobs VALUES ('33251','Collect Tickets', '18'); INSERT INTO jobs VALUES ('33252','Sell Balloons', '18'); COMMIT; ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --Building the single-entity table "Skills" database. CREATE TABLE skills( skillid NUMBER (5) NOT NULL, skillname VARCHAR2(20) NOT NULL, skilldecsr VARCHAR2(50) NOT NULL, CONSTRAINT skills_skillid_pk PRIMARY KEY (skillid)); --Populating the skills table. INSERT INTO skills VALUES ('15343','Grooming', 'Able to groom large animals'); INSERT INTO skills VALUES ('15341','Guiding', 'Able to lead groups'); INSERT INTO skills VALUES ('15342','Feeding', 'Able to feed the animals'); INSERT INTO skills VALUES ('15344','Cleaning', 'Able to clean exhibits'); INSERT INTO skills VALUES ('15345','Entertain', 'Able to lead performances'); INSERT INTO skills VALUES ('15346','Sales', 'Able to sell tickets'); INSERT INTO skills VALUES ('15347','Supervision', 'Able to supervise employees'); INSERT INTO skills VALUES ('15348','Manage habitat', 'Able to manage habitats'); INSERT INTO skills VALUES ('15349','Manage Exhibit', 'Able to manage exhibits'); INSERT INTO skills VALUES ('15350','Trainer', 'Able to train animals'); INSERT INTO skills VALUES ('15351','Speaking', 'Able to give presentations'); INSERT INTO skills VALUES ('15352','Maintenance', 'Able to fix stuff'); COMMIT; ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --Building the single-entity table "Crimes" database. felony, misdemeanor, federal, infractions CREATE TABLE crime( crimeid NUMBER (5) NOT NULL, crimename VARCHAR2(50) NOT NULL, crimedescr VARCHAR2 (75) NOT NULL, crimetype VARCHAR2 (50) NOT NULL, CONSTRAINT crime_crimeid_pk PRIMARY KEY (crimeid)); --Populating the crime table. INSERT INTO crime VALUES ('10224','Trespassing', 'Entering property without pemission','Misdemeanor'); INSERT INTO crime VALUES ('10225','Assault', 'Attacking another person','Felony'); INSERT INTO crime VALUES ('10226','Hi-Jacking', 'Taking control of structure','Federal Offense'); INSERT INTO crime VALUES ('10227','Public Nuisance', 'Disturbing the peace','Infraction'); INSERT INTO crime VALUES ('10228','Public intoxication', 'Drinking in public','Misdemeanor'); INSERT INTO crime VALUES ('10229','Vandalism', 'destruction of property','Misdemeanor'); INSERT INTO crime VALUES ('10230','Murder', 'Taking the life of a person','Felony'); INSERT INTO crime VALUES ('10231','Kidnapping', 'Abducting a person','Felony'); INSERT INTO crime VALUES ('10232','Arson', 'Destruction by fire','Felony'); INSERT INTO crime VALUES ('10233','Robbery', 'Stealing property','Felony'); INSERT INTO crime VALUES ('10234','Noncompliance', 'Not following rules','Infraction'); INSERT INTO crime VALUES ('10235','Repeated Noncompliance', 'Repeat of Not following rules','Infraction'); COMMIT; ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --Building the single-entity table "Jobs Skills" database. CREATE TABLE jobskills( skillid NUMBER (5) NOT NULL, jobid NUMBER (5) NOT NULL, CONSTRAINT jobskills_js_pk PRIMARY KEY (skillid, jobid), CONSTRAINT jobskills_skillida_fk FOREIGN KEY (skillid) REFERENCES skills (skillid), CONSTRAINT jobskills_jobida_fk FOREIGN KEY (jobid) REFERENCES jobs (jobid)); --Populating the jobs skills table. INSERT INTO jobskills VALUES ('15343','33241'); INSERT INTO jobskills VALUES ('15341','33242'); INSERT INTO jobskills VALUES ('15342','33242'); INSERT INTO jobskills VALUES ('15344','33241'); INSERT INTO jobskills VALUES ('15345','33243'); INSERT INTO jobskills VALUES ('15346','33246'); INSERT INTO jobskills VALUES ('15347','33247'); INSERT INTO jobskills VALUES ('15348','33248'); INSERT INTO jobskills VALUES ('15349','33249'); INSERT INTO jobskills VALUES ('15350','33250'); INSERT INTO jobskills VALUES ('15351','33251'); INSERT INTO jobskills VALUES ('15352','33252'); --COMMIT; ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --Building the single-entity table "Skills possessed" database. CREATE TABLE skillposs( skillid NUMBER (5) NOT NULL, persid NUMBER (10) NOT NULL, CONSTRAINT skillposs_sp_pk PRIMARY KEY (skillid, persid), CONSTRAINT skillposs_skillidb_fk FOREIGN KEY (skillid) REFERENCES skills (skillid), CONSTRAINT skillposs_persidg_fk FOREIGN KEY (persid) REFERENCES person (persid)); --Populating the skills possessed table. INSERT INTO skillposs VALUES ('15343','1435799'); INSERT INTO skillposs VALUES ('15341','1433457'); INSERT INTO skillposs VALUES ('15342','1435722'); INSERT INTO skillposs VALUES ('15345','1435722'); INSERT INTO skillposs VALUES ('15343','1445767'); INSERT INTO skillposs VALUES ('15341','1445767'); INSERT INTO skillposs VALUES ('15342','1445777'); INSERT INTO skillposs VALUES ('15345','1435793'); INSERT INTO skillposs VALUES ('15343','1443457'); INSERT INTO skillposs VALUES ('15341','1445722'); INSERT INTO skillposs VALUES ('15342','1445799'); INSERT INTO skillposs VALUES ('15344','1435722'); COMMIT; ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --Building the single-entity table "Employed by" database. CREATE TABLE employed( persid NUMBER (10) NOT NULL, emplerid NUMBER (10) NOT NULL, empleroccup VARCHAR2 (50) NOT NULL, fullorpart CHAR(1) NOT NULL, callatwork CHAR(1) NOT NULL, empstartd DATE NOT NULL, CONSTRAINT emplerz_fullorpart_cc CHECK (fullorpart IN ('F', 'P')), CONSTRAINT emplerz_callatwork_cc CHECK (callatwork IN ('Y', 'N')), CONSTRAINT idpersonz_pk PRIMARY KEY (persid, emplerid), CONSTRAINT fk_personz_persidh FOREIGN KEY (persid) REFERENCES person (persid), CONSTRAINT employerz_idb_fk FOREIGN KEY (emplerid) REFERENCES empler (emplerid)); --Populating the employed by table. INSERT INTO employed VALUES ('1435799','4000','Restaurant Owner','F','N', TO_DATE('06/07/2001','MM/DD/YYYY')); INSERT INTO employed VALUES ('1433457','4010','Electronic Store Owner','F','Y', TO_DATE('05/20/2008','MM/DD/YYYY')); INSERT INTO employed VALUES ('1435722','4030','Landscaping Contractor','P','Y', TO_DATE('12/20/2006','MM/DD/YYYY')); INSERT INTO employed VALUES ('1435767','4040','Restaurant Owner','F','N', TO_DATE('06/07/2001','MM/DD/YYYY')); INSERT INTO employed VALUES ('1435799','4050','Restaurant Owner','P','N', TO_DATE('05/20/2004','MM/DD/YYYY')); INSERT INTO employed VALUES ('1445767','4070','Landscaping Store Owner','P','N', TO_DATE('12/20/2005','MM/DD/YYYY')); INSERT INTO employed VALUES ('1445777','4040','Restaurant Owner','P','N', TO_DATE('06/07/2000','MM/DD/YYYY')); INSERT INTO employed VALUES ('1435793','4030','Landscaping Contractor','P','N', TO_DATE('06/20/2008','MM/DD/YYYY')); INSERT INTO employed VALUES ('1443457','4020','Restaurant Owner','P','N', TO_DATE('11/20/2006','MM/DD/YYYY')); INSERT INTO employed VALUES ('1435799','4090','Restaurant Owner','P','N', TO_DATE('01/07/2001','MM/DD/YYYY')); INSERT INTO employed VALUES ('1445722','4060','Restaurant Owner','P','N', TO_DATE('05/24/2005','MM/DD/YYYY')); INSERT INTO employed VALUES ('1445799','4080','Restaurant Owner','F','N', TO_DATE('12/29/2006','MM/DD/YYYY')); COMMIT; ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --Building the single-entity table "Crimes Committed" database. felony, misdemeanor, federal, infractions CREATE TABLE crimecomm( persid NUMBER (10) NOT NULL, crimeid NUMBER (5) NOT NULL, crimedate DATE NOT NULL, CONSTRAINT crimecommz_sp_pk PRIMARY KEY (crimeid, persid), CONSTRAINT crimecommz_crimeidb_fk FOREIGN KEY (crimeid) REFERENCES crime (crimeid), CONSTRAINT crimecommz_persidg_fk FOREIGN KEY (persid) REFERENCES person (persid)); --Populating the crime committed table. INSERT INTO crimecomm VALUES ('1435799','10224',TO_DATE('12/20/2004','MM/DD/YYYY')); INSERT INTO crimecomm VALUES ('1435799','10225',TO_DATE('12/20/2004','MM/DD/YYYY')); INSERT INTO crimecomm VALUES ('1435799','10234',TO_DATE('10/20/2002','MM/DD/YYYY')); INSERT INTO crimecomm VALUES ('1435799','10235',TO_DATE('04/20/2003','MM/DD/YYYY')); INSERT INTO crimecomm VALUES ('1435722','10226',TO_DATE('10/20/2000','MM/DD/YYYY')); INSERT INTO crimecomm VALUES ('1445767','10224',TO_DATE('12/20/2004','MM/DD/YYYY')); INSERT INTO crimecomm VALUES ('1445777','10224',TO_DATE('12/20/1994','MM/DD/YYYY')); INSERT INTO crimecomm VALUES ('1445793','10227',TO_DATE('10/20/1992','MM/DD/YYYY')); INSERT INTO crimecomm VALUES ('1443457','10229',TO_DATE('04/20/1993','MM/DD/YYYY')); INSERT INTO crimecomm VALUES ('1445799','10234',TO_DATE('10/20/1990','MM/DD/YYYY')); COMMIT; ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --Building the single-entity table "Degree Obtained" database. CREATE TABLE degreeobt( degreeid NUMBER (5) NOT NULL, persid NUMBER (10) NOT NULL, degreedate DATE NOT NULL, degreelocate VARCHAR2 (50) NOT NULL, CONSTRAINT degreeobtz_sp_pk PRIMARY KEY (degreeid), CONSTRAINT degreeobtz_degreeidb_fk FOREIGN KEY (degreeid) REFERENCES degree (degreeid), CONSTRAINT degreeobtz_persidm_fk FOREIGN KEY (persid) REFERENCES person (persid)); --Populating the crime committed table. INSERT INTO degreeobt VALUES ('3000','1435799',TO_DATE('05/20/1992','MM/DD/YYYY'),'UW-Eau Claire'); INSERT INTO degreeobt VALUES ('3020','1435793',TO_DATE('12/20/2004','MM/DD/YYYY'),'UI-Chicago'); INSERT INTO degreeobt VALUES ('3030','1433457',TO_DATE('05/20/2000','MM/DD/YYYY'),'UW-Madison'); INSERT INTO degreeobt VALUES ('3040','1435722',TO_DATE('12/20/2005','MM/DD/YYYY'),'UW-Milwaukee'); INSERT INTO degreeobt VALUES ('3050','1445767',TO_DATE('05/20/1992','MM/DD/YYYY'),'UW-Eau Claire'); INSERT INTO degreeobt VALUES ('3060','1445767',TO_DATE('12/20/2004','MM/DD/YYYY'),'UI-Chicago'); INSERT INTO degreeobt VALUES ('3070','1445777',TO_DATE('05/20/2000','MM/DD/YYYY'),'UW-Madison'); INSERT INTO degreeobt VALUES ('3080','1445793',TO_DATE('12/20/2005','MM/DD/YYYY'),'UW-Milwaukee'); INSERT INTO degreeobt VALUES ('3090','1443457',TO_DATE('05/20/1992','MM/DD/YYYY'),'UW-Stout'); INSERT INTO degreeobt VALUES ('4000','1445722',TO_DATE('12/20/2004','MM/DD/YYYY'),'UI-Chicago'); INSERT INTO degreeobt VALUES ('4010','1443457',TO_DATE('05/20/2000','MM/DD/YYYY'),'Michigan State'); INSERT INTO degreeobt VALUES ('3010','1445799',TO_DATE('12/20/2005','MM/DD/YYYY'),'UCLA'); COMMIT; ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --Building the single-entity table "volunteer information" database. CREATE TABLE vol_info ( info_id NUMBER (10) NOT NULL, start_date DATE NOT NULL, howfindout VARCHAR2 (100) NOT NULL, whyvolun VARCHAR2 (100) NOT NULL, conserveorg VARCHAR2 (50) NOT NULL, persid NUMBER (10) NOT NULL, CONSTRAINT vol_infoz_sp_pk PRIMARY KEY (info_id), CONSTRAINT fk_volinfo_persid FOREIGN KEY (persid) REFERENCES person (persid)); --Populating the volunteer information table. INSERT INTO vol_info VALUES ('55512',TO_DATE('01/24/1985','MM/DD/YYYY'),'Newspaper Ad', 'I love animals', 'Yes, World Wildlife Fund', '1435767'); INSERT INTO vol_info VALUES ('55513',TO_DATE('01/07/1999','MM/DD/YYYY'),'Word of Mouth', 'I need service learning for school', 'Yes, Sierra Club','1435777'); INSERT INTO vol_info VALUES ('55514',TO_DATE('07/24/2005','MM/DD/YYYY'),'Internet', 'I''m trying to earn a merit badge', 'None','1435793'); INSERT INTO vol_info VALUES ('55515',TO_DATE('11/13/2001','MM/DD/YYYY'),'I''ve been coming to the zoo for years and asked', 'I want to do something with my family', 'None','1433457'); INSERT INTO vol_info VALUES ('55516',TO_DATE('05/31/2003','MM/DD/YYYY'),'Reccommended by family', 'I love animals', 'Yes, World Wildlife Fund and Sierra Club','1435722'); INSERT INTO vol_info VALUES ('55517',TO_DATE('04/20/2009','MM/DD/YYYY'),'Friend works at the zoo', 'I grew up at the zoo and want to give back', 'None','1435722'); INSERT INTO vol_info VALUES ('55518',TO_DATE('01/24/1995','MM/DD/YYYY'),'Newspaper Ad', 'I love animals', 'Yes, World Wildlife Fund and ICF', '1445777'); INSERT INTO vol_info VALUES ('55519',TO_DATE('01/07/1998','MM/DD/YYYY'),'Radio, and Flyers', 'I want to be a zoologist', 'Yes, Sierra Club','1445793'); INSERT INTO vol_info VALUES ('55520',TO_DATE('07/24/2002','MM/DD/YYYY'),'Flyers', 'I love animals', 'None','1445793'); INSERT INTO vol_info VALUES ('55521',TO_DATE('11/13/2010','MM/DD/YYYY'),'Internet', 'Parole Option', 'None','1443457'); INSERT INTO vol_info VALUES ('55522',TO_DATE('05/31/2005','MM/DD/YYYY'),'Facebook told me', 'I used to be a zoologist', 'Yes, ICF','1445722'); INSERT INTO vol_info VALUES ('55523',TO_DATE('04/20/2010','MM/DD/YYYY'),'Literally, a little bird told me', 'I speak to animals', 'Yes, ICF and Sierra Club','1445799'); COMMIT; ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --Create Emergency Contact Table CREATE TABLE emer ( emerid NUMBER (10) NOT NULL, persid NUMBER (10) NOT NULL, emercontact NUMBER (10) NOT NULL, relationship VARCHAR2 (20) NOT NULL, CONSTRAINT emer_emerid_pk PRIMARY KEY (emerid), CONSTRAINT emer_persid_fk FOREIGN KEY (persid) REFERENCES person (persid)); --Populating the emergency contact table. INSERT INTO emer VALUES ('66612','1435767','7153779990','mother'); INSERT INTO emer VALUES ('66613','1435777','8323779990','friend'); INSERT INTO emer VALUES ('66614','1435793','7153667790','father'); INSERT INTO emer VALUES ('66615','1433457','7677789906','grandmother'); INSERT INTO emer VALUES ('66616','1435722','7159908790','mother'); INSERT INTO emer VALUES ('66617','1435799','2234459990','friend'); INSERT INTO emer VALUES ('66618','1445767','7153733390','brother'); INSERT INTO emer VALUES ('66619','1445777','8356649990','neighbor'); INSERT INTO emer VALUES ('66620','1445793','7144557790','nurse'); INSERT INTO emer VALUES ('66621','1443457','7657559906','son'); INSERT INTO emer VALUES ('66622','1445722','7244408790','daughter'); INSERT INTO emer VALUES ('66623','1445799','2230019990','friend'); COMMIT; ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --Building the single-entity table "job location" database. CREATE TABLE joblocate ( locateid NUMBER (10) NOT NULL, locatename VARCHAR2 (25) NOT NULL, CONSTRAINT joblocate_locateid_pk PRIMARY KEY (locateid)); --Populating the job location table. INSERT INTO joblocate VALUES ('777123','Great Bear Wilderness'); INSERT INTO joblocate VALUES ('777124','Discovery Center'); INSERT INTO joblocate VALUES ('777125','Wolf Woods'); INSERT INTO joblocate VALUES ('777126','Seven Seas'); INSERT INTO joblocate VALUES ('777127','Childrens Zoo'); INSERT INTO joblocate VALUES ('777128','Tropic World'); INSERT INTO joblocate VALUES ('777129','The Living Coast'); INSERT INTO joblocate VALUES ('777130','Feathers and Scales'); INSERT INTO joblocate VALUES ('777131','Baboon Island'); INSERT INTO joblocate VALUES ('777132','The Swamp'); INSERT INTO joblocate VALUES ('777133','Play Zoo'); INSERT INTO joblocate VALUES ('777134','Safari Grill'); COMMIT; ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ --Building the single-entity table "WORK SCHEDULE" database. CREATE TABLE wrksch( wrkschid NUMBER (10) NOT NULL, wrkstrd DATE NOT NULL, wrkstrtm DATE NOT NULL, wrkendtm DATE NOT NULL, wrkattend CHAR(1) NOT NULL, persid NUMBER (10) NOT NULL, jobid NUMBER (5) NOT NULL, locateid NUMBER (10) NOT NULL, CONSTRAINT wrksch_wrkschid_pk PRIMARY KEY (wrkschid), CONSTRAINT wrksch_wrkattend_cc CHECK (wrkattend IN('y', 'n')), CONSTRAINT fk_person_persidb FOREIGN KEY (persid) REFERENCES person (persid), CONSTRAINT fk_jobs_jobidb FOREIGN KEY (jobid) REFERENCES jobs (jobid), CONSTRAINT fk_joblocate_locateidb FOREIGN KEY (locateid) REFERENCES joblocate (locateid) ); --Populating the WORK SCHEDULE table. INSERT INTO wrksch VALUES ('4439681',TO_DATE('02/24/2010','MM/DD/YYYY'), TO_DATE('07:00', 'HH24:MI'), TO_DATE('12:30', 'HH24:MI'), 'y','1435767','33242','777123'); INSERT INTO wrksch VALUES ('4339681',TO_DATE('01/24/2010','MM/DD/YYYY'), TO_DATE('07:00', 'HH24:MI'), TO_DATE('12:30', 'HH24:MI'), 'y','1435767','33242','777125'); INSERT INTO wrksch VALUES ('4329968',TO_DATE('01/20/2010','MM/DD/YYYY'), TO_DATE('09:15', 'HH24:MI'), TO_DATE('17:30', 'HH24:MI'), 'y','1435777','33243','777127'); INSERT INTO wrksch VALUES ('4119981',TO_DATE('12/24/2010','MM/DD/YYYY'), TO_DATE('10:15', 'HH24:MI'), TO_DATE('15:30', 'HH24:MI'), 'y','1435793','33245','777127'); INSERT INTO wrksch VALUES ('4349681',TO_DATE('12/31/2010','MM/DD/YYYY'), TO_DATE('09:30', 'HH24:MI'), TO_DATE('17:45', 'HH24:MI'), 'y','1433457','33241','777128'); INSERT INTO wrksch VALUES ('4429681',TO_DATE('01/04/2010','MM/DD/YYYY'), TO_DATE('11:15', 'HH24:MI'), TO_DATE('15:30', 'HH24:MI'), 'y','1435722','33244','777128'); INSERT INTO wrksch VALUES ('4899681',TO_DATE('01/07/2010','MM/DD/YYYY'), TO_DATE('07:15', 'HH24:MI'), TO_DATE('12:30', 'HH24:MI'), 'n','1435799','33245','777126'); INSERT INTO wrksch VALUES ('4339682',TO_DATE('01/24/2010','MM/DD/YYYY'), TO_DATE('07:00', 'HH24:MI'), TO_DATE('12:30', 'HH24:MI'), 'y','1445767','33242','777125'); INSERT INTO wrksch VALUES ('4329962',TO_DATE('01/20/2010','MM/DD/YYYY'), TO_DATE('09:15', 'HH24:MI'), TO_DATE('17:30', 'HH24:MI'), 'y','1445777','33243','777127'); INSERT INTO wrksch VALUES ('4119982',TO_DATE('12/24/2010','MM/DD/YYYY'), TO_DATE('10:15', 'HH24:MI'), TO_DATE('15:30', 'HH24:MI'), 'y','1445793','33245','777127'); INSERT INTO wrksch VALUES ('4349682',TO_DATE('12/31/2010','MM/DD/YYYY'), TO_DATE('09:30', 'HH24:MI'), TO_DATE('17:45', 'HH24:MI'), 'y','1443457','33241','777128'); INSERT INTO wrksch VALUES ('4429682',TO_DATE('01/04/2010','MM/DD/YYYY'), TO_DATE('11:15', 'HH24:MI'), TO_DATE('15:30', 'HH24:MI'), 'y','1445722','33244','777128'); INSERT INTO wrksch VALUES ('4899682',TO_DATE('01/07/2010','MM/DD/YYYY'), TO_DATE('07:15', 'HH24:MI'), TO_DATE('12:30', 'HH24:MI'), 'n','1445799','33245','777126'); COMMIT; ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ --Building the single-entity table "Program" database. CREATE TABLE program( programid NUMBER (5) NOT NULL, programname VARCHAR2(50) NOT NULL, CONSTRAINT program_programid_pk PRIMARY KEY (programid)); --Populating the program table. INSERT INTO program VALUES ('8100','Butterflies Exhibit'); INSERT INTO program VALUES ('8110','Childrens Zoo'); INSERT INTO program VALUES ('8120','Interpretive Volunteer'); INSERT INTO program VALUES ('8130','Docent Program'); INSERT INTO program VALUES ('8140','Guest Guide'); INSERT INTO program VALUES ('8150','Hamill Family Play Zoo'); COMMIT; ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --Building the single-entity table "Program Attended" database. CREATE TABLE prgrattd( attendid NUMBER (5) NOT NULL, degreedate DATE NOT NULL, persid NUMBER (10) NOT NULL, programid NUMBER (5) NOT NULL, CONSTRAINT prgrattdz_sp_pk PRIMARY KEY (attendid), CONSTRAINT prgrattdz_programidb_fk FOREIGN KEY (programid) REFERENCES program (programid), CONSTRAINT prgrattdz_persidm_fk FOREIGN KEY (persid) REFERENCES person (persid)); --Populating the Program Attended table. INSERT INTO prgrattd VALUES ('7110',TO_DATE('07/20/1998','MM/DD/YYYY'),'1435799','8110'); INSERT INTO prgrattd VALUES ('7111',TO_DATE('12/20/2004','MM/DD/YYYY'),'1435793','8120'); INSERT INTO prgrattd VALUES ('7112',TO_DATE('07/20/2006','MM/DD/YYYY'),'1433457','8140'); INSERT INTO prgrattd VALUES ('7113',TO_DATE('12/20/2005','MM/DD/YYYY'),'1435722','8120'); INSERT INTO prgrattd VALUES ('7114',TO_DATE('07/20/1999','MM/DD/YYYY'),'1445767','8150'); INSERT INTO prgrattd VALUES ('7115',TO_DATE('12/20/2004','MM/DD/YYYY'),'1445767','8140'); INSERT INTO prgrattd VALUES ('7116',TO_DATE('07/20/2009','MM/DD/YYYY'),'1445777','8130'); INSERT INTO prgrattd VALUES ('7117',TO_DATE('12/20/2005','MM/DD/YYYY'),'1445793','8130'); INSERT INTO prgrattd VALUES ('7118',TO_DATE('07/20/1999','MM/DD/YYYY'),'1443457','8150'); INSERT INTO prgrattd VALUES ('7119',TO_DATE('12/20/2004','MM/DD/YYYY'),'1445722','8110'); INSERT INTO prgrattd VALUES ('7120',TO_DATE('07/20/2006','MM/DD/YYYY'),'1443457','8110'); INSERT INTO prgrattd VALUES ('7121',TO_DATE('12/20/2005','MM/DD/YYYY'),'1445799','8150'); COMMIT; ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --To show the entire person table. Select * from person; --To show the entire contact info table. Select * from contact; --To show the entire the work schedule table. Select * from wrksch; --To show the entire AVAILABILITY table. Select * from avail; --To show the entire degree table. Select * from degree; --To show the entire employer table. Select * from empler; --Donation table. Select * from donate; --jobs table. SELECT * from jobs; --SKILLS table. Select * from skills; --crime table. SELECT * from crime; --Employed by table. SELECT * from employed; --crimes committed table. Select * from crimecomm; --degrees obtained table. SELECT * from degreeobt; --zipcode table. SELECT * from zipcode; --volunteer info table SELECT * from vol_info; --emergency contact table. SELECT * from emer; --skills possessed table. SELECT * From skillposs; --job skills table. SELECT * From jobskills; --job location table. SELECT * From joblocate; --work schedule table. SELECT * From wrksch; --program table. SELECT * From program; --program attended table. SELECT * From prgrattd; ----------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------