Third Normal Form in DBMS

Question

I was just reading the definition of 3NF in DBMS, it states that:

The functional dependency X --> A is allowed if: 1. X is a super key. 2. A is part of some key.

I have 2 doubts:

a: I don't get the point of how is the second condition useful to remove the anomalies.Why have BCNF form, instead we can define 3NF form to not have the 2nd condition.

I get the point of removing the tranisitive dependency. If Y --> Z and X is the primary key. Then there is a transitive dependency as X --> Y --> Z means that we cant store values of (X,Y) unless we know Z.

b: Please explain how removing the 2nd condition in the 3NF would remove the anomalies and/or restrict redundancy.

Answer 1

"The functional dependency X --> A is allowed if: 1. X is a super key. 2. A is part of some key."

The second condition of your definition is wrong. A should be a non-prime attribute (i.e. it shouldn't be a part of the key to satisfy 3NF).

In fact better way to understand 3NF is -
given a functional dependency X->Y if Y is a non-prime attribute then X has to be a superkey.
(Notice that this ensures no transitive dependency)

On the other hand BCNF is-
for every functional dependency X->Y the left hand side (X) has to be a superkey (whether the right hand side(Y) is prime or not prime it doesn't matter)

Now for your first doubt

"Why have BCNF form, instead we can define 3NF form to not have the 2nd condition?" Yes if you remove the second condition of 3NF (that is Y should be non-prime attribute) then it is equivalent to BCNF. But why will you want to do that? There are times when you are not able to reduce a relation to BCNF ( because it is not always possible to get a BCNF decomposition that is dependency preserving ) then reducing it to 3NF is at least better then letting it to be in 2NF

For example-

R=(J,K,L)
F={JK->L,L->K}

Here we have 2 candidate keys - JK and JL and R is not in BCNF. Any decomposition of R will fail to preserve JK->L. This implies that testing for JK->L requires a join. So there are situations where -

• BCNF is not dependency preserving, and
• efficient checking for functional dependency violation on updates is important

Solution: define a weaker normal form, called 3NF. There is always a lossless-join, dependency-preserving decomposition into 3NF.

I don’t really get your second doubt - "how...3NF would remove the anomalies and/or restrict redundancy". 3NF would remove redundancy buy removing any transitive dependency!

This might be of some help too.