BCNF – Boyce Codd Normal Form / What is BCNF normal form example?

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

Here in this blog post(What is BCNF normal form example), we will explore What is BCNF normal from an example? How do I convert to BCNF? How is BCNF related to other normal forms? What is BCNF and 4nf with example?……..

…Boyce-Codd normal form example pdf, Fourth normal form, BCNF in DBMS in Hindi, BCNF vs 3NF, BCNF decomposition.

What is BCNF normal form example: 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 arrows out of candidate keys, and there are no other, meaning there are no arrows that can be eliminated by the 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 to the 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 to 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 determinant 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 language and may have more than one hobby 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 Singing

But this would mean that Justin has a hobby of 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 Singing

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.

You can check out some amazing blog posts related to this topic:

What is Normalization and why is it needed?

What is 1NF in DBMS?

What is 2NF in DBMS?

What is 3NF in DBMS?

Conclusion:

We can conclude it, in short, the classification of BCNF(What is BCNF normal form example). 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.

Using this blog post(What is BCNF normal form example) we have gone through What is BCNF normal form example? How do I convert to BCNF? How is BCNF related to other normal forms? What are BCNF and 4nf for example? Boyce-Codd normal form example pdf Fourth normal form, BCNF in DBMS in Hindi, BCNF vs 3NF, BCNF decomposition.

Hope! you would have enjoyed this informative blog post(What is BCNF normal form example), Please feel free to write to us at [email protected] if you have any queries regarding this blog.

Have a great time!

Anurag

I am a blogger by passion, a software engineer by profession, a singer by consideration and rest of things that I do is for my destination.