The owner of Colonial Adventure Tours would like you to complete the following tasks to help him

Question:

The owner of Colonial Adventure Tours would like you to complete the following tasks to help him maintain his database. In the following exercises, you will use the data in the Colonial Adventure Tours database shown in Figures 1-15 through 1-19 in Chapter 1. (If you use a computer to complete these exercises, use a copy of the original Colonial Adventure Tours database so your data will not reflect the changes you made in Chapter 3.) If you have access to a DBMS, use the DBMS to perform the tasks and explain the steps you used in the process. If not, explain how you would use SQL to obtain the desired results. Check with your instructor if you are uncertain about which approach to take.
1. Create a view named NHTrips. It consists of the trip ID, trip name, start location, distance, maximum group size, type, and season for every trip located in New Hampshire (NH). Display the data in the view.
2. Create a view named Hiking. It consists of the trip ID, trip name, start location, state, distance, maximum group size, and season for every hiking trip. Display the data in the view.
3. Create a view named ReservationCustomer. It consists of the reservation ID, trip ID, trip date, customer number, customer last name, customer first name, and phone number. Display the data in the view.
4. Create the following indexes. If it is necessary to name the index in your DBMS, use the indicated name.
a. Create an index named TripIndex1 on the TripName field in the Trip table.
b. Create an index named TripIndex2 on the Type field in the Trip table.
c. Create an index named TripIndex3 on the Type and Season fields in the Trip table and list the seasons in descending order.
5. Drop the TripIndex3 index from the Trip table.
6. Specify the integrity constraint that the distance of any trip must be equal to or greater than 4.
7. Ensure that the following are foreign keys (that is, specify referential integrity within the Colonial Adventure Tours database).
a. CustomerNum is a foreign key in the Reservation table.
b. TripID is a foreign key in the Reservation table.
8. Add to the Customer table a new character field named Waiver that is one character in length.
9. Change the value in the Waiver field in the Customer table to Y for the customer with the last name of Ocean.
10. Change the length of the StartLocation field in the Trip table to 60.
11. What command would you use to delete the Trip table from the Colonial Adventure Tours database? (Do not delete the Trip table.)
12. If you are using Access 2013, complete the following steps.
a. Add a PreviousTrip field to the Customer table. Create and run a totals query on the Reservation table to count the number of reservations by customer. Manually update the Customer table with these values. Assign the value 0 to customers 110 and 123.
b. Create a data macro associated with the After Insert event for the Reservation table to increment the PreviousTrip field for the appropriate customer when inserting a row in the Reservation table. Test the data macro by adding a record to the Reservation table and ensuring that the corresponding customer€™s previous trip total is updated correctly.
c. Create a data macro associated with the After Delete event for the Reservation table to subtract one on the record being deleted from the customer€™s previous trip total. Test the data macro by deleting a record from the Reservation table and ensuring that the corresponding customer€™s previous trip total is updated correctly.
13. In Question 7, you specified referential integrity for the Reservation table. What other table(s) in the Colonial Adventure Tours database require that you specify referential integrity? Identify the foreign keys in the table(s).
14. Review the trip data for Colonial Adventure Tours shown in Figure 1-16 on page 17. In addition to the integrity constraint specified in Question 6, what other integrity constraints could you add to at least two other fields in the Trip table?

Figure 1-15