Normalization of Tables: From 3NF to BCNF
The third normal form (3NF) ensures that a table is free from transitive dependencies, where non-key attributes depend on other non-key attributes. However, it is still possible for a table to have a set of attributes that depend on only a part of the primary key, which is known as a partial dependency. To address this issue, we can use Boyce-Codd normal form (BCNF) to further normalize the database.
Boyce-Codd normal form (BCNF) is a higher level of normalization than 3NF. It requires that every determinant (an attribute that determines the value of another attribute) in a table must be a candidate key. A table is in BCNF if and only if every non-trivial functional dependency in the table is a dependency on a superkey.
Let's continue with the example from the 3NF normalization. Suppose we have a table called "Departments" that contains information about each department and the books it contains:
| Department | ISBN |
| Fiction | 978-0316067928 |
| Fiction | 978-0316015844 |
| Fiction | 978-0547928227 |
| Fiction | 978-0590353427 |
| Science Fiction | 978-0312937385 |
| Science Fiction | 978-0451524935 |
| Science Fiction | 978-0345313157 |
This table is in 3NF because it is free from transitive dependencies. However, there is a partial dependency, where the "Department" attribute depends only on a subset of the primary key (ISBN). For example, the "Fiction" department is associated with four different ISBNs.
To normalize this table to BCNF, we need to split it into two tables. The first table, "Departments", will contain information about each department and its ID:
| Department | DepartmentID |
| Fiction | 1 |
| Science Fiction | 2 |
The second table, "DepartmentBooks", will contain information about each book and the department it belongs to:
| ISBN | DepartmentID |
| 978-0316067928 | 1 |
| 978-0316015844 | 1 |
| 978-0547928227 | 1 |
| 978-0590353427 | 1 |
| 978-0312937385 | 2 |
| 978-0451524935 | 2 |
| 978-0345313157 | 2 |
In this normalized database, each determinant (ISBN and DepartmentID) is a candidate key. Every non-trivial functional dependency in the table is a dependency on a superkey, which means that the table is in BCNF.
This normalization improves data consistency and maintainability, and reduces data redundancy by eliminating partial dependencies in the original table. However, it also increases the number of tables in the database, which can make it more complex to manage.