Last Updated on April 17, 2023 by Prepbytes
BCNF in DBMS stands for Boyce-Codd Normal Form which was formed in the 1970s by R.F Boyce and E.F Codd. It is a technique applied to normalize the table by removing any pre-existing redundancy and anomalies. As a result, it improves data integrity. Let us learn more about BCNF in DBMS.
What is BCNF in DBMS?
BCNF in DBMS is a higher level of normalization than the third normal form (3NF) and is often known as 3.5NF. It is based on functional dependencies between attributes in a relation. Although, it is very rare that a table satisfies the criteria for 3NF but does not satisfy BCNF.
The properties followed by BCNF in DBMS are as-
- It should already follow the properties of 3NF
- For a functional dependency, A->B, A must be a super key or candidate key.
As stated above in the properties, apart from already being in 3NF, every non-trivial functional dependency in the relation must have a super key as its determinant. Thus ensuring as a result that the data is free of redundancy and consistent.
Conditions for BCNF in DBMS
For a relation to be in BCNF in DBMS, the following conditions must be met:
- Every determinant (or candidate key) of the relation must be functionally dependent on all the attributes in the relation.
- There should be no non-trivial functional dependencies between the attributes of the relation, where non-trivial means that the dependent attribute is not a subset of any candidate key.
- If the relationship has more than one candidate key, all the candidate keys must be irreducible, meaning that no subset of the attributes can functionally determine any other attribute.
If the above, conditions are to be met, it is proved to be a relation that ensures that there is no redundancy or data inconsistency in the relation.
Example to Understand BCNF in DBMS
Now that we have learned about the basic properties of BCNF in DBMS, let us move on to look at the example for a better understanding of BCNF in DBMS and how BCNF in DBMS functions:
Let us suppose we have a table containing information about football clubs around the world with their respective city, countries, stadium names and stadium capacities given below:-
Table: Club Details
| Club | Stadium | City | Country | Stadium Capacity |
|---|---|---|---|---|
| Barcelona | Camp Nou | Barcelona | Spain | 99354 |
| Real Madrid | Santiago Bernabeu | Madrid | Spain | 81044 |
| PSG | Parc Des Princes | Paris | France | 48712 |
| Manchester United | Old Trafford | Manchester | England | 74879 |
| Bayern Munich | Allianz Arena | Munich | Germany | 75000 |
The primary key in the following table in the combination of Club Name and Stadium Name.
Let’s suppose we take a functional dependency (FD) from the table, City -> Country that justifies the City determines the Country uniquely. But since there can be many clubs from the same city, it does not qualify as a superkey. Hence, the table does not follow the properties of BCNF in DBMS.
But there is a way we can turn it into a BCNF-obeying table. We can decompose the table into two with one table consisting of City and Country with the City set as the Primary Key, the reason being that there can be multiple cities from a single country.
Now removing the Country Column from the City, we can the decomposed two tables as follows:-
Table: CityCountry
| City | Country |
|---|---|
| Barcelona | Spain |
| Madrid | Spain |
| Paris | France |
| Manchester | England |
| Munich | Germany |
The main table, after the removal of the Country column can be presented as:-
Table: Club Details
| Club | Stadium | City | Stadium Capacity |
|---|---|---|---|
| Barcelona | Camp Nou | Barcelona | 99354 |
| Real Madrid | Santiago Bernabeu | Madrid | 81044 |
| PSG | Parc Des Princes | Paris | 48712 |
| Manchester United | Old Trafford | Manchester | 74879 |
| Bayern Munich | Allianz Arena | Munich | 75000 |
On performing this, the table Club Details is in BCNF as all the non-trivial functional dependency has a super key as its determinant. By decomposing a relation into smaller tables, we can bring it to BCNF and improve the overall efficiency of the database.
Conclusion
In this article, we studied the topic of BCNF, got to know what is BCNF in DBMS, the properties it follows, and how it helps it improve data integrity and remove redundancy.
Later we moved to study the example of BCNF in DBMS to solidify the topic understanding. We expect you liked this article and hope to see you again with another piece of article from our side.
Frequently Asked Questions
Some Frequently Asked Questions related to “BCNF in DBMS” are given below.
1. What is BCNF in DBMS?
BCNF stands for Boyce-Codd Normal Form, which is a higher level of database normalization. It ensures that each determinant of a relation is a candidate key, which means that all non-trivial functional dependencies are eliminated.
2. Why is BCNF important in DBMS?
BCNF in DBMS is important because it helps to eliminate data redundancy and inconsistencies in a database. By ensuring that each determinant of a relation is a candidate key, BCNF in DBMS helps to prevent update anomalies and maintain data integrity.
3. How do you check if a relation is in BCNF?
To check if a relation is in BCNF, you need to identify all the functional dependencies in the relation and check if each determinant is a candidate key. If any determinant is not a candidate key, then the relation is not in BCNF and needs to be decomposed into smaller, normalized relations.
4. Differentiate between 3NF and BCNF in DBMS?
3NF (Third Normal Form) and BCNF in DBMS are both levels of database normalization, but BCNF is a higher le vel than 3NF. 3NF requires that every non-key attribute is dependent on the primary key, while BCNF requires that every determinant of a relation is a candidate key.
5. When should you use BCNF in database design?
You should use BCNF in database design when you need to ensure that each determinant of a relation is a candidate key. This is important for maintaining data integrity and preventing update anomalies, especially in complex databases with many interrelated tables. However, BCNF may not always be necessary or practical, depending on the specific requirements of the database and the trade-offs between normalization and performance.
6. Is BCNF Normalized table completely free from redundancy?
If a table has multiple candidate keys, the data may be duplicated across the different candidate keys. In addition, in some cases, it may be necessary to join multiple tables together to retrieve all the required data, which can lead to some level of redundancy. However, in general, a table that is in BCNF should have a minimal amount of redundancy compared to tables in lower normal forms.