BCNF – Boyce Codd Normal Form

Boyce code norma form (BCNF) was proposed as a simple form of 3NF, but it was found stricter than 3NF because of every relation in BCNF is also in 3NF. however, a relation in 3NF is not necessarily in BCNF.

The BCNF can be defined in different ways given below.

A relation is in BCNF if and only if every nontrivial, left irreducible FD has a candidate key as its determinant.

In other words, a relation is in BCNF if and only if determinants are candidate keys.

A relation is said to be in BCNF, if whenever X->A holds in R and A is not in X, Then X is a candidate key for R.

In another way, the only arrows in the FD diagram are arrow out of candidate keys, and there are no other, meaning there are no arrows that can be eliminated by normalization procedure.

With reference to the blog 2NF and 3NF, the relation sale and S which are not in 3NF are not in BCNF, while the relation SP, SC, CS which are in 3NF, are also in BCNF. Because in relation sale, we have four determinants {S#}, {P#}, {City}, {S#, P#}, of this only {S#, P#} is a candidate key, so the sale is not in BCNF.

Similarly, in relation S, we have 2 determinants {S#}, {City}, and the city is not a candidate key. So it is also not in BCNF.

While relation SP, SC, and CS are each in BCNF because in each case the supplier candidate key is the only determinants in the relation.

Mostly relation that is in 3NF is also in BCNF. Infrequently, a 3NF relation is not in BCNF and this happens only if

  1. The candidate keys in the relation are a composite key.
  2. there are more than one candidate keys in the relation.
  3. The key is not disjoint, that is, some attributes in the keys are common.

Now we take another example that is in BCNF.

Table: Relation – Player

NameLanguageHobby
NitinHindiFootball
Nitin EnglishSwimming
Nitin HindiSwimming
Nitin EnglishFootball
JitinFrenchJogging
Jitin SpanishJogging

Here we are supposing name as a unique key. Here a player can know more than one languages and may have more than one hobbies too. So, each of the attributes is a primary key. So it is in BCNF. But this is also suffering from some anomalies, such as, if we want to add one more hobby of jitin that is singing, then we can do this in the following manner given below.

NameLanguageHobby
NitinHindi Football
Nitin EnglishFootball
Nitin Hindi Swimming
Nitin EnglishSwimming
JitinFrenchJogging
Jitin Spanish Jogging
Jitin French Jogging

But this would mean that jitin has hobby singing in French language but he does not know singing in the Spanish language. So, adding a tuple with hobby singing and language Spanish would result

NameLanguageHobby
NitinHindi Football
Nitin EnglishFootball
Nitin Hindi Swimming
Nitin EnglishSwimming
JitinFrenchJogging
Jitin Spanish Jogging
Jitin French Jogging
Jitin Spanish Jogging

BCNF faces some anomalies.

Insert:

As we want to add a single tuple then several others have to be added as given above.

Delete:

Similar to insertion, If we delete a single tuple, several other tuples also have to be deleted.

for example: if want to delete(Nitin, Hindi, Football) then (Nitin, English, Football) also have to be deleted.

Conclusion:

We can conclude it, in short, the classification of BCNF. Every relation that is in BCNF must be in 3NF, but every relation that it is in 3NF may or may not be in BCNF. If a relation is in BCNF then all the functional dependencies will have a super key at its left side. you can consider this as an introduction of a composite key.
IF if have a functional dependency x->y then x must be a superkey in that relation.

Hope! you would have enjoyed this informative blog post, Please feel free to write to us at a5theorys@gmail.com if you have any query regarding this blog.

Have a great time!