Boyce Codd Normal Form (BCNF) in DBMS

What You Should Know

1. BCNF is a stricter form of 3NF used in relational database normalisation

2. A relation can satisfy 3NF without necessarily satisfying BCNF

3. BCNF focuses on how determinants and superkeys relate within a relation

4. Relations that violate BCNF can often be decomposed into smaller relations

5. BCNF decomposition helps organise related data into appropriately structured relations

Imagine a supercharged version of database normalisation that helps you build efficient, reliable databases. That’s what Boyce Codd Normal Form (BCNF) offers. In Database Management Systems (DBMS), BCNF reduces redundancy associated with functional dependencies by ensuring that every non-trivial functional dependency has a superkey as its determinant.

This blog explores the importance of BCNF in DBMS and uses practical examples to explain its rules, identify violations and demonstrate how relations can be decomposed to satisfy BCNF.

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. Every relation in BCNF is also in 3NF, but a relation can satisfy 3NF without satisfying BCNF.

The properties followed by BCNF in a DBMS are as follows: 

1) It should already satisfy 3NF

2) For every non-trivial functional dependency A → B, A must be a superkey

These conditions make BCNF stricter than 3NF and help prevent anomalies caused by determinants that are not superkeys.

BCNF Rules

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

1) For every non-trivial functional dependency X → Y, X must be a superkey of the relation. If it is not, the relation violates BCNF.

2) A functional dependency X → Y is non-trivial when Y is not a subset of X.

Trainer Insight

When checking BCNF, focus on the left-hand side of each non-trivial functional dependency. If that determinant cannot uniquely identify every attribute in the relation, it is not a superkey and the dependency violates BCNF.

Examples

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

Example 1

Consider a table StudentCourse with the following attributes:

What Prevents This Table From Being in BCNF?

If each CourseID has one Instructor, and CourseID does not identify every attribute in StudentCourse, CourseID → Instructor is a non-trivial functional dependency whose determinant 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 separates the BCNF-violating dependency into a relation where CourseID is a key, allowing the resulting relations to satisfy BCNF, assuming the stated functional dependencies are complete.

Learn about database approaches and database management systems with our Relational Databases & Data Modelling Training – Join now!

Example 2

Consider a table ProjectAssignment with the following attributes:

What Prevents This Table From Being in BCNF?

If each ProjectID determines one ProjectManager, but does not identify every attribute in ProjectAssignment, ProjectID → ProjectManager is a non-trivial functional dependency whose determinant is not a superkey.

How to Satisfy BCNF?

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

1) Project Manager Table:

2) EmployeeProject Table:

After decomposition, the relevant determinants are superkeys in their respective relations, satisfying the BCNF condition.

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 AuthorID → AuthorName is a non-trivial functional dependency and 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 places AuthorID → AuthorName in a relation where AuthorID is a key, resolving the BCNF violation.

Common Mistake

Do not assume that being in 3NF automatically means a relation is in BCNF. BCNF applies the stricter requirement that the determinant of every non-trivial functional dependency must be a superkey.

Gain hands-on experience in managing non-relational data with our Introduction to NoSQL Course – Register now!