BCNF in DBMS

Boyce-Codd Normal Form (BCNF) is a stronger version of Third Normal Form (3NF) used in database normalization. It minimizes data redundancy and improves data consistency by ensuring that the left-hand side (determinant) of every functional dependency is a super key or candidate key. BCNF is especially useful for handling situations where a relation has multiple candidate keys.

Note: A candidate key is a minimal super key.

Rules for BCNF

• Rule 1: The relation must satisfy the requirements of Third Normal Form (3NF).
• Rule 2: For every non-trivial functional dependency X → Y, X (the determinant) must be a candidate key or super key.

Trivial Functional Dependency	Non-Trivial Functional Dependency
Definition: The RHS is a subset of the LHS.	Definition: The RHS is not a subset of the LHS.
Example: AB → A (A is already part of AB)	Example: A → B (B is not part of A)
Always true by definition.	Represents a meaningful dependency between attributes.
Ignored when checking BCNF because it does not cause a BCNF violation.	It is checked to see whether it satisfies the BCNF rule or not

Key Points

• To check whether a relation is in BCNF, identify all determinants (the left-hand side of each functional dependency) and verify that each determinant is a candidate key or super key.
• Every relation in BCNF is also in 3NF, 2NF, and 1NF.

Progression of Normal Forms

As we move from 1NF to BCNF, the normalization rules become stricter and reduce more redundancy.

• 1NF: Every attribute contains only atomic (indivisible) values.
• 2NF: The relation has no partial dependency on any candidate key.
• 3NF: The relation has no transitive dependency on a candidate key.
• BCNF: Every determinant (left-hand side of a functional dependency) must be a candidate key or super key.

Why Do We Need BCNF?

Although 2NF and 3NF remove many types of redundancy, some anomalies can still remain when a functional dependency has a determinant that is not a super key. BCNF eliminates these remaining issues by applying a stricter rule.

Benefits of BCNF:

• Reduces data redundancy more effectively than 3NF.
• Eliminates update, insert, and delete anomalies.
• Handles relations with multiple candidate keys more efficiently.
• Improves data consistency and integrity.
• Produces a cleaner, more reliable, and easier-to-maintain database design.

In the following sections, we will solve several examples to understand the properties and application of Boyce-Codd Normal Form (BCNF).

BCNF Example 01:

Consider the relation R(A, B, C, D), which is already in 3NF with the functional dependencies (FDs). Determine whether it is in BCFN or not.

• C→B
• BC→A
• A→C
• BD → A

Solution

Let’s calculate the candidate keys from the given FD’s

• Candidate Keys (CK): {AD}, {BD}, {CD}

Following the diagram, explain Example 1 in detail

Let’s explain the above diagram

FD 1: C → B

• LHS (C) is neither a candidate key nor a superkey. So, it is not a valid FD for BCNF.

This functional dependency does not satisfy the condition for BCNF.

FD 2: BC → A

• LHS (BC) is not a candidate key or super key. So, it is not a valid FD for BCNF.

This functional dependency does not satisfy the condition for BCNF.

FD 3: A→ C

• LHS (A) is neither a candidate key nor a superkey. So, it is not a valid FD for BCNF.

This functional dependency does not satisfy the condition for BCNF.

FD 4: BD→ A

• LHS (BD) is a candidate key. So, it is a valid FD for BCNF.

This functional dependency satisfies the condition for BCNF.

Result:

The relation is not in BCNF. Since FD 1 (C → B), FD 2 (BC → A), and FD 3 (A → C) do not satisfy the BCNF condition, only FD 4 (BD → A) satisfies the BCNF condition. Note that all given FD’s are non-trivial

BCNF Example 02:

Consider the relation R(A, B, C, D), which is already in 3NF with the functional dependencies (FDs). Determine whether it is in BCNF or not.

• BD→C
• AB→D
• AC→B
• BD → A

Solution

Let’s calculate the candidate keys from the given FD’s.

• Candidate Keys (CK): {AB}, {AC}, {BD}

Following the diagram, explain Example 2 in detail.

Let’s explain the above diagram

FD 1: BD → C

• LHS (BD) is a candidate key. So, it is a valid FD for BCNF.

This functional dependency satisfies the condition for BCNF.

FD 2: AB → D

• LHS (AB) is a candidate key. So, it is a valid FD for BCNF.

This functional dependency satisfies the condition for BCNF.

FD 3: AC → B

• LHS (AC) is a candidate key. So, it is a valid FD for BCNF.

This functional dependency satisfies the condition for BCNF.

FD 4: BD → A

• LHS (BD) is a candidate key. So, it is a valid FD for BCNF.

This functional dependency satisfies the condition for BCNF.

Result:

The relation is in BCNF. All the given functional dependencies are non-trivial, and the left-hand side of each functional dependency is a candidate key. Therefore, all functional dependencies satisfy the BCNF condition.

BCNF Example 03:

Consider the relation R(A, B, C, D), which is already in 3NF with the functional dependencies (FDs). Determine whether it is in BCNF or not.

• A→D
• C→A
• D→B
• AC→ B

Solution

Let’s calculate the candidate keys from the given FD’s.

Candidate Keys (CK): {C}

Following the diagram, explain Example 3 in detail.

Let’s explain the above diagram

FD 1: A → D

• LHS (A) is neither a candidate key nor a super key. So, it is not a valid FD for BCNF.

This functional dependency does not satisfy the condition for BCNF.

FD 2: C → A

• LHS (C) is a candidate key. So, it is a valid FD for BCNF.

This functional dependency satisfies the condition for BCNF.

FD 3: D → B

• LHS (D) is neither a candidate key nor a super key. So, it is not a valid FD for BCNF.

This functional dependency does not satisfy the condition for BCNF.

