Question about 3rd Normal Form

Question

The following table :

EmpId  State   Zip
1       FL   342008
2       CA   342001

is not in 3rd normal form because State is dependent on the primary key(EmpId) transitively.

So, if I break it like this : (EmpId,State) (State,PIN)

it's in 3NF.

I could also break it like : (EmpId,State) (EmpId,PIN) and it will again be in 3NF.

But in the second case, there is redundancy of information, for e.g.

1  FL
2  FL

1 342008
2 342008

Which property does the second decomposition violate?

Answer 1

This doesn't directly address your question, but, strictly speaking, the state column is redundant, because it can be derived from the ZIP code (although you're showing six-digit ZIP codes, which aren't standard in the USA.) You could break the table down by EmpID and ZIP, leaving State by itself in a lookup table.

Answer 2

As already said, there is redundancy because the State can be inferred by the ZIP Code, hence, you should have your tables in this way:

EmpId Zip
1     342008

Zip    State
342008 FL

Always think on what depends on what. Of course, there is already an standard process to normalize based on set theory. This could help you too.

Answer 3

To directly address the question asked, the violated property is FFD (full functional dependency on the key).