Definition of Boyce-Codd Normal Form (BCNF):
Boyce-Codd Normal Form (BCNF) is a further refinement of the normalization process in relational databases. A table is said to be in BCNF if it is in 3NF and every determinant (attribute that uniquely determines another attribute) is a candidate key. BCNF addresses certain cases where 3NF might still have anomalies related to functional dependencies.
Key Concepts:
- Determinant:
- In the context of BCNF, a determinant is an attribute that uniquely determines another attribute.
- Candidate Key:
- A candidate key is a minimal set of attributes that uniquely identifies each record in a table. In BCNF, every determinant must be a candidate key.
Example:
Consider a table that violates BCNF:
Table: EmployeeProjects
| EmployeeID | ProjectID | EmployeeName | ProjectName |
|---|---|---|---|
| 1 | 101 | Alice | ProjectA |
| 1 | 102 | Alice | ProjectB |
| 2 | 101 | Bob | ProjectA |
In this table, the primary key is {EmployeeID, ProjectID}, and there is a functional dependency where “EmployeeID” uniquely determines “EmployeeName.”
Identifying Violation of BCNF:
- In the given table, “EmployeeID” determines “EmployeeName,” and “EmployeeID” is part of the primary key.
- This violates BCNF because every determinant must be a candidate key, and here “EmployeeID” is not a candidate key by itself.
Converting to BCNF:
To achieve BCNF, we need to decompose the table and create a new table for employee information:
Table: Employees
| EmployeeID | EmployeeName |
|---|---|
| 1 | Alice |
| 2 | Bob |
Table: EmployeeProjects
| EmployeeID | ProjectID | ProjectName |
|---|---|---|
| 1 | 101 | ProjectA |
| 1 | 102 | ProjectB |
| 2 | 101 | ProjectA |
Explanation:
- Identifying Violation:
- The original table violated BCNF because “EmployeeID” determined “EmployeeName,” and “EmployeeID” is not a candidate key on its own.
- Decomposing the Table:
- The “Employees” table now contains unique pairs of “EmployeeID” and “EmployeeName.”
- The “EmployeeProjects” table contains information about employee projects, and “EmployeeID” is a foreign key referencing the “Employees” table.
- Achieving BCNF:
- The resulting tables are now in BCNF.
- Every determinant is a candidate key, addressing the violation.
By decomposing the original table into two tables based on functional dependencies, we ensure that the tables are now in Boyce-Codd Normal Form (BCNF), providing a higher level of normalization and reducing the risk of certain anomalies related to functional dependencies.