Search code examples
databaserelational-databasedatabase-normalization3nf

Deciding whether a relation is 3NF or 2NF

From the Database Management Systems book: given the relation SNLRWH (each letter denotes an attribute) and the following functional dependencies:

  • S->SNLRWH (S is the PK)
  • R->W

My attempt:

  • First, it is not 3NF: for the second FD, neither R contains W, nor R contains a key, nor W is part of a key.
  • Second, it is/not 2NF. If we examine the second FD, W is dependent on R, which in turn is not part of a key. STUCK.
Solution

  • 2NF is violated if some proper subset of a candidate key appears as a determinant on the left hand side of one of your (non-trivial) dependencies. Ask yourself whether any of your determinants is a subset of a candidate key.

    Usually 2NF is violated only when a relation has a composite key - a key with more than one attribute. It is technically possible for a relation with only simple keys (single attribute keys) to violate 2NF if the empty set (∅) happens to be a determinant. Such cases are fairly unusual and rarely thought worthy of consideration because they are so obviously "wrong". For completeness, here's a fun example of that special case. In the following relation Circumference and Diameter are both candidate keys. The dependency in violation of 2NF is ∅ -> Pi, the ratio of the circumference to the diameter.

    CIRCLE relation