Convert a Table into Boyce-Codd Normal Form (BCNF)

Achieving Boyce-Codd Normal Form (BCNF)

Write a SQL query to resolve overlapping candidate keys and achieve BCNF

Solution:

-- Original table with overlapping candidate keys.
CREATE TABLE Courses (
    CourseID INT,
    InstructorID INT,
    InstructorName VARCHAR(100),
    PRIMARY KEY (CourseID, InstructorID)
);

-- Resolve overlapping candidate keys by splitting into two tables.
CREATE TABLE Instructors (
    InstructorID INT PRIMARY KEY,
    InstructorName VARCHAR(100)
);

CREATE TABLE Courses_BCNF (
    CourseID INT,
    InstructorID INT,
    PRIMARY KEY (CourseID, InstructorID),
    FOREIGN KEY (InstructorID) REFERENCES Instructors(InstructorID)
);

Explanation:

• Purpose of the Query :
• The goal is to resolve overlapping candidate keys and achieve BCNF.
• Key Components :
• Instructors: Stores instructor details independently.
• Courses_BCNF: Links courses to instructors via a foreign key.
• Why use BCNF? :
• BCNF eliminates anomalies caused by overlapping candidate keys.
• It ensures that every determinant is a candidate key.
• Real-World Application :
• In educational systems, separating instructor details avoids conflicts in course assignments.

Notes:

• BCNF is stricter than 3NF and resolves anomalies not addressed by 3NF.
• Identify all candidate keys before applying BCNF.
• Important Considerations:
• BCNF may require additional tables, increasing complexity.

For more Practice: Solve these Related Problems:

• Write a SQL query to resolve overlapping candidate keys in a table where multiple instructors can teach the same course, achieving BCNF.
• Write a SQL query to normalize a table containing project assignments and team lead details to eliminate overlapping candidate keys for BCNF.
• Write a SQL query to redesign a table storing product categories and subcategories to resolve overlapping candidate keys and achieve BCNF.
• Write a SQL query to split a table containing course schedules and instructor availability into separate tables for BCNF compliance.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Eliminating Transitive Dependencies for Third Normal Form (3NF).
Next SQL Exercise: Identifying and Resolving Update Anomalies.