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

State State State State, City State, City State, City- Governor ID Number Governor Name State Flower Mayor ID Number Mayor Name City Hall Address Mayor Telephone Mayor Name Mayor Telephone Administrator State, City- Mayor ID Number - Mayor ID Number - Federal Agency State, City, Federal Grant Agency Annual