Need some help with SQL, these questions are really confusing.

Colonial Adventure Tours Answer each of the following questions using the Colonial Adventure Tours data shown in Figures 1-4 through 1-8 in Chapter 1. No computer work is required. 1. Using the types of entities found in the Colonial Adventure Tours database (trips, guides customers, and reservations), create an example of a table that is in first normal form but not in second normal form, and an example of a table that is in second normal form but not in third normal form. In each case, justify your answers and show how to convert to the higher forms 2. Identify the functional dependencies in the following unnormalized table. Convert the table to third normal form. TRIP (TRIP ID, TRIP NAME, STATE ABBREVIATION, STATE NAME, (GUIDE NUM GUIDE LAST, GUIDE FIRST) ) 3. Colonial Adventure Tours is considering offering outdoor adventure classes to prepare people to participate in hiking, biking, and paddling adventures. Only one class is taught on any given day. Participants can enroll in one or more classes. Classes are taught by the guides that Colonial Adventure employs. Participants do not know who the instructor for a particular class will be until the day of the class. Colonial Adventure Tours needs your help with the database design for this new venture. In each step, represent your answer using the shorthand representation and a diagram. You may use any of the styles presented in this chapter for the diagram. a. For each participant, list his or her number, last name, first name, address, city state, postal code, telephone number, and date of birth. For each adventure class, list the class number, class description, maximum number of people in the class, and class fee For each participant, list his or her number, last name, first name, and the class number, class description, and date of the class for each class in which the partici- pant is enrolled. For each class, list the class date, class number, and class description; and the number, last name, and first name of each participant in the class. b. c. d. GUIDE GUIDE NUM LAST NAME FIAST NAME ADDRESS AMO1 BROI DHOT G201 KS01 KS02 MRO1 RIHOT SLOI CITY STATE POSTAL CODE PHONE_NUM HIRE DATE hrams Boyers Devon Gregory |Zach Miles Rita Harley Williamshung MA 010M NH 03452 25 Old Ranch Rd. Sunderland MA 01375 NH 03588 ( 452 NH 03580 24 Shenandoah Rd. Springri MA 01101 04660 VT 05825 34 Quest Ave 617-558-6032 603-555-2134 781-555-7767 603-555-8765114201 603-555-1230 603-555-0003 201 781-555-2323 914/2015 207-555-9009 S02-555-3339 203-555-8534 3/2012 /4/201 AV2012 140 Oakton Rd.Jaffrey 7 Moose Head Rd. Dummer 943 Oukton Rd.Jaffrey 9 Congaree Ave.Fraconia NII 4/V201 Kelly Marston Rowan Stevens Unser lla Lori Glory 12 Heather Rd 15 Riverton Rd. Coventry 342 Pinevlew St.Danbury Mount Desert ME /2/2014 95/2014 2/2/2015 106810 FIGURE 1-4 Sample guide data for Colonial Adventure Tours TRIP TRIP ID TRIP NAME START LOCATION STATE DISTANCE MAX GAP SIZE TYPE Arethusa Falls North Peak rin 10 lliking Early Fall Mt Ascutncy West Peak Mountain Ride Weathersfield Lewiston-Aul North Newry Batchelders Grant ME Baldpate Mountain y Mountain S Hiki 6 Paddli 12 Hiki 7 Bloomfeld Maidstone Late S Black Pond Lincoln Tamwortlh Big Rock Cave 10 Mt. Cardigan-Firesere 11 Chocorua Lake Tour Tamworth 16 Early Fall 3 Cadillac Mountain 14 Cannon Mtn 15| Crawford Path Presiden Is I like Bar Harbor NH Crawford Notch Hiking Summer 17 Huguenot Head Hike IS Low Bald Spot Hike 19 Mason's Farm 20 Lake 21 Long Pond Bar Harbor 10 Hikine Early Fall Fall North Stratford pri 12 Hikin 15 Paddling Late Spring 8 Biking Early Fall Rutland MA Greenville 12 23 Lower Pond Tour 24 Mt Adams NH 23 Mount Battie Ride 26 27 Mt.CChooorua 28 Mount Garheld Hike Mount Cardisan Ilike Late Fall lhany Woodstock 10 liking Spring 10 miki 12 | ilikin 16 Hiki 10 Paddli Early Fall Late Spring Metacomet-Monadnock Trail Hike Pelham MLA Tyringham 30 McLennan Reservation Hike oi River- Summer 32 Northern Forest Canoe Traillarl ark Mount Desert Island MI NII S Biking Late Spring 3 Park Loop Rid 34 Pontook Reservoir Tour 5 Pisgah Suate Park Rid rry Trail Ride White Bar Harbor Mount Car 16 37 Seal Beach Harbor r River Ride 16 Hiking Early Spri o Miking 10 Biking Late Spri NH Welch and Dickey Mountains ike Thorton Princeto 0 Wachusett Mountain Early Spring 1 Westficld River Loop Fort Fairficld FIGURE 1-5 Sample trip data for Colonial Adventure Tours CUSTOMER CUSTOMER NUM LAST NAME FIRST NAME ADDRESS STATE POSTAL CODE PHONE Old Mill Rd 2332 South St. Apt 3 Springficld 132 Main St. #1 164A South Bend Rd. Lowell 45 Lower Ave 156 Scholar St 76 Cross Rd 2 Sheep Stop St 10 Greenheld St 65 Granite St 73 Highland Ave 1 292 Ocean Walk 7 Cherry Blossom St. Weymouth 18 Ark Ledge La 24 Stump Rd 10 Old Main St Northfold LondonderryINH MA East Hartford CT 102 13-555-3212 560-555-0703 781-555-8423 585-555-5321 401-555-4848 603-555-0456 814-555-5521 07-555-9643 585-555-0111 857-555-6258 609-555-5231 617-555-6665 502-555-3096 207-555-4507 502-555-3443 603-355-3476 860-555-9876 857-555-5532 585-555-0900 860-555-3234 104 105 06 Goff McLean Morontoia Marchand Rulf Wolcott Johnston Bath 01854 14590 02919 NY RI 108 109 110 loseph Qhiinn Uschi lean Lae Martha PA 16412 14%A 14592 Bers lones Vaceari Murakami Chau Gernowski Sadie Bretton-Borak Siam Hefferson larnett ork Somerville Ocean NY MA N.I IA Londonderry VT IE 08226 188 05148 04912 05444 116 119 120 Clement thens ambriddse 122 South St. Apt 27 25 Stag Rd 12 Foster St 51 Fredrick St Manchester NH 124 125 06074 NY 14411 BrownBrianne 154 Central St. Vernn CT 06066 South Windsor |CT bion FIGURE 1-6 Sample customer data for Colonial Adventure Tours RESERVATION RESERVATION ID TRIP ID TRIP DATE NUM PERSONS TRIP PRICE OTHER FEES CUSTOMER NUM 1600001 1600002 1600003 1600004 1600005 1600006 1600007 1600008 1600009 1600010 1600011 1600012 1600013 1600014 1600015 1600016 1600017 1600018 1600019 1600020 1600021 1600022 1600024 1600025 1600026 1600029 1600030 40 3V26/2016 21 /8/2016 28 9/12/2016 26 10 16/2016 39 6/25/2016 321 6/18/2016 227/9/2016 28 9/12/2016 38 911/2016 2 5/14/2016 39/15/2016 1 12/2016 | 7/9/2016 12 10V1/2016 10 7/23/2016 11 7/23/2016 39 18/2016 38 9/18/2016 25 8/29/2016 281 8/27/2016 32 11/2016 21 682016 38 9/11/2016 38 911/2016 12 101/2016 49/19/2016 15 7/25/2016 $95.00 $35.00 $45.00 $55.00 $80.00 $75.00 $35.00 $90.00 $25.00 $25.00 $15.00 $20.00 $40,00 $20.00 $75.00 $20 00 $85.00 $110.00 $35.00 $90.00 $95.00 $70.00 $70.00 $40.00 4 $105.00 $60.00 $0,00 101 $0.00 103 $15.00 104 $0.00 105 $20,00 106 $10.00 107 $0.00 108 $40.00 109 $0.00 102 $0.00 102 $0.00 115 $5.00 116 $5.00 119 $0.00 120 $15.00 121 $5.00 122 $15.00 126 $25.00 124 $10.00 124 $20.00 112 $25.00 119 $30.00 121 $45.00 125 $0.00 1206 $25.00 120 $15.00 104 3 FIGURE 1-7 Sample reservations data for Colonial Adventure Tours TRIP_GUIDES TRIP_ GUIDES (CONTINUED) TRIP ID GUIDE NUM TRIP ID GUIDE NUM 19 DHO1 20 SLo1 21 AMO1 22 UG01 23 DH01 23 SLO1 24 BRO 25 BRO1 26 GZO1 27 G201 28 BRO1 29 DH01 0 AMO 31 SLO1 32 KS01 3.3 UGOI 1 RH01 2 AMO1 2 SLo1 3 SLO1 4 BRO1 5 KS01 5 UGO1 6 RHO1 7 SLO1 S BRO1 9 BRO1 10 GZ01 11 DHO1 11 KSO1 11 I UG01 12 BRO1 13 RHO 14 KS02 15 GZ01 16 KSOZ2 17 RHO1 18 KS02 35 G2O1 36 | KS02 37 RHO1 38 KS02 39 BRO1 40 DHOI 41 BROI FIGURE 1-8 Table used to relate trips and guides