Boyce-Codd normal form

From Wikipedia, the free encyclopedia

Boyce-Codd normal form (or BCNF) is a normal form used in database normalization. It is a slightly-stronger version of third normal form (3NF). Boyce-Codd normal form requires that there cannot be any non-trivial functional dependencies of attributes on something other than a superset of a candidate key (called a superkey). In a 3NF table, all attributes are dependent on a key, a whole key and nothing but a key, excluding trivial dependencies, like A->A. A table is said to be in BCNF if and only if it is in 3NF and every non-trivial, left-irreducible functional dependency has a candidate key as its determinant. In more informal terms, a table is in BCNF if it is in 3NF and the only determinants are the candidate keys.

[edit] Example

Product Type Product Category Product Subcategory Stock Category Range Code Range Subcode Wooden Shelf Ornate Large Large Shelves LARGE-WOODEN SHELF-ORNATE Metal Shelf Basic Small Small Shelves SMALL-METAL SHELF-BASIC Glass Shelf Ornate Medium Medium Shelves MEDIUM-GLASS SHELF-ORNATE Glass Shelf Ornate Large Large Shelves LARGE-GLASS SHELF-ORNATE Metal Table Basic Medium Medium Tables MEDIUM-METAL TABLE-BASIC Glass Table Ornate Small Small Tables SMALL-GLASS TABLE-ORNATE Glass Table Basic Small Small Tables SMALL-GLASS TABLE-BASIC Glass Table Contemporary Small Small Tables SMALL-GLASS TABLE-CONTP Glass Table Avant Garde Small Small Tables SMALL-GLASS TABLE-AVANT

The primary key is (Product Category, Product Subcategory), although (Range Code, Range Subcode) would have done equally well.

The table is in 3NF, for each of its non-key attributes is non-transitively dependent on the full key (Product Category, Product Subcategory). Yet the table still contains redundant data. Notice that the last four rows shown in the table above feature the same combination of Product Category, Stock Category, and Range Code. Indeed, Range Code is dictated by the combination of Product Category and Stock Category. This scenario, in which a 3NF table features a non-trivial dependency on something other than a candidate key, is in violation of Boyce-Codd Normal Form.

One way of normalizing this design into BCNF would be to split it out into three tables:

Product Type Product Category Product Subcategory Stock Category Wooden Shelf Ornate Large Large Shelves Metal Shelf Basic Small Small Shelves Glass Shelf Ornate Medium Medium Shelves Glass Shelf Ornate Large Large Shelves Metal Table Basic Medium Medium Tables Glass Table Ornate Small Small Tables Glass Table Basic Small Small Tables Glass Table Contemporary Small Small Tables Glass Table Avant Garde Small Small Tables

Range Code Mapping Product Category Stock Category Range Code Wooden Shelf Large Shelves LARGE-WOODEN Metal Shelf Small Shelves SMALL-METAL Glass Shelf Medium Shelves MEDIUM-GLASS Glass Shelf Large Shelves LARGE-GLASS Metal Table Medium Tables MEDIUM-METAL Glass Table Small Tables SMALL-GLASS Range Subcode Mapping Product Subcategory Stock Category Range Subcode Ornate Large Large Shelves SHELF-ORNATE Basic Small Small Shelves SHELF-BASIC Ornate Medium Medium Shelves SHELF-ORNATE Basic Medium Medium Tables TABLE-BASIC Ornate Small Small Tables TABLE-ORNATE Basic Small Small Tables TABLE-BASIC Contemporary Small Small Tables TABLE-CONTP Avant Garde Small Small Tables TABLE-AVANT

Alternatively, a more granular scheme of categories (Type of Item, Material, Style, Size), could have been used to describe product types, avoiding the problem in the first place.

[edit] References

• Rules Of Data Normalization
• Date, C. J. (1999), An Introduction to Database Systems (8th ed.). Addison-Wesley Longman. ISBN 0-321-19784-4.
• Date, C.J., & Darwen, H., & Pascal, F. Database Debunkings
• H.-J. Schek, P.Pistor Data Structures for an Integrated Data Base Management and Information Retrieval System
• Advanced Normalization by ITS, University of Texas.

Topics in Database normalization

First normal form | Second normal form | Third normal form Boyce-Codd normal form | Fourth normal form | Fifth normal form | Domain/key normal form | Sixth normal form Denormalization