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

For each of the following tables, first write the
table's current normal form (as 1NF, 2NF, or 3NF).
Then, reconstruct those tables that are currently in
1 NF or 2 NF as well structured 3 NF tables. Primary
key attributes are underlined. Do not assume any
functional dependencies other than those shown.
a. State, City, Governor Name, Mayor ID Number,
Mayor Name, Mayor Telephone
b. State, City, Mayor Name, Mayor Telephone
c. State, City, Federal Agency, Governor Name,
Administrator, Annual Grant
d. State, City, Governor Name, State Flower, Mayor
Telephone
e. State, City, City Hall Address, Mayor ID Num-
ber, Mayor Name, Mayor Telephone
5. Consider the General Hardware relational database
shown in Figure 7.19.

a. Write an SQL command to create the CUS-
TOMER table.
b. Write an SQL command to create a view of
the CUSTOMER table that includes only the
Customer Number and HQ City attributes.
c. Write an SQL command to discard the OFFICE
table.
d. Assume that Customer Number 8429 is the
responsibility of Salesperson Number 758. Write
an SQL command to change that responsibility
to Salesperson Number 311.
e. Write an SQL command to add a new record
to the CUSTOMER table for Customer Number
9442. The Customer Name is Smith Hardware
Stores, the responsible salesperson is Salesperson
Number 577, and the HQ City is Chicago.

MINICASES

1. Happy Cruise Lines. Convert the Happy Cruise Lines
entity-relationship diagram on the next page into a well
structured relational database.
2. Super Baseball League. The Super Baseball League
wants to keep track of information about its players,
its teams, and the minor league teams (which we will
call minor league ''clubs'' to avoid using the word
''team'' twice). Minor league clubs are not part of the
Super Baseball League but players train in them with
the hope of eventually advancing to a team in the Super
Baseball League. The intent in this problem is to keep
track only of the current team on which a player plays
in the Super Baseball League. However, the minor
league club data must be historic and include all of the
minor league clubs for which a player has played. Team
names, minor league club names, manager names, and
stadium names are assumed to be unique, as, of course,
is player number.
Design a well structured relational database for this
Super Baseball League environment using the data
normalization technique. Progress from first to second
normal form and then from second to third normal form
justifying your design decisions at each step based on
the rules of data normalization. The attributes and func-
tional dependencies in this environment are as follows:
Attributes
Player Number

Player Name
Player Age
Team Name
Manager Name
Stadium Name
Minor League Club Name
Minor League Club City
Minor League Club Owner
Minor League Club Year Founded
Start Date
End Date
Batting Average

Functional Dependencies
Player Number Player Name
Player Number Age
Player Number Team Name
Player Number Manager Name
Player Number Stadium Name
Minor League Club Name City

Minor League Club Name Owner

Minor League Club Name Year Founded

Team Name Manager Name

Team Name Stadium Name

Player Number, Minor League Club Name

Start Date, End Date, Batting Average