Normalization in DBMS

Boyce–Codd Normal Form (BCNF) and Higher Normal Forms

In the previous article, we studied Second Normal Form (2NF) and Third Normal Form (3NF).
3NF removes partial and transitive dependencies, and in most practical cases, it is sufficient.

However, there are situations where a relation satisfies 3NF but still has redundancy. To handle such cases, we use Boyce–Codd Normal Form (BCNF).

This article explains BCNF carefully with proper reasoning and examples.

1. Why 3NF Is Sometimes Not Enough

A relation is in 3NF if:

For every functional dependency X → Y, at least one of the following is true:

  1. X is a super key
  2. Y is a prime attribute (part of some candidate key)

Because of the second condition, some dependencies are allowed even when the determinant is not a super key.

This can still lead to redundancy.

BCNF removes this relaxation and applies a stricter rule.

2. Boyce–Codd Normal Form (BCNF)

2.1 Definition

A relation is in BCNF if:

For every non-trivial functional dependency X → Y,
X must be a super key.

This is stricter than 3NF.

In simple terms:

Every determinant must be a candidate key.

3. Difference Between 3NF and BCNF

Feature

3NF

BCNF

Removes Partial Dependency

Yes

Yes

Removes Transitive Dependency

Yes

Yes

Allows a determinant that is not a key (if the RHS is prime)

Yes

No

Stricter Normal Form

No

Yes

BCNF eliminates certain anomalies that 3NF allows.

4. Example Where Relation Is in 3NF but Not in BCNF

Consider the relation:

R(Student, Course, Instructor)

Assume the following rules:

  1. A student can enroll in multiple courses.
  2. Each course has exactly one instructor.
  3. An instructor teaches only one course.

Functional Dependencies:

(Student, Course) → Instructor
Instructor → Course

Let us analyze.

Step 1: Find Candidate Keys

From:

Instructor → Course

Since each instructor teaches only one course.

(Student, Instructor) can determine Course (using Instructor → Course).

Thus, (Student, Instructor) is a candidate key.

Also, (Student, Course) determines Instructor.

So we have multiple candidate keys.

Step 2: Check 3NF

Dependency:

Instructor → Course

Here:

  • Instructor is not a super key.
  • The course is part of a candidate key (prime attribute).

So it satisfies 3NF.

Thus, the relation is in 3NF.

Step 3: Check BCNF

BCNF requires:

Every determinant must be a super key.

But:

Instructor → Course

Instructor is not a super key.

Therefore:

The relation is NOT in BCNF.

5. Converting to BCNF

To convert into BCNF, decompose the relation.

Step 1: Create Instructor Table

Instructor → Course

Instructor

Course

I1

DBMS

I2

OS

Step 2: Create Enrollment Table

Student

Instructor

S1

I1

S2

I2

Now:

  • Every determinant is a key.
  • The relation satisfies BCNF.

Redundancy is removed.

6. Steps to Check BCNF

To verify whether a relation is in BCNF:

  1. List all functional dependencies.
  2. Find all candidate keys.
  3. For each FD X → Y:
    • Check if X is a super key.
  4. If any determinant is not a super key, decompose.

7. Fourth Normal Form (4NF) – Brief Introduction

Even BCNF may not handle all redundancy problems.

4NF deals with Multivalued Dependencies (MVD).

A relation is in 4NF if:

For every non-trivial multivalued dependency X →→ Y,
X must be a super key.

Example scenario:

If a student can have multiple hobbies and multiple languages independently, storing both in one table causes redundancy.

4NF separates independent multivalued facts into different tables.

In most practical designs, BCNF is usually sufficient.

8. Fifth Normal Form (5NF) – Brief Overview

5NF deals with Join Dependencies.

A relation is in 5NF if it cannot be further decomposed without losing information.

It is rarely required in practical database design and mostly appears in advanced theoretical discussions.

9. Practical Perspective

In real-world database systems:

  • 1NF ensures structured data.
  • 2NF removes partial dependency.
  • 3NF removes transitive dependency.
  • BCNF ensures strong consistency.
  • 4NF and 5NF are rarely needed in common applications.

Most production databases are designed up to 3NF or BCNF.

Summary

  • 3NF may still allow some redundancy.
  • BCNF is stricter than 3NF.
  • In BCNF, every determinant must be a super key.
  • BCNF eliminates anomalies that 3NF may allow.
  • 4NF handles multivalued dependency.
  • 5NF handles join dependency.