BCNF- What is the 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 e
very 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 are 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 others, meaning there are no arrows that can be eliminated by the normalization procedure.
Concerning the blogs 2NF and 3NF, the relation sale and S which are not in 3NF are not in BCNF, while the relation SP, SC, and CS which are in 3NF, are also in BCNF.
Because about 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, about 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.
Most relations that are in 3NF are also in BCNF. Infrequently, a 3NF relation is not in BCNF and this happens only if
- The candidate keys in the relation are composite.
- there is more than one candidate key in the relationship.
- 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
| Name | Language | Hobby |
| Nitin | Hindi | Football |
| Nitin | English | Swimming |
| Nitin | Hindi | Swimming |
| Nitin | English | Football |
| Jitin | French | Jogging |
| Jitin | Spanish | Jogging |
Here we are supposing a 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 which is singing, then we can do this in the following manner given below.
| Name | Language | Hobby |
| Nitin | Hindi | Football |
| Nitin | English | Football |
| Nitin | Hindi | Swimming |
| Nitin | English | Swimming |
| Jitin | French | Jogging |
| Jitin | Spanish | Jogging |
| Jitin | French | Singing |
This would mean that Justin has a hobby of singing in the French language but he does not know how to sing in the Spanish language. So, adding a tuple with hobby singing and language Spanish would result
| Name | Language | Hobby |
| Nitin | Hindi | Football |
| Nitin | English | Football |
| Nitin | Hindi | Swimming |
| Nitin | English | Swimming |
| Jitin | French | Jogging |
| 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, and Football) then (Nitin, English, and 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?
You can also go through a few more amazing blog links related to DBMS below:
Composite key: Composite key vs Super key…
What is the null value Problem in DBMS…
What is the super key in DBMS…
What is a candidate key in DBMS…
What is Key in DBMS…
BCNF – Boyce Codd Normal Form…
3NF – Third normal form…
2NF – Second Normal Form…
What is1NF(First Normal Form)…
What is normalization with an example…
What do you mean by distributed DBMS and what are its types…
What is DBMS ARCHITECTURE…
DBMS vs RDBMS: What is the main difference between DBMS and RDBMS…
What are the functions of DBA in DBMS…
Integrity constraints 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 relationship that 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 on its left side. you can consider this as an introduction to a composite key. If it has 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 a5theorys@gmail.com if you have any queries regarding this blog.
Have a great time!
