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

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

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

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

Hint

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

Practice

1. What is the main goal of Boyce-Codd Normal Form (BCNF) in database design?

easy

A. To ensure every functional dependency is based on a candidate key

B. To allow duplicate rows in a table

C. To combine multiple tables into one

D. To remove all foreign keys from a table

Solution

1. Step 1: Understand BCNF definition

   BCNF requires that for every functional dependency X -> Y, X must be a candidate key.

2. Step 2: Analyze options

   Only To ensure every functional dependency is based on a candidate key correctly states this goal. Other options describe unrelated or incorrect concepts.

3. Final Answer:

   To ensure every functional dependency is based on a candidate key -> Option A

4. Quick Check:

   BCNF = dependencies from candidate keys [OK]

Hint: BCNF means all dependencies come from keys [OK]

Common Mistakes:

• Thinking BCNF removes foreign keys
• Confusing BCNF with merging tables
• Believing BCNF allows duplicates

2. Which of the following functional dependencies violates BCNF in a relation?

easy

A. C -> D, where C is a candidate key

B. A -> B, where A is a candidate key

C. B -> C, where B is not a candidate key

D. D -> A, where D is a candidate key

Solution

1. Step 1: Recall BCNF condition

   Every functional dependency's left side must be a candidate key.

2. Step 2: Check each dependency

   Only B -> C has a left side (B) that is not a candidate key, violating BCNF.

3. Final Answer:

   B -> C, where B is not a candidate key -> Option C

4. Quick Check:

   Dependency from non-key violates BCNF [OK]

Hint: Check if left side of dependency is a candidate key [OK]

Common Mistakes:

• Assuming all dependencies are allowed
• Confusing candidate keys with foreign keys
• Ignoring the key status of the left side

3. Consider a relation R(A, B, C) with functional dependencies: A -> B and B -> C. Which of the following is true about R?

medium

A. R is not in BCNF because B -> C violates BCNF

B. R is in BCNF because A is a candidate key

C. R is in BCNF because B determines C

D. R is not in BCNF because A -> B violates BCNF

Solution

1. Step 1: Identify candidate keys

   A determines B, and B determines C, so A determines B and C. Thus, A is a candidate key.

2. Step 2: Check BCNF condition

   Dependency B -> C has B on left, which is not a candidate key. This violates BCNF.

3. Final Answer:

   R is not in BCNF because B -> C violates BCNF -> Option A

4. Quick Check:

   Non-key dependency breaks BCNF [OK]

Hint: Check if all left sides are candidate keys [OK]

Common Mistakes:

• Assuming A -> B means all dependencies are fine
• Ignoring transitive dependencies
• Confusing BCNF with 3NF

4. Given a relation with attributes (X, Y, Z) and dependencies: X -> Y and Y -> X, what is the problem if we say this relation is in BCNF?

medium

A. There is no problem; it is in BCNF

B. X and Y are candidate keys, so BCNF is satisfied

C. X and Y together form a candidate key, so BCNF is violated

D. X and Y are not candidate keys, so BCNF is violated

Solution

1. Step 1: Analyze dependencies

   X -> Y and Y -> X mean X and Y determine each other, but neither determines Z. Candidate keys must include Z (e.g., {X, Z}, {Y, Z}).

2. Step 2: Check BCNF condition

   X -> Y has X on left, but X is not a superkey (does not determine Z), violating BCNF. Similarly for Y -> X.

3. Final Answer:

   X and Y are not candidate keys, so BCNF is violated -> Option D

4. Quick Check:

   Determinants must be superkeys covering all attributes [OK]

Hint: Mutual dependencies don't make keys without covering all attributes [OK]

Common Mistakes:

• Thinking mutual dependencies make X and Y candidate keys
• Forgetting to verify determination of all attributes like Z
• Ignoring that both sides can be keys

5. You have a relation R(A, B, C, D) with dependencies: A, B -> C, C -> D. To convert R into BCNF, what should you do?

hard

A. Remove dependency C -> D

B. Decompose R into R1(A, B, C) and R2(C, D)

C. Add C to the candidate key

D. Combine all attributes into one table

Solution

1. Step 1: Identify violating dependency

   C -> D violates BCNF because C is not a candidate key (A,B is key).

2. Step 2: Decompose relation

   Decompose into R1(A, B, C) with key (A,B) and R2(C, D) with key C to fix violation.

3. Final Answer:

   Decompose R into R1(A, B, C) and R2(C, D) -> Option B

4. Quick Check:

   Decompose on violating dependency to achieve BCNF [OK]

Hint: Decompose on dependencies with non-key left side [OK]

Common Mistakes:

• Trying to remove dependencies instead of decomposing
• Adding attributes incorrectly to keys
• Merging tables instead of decomposing