The Boyce Codd Normal Form in DBMS was proposed as a simpler form of 3NF, but it was found to be stricter than 3NF. That is, every relation in BCNF is also in 3NF; however, a relation in 3NF is not necessarily in BCNF.
Table of Contents
Intuitively, we can see the need for a stronger normal form than 3NF (Check the below Image) with its four functional dependencies FD1 through FD4. Suppose that we have thousands of lots in the relation but the lots are from only two counties: DeKalb and Fulton.
The area of a lot that determines the county, as specified by FD5, can be represented by 16 tuples in a separate relation R(Area, County_name), since there are only 16 possible Area values. This representation reduces the redundancy of repeating the same information in the thousands of LOTS1A tuples. BCNF is a stronger normal form that would disallow LOTS1A and suggest the need for decomposing it.
Definition: A relation schema R is in BCNF if whenever a non trivial functional dependency X → A holds in R, then X is a superkey of R.
Understanding BCNF
To understand BCNF, it’s essential to first grasp the concept of functional dependency and candidate keys:
- Functional Dependency: A functional dependency occurs when a particular attribute (or set of attributes) in a relation uniquely determines another attribute (or set of attributes). For example, in a table of employees, if the employee ID uniquely determines the employee’s name, there’s a functional dependency between the employee ID and the name.
- Candidate Key: A candidate key is a minimal set of attributes that can uniquely identify a tuple (row) in a relation. Every relation has at least one candidate key, and it can have more than one.
Steps to Achieve Boyce Codd Normal Form
- Identify Functional Dependencies: Determine all the functional dependencies in the table.
- Check for Violations: For each functional dependency, check if the left-hand side is a superkey. If it’s not, the table violates BCNF.
- Decompose the Table: If a violation is found, decompose the table into smaller tables that each satisfy BCNF, ensuring that the decomposed tables are still in BCNF.
Example of BCNF
Consider a table StudentCourses with the following attributes:
| StudentID | CourseID | Instructor |
|---|---|---|
1 | CS 101 | Prof. A |
2 | CS 102 | Prof. B |
1 | CS 102 | Prof. B |
3 | CS 101 | Prof. A |
Functional Dependencies:
- StudentID, CourseID → Instructor\text{StudentID, CourseID} \rightarrow\text{Instructor}StudentID, CourseID → Instructor
- CourseID → Instructor\text{CourseID}\rightarrow \text{Instructor}CourseID → Instructor
Here, CourseID uniquely determines Instructor, but CourseID alone is not a superkey because it does not uniquely identify rows in the table (i.e., multiple students can be enrolled in the same course). Therefore, the table is not in BCNF.
Decomposition
To achieve BCNF, the table can be decomposed into two tables:
1. CourseInstructor
| CourseID | Instructor |
|---|---|
CS 101 | Prof. A |
CS 102 | Prof. B |
2. StudentCourses
| StudentID | CourseID |
|---|---|
1 | CS 101 |
2 | CS 102 |
1 | CS 102 |
3 | CS 101 |
Now, both tables are in BCNF as all functional dependencies have a superkey on the left-hand side.
Importance of Boyce Codd Normal Form:
- Eliminates Redundancy: BCNF helps reduce data redundancy by breaking down the tables into smaller, well-structured tables.
- Avoids Anomalies: Ensures that insertion, update, and deletion anomalies are avoided, leading to more consistent and reliable data.
- Data Integrity: Enforces strict rules for functional dependencies, ensuring that data integrity is maintained throughout the database.
Related Articles
- Three Schema Architecture in DBMS
- Floyd-Warshall Algorithm
- Knapsack Problem using Branch and Bound
- Prim’s Algorithm in DAA
- Horspool’s Algorithm in DAA
- Radix Sort Algorithm
- Heap Sort Algorithm
- Huffman Coding Algorithm
- Kadane’s Algorithm
- Depth First Search Algorithm
- Karnaugh Map Solver
- Whack-a-Mole Game using JavaScript
- Fake Coin Problem