Boyce-Codd Normal Form (BCNF) in DBMS Theory - Mini Project: Build & Apply

Understanding Boyce-Codd Normal Form (BCNF)

📖 Scenario: You are working with a database table that stores information about university courses, instructors, and classrooms. The table currently has some redundancy and anomalies.Your task is to understand and apply Boyce-Codd Normal Form (BCNF) to improve the table design.

🎯 Goal: Build a step-by-step understanding of BCNF by identifying functional dependencies, candidate keys, and decomposing the table to satisfy BCNF.

📋 What You'll Learn

Create a table structure with given attributes and sample functional dependencies

Identify candidate keys for the table

Apply BCNF rules to check if the table is in BCNF

Decompose the table into BCNF-compliant tables

💡 Why This Matters

🌍 Real World

Database designers use BCNF to organize data efficiently and avoid update anomalies in real-world applications like university course management.

💼 Career

Understanding BCNF is essential for roles like database administrator, data analyst, and software developer to design robust databases.

1

Create the initial table structure with attributes and functional dependencies

Create a dictionary called table with keys as attribute names: 'CourseID', 'Instructor', 'Classroom', and 'TimeSlot'. Also create a list called functional_dependencies containing these exact tuples: ('CourseID', 'Instructor'), ('Instructor', 'Classroom'), and ('CourseID', 'TimeSlot').

DBMS Theory

# Create the table dictionary and functional_dependencies list
# Your code here

Need a hint?

Use a set for table attributes and a list of tuples for functional_dependencies.

2

Identify candidate keys for the table

Create a list called candidate_keys containing the exact candidate key {'CourseID'}.

DBMS Theory

table = {'CourseID', 'Instructor', 'Classroom', 'TimeSlot'}
functional_dependencies = [('CourseID', 'Instructor'), ('Instructor', 'Classroom'), ('CourseID', 'TimeSlot')]
# Define candidate_keys list
# Your code here

Need a hint?

Candidate keys are minimal sets of attributes that can identify all other attributes.

3

Check if the table is in Boyce-Codd Normal Form (BCNF)

Create a boolean variable called is_bcnf and set it to False because the functional dependency ('Instructor', 'Classroom') violates BCNF as 'Instructor' is not a candidate key.

DBMS Theory

table = {'CourseID', 'Instructor', 'Classroom', 'TimeSlot'}
functional_dependencies = [('CourseID', 'Instructor'), ('Instructor', 'Classroom'), ('CourseID', 'TimeSlot')]
candidate_keys = [{'CourseID'}]
# Set is_bcnf boolean
# Your code here

Need a hint?

BCNF requires every determinant to be a candidate key. Here, 'Instructor' is not a candidate key.

4

Decompose the table into BCNF-compliant tables

Create a list called bcnf_tables containing two sets: {'CourseID', 'Instructor', 'TimeSlot'} and {'Instructor', 'Classroom'} representing the decomposed tables that satisfy BCNF.

DBMS Theory

table = {'CourseID', 'Instructor', 'Classroom', 'TimeSlot'}
functional_dependencies = [('CourseID', 'Instructor'), ('Instructor', 'Classroom'), ('CourseID', 'TimeSlot')]
candidate_keys = [{'CourseID'}]
is_bcnf = False
# Define bcnf_tables list
# Your code here

Need a hint?

Decompose by separating the dependency that violates BCNF into its own table.