Answer:
For constructing relational schema, consider the relation as:
Data Attributes for USPeople: name, Social Security number (SSN), street address, city, state,
ZIP code, area code, phone number
1. Functional Dependency (FD):
A functional dependency (FD) is a relationship between the primary key and non-key attributes of the relation. For finding FD, we need to know about the primary key (PK) which should be unique.
Social Security number (SSN) is the only attribute that holds unique value for relation. R, attribute for any relation Y is functionally dependent on attribute X (usually the PK), if for every valid instance of X, that value of X uniquely determines the value of Y.
Social Security number (PK) ->;name, street address, city, state, ZIP code, area code, phone number
2. keys for the relation
Primary key = Social Security Number (PK)
Foreign key = Area code
In the normalization process, we can separate address table (street address, city, state, ZIP code, area code)
Can an area code straddle two states?
Yes, it can straddle between two states; it might be in cities/regions that “lie” across two states. For example, Kansas City is in Missouri and Kansas. A lot of people share the same area code.
Can a ZIP code straddle two area codes?
Yes, it can straddle between two states; it might be in cities/regions that “lie” across two states.
Can same Social Security number is allotted to two people?
NO, everyone has a unique SSN number.
Can peoples living together have the same address or phone number?
Yes, people living in the same house share addresses or phone numbers.
