Question $1$:

Suppose that our database has the following table.

Person

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

Based on the above assumptions, what do you choose to be the primary key of

Person table? Why?

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.

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..

Draw your ERD based on fully normalized table $($Reverse Engineering$).$