Boyce–Codd Normal Form (BCNF)

 

1.Boyce–Codd Normal Form (BCNF)

Introduction

Boyce–Codd Normal Form (BCNF) is a stronger and stricter version of Third Normal Form (3NF).

  • Every relation in BCNF is also in 3NF

  • But a relation in 3NF may not be in BCNF

BCNF was introduced because 3NF sometimes still allows certain dependencies that may cause redundancy.

 2.Formal Definition of BCNF

A relation schema R is in BCNF if:

For every non-trivial functional dependency
X → A,
X must be a superkey of R.

Important Points

  • Non-trivial FD: A is not part of X.

  • Superkey: An attribute set that uniquely identifies tuples.

So BCNF rule:

If X → A
then X must be a superkey

If X is not a superkey, the relation violates BCNF.

3.Difference Between 3NF and BCNF

3NF Rule

A dependency X → A is allowed if:

  1. X is a superkey, OR

  2. A is a prime attribute

BCNF Rule

A dependency X → A is allowed only if:

X is a superkey

BCNF removes the second condition.

Comparison Table

Normal FormCondition
3NF    X is superkey OR A is prime attribute
BCNF    X must be superkey

Thus BCNF is stricter.

4.Example Using LOTS Relation

Consider relation:

LOTS1A(Property_id#, County_name, Lot#, Area)

Assume a new dependency:

FD5: Area → County_name

Meaning:

  • Lot area determines the county

Example assumption:

Assume the database stores thousands of land lots, but they belong to only two counties:

  • DeKalb

  • Fulton

Each county allows specific lot sizes (areas).

Allowed Lot Sizes

County        Possible Lot Sizes (acres)
DeKalb        0.5, 0.6, 0.7, 0.8, 0.9, 1.0
Fulton        1.1, 1.2, 1.3, … , 1.9, 2.0

This means:

  • If the area of a lot is known, we can determine which county it belongs to.

Why This Creates a Functional Dependency

Because each area value belongs to only one county, the following dependency exists:

Area → County_name

Meaning:

  • Area determines the County_name

Examples:

Area    County_name
0.5    DeKalb
0.7    DeKalb
1.3    Fulton
1.8    Fulton

So whenever we know Area, we can automatically know the County_name.

Why This Matters in Normalization

If the LOTS table has thousands of records, the same relationship will repeat many times:

Example:

Property_id    Area    County_name
    P1    0.5        DeKalb
    P2    0.6        DeKalb
    P3    1.3        Fulton
    P4    1.5        Fulton

The same area–county information repeats many times, causing data redundancy.

Better Design

Instead of repeating this information, we can store it in a separate relation:

AREA_COUNTY Table

Area    County_name
0.5    DeKalb
0.6    DeKalb
0.7    DeKalb
...    ...
1.9        Fulton
2.0    Fulton

There are only 16 possible area values, so this table would contain just 16 rows, instead of repeating the same information in thousands of rows.

Key Idea

Because Area uniquely determines County_name, the dependency

Area → County_name

exists.

This dependency violates BCNF because:

  • Area is not a superkey

  • Yet it determines another attribute.

Therefore, BCNF suggests decomposing the relation to remove redundancy.


Why LOTS1A is Still in 3NF

Dependency:

Area → County_name

Check 3NF conditions:

Condition        Result
Area is superkey        ❌ No
County_name is prime attribute        ✅ Yes

Since County_name is part of candidate key, 3NF allows this dependency.

But BCNF Rejects It

BCNF requires:

X must be a superkey

But:

Area is NOT a superkey

Therefore LOTS1A violates BCNF.

5. BCNF Decomposition

To remove the BCNF violation, split the relation.

Relation 1

LOTS1AX(Area, County_name)

Dependency:

Area → County_name

Relation 2

LOTS1AY(Property_id#, Lot#, Area)

Now both relations satisfy BCNF.



6. Functional Dependency Loss

One problem with BCNF decomposition:

Some functional dependencies may be lost.

Example:

FD2: {County_name, Lot#} → Property_id#

After decomposition:

  • These attributes may not exist together

  • So the dependency cannot be enforced directly

Thus BCNF may lose dependency preservation.

7. Example: TEACH Relation

Relation:

TEACH(Student, Course, Instructor)

Functional dependencies:

FD1: {Student, Course} → Instructor
FD2: Instructor → Course

Candidate Key

(Student, Course)

Check 3NF

Dependency:

Instructor → Course

  • Instructor is not a superkey

  • Course is prime attribute

Thus 3NF allows it.

So relation is in 3NF.

But It Violates BCNF

Because:

Instructor → Course

Instructor is not a superkey.

So BCNF is violated.



8. Possible Decompositions

To achieve BCNF, the relation must be decomposed into smaller relations.

Possible decompositions:

Option 1

R1(Student, Instructor)
R2(Student, Course)

Option 2

R1(Course, Instructor)
R2(Course, Student)

Option 3

R1(Instructor, Course)
R2(Instructor, Student)


9. Problem: Functional Dependency Loss

All three decompositions lose FD1:

(Student, Course) → Instructor

Because the attributes Student, Course, Instructor are no longer together in one table.

However, during normalization two properties must be considered:

  1. Lossless (Nonadditive) Join

  2. Dependency Preservation

If we must choose, lossless join is mandatory.

10. Nonadditive Join (Lossless Join)

A decomposition is lossless if the original table can be reconstructed without creating incorrect tuples.

To test this, we use the Nonadditive Join Test for Binary Decompositions(NJB).

11. NJB Test Rule

For decomposition:

R → R1, R2

The join is lossless if:

(R1 ∩ R2) → (R1 − R2) in F+
OR
(R1 ∩ R2) → (R2 − R1) in F+

The notation F+ refers to the cover of the set of functional dependencies and includes all 
f.d.’s implied by F.


12. Apply NJB Test

For Option 3 ( other two fails)

Relations:

R1(Instructor, Course)
R2(Instructor, Student)

Common attribute:

Instructor

So:

R1 ∩ R2 = Instructor

R1-R2=Course

Check:

Instructor → Course

This dependency exists (FD2).

Therefore the NJB condition is satisfied.

So Option 3 gives a lossless decomposition.

12. Final BCNF Relations

The correct decomposition is:

TEACH1

(Instructor, Course)

TEACH2

(Instructor, Student)

These relations are in BCNF.


13. BCNF Decomposition Procedure

General method:

  1. Find a functional dependency that violates BCNF.

  2. Suppose:

        X → A

  1. Decompose relation R into:

        R1 = (R − A)

        R2 = (X ∪ A)

  1. Check each relation again.

  2. Repeat until all relations are in BCNF.

14. Important Observation

If we choose (Student, Instructor) as the primary key:

(Student, Instructor)

Then dependency:

Instructor → Course

creates a partial dependency.

This can also be removed during 2NF normalization.

So sometimes different normalization paths lead to the same final BCNF design.

Comments

Post a Comment

Popular posts from this blog

Database Management Systems DBMS PCCST402 Semester 4 KTU CS 2024 Scheme

 About Me Database Management Systems DBMS PCCST402 Scheme and Syllabus Model Question Paper - KTU BTech DBMS PCCST402 2024 Scheme Module-1 Introduction to DBMS Introduction to DBMS Example  Database Database System Vs Traditional File Processing Advantages of using DBMS Database System Concepts and Architecture Data Models, Schemas and Instances   Three schema architecture and Data Independence Database Languages and Interfaces Centralized and Client Server Architectures in DBMS Classification of Database Management Systems Conceptual Data Modeling and Database Design Conceptual Modeling and the Role of the ER Model in Database Design Entities and Attributes  Company Database Application - Example Entity Types, Entity Sets, Keys, and Value Sets in the ER Model Initial Conceptual Design of the COMPANY Database Relationship Types in the ER Model Relationship Degree, Role Names, and Recursive Relationships Constraints on Binary Relationship Types Attributes of Relation...
Read more

Introduction to Database Management System -DBMS

Image
In today’s digital world, almost every application—from mobile banking and e-commerce to university portals and hospital systems—depends on the efficient storage, organization, and retrieval of data. As the amount of information generated by individuals and organizations continues to grow, managing this data in a structured and meaningful way becomes essential. This is where database systems play a central role. A database can be thought of as a well-organized collection of related data, designed so that it can be easily accessed, managed, and updated. But the database itself is only one component. To manage it effectively, we rely on a specialized software package known as a Database Management System (DBMS) . A DBMS provides a systematic and automated way to store large volumes of data, enforce rules for consistency and correctness, protect data from unauthorized access, and allow multiple users to work with the data simultaneously without conflicts. A database typically has three...
Read more

Data Models, Schemas and Instances

  Data Models, Schemas and Instances  1. Data Abstraction In databases, data abstraction means hiding the complex details of how data is stored and focusing only on what is important. Different users can view data at different levels of detail depending on their needs. 2. Data Models A data model is a collection of concepts used to describe the structure of a database. It defines: Data types Relationships among data Constraints on data Operations for accessing and updating data Data models help us understand and design databases without worrying about low-level storage details. Categories of Data Models a) High-level / Conceptual Data Models Close to how users think about data. Use concepts like entities , attributes , and relationships . Example: ER (Entity–Relationship) Model . b) Representational (Implementation) Data Models In between conceptual and physical models. Easy for users to understand but still close to how data is st...
Read more