BCNF

What is BCNF?

BCNF can be expanded as Boyce Codd Normal Form, the fourth form 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 inconsistency to the data in the database. It can be applied to 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-

1. It is in 3NF.
2. A->B, A should be a super key or a candidate key for any functional dependency. In other words, if B is a prime attribute, A cannot be a non-prime attribute.

To understand more, few concepts 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 the dependent. 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 dependencies. 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,

1. Determine the functional dependency that violates the BCNF.
2. For every functional dependency X->Y which violates, decompose the relation into R-Y and XY. Here R is a relation.
3. Repeat until all the relations satisfy BCNF.

Examples to Implement BCNF

Below are the examples:

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 FD attributes, 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 (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}

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.

1. The first FD applies, WX -> Y, and WX is a key, so it is in BCNF.
2. The second FD doesn’t apply (there is any Z in it).
3. 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

• It is a more restricted form of normalization so that the database does not end in anomalies.
• The business rules expressed in 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, it will satisfy 1NF, 2NF, and 3NF by default. It ensures that for every functional dependency X->Y, X is a super key of the table, making BCNF a stronger variation of 3NF.

Recommended Articles

This is a guide to BCNF. Here we discuss BCNF and how it works, decomposition, advantages, and examples. You may also look at the following articles to learn more-

1. Top 7 Types of DBMS Keys
2. What is Hashing in DBMS?
3. RDBMS Interview Questions
4. Normal Forms in DBMS