Identifying the normal form that a certain relation complies with

normalizationrelational-theory

I have a little problem with a normalization exercise. I have the following relation:

R = {L,K,D,S,O,W}

which presents the functional dependencies (FD) shown below:

F = { {D,S,K} -> {O},
      {O} -> {K},
      {O,L} -> {W}
      {W,L,K} -> {O},
      {D,S,W} -> {L}}

I have to determine what normal form (NF) is satisfies. So I started from determining the candidate keys; they are:

{D,S,O,L}

{D,S,L,K}

{D,S,K,W}

{D,S,O,W}

Then, I worked from-top-to-bottom (not sure if it is good strategy).

  1. This relation isn't in BCNF because the left side of every FD does not contain key(s) e.g. {O} -> {K}

  2. My definition for 3NF was that, for every nontrivial, simple FD, the left side contains a key or the right side is part of a key (or it is a key). Everything matches and I thought that the relation R is in 3NF.

Then I look on the conditions to be in 2NF and saw that:

  1. This relation contains partial dependency e.g. {O} -> {K}

  2. This relation contains transitive dependency.

Transitive dependency is for every FD X -> A, X is a subset or superset of the key (is that right, but if X is a subset of the key then this is a partial dependecy)?

This dependency doesn't work for me {W,L,K} -> {O} – this is transitive. So, is relation R in 1NF? This is something weird, either I have a bad definition of 3NF or this relation is in 3NF… but it isn't in 2NF, which is impossible. What's wrong?

Best Answer

All attributes are prime, they belong to at least one candidate key.

Therefore, the relation is - trivially - in 3NF.

If you look at the requirements for both 2NF and 3NF, the exceptions start with "every non-prime attribute ...". Since the relation has 0 non-prime attributes, these requirements are trivially covered.

About your analysis, the first parts are OK (finding the candidate keys, that the relation is not in BCNF and that it is in 3NF).

The last part, about 2NF, shows confusion. Check again the 2NF definition and how do you define partial dependency and transitive dependency.

Related Question