The management of Colonial Adventure Tours wants you to complete the following exercises. You do not use the Colonial Adventure Tours database for any of these exercises.
1. The DBA asks for your help in planning the data archive for the following Colonial Adventure Tours database:
Guide (GuideNum, LastName, FirstName, Address, City, State,
PostalCode, PhoneNum, HireDate)
Trip (TripID, TripName, StartLocation, State, Distance,
MaxGrpSize, Type, Season)
Customer (CustomerNum, LastName, FirstName, Address, City,
State, PostalCode, Phone)
Reservation (ReservationID, TripID, TripDate, NumPersons,
TripPrice, OtherFees, CustomerNum)
TripGuides (TripID, GuideNum)
Determine which data from the database to archive; that is, for each table, specify whether data needs to be archived. If it does, specify which data, when it should be archived, and whether it should be archived with data from another table.
2. The DBA denormalized some of the data in the Colonial Adventure Tours database to improve performance, and one of the resulting tables is the following:
Reservation (ReservationID, TripID, TripDate, NumPersons,
TripPrice, OtherFees, CustomerNum, LastName, FirstName)
Which field or fields cause the table to no longer be in third normal form? In which normal form is the denormalized table?
3. Interview the DBA at your school or at a local business to determine the safeguards used to segregate the production system from the test system.
4. You are the DBA for Colonial Adventure Tours. The company is considering accepting reservations from sports clubs, such as hiking clubs. The reservation would be for the entire group. What changes to the database structure would you need to make to accommodate accepting reservations from groups or organizations as well as individuals?