Mastering Database Management

0 of 78 lessons complete (0%)

Data Modeling

Boyce-Codd Normal Form

You don’t have access to this lesson

Please register or sign in to access the course content.

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:

  1. Determinant:
    • In the context of BCNF, a determinant is an attribute that uniquely determines another attribute.
  2. 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

EmployeeIDProjectIDEmployeeNameProjectName
1101AliceProjectA
1102AliceProjectB
2101BobProjectA

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

EmployeeIDEmployeeName
1Alice
2Bob

Table: EmployeeProjects

EmployeeIDProjectIDProjectName
1101ProjectA
1102ProjectB
2101ProjectA

Explanation:

  1. Identifying Violation:
    • The original table violated BCNF because “EmployeeID” determined “EmployeeName,” and “EmployeeID” is not a candidate key on its own.
  2. 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.
  3. 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.