FD 4: AC → B

• LHS (AC) is a super key because it contains the candidate key C. So, it is a valid FD for BCNF.

This functional dependency satisfies the condition for BCNF.

Result:

The relation is not in BCNF. Since FD 1 (A → D) and FD 3 (D → B) do not satisfy the BCNF condition, only FD 2 (C → A) and FD 4 (AC → B) satisfy the BCNF condition.

 

BCNF Example 04:

Consider the relation R(A, B, C, D, E) which is already in 3NF with the functional dependencies (FDs). Determine whether it is in BCNF or not.

• ABC→D
• CD→A
• AD→E

Solution

Let’s calculate the candidate keys from the given FD’s.

Candidate Keys (CK): {ABC}, {BCD}

Following the diagram, explain Example 4 in detail.

Let’s explain the above diagram

FD 1: ABC → D

• LHS (ABC) is a candidate key. So, it is a valid FD for BCNF.

This functional dependency satisfies the condition for BCNF.

FD 2: CD → A

• LHS (CD) is neither a candidate key nor a super key. So, it is not a valid FD for BCNF.

This functional dependency does not satisfy the condition for BCNF.

FD 3: AD → E

• LHS (AD) is neither a candidate key nor a super key. So, it is not a valid FD for BCNF.

This functional dependency does not satisfy the condition for BCNF.

Result:

The relation is not in BCNF. Since FD 2 (CD → A) and FD 3 (AD → E) do not satisfy the BCNF condition, only FD 1 (ABC → D) satisfies the BCNF condition.

BCNF Example 05:

Consider the relation R(A, B, C, D, E), which is already in 3NF with the following functional dependencies (FDs). Determine whether it is in BCNF or not.

• AB → C
• AD → BCE
• CD → ABE

Solution

Let’s calculate the candidate keys from the given FD’s.

Candidate Keys (CK): {AD}, {CD}

Following the diagram, explain Example 5 in detail.

Let’s explain the above diagram

FD 1: AB → C

• LHS (AB) is neither a candidate key nor a super key. So, it is not a valid FD for BCNF.

This functional dependency does not satisfy the condition for BCNF.

FD 2: AD → BCE

• LHS (AD) is a candidate key. So, it is a valid FD for BCNF.

This functional dependency satisfies the condition for BCNF.

FD 3: CD → ABE

• LHS (CD) is a candidate key. So, it is a valid FD for BCNF.

This functional dependency satisfies the condition for BCNF.

Result:

The relation is not in BCNF. Since FD 1 (AB → C) does not satisfy the BCNF condition, only FD 2 (AD → BCE) and FD 3 (CD → ABE) satisfy the BCNF condition.

 

BCNF vs. 3NF

While 3NF deals with transitive dependencies, BCNF further refines the normalization by eliminating cases where a non-prime attribute (an attribute that is not part of any candidate key) determines another non-prime attribute.

In 3NF, a table can still have a situation where an attribute not part of the candidate key determines another attribute, as long as the attribute being determined is a prime attribute (part of a candidate key). BCNF eliminates this by ensuring that the left-hand side of every functional dependency is a superkey.

Conditions for a Table to be in BCNF

1. The table must first be in 3NF.

2. For every functional dependency (X → Y):

   • X must be a superkey (a set of attributes that can uniquely identify each row in the table).

   • If X is not a superkey, the table is not in BCNF.

Example: Table Not in BCNF

Consider the following table Student_Courses with the following attributes:

Functional Dependencies:

1. StudentID → Instructor (A student determines the instructor for their course).

2. CourseID → Department (A course determines the department it belongs to).

3. CourseID → Instructor (A course determines the instructor).

Analysis of BCNF:

• Functional Dependency 1: StudentID → Instructor

  • StudentID is a superkey because it can uniquely identify each row.

  • This satisfies the BCNF condition.

• Functional Dependency 2: CourseID → Department

  • CourseID is not a superkey because it doesn’t uniquely identify each row (multiple students can be enrolled in the same course).

  • This violates BCNF.

• Functional Dependency 3: CourseID → Instructor

  • CourseID is not a superkey, as it doesn’t uniquely identify rows in the table (a course can have multiple students).

  • This also violates BCNF.

Thus, the table Student_Courses is not in BCNF because CourseID is determining Department and Instructor, but CourseID is not a superkey.

Converting the Table to BCNF

To convert the table into BCNF, we must eliminate the violations by ensuring that the determinant (on the left-hand side) of each functional dependency is a superkey. This can be done by decomposing the table into smaller tables.

Decomposing the Table:

1. Table 1: Student_Courses (removes dependency on non-superkey CourseID for Instructor):

2. Table 2: Course_Department (removes dependency on non-superkey CourseID for Department):

Functional Dependencies in the Decomposed Tables:

• Student_Courses: StudentID, CourseID → Instructor (This table is in BCNF because {StudentID, CourseID} is a superkey).

• Course_Department: CourseID → Department (This table is in BCNF because CourseID is the superkey).

Now, the tables are in BCNF because every functional dependency has a superkey as the determinant.

When Does BCNF Violate 3NF?

BCNF is stricter than 3NF. A table can be in 3NF but not in BCNF if:

• A non-prime attribute (not part of any candidate key) determines another non-prime attribute.

• In 3NF, this is allowed as long as the non-prime attribute depends on a superkey or is part of the candidate key. However, in BCNF, all determinants must be superkeys.

Conclusion

• BCNF is a stricter form of 3NF, ensuring that every determinant is a superkey.

• A table is in BCNF if:

  1. It is in 3NF.

  2. For every functional dependency, the left-hand side (LHS) is a superkey.

• BCNF eliminates all anomalies that could still exist in a table in 3NF, particularly those involving non-prime attributes determining other non-prime attributes.