LOGICAL DATABASE DESIGN - Fundamentals of Database Management Systems - page 195

EXERCISES

1. Convert the Video Centers of Europe, Ltd., entity-
relationship diagram in Exercise 2.2 into a well
structured relational database.
2. Convert the Central Hospital entity-relationship
diagram on the next page into a well-structured
relational database.
3. Video Centers of Europe, Ltd., is a chain of movie
DVD rental stores. It must maintain data on the
DVDs it has for rent, the movies recorded on
the DVDs, its customers, and the actual rental.
Each DVD for rent has a unique serial number.
Movie titles and customer numbers are also unique
identifiers. Assume that each movie has exactly one
''star.'' Note the difference in the year that the
movie was originally filmed and the date that a
DVD—an actual disk—was manufactured. Some of
the attributes and functional dependencies in this
environment are as follows:
Attributes
DVD Number
Manufacture Date
Movie Title
Star
Year Filmed
Length [in minutes]
Customer Number
Customer Name
Customer Address
Rental Date
Return Date
Fee Paid

tables. Primary key attributes are underlined. Do
not assume any functional dependencies other than
those shown.
a. Movie Title, Star, Length, Year Filmed
b. DVD Number, Customer Number, Rental Date,
Customer Name, Return Date, Fee Paid
c. DVD Number, Manufacture Date, Movie Title,
Star
d. Movie Title, Customer Number, Star, Length,
Customer Name, Customer Address
e. DVD Number, Customer Number, Rental Date,
Return Date, Fee Paid
4. The U.S. government wants to keep track of
information about states, governors, cities, and
mayors. In addition, it wants to maintain data on the
various federal agencies and the annual grants each
agency gives to the individual states. Each federal
agency is headed by an administrator. Agency names
and state names are unique but city names are unique
only within a state. The attributes and functional
dependencies in this environment are as follows:
Attributes
State
Governor ID Number
Governor Name
State Flower
City
Mayor ID Number
Mayor Name
City Hall Address
Mayor Telephone
Federal Agency
Administrator
Annual Grant
Functional Dependencies
State Governor ID Number
State Governor Name
State State Flower
State, City Mayor ID Number
State, City Mayor Name
State, City City Hall Address
State, City Mayor Telephone
Mayor ID Number Mayor Name
Mayor ID Number Mayor Telephone
Federal Agency Administrator
State, City, Federal Agency

Functional Dependencies
DVD Number Movie Title
DVD Number Star
DVD Number Manufacture Date
Movie Title Star
Movie Title Length
Movie Title Year Filmed
Customer Number Customer Name
Customer Number Customer Address
DVD Number, Customer Number,
Rental Date Return Date, Fee Paid
For each of the following tables, first write the
table's current normal form (as 1 NF, 2 NF, or 3 NF).
Then, take those tables that are currently in 1 NF or
2 NF and reconstruct them as well structured 3 NF

Annual

Grant