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.[1]
Chris Date has pointed out that a definition of what we now know as BCNF appeared in a paper by Ian Heath in 1971.[2] 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."[3]
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.[4] 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:
Start Time
End Time
Rate Type
|-
|1
09:30
10:30
SAVER
|-
|1
11:00
12:00
SAVER
|-
|1
14:00
15:30
STANDARD
|-
|2
10:00
11:30
PREMIUM-B
|-
|2
11:30
13:30
PREMIUM-B
|-
|2
15:00
16:30
PREMIUM-A
|-
|}
Each row in the table represents a court booking at a tennis club that has one hard court (Court 1) and one grass court (Court 2)
A booking is defined by its Court and the period for which the Court is reserved
Additionally, each booking has a Rate Type associated with it. There are four distinct rate types:
SAVER, for Court 1 bookings made by members
STANDARD, for Court 1 bookings made by non-members
PREMIUM-A, for Court 2 bookings made by members
PREMIUM-B, for Court 2 bookings made by non-members
The table's candidate keys are:
{Court, Start Time}
{Court, End Time}
{Rate Type, Start Time}
{Rate Type, End Time}
Recall that 2NF prohibits partial functional dependencies of non-prime attributes on candidate keys, and that 3NF prohibits transitive functional dependencies of non-prime attributes on candidate keys. In the Today's Court Bookings table, there are no non-prime attributes: that is, all attributes belong to candidate keys. Therefore the table adheres to both 2NF and 3NF.
The table does not adhere to BCNF. This is because of the dependency Rate Type ? Court, in which the determining attribute (Rate Type) is neither a candidate key nor a superset of a candidate key.
Any table that falls short of BCNF will be vulnerable to logical inconsistencies. In this example, enforcing the candidate keys will not ensure that the dependency Rate Type ? Court is respected. There is, for instance, nothing to stop us from assigning a PREMIUM A Rate Type to a Court 1 booking as well as a Court 2 booking?a clear contradiction, as a Rate Type should only ever apply to a single Court.
The design can be amended so that it meets BCNF:
Court
Member Flag
|-
|SAVER
1
Yes
|-
|STANDARD
1
No
|-
|PREMIUM-A
2
Yes
|-
|PREMIUM-B
2
No
|-
|}
Start Time
End Time
Member Flag
|-
|1
09:30
10:30
Yes
|-
|1
11:00
12:00
Yes
|-
|1
14:00
15:30
No
|-
|2
10:00
11:30
No
|-
|2
11:30
13:30
No
|-
|2
15:00
16:30
Yes
|-
|}
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.[5] 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:
Shop Type
Nearest Shop
|-
|Davidson
Optician
Eagle Eye
|-
|Davidson
Hairdresser
Snippets
|-
|Wright
Bookshop
Merlin Books
|-
|Fuller
Bakery
Doughy's
|-
|Fuller
Hairdresser
Sweeney Todd's
|-
|Fuller
Optician
Eagle Eye
|}
For each Person / Shop Type combination, the table tells us which shop of this type is geographically nearest to the person's home.
The candidate keys of the table are:
{Person, Shop Type}
{Person, Nearest Shop}
Because all three attributes are prime attributes (i.e. belong to candidate keys), the table is in 3NF. The table is not in BCNF, however, as the Shop Type attribute is functionally dependent on a non-superkey: Nearest Shop.
The violation of BCNF means that the table is subject to anomalies. For example, Eagle Eye might have its Shop Type changed to "Optometrist" on its "Fuller" record while retaining the Shop Type "Optician" on its "Davidson" record. This would imply contradictory answers to the question: "What is Eagle Eye's Shop Type?" Holding each shop's Shop Type only once would seem preferable, as doing so would prevent such anomalies from occurring:
Shop
|-
|Davidson
Eagle Eye
|-
|Davidson
Snippets
|-
|Wright
Merlin Books
|-
|Fuller
Doughy's
|-
|Fuller
Sweeney Todd's
|-
|Fuller
Eagle Eye
|}
Shop Type
|-
|Eagle Eye
Optician
|-
|Snippets
Hairdresser
|-
|Merlin Books
Bookshop
|-
|Doughy's
Bakery
|-
|Sweeney Todd's
Hairdresser
|}
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.[6] This design consists of the original "Nearest Shops" table supplemented by the "Shop" table described above.
Shop Type
Nearest Shop
|-
|Davidson
Optician
Eagle Eye
|-
|Davidson
Hairdresser
Snippets
|-
|Wright
Bookshop
Merlin Books
|-
|Fuller
Bakery
Doughy's
|-
|Fuller
Hairdresser
Sweeney Todd's
|-
|Fuller
Optician
Eagle Eye
|}
Shop Type
|-
|Eagle Eye
Optician
|-
|Snippets
Hairdresser
|-
|Merlin Books
Bookshop
|-
|Doughy's
Bakery
|-
|Sweeney Todd's
Hairdresser
|}
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
↑ 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).
↑ 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, C.J. Database in Depth: Relational Theory for Practitioners. O'Reilly (2005), p. 142.
↑ 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.
↑ 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.
↑ Zaniolo, Carlo. "A New Normal Form for the Design of Relational Database Schemata." ACM Transactions on Database Systems 7(3), September 1982, pp. 493.
Bibliography
Date, C. J. (1999). An Introduction to Database Systems (8th ed.). Addison-Wesley Longman. ISBN 0-321-19784-4.
Encyclopedia
top
