The following exercises are based on the Colonial Adventure Tours database shown in Figures 1-15 through 1-19 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. Colonial Adventure Tours is considering changing the way it handles reservations. Instead of storing the number of persons associated with one reservation, the company would like to store the name and address of each person associated with each reservation. If Colonial Adventure Tours decides to implement this change, the trip price and other fees amount for each trip would be dependent on only the trip ID. Determine the multivalued dependencies in the following table, and then convert this table to an equivalent collection of tables that are in fourth normal form.

Reservation (ReservationID, TripID, TripDate, TripPrice, OtherFees,
(CustomerNum, CustomerLastName, CustomerFirstName, Address,
City, State, PostalCode, Phone) )
3. Identify the functional dependencies in the following unnormalized table. Convert the table to third normal form. Is the result also in fourth normal form? Why or why not?
Trip (TripID, TripName, StateAbbreviation, StateName,
(GuideNum, GuideLast, GuideFirst) )
4. Currently, each trip is identified with only season. For example, the Arethusa Falls trip is offered only in the Summer season. Colonial Adventure Tours is considering offering the same trip in more than one season; that is, the Arethusa Falls trip could be offered in both the Summer and Late Spring seasons. Using this new information, identify all dependencies and convert the current Trip table to third normal form. You may need to make some assumptions. Identify these assumptions in your solution.

Figure 1-15

Transcribed Image Text:

GuideNum LastName FirstName Address State PostalCode PhoneNum City HireDate AM01 Miles Williamsburg MA 01096 NH 03452 MA 01375 03588 54 Quest Ave. 140 Oakton Rd. 25 Old Ranch Rd. 7 Moose Head Rd. Dummer 943 Oakton Rd. 9 Congaree Ave. 24 Shenandoah Rd. Springfield 12 Heather Rd. 15 Riverton Rd. 342 Pineview St. 617-555-6032 6/3/2012 603-555-2134 Abrams BR01 3/4/2012 Jaffrey Boyers Rita Devon DH01 Harley 781-555-7767 1/8/2012 Sunderland Zach GZ01 603-555-8765 11/4/2012 603-555-1230 4/8/2013 Gregory NH Jaffrey Fraconia KS01 03452 Kiley Susan NH 603-555-0003 6/10/2013 03580 Kelly Marston KS02 Sam NH MR01 Ray MA 01101 Mount Desert ME VT 781-555-2323 9/14/2015 207-555-9009 6/2/2014 RH01 04660 Hal Rowan Stevens Lori SL01 05825 802-555-3339 9/5/2014 Coventry Unser UG01 06810 203-555-8534 2/2/2015 CT Glory Danbury