Description
Question 1:
Suppose that our database has the following table.
Person
Con
ID
Con
Name
Con
Pop
Con
Size
State
code
State
Name
State
Rgn
State
Size
State
Pop
Cty
Code
Cty
Name
Cty
Size
Per
SSN
Per
Name
Per
Age
Per
DofB
Per
Add
Field Explanation:
Con: Stands for Country
Pop: Stands for population
Rgn: Stands for region (like west, east, central, etc.)
Cty: Stands for City
Per: Stands for Person
DofB: Stands for date of birth
Add: Stands for Address
It is assumed that
– Every country in the world has a different country ID
– Every city in the world has a different city code
– Every state in the world has a different state code, and
– Every person in the world has a different SSN
– Every person in the world has only one citizenship and has only one address
–
Other Assumptions:
– There is no village, county, area, etc. A country consists of several states and each state has
several cities
1) Based on the above assumptions, what do you choose to be the primary key of
Person table? Why?
2) Explain the anomalies exist in the Person table. Choose only one example of insert
anomaly, one example of delete anomaly and one example of update anomaly. Note
that update does not mean adding or deleting records. It only refers to modifications
of values in some rows of the table.
3) Normalize the table; create as many as tables necessary such that all new tables are
in third normal form. All the transitive and derived dependencies must be removed..
4) Draw your ERD based on fully normalized table (Reverse Engineering).
