The u.s. government wants to keep track of information about states,
Using MySQL Workbench build a database that:
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 Annu
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
1NF or 2 NF as well structured 3NF 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
Leave a Reply
Want to join the discussion?Feel free to contribute!