Boyce Codd Normal Form (BCNF) in DBMS

Imagine a supercharged version of database normalisation that helps you craft efficient, rock-solid databases with unprecedented reliability! That's what Boyce Codd Normal Form (BCNF) is, designed to tackle all those sneaky anomalies that can slip in through other normalisation levels. BCNF in Database Management System (DBMS) aims to eliminate redundancy and dependency anomalies by ensuring that every non-trivial functional dependency in a table is based on a superkey. 

This blog explores the importance of BCNF in DBMS and outlines, with examples, how it helps maintain data integrity and consistency. Read on to level up your Database Management skills. 

Table of Contents 

1) What is BCNF in DBMS? 

2) BCNF Rules 

3) Examples 

       a) Example 1 

        b) What Prevents This Table From Being in BCNF? 

         c) How to Satisfy BCNF? 

4) Conclusion 

What is BCNF in DBMS? 

BCNF in DBMS, often known as 3.5NF, is a higher level of normalisation than the third normal form (3NF). It is based on functional dependencies between attributes in a relation. Although it is very rare that a table satisfies the criteria for 3NF, it still needs to satisfy BCNF.  

The properties followed by BCNF in DBMS are as follows: 

1) It should already follow the properties of 3NF 

2) A->B, A must be a super key or candidate key for a functional dependency.  

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. This ensures that the data is consistent and free of redundancy. 

BCNF Rules 

For a relation to be in BCNF in DBMS, the following rules must be followed:  

1) Every determinant of the relation must be functionally dependent on all its attributes. 

2) 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. 

3) If the relationship has more than one candidate key, all the candidate keys must be irreducible. This means that no subset of the attributes can functionally determine any other attribute.  

Examples 

Now that you have a proper understanding of the rules and properties of BCNF in DBMS has been established, let’s explore this concept with some examples. 

Example 1 

Consider a table StudentCourse with the following attributes: 

What Prevents This Table From Being in BCNF? 

This table is not in BCNF because the attribute Instructor is dependent on CourseID, but CourseID is not a superkey. This leads to redundancy, as the same instructor appears multiple times for the same course. 

How to Satisfy BCNF? 

To satisfy BCNF, we can decompose the table into two tables: 

1) CourseInstructor Table: 

2) StudentCourse Table: 

This decomposition removes the partial dependency and satisfies BCNF. 

Lay down a strong foundation for your Database Management skills with our Introduction to Database Course – Sign up now! 

Example 2 

Consider a table ProjectAssignment with the following attributes: 

What Prevents This Table From Being in BCNF? 

This table violates BCNF because ProjectManager depends on ProjectID, but ProjectID is not a superkey. 

How to Satisfy BCNF? 

To satisfy BCNF, we can decompose the table into two tables: 

1) ProjectManager Table: 

2) EmployeeProject Table: 

This ensures that ProjectManager is only dependent on a superkey. 

Explore how to make fast APIs with GraphQL with our comprehensive GraphQL Database Training With React Course - Sign up now! 

Example 3 

Consider a table BookAuthors with the following attributes: 

What Prevents This Table From Being in BCNF? 

The table is not in BCNF because AuthorName depends on AuthorID, but AuthorID is not a superkey. 

How to Satisfy BCNF? 

To satisfy BCNF, we can decompose the table into two tables: 

1) AuthorDetails Table: 

2) BookAuthors Table: 

This decomposition eliminates the dependency problem and puts the table in BCNF. 

Conclusion 

BCNF is a critical step in database normalisation. By refining the third normal form (3NF), it ensures minimal redundancy and prevents anomalies. By mandating that every non-trivial functional dependency has a candidate key as its determinant, BCNF in DBMS promotes a more efficient and reliable database design. Having a firm grasp of this technique will help you expand your Database Management skills. 

Want to learn about modern Database technologies and their applications? Sign up for our Introduction To NoSQL Course now!