The U.S. government wants to keep track of information about states, governors, cities, and mayors. In addition,
Question:
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
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 as 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 Number, Mayor Name, Mayor Telephone