Third Normal Form   «Prev  Next»

Lesson 8 Boyce-Codd Normal Form (BCNF)
Objective Verifies that all data sets are identified and segregated.

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 known as 3.5NF

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


Columns which have potential to be NULL

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.

3NF and beyond removing NULL fields to new tables.
Figure 4-81: 3NF and beyond removing NULL fields to new tables.

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: 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.