What is the Boyce-Codd normal form?

Could you provide information about the Boyce-Codd normal form?

The Boyce-Codd normal form (abbreviated BCNF) is a "key heavy" derivation of the third normal form. The simplest way I can think to define it is if the key uniquely identifies a row, but the key includes more columns than are actually required to uniquely identify a row, then a table is in BCNF. For example:

CREATE TABLE t_employees1 (
   employee_id    INT         IDENTITY
,  last_name      VARCHAR(25) NOT NULL
,  first_name     VARCHAR(25) NOT NULL
   CONSTRAINT XPKt_employees1
      PRIMARY KEY (employee_id, last_name, first_name)
--  other columns as needed
   )

This example of the t_employees1 table is in BCNF.  To coax it into 3NF (third normal form), I would use:

CREATE TABLE t_employees2 (
   Employee_id     INT         IDENTITY
   CONSTRAINT XPKt_employees2
      PRIMARY KEY (employee_id)
,  last_name       VARCHAR(25) NOT NULL
,  first_name      VARCHAR(25) NOT NULL
--  other columns as needed
   )

For More Information

• What do you think about this answer? E-mail us at editor@searchDatabase.com with your feedback.
• The Best Database Design Web Links: tips, tutorials, scripts, and more.
• Have a Database Design tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
• Ask your technical Database Design questions--or help out your peers by answering them--in our live discussion forums.
• Ask the Experts yourself: Our Database Design guru is waiting to answer your toughest questions.