Database Reference
In-Depth Information
c. Create a data macro associated with the After Update event for the Copy table to implement the trigger in
Step 13b. Test the data macro by changing a price on a record in the Copy table and ensuring that the cor-
responding branch's total value is updated correctly.
d. Create a data macro associated with the After Delete event for the Copy table to implement the trigger in
Step 13c. Test the data macro by deleting a record from the Copy table and ensuring that the correspond-
ing branch's total value is updated correctly.
152
Alexamara Marina Group Case
In the following exercises, you will use the data in the Alexamara Marina Group database shown in Figures 1-20
through 1-24. (If you use a computer to complete these exercises, use a copy of the original Alexamara 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 per-
form 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 not certain about which approach to take.
1. Create a view named LargeSlip using the data in the MarinaNum, SlipNum, RentalFee, BoatName, and
OwnerNum columns in the MarinaSlip table for those slips with lengths of 40 feet. Display the data in the view.
2. Create a view named InitialService using the slip ID, category number, category description, and estimated
hours for every service request for which the spent hours are zero. Display the data in the view.
3. Create a view named TypesOfBoats using the boat type and a count of all boats of each type. 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 BoatIndex on the BoatName field in the MarinaSlip table.
b. Create an index named BoatIndex2 on the OwnerNum field in the MarinaSlip table.
c. Create an index named BoatIndex3 on the Length and BoatName fields in the MarinaSlip table and list the
lengths in descending order.
5. Drop the BoatIndex3 index.
6. Assume the MarinaSlip table has been created, but there are no integrity constraints. Create the necessary
integrity constraints so the rental fee must be less than $5,000 and the slip length must be 25, 30, or 40.
7. Ensure that the following are foreign keys (that is, specify referential integrity) in the Alexamara database.
a. MarinaNum is a foreign key in the MarinaSlip table.
b. OwnerNum is a foreign key in the MarinaSlip table.
c. CategoryNum is a foreign key in the ServiceRequest table.
d. SlipID is a foreign key in the SerivceRequest table.
8. Add to the MarinaSlip table a new character field named FeePaid that is one character in length. On all records,
change the value for the FeePaid field to Y.
9. Change the FeePaid field in the MarinaSlip table to N for the slip whose slip ID is 4.
10. Change the length of the BoatName field in the MarinaSlip table to 60.
11. Write a stored procedure that will change the rental fee of a slip with a given slip ID. How would you use this
stored procedure to change the rental fee of slip ID 3 to 3,700.00?
12. Assume the Owner table contains a column called TotalRental that represents the total rental fee for all slips
rented by that owner. Write the code for the following triggers following the style shown in the text.
a. When inserting a row in the MarinaSlip table, add the rental fee to the total rental for the appropriate owner.
b. When updating a row in the MarinaSlip table, add the difference between the new rental fee and the old
rental fee to the total rental for the appropriate owner.
c. When deleting a row in the MarinaSlip table, subtract the rental fee from the total rental for the appropriate
owner.
13.
If you are using Access 2010, complete the following steps.
a. Add a Currency field named TotalRental to the Owner table. Create and run a query on the MarinaSlip table
to determine the appropriate values for this field and then update the Owner table with these values.
