Database Reference
In-Depth Information
stores the number of rentals a member has made and the date the member joined the club. He periodically has
promotions during which members can earn bonus units that they can later apply to the cost of renting DVDs.
He needs to store the number of bonus units a member has earned.
3. Expand the database design you created in Exercise 1 and modified in Exercise 2 so that it will also support the
following situation: Ray wants to store information about the DVDs the club owns. When the club purchases a
DVD, Ray assigns it a number. Along with the number, he stores the number of the movie on the DVD, the date
the DVD was purchased, the number of times it has been rented, and the number of the member who is cur-
rently renting it. (If the DVD is not currently being rented, the member number will be null.) Ray also needs to
store the number of the branch to which the DVD is assigned. Finally, Ray would like to store the history of the
rental of each particular DVD. In particular, he needs to store the DVD number, date of the rental, date it was
returned, and number of the member who rented the DVD. Assume a DVD could potentially be rented more
than once on the same day. (Hint: Review the discussion of the categories of primary keys to determine what
type of primary key would be appropriate for this relation.)
227
Alexamara Marina Group Case
Complete the following tasks. In each exercise, represent your answer in both DBDL and with a diagram. You may
use any of the styles presented in this chapter for the diagram.
1. Design a database to produce the following reports. Do not use any surrogate keys in your design.
a. For each marina, list the marina number, name, address, city, state, and zip code.
b. For each boat owner, list the owner number, last name, first name, address, city, state, and zip code.
c. For each marina, list all the slips in the marina. For each slip, list the length of the slip, annual rental fee,
name and type of the boat occupying the slip, and boat owner's number, last name, and first name.
d. For each service category, list the category number and description. In addition, for each service request
in each category, list the marina number, slip number of the boat receiving the service, estimated hours
for the service, hours already spent on the service, and next scheduled service date.
e. For each service request, list the marina number, slip number, category description, description of the
particular service, and a description of the current status of the service.
2. Expand the database design you created in Exercise 1 so that it will also support the following situation: A spe-
cific technician handles each service request. Along with all the details concerning a service request listed in
Exercise 1, list the number, last name, and first name of the technician assigned to handle the request.
