database functional dependency decomposition

Question

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.

Answer 1

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.