Second Normal Form; 2NF; why is not in 2NF

Question

I have a following problem.

I need to determine is the following assingment in the second normal form, and I have some problems understanding what the second normal form is, and how to determine it.

I have this example for learning/practicing.

R = ({A, B, C, D, E, F};{AB->ACD, BC->DE})

The primary key is ABF.

In the solutions it is written that the table is not in the second normal form because not every NKA(non key attribute) is dependent on KA(key attribute), and is also written

example: AB->C

I dont know how to get to this solution, or what AB->C has to do with anythoing(because it is not in the assingment).

Can somebody please explain this to me.

Thanks!!!

Answer 1

First let's define 2NF:

Let R be a relation schema with set of functional dependencies F. Let X be a set of attributes of R. A is a non-prime attribute not in X. Then R is in 2NF if whenever X->A is in F⁺, then X is not a proper subset of any key.

Now looking at your problem. Here R is your relation R(A,B,C,D,E,F) and F is {AB->ACD, BC->DE}. Primary key is ABF and therefore A, B and F are prime attributes and C, D and E are non-prime attributes.

Now let's find F⁺ (F-closure) , that is, set of all dependencies that are derivable from F.

We get F⁺ as {AB->A, AB->C, AB->D, BC->D ,BC->E, ...}

Now our definition above says that for any X->A in F⁺, X should not be a proper subset of the key.

Here one of the dependencies in F⁺ is AB->C . Here C is a non-prime attribute and AB is a proper subset of the key ABF. Therefore it violates second normal form.