What is BCNF?
BCNF can be expanded as Boyce Codd Normal Form, which is the fourth form level of normalization on a database. It is necessary to normalize the Multidimensional database systems up to the last level of normalization, until there is no more space for normalization to be carried out anymore. Normalization reduces or removes redundancy in the data, in turn to maintain the database without any duplicate values and bring in consistency to the data in the database. BCNF can be applied on a database that obeys two conditions, namely, it should be in 3NF stage and when at least one of the reference tables consists of a primary key.
A relation or a table which is in Boyce Codd Normal Form is by default considered to an in all the below forms.
- 1NF
- 2NF
- 3NF
There are other normal forms such as 4NF and 5NF but they are rarely used.
How does BCNF Work?
A table is said to be BCNF when it satisfies the below two concepts-
- It is in 3NF.
- For any functional dependency A->B, A should be a super key or a candidate key. In other words, if B is a prime attribute, A cannot be a non-prime attribute.
To understand more about BCNF, there are few concepts that need to be discussed such as keys and attributes.
- Attributes: Attributes that are a part of the candidate key are called prime attributes and the rest of the attributes are known as Non-prime attributes.
- Super Key: This is the combination of columns that will uniquely identify the rows in a table. A candidate key is selected from the given super keys based on the minimum number of attributes. And the primary key is one among the candidate keys.
Consider a Students table with the attributes- StudentID, Roll_no, Name
Super Key:
{StudentID}
{Roll_no}
{StudentID, Roll_no}
{StudentID, Name}
{Roll_no, Name}
{StudentID, Roll_no, Name}
Candidate Key:
{StudentID}
{Roll_no}
Primary Key:
{StudentID} or {Roll_no}
- Functional Dependency: It is the relationship between two attributes- one known as the determinant and one known as the dependant. An FD has the given representation X->Y which states that X determines Y.
Decomposition into BCNF
When a table is in 3NF, it may or may not be in the Boyce Codd Normal Form. Each table/relation will have a set of functional dependency and if the FD does not satisfy the second condition of BCNF the table is decomposed (breaking into smaller tables) recursively until all the functional dependency meets the super key criteria.
The algorithm to be followed for decomposition is,
- Determine the functional dependency that violates the BCNF.
- For every functional dependency X->Y which violates, decompose the relation into R-Y and XY. Here R is a relation.
- Repeat until all the relations satisfy BCNF.
Examples to Implement BCNF
Below are the examples to implement BCNF:
Example #1
Let’s consider a Relation R with five attributes.
R=ABCDE
The functional dependencies are
FD = {A -> BC, C -> DE)
Candidate keys are {A}
Algorithm:
Inspect each of the FD to check whether it satisfies the second condition of BCNF as it is in 3NF.
- The first FD A -> BC, since A is a key for R this FD does not violate BCNF.
- Second FD C -> DE, C is not a key of R. We decompose R into (CDE) (ABC).
The two schemas are created with the attributes of the FD which violates and the other with original attributes minus the right-hand side of the violating FD. Now we will check both the newly created relations to check whether they are in BCNF or not. A is the key in (ABC) and C is the key in (CDE) they do not violate BCNF. Thus the relation is in BCNF.
Example #2
Let’s consider a Relation R with five attributes.
R = (WXYZ)
The functional dependencies are
F = {WX -> Y, X -> Z; Y -> W}
4.7 (3,220 ratings)
View Course
Candidate keys are {WX, XY}
Algorithm:
- For the first FD WX -> Y, since WX is a key for R it does not violate BCNF.
- Second FD, X -> Z violates BCNF as X is not a key. Thus we create two relations (XZ) and (WXY).
Now inspect the given two relations for BCNF,
For (XZ) the candidate key is X. The only FD that applies here is X -> Z, so it is in BCNF.
For (WXY), the candidate keys are WX and XY.
- The first FD applies, WX -> Y, and WX is a key so it is in BCNF.
- The second FD doesn’t apply (there is no Z in it).
- The third FD Y->W, Y is not a super key thus we need to decompose by creating a new relation.
(XZ)(YW)(XY)
- XZ: The XZ is still in BCNF as before.
- YW: The YW has Y as the candidate key, and the only FD that applies is Y-> W. It is in BCNF.
- XY: The XY has XY as the candidate key, and no FDs apply, so it is in BCNF.
Thus our final decomposition is:
(XZ)(YW)(XY)
Advantages of BCNF
- It is a more restricted form of normalization. So that the database does not end in anomalies.
- The business rules which are expressed in the form of functional dependencies are enforced using keys and BCNF ensures that it is correctly followed.
Conclusion
The fourth and restricted form of normalization is BCNF and it makes sure that the table is in 3NF and for each functional dependency the determinant is a candidate or super key. If a relation is in BCNF, by default it will satisfy 1NF, 2NF, and 3NF. It ensures that for every functional dependency X->Y, X is a super key of the table which makes BCNF a stronger variation of 3NF.
Recommended Articles
This is a guide to BCNF. Here we discuss what is BCNF and how does it work, decomposition, advantages, and examples of BCNF. You may also look at the following articles to learn more-
