BCNF in DBMS

Boyce-Codd Normal Form (BCNF) is an advanced version of Third Normal Form (3NF) that eliminates any remaining anomalies that can still occur in a table that is in 3NF. A table is in BCNF if it satisfies the following conditions:

1. It is in 3NF.

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

In simpler terms, BCNF aims to ensure that every non-trivial functional dependency has a superkey on the left-hand side, meaning that the determinant (the attribute or set of attributes determining other attributes) must be able to uniquely identify every row in the table.

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.