Boyce-Codd Normal Form (BCNF)

On a more practical note, just getting to 3NF or BCNF is the goal of many project teams. 3NF is the Third normal form used in relational database normalization. According to the Codd's definition, a table is said to be in (3NF) third normal form, if and only if,

1. that table is in the second normal form (2NF),
2. and every attribute in the table that do not belong to a candidate key should directly depend on every candidate key of that table.

BCNF (also known as 3.5NF) is another normal form used in relational database normalization. It was introduced to capture some the anomalies that are not addressed by the 3NF. A table is said to be in BCNF, if and only if, for each of the dependencies of the form A --> B that are non-trivial, A is a super-key. The following table outlines the similarities and differences between 3NF and BCNF.

Properties	3NF	BCNF
Achievability	Always achievable	Not always Always achievable
Quality of the tables	Less	More
Non-key Determinants	Can have non-key attributes as determinants	Cannot have non-key attributes as determinants
Proposed by	Edgar F. Codd	Raymond F. Boyce and Edgar F. Codd jointly proposed
Decomposition	Loss-less join decomposition can be achieved	Sometimes loss-less join decomposition cannot be achieved

Figure 4-81 shows removal of two values, which have the potential to be NULL values. These valued fields from a table called EDITION, will be used to create the new table called RANK. The result is a zero or one-to-one relationship between the RANK and EDITION tables. This implies that if a RANK record exists, then a corresponding EDITION record must exist as well. In the opposite case, however, an EDITION record can exist where a RANK record does not have to exist. This opposite case, accounts for an edition of a publication having no RANK and INGRAM_UNITS values. A recently published publication will rarely have any statistical information.

A stronger definition of 3NF called Boyce-Codd normal form (BCNF) was proposed later by Boyce and Codd. All these normal forms are based on a single analytical tool, which are the functional dependencies among the attributes of a relation. Later, a fourth normal form (4NF) and a fifth normal form (5NF) were proposed, based on the concepts of multivalued dependencies and join dependencies, respectively.
Boyce-Codd Normal Form (BCNF) is based on the functional dependencies (FDs) among the attributes of a relation. It is a stricter version of Third Normal Form (3NF) that further addresses anomalies caused by transitive dependencies.
Here's a breakdown of the relationship between BCNF and functional dependencies:
Functional Dependencies:

• These are relationships between attributes in a relation, where the value of one attribute (determinant) uniquely determines the value of another (dependent).
• Example: `OrderID -> CustomerName` (knowing the order ID uniquely tells you the customer's name).

Boyce-Codd Normal Form:

• A relation is in BCNF if it satisfies two conditions:
  
  1. It's in 3NF (no partial dependencies or transitive dependencies).
  2. For every FD (X -> Y) in the relation, where X is not a superkey (a minimal set of attributes that uniquely identifies a tuple), X must be a candidate key (a potential superkey).

Why BCNF focuses on FDs:

• Transitive dependencies occur when an attribute depends on another, which in turn depends on a third. This can lead to redundant data and update anomalies.
• BCNF eliminates such anomalies by ensuring that determinants (X in X -> Y) are always candidate keys. This guarantees that any updates to the determinant will propagate directly to all dependent attributes, maintaining data consistency.

Visualizing the connection:
Imagine a table with attributes `CustomerID`, `OrderID`, `ProductName`, and `Price`.

• `CustomerID -> OrderID` and `OrderID -> ProductName` are FDs.
• However, `CustomerID -> ProductName` is a transitive dependency, derived from the existing FDs.
• If the table is not in BCNF, updating `CustomerID` might not automatically update `ProductName`, leading to inconsistencies.

By enforcing the BCNF condition, we can restructure the table to eliminate transitive dependencies and ensure proper data flow based on FDs.