Boyce-Codd Normal Form (BCNF)

Sub-Page 4: Ensuring Every Determinant is a Candidate Key

What is BCNF?

Boyce-Codd Normal Form (BCNF) is a stricter version of 3NF. A table is in BCNF if it is in 3NF and for every functional dependency X → Y, X is a candidate key. This eliminates anomalies from determinants that are not candidate keys.

BCNF ensures no non-trivial functional dependencies exist where the determinant is not a candidate key, reducing update anomalies.

Example Table (in 3NF):

StudentID	Course	Instructor
101	CS101	Dr. Smith
102	CS101	Dr. Smith

Functional dependency: Course → Instructor (not a candidate key). Candidate keys: {StudentID, Course}, {StudentID, Instructor}. Issue: Updating Instructor for Course requires multiple row updates.

BCNF Real-World Scenarios

Scenario 1: University Teaching Assignments

StudentID	Course	Instructor
101	CS101	Dr. Smith
102	CS101	Dr. Smith
103	MATH201	Dr. Jones

Functional dependency: Course → Instructor (not a candidate key). Candidate keys: {StudentID, Course}, {StudentID, Instructor}. Issue: Changing the instructor for a course requires updating multiple rows, risking inconsistency.

BCNF Solution

Split into CourseInstructors and Enrollments tables to make Course a candidate key.

CourseInstructors(Course, Instructor)

Enrollments(StudentID, Course)

Scenario 2: Course Prerequisite Requirements

StudentID	Course	Prerequisite
101	CS201	CS101
102	CS201	CS101
103	MATH301	MATH201

Functional dependency: Course → Prerequisite (not a candidate key). Candidate keys: {StudentID, Course}, {StudentID, Prerequisite}. Issue: Updating a course’s prerequisite requires multiple row updates.

BCNF Solution

Split into CoursePrerequisites and StudentCourses tables.

CoursePrerequisites(Course, Prerequisite)

StudentCourses(StudentID, Course)

SQL for BCNF

-- SQL for Scenario 1: University Teaching Assignments
CREATE TABLE CourseInstructors (
    Course VARCHAR(10) PRIMARY KEY,
    Instructor VARCHAR(50)
);
CREATE TABLE Enrollments (
    StudentID INT,
    Course VARCHAR(10),
    PRIMARY KEY (StudentID, Course),
    FOREIGN KEY (Course) REFERENCES CourseInstructors(Course)
);

-- SQL for Scenario 2: Course Prerequisite Requirements
CREATE TABLE CoursePrerequisites (
    Course VARCHAR(10) PRIMARY KEY,
    Prerequisite VARCHAR(10)
);
CREATE TABLE StudentCourses (
    StudentID INT,
    Course VARCHAR(10),
    PRIMARY KEY (StudentID, Course),
    FOREIGN KEY (Course) REFERENCES CoursePrerequisites(Course)
);

These schemas ensure every determinant is a candidate key, eliminating anomalies from non-candidate key dependencies.

Key Takeaways

Determinants as Candidate Keys

BCNF ensures every functional dependency’s determinant is a candidate key.

Stricter than 3NF

BCNF eliminates anomalies not addressed by 3NF.

Foundation for Higher Forms

BCNF prepares for advanced normalization like 4NF.

Back to 3NF Next: 4NF