1. Populate the following tables using CREATE TABLE and make a spoolscript in a file named colonial.sql

GUIDE(Guide_Num, Last_Name, First_Name, Address, City, State, Postal_Code, Phone_Num, Hire_Date) Primary Key: Guide_Num

CUSTOMERS(Customer_Num, Last_Name, First_Name, Address, City, State, Postal_Code, Phone)

Primary Key: Customer_Num

TRIP(Trip_Id, Trip_Name, Start_Location, State, Distance, Max_Grp_Size, Type, Season)

Primary Key: Trip_Id

RESERVATION(Reservation_Id, Trip_Id, Trip_Date, Num_Persons, Trip_Price, Other_Fees, Customer_Num) Primary Key: Reservation_Id (also a Surrogate Key)

Alternate Key: Trip_Id + Trip_Date + Customer_Num

Foreign Key: Trip_Id References TRIP

Foreign Key: Customer_Num References CUSTOMERS

TRIP_GUIDES(Trip_Id, Guide_Num)

Primary Key: Trip_Id + Guide_Num

Foreign Key: Trip_Id References TRIP

Foreign Key: Guide_Num References GUIDE

2. Create a spool script named colonialinput.sql which uses INSERT INTO commands to insert the following data:

GUIDE CITY STATE POSTAL CODE PHONE NUM HIRE DATE GUIDE NUM LAST NAME FIRST NAME ADDRESS Abrams Williamsburg MA 617-555-6032 6/3/2012 AM01 54 Quest Ave 01096 140 Oakton Rd. Jaffrey BR01 AV4/2012 Devon Harley 25 Old Ranch Rd. Sunderland MA 01375 781-555-7767 1/s/2012 DH01 Gzol Gregory Zach 7 Moose Head Rd. Dummer NH 035ss 603-555-8765 11/4/2012 KS01 Ekiley Susan 943 Oakton Rd. Jaffrey NII 452 4/8/2013 603-555-1230 9 Congaree Ave. Fraconia. 603-555-0003 6/10/2013 Ray 24 Shenandoah Rd. Springfield MA 01101 MR01 Marston 781-555-2323 9/14/2015 RI101 Rowan Ilal 04660 12 Heather Rd Mount Desert ME 207-555-9009 6/2/2014 Lori 15 Riverton Rd Coventry DVT S02-555-3339 SL01 Stevens 05825 9/5/2014 Unser Glory 342 Pineview St Danbury, CT 06s10 2/2/2015 UG01 203-555-8534