CODEWITHSUNDEEP
database-designfunctional-dependencies
Oscar Wiigh
Oscar Wiigh

Reputation: 19

Constructing Functional Dependencies

If we were to have a relation with people in the US, including their name, social security number, street adress, city, state, ZIP code, area code, and phone number, then what functional dependencies would we expect the relation to hold?

My attempt is this: 

Social security number -> name
Phone number -> Social security number
Street adress, city -> State, ZIP, area code
Area code -> city

Is this correct?

Upvotes: 1

Views: 205

Answers (1)

nvogel
nvogel

Reputation: 25526

There is no objective right or wrong answer to a question like this. All we really have to go on are a few attribute names.

The point of a database design is that it accurately represents the intended interpretation of some domain of discourse. A properly designed database schema is a set of predicates that is fit for purpose in the context where it will be used. In practical database design, functional dependencies are business rules that you choose to enforce in a data model because they match the requirements of the business domain.

For example, in many contexts the FD {Phone number}→{X} is not a very realistic or useful rule and probably would not form part of most data models. For a phone company however, phone number is very important and it's quite likely that {Phone number}→{X} would be a part of some data models in a phone company, e.g. where X is a subscriber account number. As so often in database design, the answer is: it depends...

Assuming this is homework, if you have only the attribute names to go on then what matters is that you can explain your answer. Did you put phone number as a determinant for SSN because you believe that everyone with a SSN will always have a unique phone number? Why? What do you expect the values of the city attribute to consist of and why would they be a determinant for state?

Upvotes: 3

Related Questions