CODEWITHSUNDEEP
databasedatabase-designnormalizationdatabase-normalizationfunctional-dependencies
Firefox
Firefox

Reputation: 951

database functional dependency decomposition

Table - Person {ID, Name, Age, Line1, City, State, Zip}

FD set

1) ID -> every other attribute as it is PK

2) I'm not able to determine whether 

 zip -> {Line1, City, State} or.. 

{Line1, City, State} -> zip?  

[both of these are candidate keys I guess]

In either case, it becomes transitive dependency since

ID -> Zip -> other address (or ID -> address related -> Zip).

It violates 3NF (transitive dependency).

Could you please explain how do I decompose the given relation, and what becomes PK in the other relation containing address related.

Upvotes: 1

Views: 2982

Answers (2)

nvogel
nvogel

Reputation: 25526

Assuming {Line1, City, State}->{Zip} and {Zip}->{City, State} then the following decomposition is in 3NF:

Person {ID, Name, Age, Line1, Zip} (key= {ID})
Address {City, State, Zip} (keys = {City, State} and {Zip])

In practice that may not be useful because real address data is often inconsistent or has parts missing. The real question is which dependencies you actually want to enforcein your database. That is why exercises that depend on identifying dependencies only from a list of attribute names are so highly subjective. The only way to give a definitive answer is to start with the set of dependencies which you want the schema to satisfy.

Upvotes: 1

Raihan
Raihan

Reputation: 10405

If you know (Line1, City, State) you can determine zip. So,

{Line1, City, State} -> zip

Not the other way around. Because the same zip may contain multiple Line1 values for the same City and State (e.g. different house numbers on the same street).

For 3NF the relations can be

  • Person {ID, Name, Age, Line1, City, State}
  • Address {Line1, City, State, Zip}

From practicality it seems redundent and waste of space in database tables.

Upvotes: 1

Related Questions