Question

Consider a relation about people in the United States, including their name, Social Security number, street address, city, state, ZIP code, area code, and phone number (7 digits). What FD’s would you expect to hold? What are the keys for the relation? To answer this question, you need to know something about the way these numbers are assigned. For instance, can an area code straddle two states? Can a ZIP code straddle two area codes? Can two people have the same Social Security number? Can they have the same address or phone number?

Answer 1

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.