Second Normal Form - Partial dependency | Prime and non-prime attributes

Before we learn about the second normal form, we need to understand the following

Prime attribute − An attribute, which is a part of the candidate-key, is known as a prime attribute.

Non-prime attribute − An attribute, which is not a part of the prime- key, is said to be a non-prime attribute.

Partial Dependency occurs when a non-prime attribute is functionally dependent on part of a candidate key.

For the second normal form, the relation must first be in 1NF. The relation is automatically in 2NF if, and only if, the PK comprises a single attribute.

If the relation has a composite PK, then each non-key / non-prime attribute must be fully dependent on the entire PK and not on a subset of the PK (i.e., there must be no partial dependency or augmentation).

 Example:-

Consider the the relation R(ABCD) with FD's ={AB->C, B->D}. Is it in 2NF if not decompose it into 2NF.

Solution:

First we find the candidate key for the given relation, so that it is easy to find prime and non-prime attributes.

 R(ABCD)

 FD's ={AB->C, B->D}

Therefore the CK is AB and prime attributes are A, B and non-prime are C,D

Now check the given FD's weather they are fully dependent on CK or not.

1. AB->C ( Left is a CK so it is in 2NF)

2. B->D ( Let side is prime attribute and it determines non-prime attribute hence it is partial dependency not allowed in 2NF and it is problem making FD).

We decompose the relations into two relation as R1(BD) and R2(AC)

 Take the problem making FD and find closure of left side attribute 

R1(BD)

B->D

B+={B,D}

Key: B

R2(AC) 

Key: AB

 and to preserve dependency between attributes we keep key of R1 into R2. Then R2(ACB)