Search code examples
databasedatabase-normalizationfunctional-dependencies3nfcandidate-key

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


I've viewed this answer at Difference between 2nd normalization phase / normal form and 3rd normalization phase / normal form but I don't understand the terms used (non-trivial functional dependency, superkey).

Maybe 2nd normal form to relates to composite keys whereas 3rd normal form relates to primary keys.

2nd normal form: There's a composite key and all fields in the table must relate to both of the composite key fields. If something doesn't relate then it should be refactored into another table.

3rd normal form: Everything has to be dependent on the primary key. I guess that in 3rd normal form there's only 1 key rather than in 2nd normal form where there can be composite keys.


Solution

  • 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.)