Boyce-Codd normal form

Boyce-Codd normal form (or BCNF) is a normal form used in database normalization. It is a slightly stronger version of the third normal form (3NF). A table is in Boyce-Codd normal form if and only if, for every one of its non-trivial functional dependencies "X → Y", "X" is a superkey—that is, "X" is either a candidate key or a superset thereof.

BCNF was developed in 1974 by Raymond F. Boyce and Edgar F. Codd to address certain types of anomaly not dealt with by 3NF as originally defined.Codd, E. F. "Recent Investigations into Relational Data Base Systems." IBM Research Report RJ1385 (April 23rd, 1974). Republished in "Proc. 1974 Congress" (Stockholm, Sweden, 1974). New York, N.Y.: North-Holland (1974).]

Chris Date has pointed out that a definition of what we now know as BCNF appeared in a paper by Ian Heath in 1971.Heath, I. "Unacceptable File Operations in a Relational Database." "Proc. 1971 ACM SIGFIDET Workshop on Data Description, Access, and Control", San Diego, Calif. (November 11th-12th, 1971).] Date writes:

"Since that definition predated Boyce and Codd's own definition by some three years, it seems to me that BCNF ought by rights to be called "Heath" normal form. But it isn't."Date, C.J. "Database in Depth: Relational Theory for Practitioners". O'Reilly (2005), p. 142.]

3NF tables not meeting BCNF

Only in rare cases does a 3NF table not meet the requirements of BCNF. A 3NF table which does not have multiple overlapping candidate keys is guaranteed to be in BCNF.Vincent, M.W. and B. Srinivasan. "A Note on Relation Schemes Which Are in 3NF But Not in BCNF." "Information Processing Letters" 48(6), 1993, pp. 281-83.] Depending on what its functional dependencies are, a 3NF table with two or more overlapping candidate keys may or may not be in BCNF.

An example of a 3NF table that does not meet BCNF is:

The candidate keys for the Rate Types table are {Rate Type} and {Court, Member Flag}; the candidate keys for the Today's Bookings table are {Court, Start Time} and {Court, End Time}. Both tables are in BCNF. Having one Rate Type associated with two different Courts is now impossible, so the anomaly affecting the original table has been eliminated.

Achievability of BCNF

In some cases, a non-BCNF table cannot be decomposed into tables that satisfy BCNF and preserve the dependencies that held in the original table. Beeri and Bernstein showed in 1979 that, for example, a set of functional dependencies {AB → C, C → B} cannot be represented by a BCNF schema.Beeri, Catriel and Bernstein, Philip A. "Computational problems related to the design of normal form relational schemas." "ACM Transactions on Database Systems" 4(1), March 1979, p. 50.] Thus, unlike the first three normal forms, BCNF is not always achievable.

Consider the following non-BCNF table whose functional dependencies follow the {AB → C, C → B} pattern:

In this revised design, the "Shop Near Person" table has a candidate key of {Person, Shop}, and the "Shop" table has a candidate key of {Shop}. Unfortunately, although this design adheres to BCNF, it is unacceptable on different grounds: it allows us to record multiple shops of the same type against the same person. In other words, its candidate keys do not guarantee that the functional dependency {Person, Shop Type} → {Shop} will be respected.

A design that eliminates all of these anomalies (but does not conform to BCNF) is possible.Zaniolo, Carlo. "A New Normal Form for the Design of Relational Database Schemata." "ACM Transactions on Database Systems" 7(3), September 1982, pp. 493.] This design consists of the original "Nearest Shops" table supplemented by the "Shop" table described above.

If a referential integrity constraint is defined to the effect that {Shop Type, Nearest Shop} from the first table must refer to a {Shop Type, Shop} from the second table, then the data anomalies described previously are prevented.

References

Bibliography

* Date, C. J. (1999). "An Introduction to Database Systems" (8th ed.). Addison-Wesley Longman. ISBN 0-321-19784-4.

External links

* [http://www.datamodel.org/NormalizationRules.html Rules Of Data Normalization]
* [http://www.utexas.edu/its/windows/database/datamodeling/rm/rm8.html Advanced Normalization] by ITS, University of Texas.