Convert the attributes listed below “individually” in steps a through g showing
ID: 3735130 • Letter: C
Question
Convert the attributes listed below “individually” in steps a through g showing the data in 1st Normal Form, 2nd Normal Form and 3rd Normal Form.
Use Excel or a Table in Word to show the data listed below representing current tables. Use a single underline for the primary keys and a double underline for the foreign keys.
a. State, City, Governor ID, Governor Name, Governor Political Party, Mayor ID Number, Mayor Name, Mayor Political Party
b. State, City, Governor ID, Governor Name, Governor Political Party, Governor Telephone Number, Mayor ID Number, Mayor Name, Mayor Political Party, Mayor Telephone Number
c. State, City, Governor ID, Governor Name, Governor Political Party, Governor Telephone Number, Mayor ID Number, Mayor Name, Mayor Political Party, Mayor Telephone Number, Federal Agency ID,Federal Agency
d. State, City, Governor ID, Governor Name, Governor Political Party, Governor Telephone Number,Governor Year Elected, State Flower, Mayor ID Number, Mayor Name, Mayor Political Party, Mayor Telephone Number, Mayor Year Elected, Federal Agency ID, Federal Agency
e. State, City, Governor ID, Governor Name, Governor Political Party, Governor Telephone Number, Governor Year Elected, State Flower, Mayor ID Number, Mayor Name, Mayor Political Party, Mayor Telephone Number, Mayor Year Elected, City Hall Address, Federal Agency ID, Federal Agency,Administrator ID, Administrator Name, Annual Grant Amount
f. State, City, Governor ID, Governor Name, Political Party Code, Governor Political Party, Governor Telephone Number, Governor Year Elected, State Flower, Mayor ID Number, Mayor Name, Political Party Code, Mayor Political Party, Mayor Telephone Number, Mayor Year Elected, City Hall Address, Federal Agency ID, Federal Agency, Administrator ID, Administrator Name, Annual Grant Amount
g. State, City, Governor ID, Governor Name, Political Party Code, Governor Political Party, Governor Telephone Number, Governor Year Elected, State Flower, Mayor ID Number, Mayor Name, Political Party Code, Mayor Political Party, Mayor Telephone Number, Mayor Year Elected, City Hall Address, Federal Agency ID, Federal Agency, Administrator ID, Administrator Name, Year of Annual Grant, Annual Grant Amount.
Explanation / Answer
a)
State
City
Governor_ID
Governor_Name
Governor_Political_Party
MayorID_Number
Mayor_Name
Mayor_Political_Party
Here candidate key is (state,city) which enough to identify each row uniquely.
Functional dependencies are the following,
State,city -> Governor_ID, MayorID_Number
Governor_ID -> Governor_Name, Governor_Political_Party
MayorID_Number -> Mayor_Name, Mayor_Political_Party
Prime attribute : The attributes which are the members of candidate key.
Non-prime attribute : The attributes which are not the members of candidate key.
Prime attributes : State , city
1st Normal form
2nd Normal form
Rule : the table must be in 1st normal form and there should not be any partial dependency between attributes. That is every non-prime attribute must be fully dependent on candidate key.
Here there is no partial dependency between attributes. Because candidate key is (state,city). Only first dependency is dependent on this candidate key which is not a partial dependency, the determinants ( Governor_ID, MayorID_Number ) are fully dependent on prime attributes state,city. Other dependencies are not determined by prime attributes so there is no relevance for partial dependency.
State,city -> Governor_ID, MayorID_Number
3rd Normal form
Rule : the table must be 2nd normal form and for a non-trivial functional dependency X -> Y , it should satisfy either of the following two conditions,
Now we are decomposing the main table into sub tables based on the dependency to make it in 3rd normal form. Decomposed tables are,
State
city
Governor_ID
MayorID_Number
Dependency of this table is => State,city -> Governor_ID, MayorID_Number
Governor_ID
Governor_Name
Governor_Political_Party
Dependency of this table is => Governor_ID -> Governor_Name, Governor_Political_Party
In this table, Governor_ID is the candidate key.so it is also super key. Since this table satisfies the rule of 3rd NF.
MayorID_Number
Mayor_Name
Mayor_Political_Party
Dependency of this table is => MayorID_Number -> Mayor_Name, Mayor_Political_Party
In this table, MayorID_Number is the candidate key.so it is also super key. Since this table satisfies the rule of 3rd NF.
Now we got 3 tables instead of the main table which are in 3rd normal form.
State
city
Governor_ID
MayorID_Number
Governor_ID
Governor_Name
Governor_Political_Party
MayorID_Number
Mayor_Name
Mayor_Political_Party
b)
State
City
Governor_ID
Governor_Name
Governor_Political_Party
MayorID_Number
Mayor_Name
Mayor_Political_Party
Governor_Telephone _Number
Mayor_Telephone _Number
Here candidate key is (state,city) which enough to identify each row uniquely.
Functional dependencies are the following,
State,city -> Governor_ID, MayorID_Number
Governor_ID -> Governor_Name, Governor_Political_Party, Governor_Telephone_Number
MayorID_Number -> Mayor_Name, Mayor_Political_Party, Mayor_Telephone _Number
Prime attribute : The attributes which are the members of candidate key.
Non-prime attribute : The attributes which are not the members of candidate key.
Prime attributes : State , city
1st Normal form
2nd Normal form
Rule : the table must be in 1st normal form and there should not be any partial dependency between attributes. That is every non-prime attribute must be fully dependent on candidate key.
Here there is no partial dependency between attributes. Because candidate key is (state,city). Only first dependency is dependent on this candidate key which is not a partial dependency, the determinants ( Governor_ID, MayorID_Number ) are fully dependent on prime attributes state,city. Other dependencies are not determined by prime attributes so there is no relevance for partial dependency.
State,city -> Governor_ID, MayorID_Number
3rd Normal form
Rule : the table must be 2nd normal form and for a non-trivial functional dependency X -> Y , it should satisfy either of the following two conditions,
Now we are decomposing the main table into sub tables based on the dependency to make it in 3rd normal form. Decomposed tables are,
State
city
Governor_ID
MayorID_Number
Dependency of this table is => State,city -> Governor_ID, MayorID_Number
Governor_ID
Governor_Name
Governor_Political_Party
Governor_Telephone_Number
Dependency of this table is => Governor_ID -> Governor_Name, Governor_Political_Party, Governor_Telephone_Number
In this table, Governor_ID is the candidate key.so it is also super key. Since this table satisfies the rule of 3rd NF.
MayorID_Number
Mayor_Name
Mayor_Political_Party
Mayor_Telephone _Number
Dependency of this table is => MayorID_Number -> Mayor_Name, Mayor_Political_Party, Mayor_Telephone _Number
In this table, MayorID_Number is the candidate key.so it is also super key. Since this table satisfies the rule of 3rd NF.
Now we got 3 tables instead of the main table which are in 3rd normal form.
State
city
Governor_ID
MayorID_Number
Governor_ID
Governor_Name
Governor_Political_Party
Governor_Telephone_Number
MayorID_Number
Mayor_Name
Mayor_Political_Party
Mayor_Telephone _Number
State
City
Governor_ID
Governor_Name
Governor_Political_Party
MayorID_Number
Mayor_Name
Mayor_Political_Party