Normal forms – 2nd vs 3rd – is the difference just composite keys? non trivial dependency?

We write S -> T and say that a set of columns S functionally determines another set T. S -> T in a table value when each subrow for the first always appears with the same subrow for the second. S -> T in a table variable/schema when S -> T in every value that can arise for it per the constraints.

We say S is the determinant set and T is the determined set. We call S -> T a FD (functional dependency). When S is a superset of T we say it is a trivial FD. When S is {A} we say A -> T & when T is {A} we say S -> A.

A superkey is a set of columns that uniquely identifies rows. That is so when it functionally determines every attribute. A proper subset or superset of a set is one that is not equal to it. A CK (candidate key) is a superkey that contains no proper superkey. We can pick a CK as PK (primary key). A column is prime when it is in some CK.

That’s enough to understand the answer that you link to:

The difference between 2NF and 3NF is this. Suppose that some relation
satisfies a non-trivial functional dependency of the form A->B, where
B is a nonprime attribute.

2NF is violated if A is not a superkey but is a proper subset of a
candidate key

3NF is violated if A is not a superkey

The quote says a NF “is violated if” such an FD exists. Also it’s only violated if such an FD exists.

A FD S -> T is partial when a proper subset of S also functionally determines T; otherwise it is full. Note that this does not involve CKs. A table is in 2NF when every non-prime column is fully functionally dependent on every CK.

S -> T is transitive when there is an X where S -> X and X -> T and not X -> S and not X = T. Note that this does not involve CKs. A table is in 3NF when every non-prime column is non-transitively dependent on every CK.

(Note the alternate definitions of 2NF & 3NF different from the quote.)

Leave a Comment

tech