What Is Boyce-Codd Normal Form (BCNF)?

The goal of Boyce-Codd Normal Form is to increase data integrity by organizing the columns and tables of a relational database in order to achieve database normalization. Database normalization occurs when there are established relationships between tables and when the tables have defined rules to make the database more flexible and to preserve the data.

The goals of database normalization are to eliminate redundant data and to ensure data dependencies make sense. A database is normalized when the same data is not stored in more than one table and when only related data is stored in a table.

Origin of Boyce-Codd Normal Form

Following a series of guidelines ensure that databases are normalized. These guidelines are referred to as normal forms and are numbered from one through five. A relational database is described as normalized if it meets the first three forms: 1NF, 2NF, and 3NF.

BCNF was created as an extension to the third normal form, or 3NF, in 1974 by Raymond Boyce and Edgar Codd. The men were working to create database schemas that minimalize redundancies with the goal of reducing computational time. The third normal form removes columns that are not dependent on the primary key in addition to meeting the guidelines in the first and second normal forms. BCNF, which is sometimes referred to as 3.5NF, meets all the requirements of 3NF and requires that candidate keys not have any dependency on other attributes in a table.

At the time of BCNF's creation, Boyce was one of the key developers of Structured English Query Language, later standardized as SQL, which improved data retrieval by using Codd's relational model. In this model, Codd posited that structural complexity of databases could be reduced, which meant queries could be more powerful and flexible.

Using his relational database insights, Codd defined 1NF, 2NF, and 3NF guidelines. He teamed with Boyce to define BCNF.

Candidate Keys and BCNF

A candidate key is a column or combination of columns in a table that forms a unique key in the database. The combination of attributes identifies a database record without referring to any other data. Each table can contain multiple candidate keys, any one of which can qualify as the primary key. A table contains only one primary key.

Candidate keys must be unique.

A relation is in BCNF if every determinant is a candidate key. Consider a database table that stores employee information and has the attributes <Employee_Id>, <First_name>, <Last_name>, and <Title>.

In this table, the field <Employee_Id> determines first_name and last_name. Similarly, the tuple (<First_name>, <Last_name>) determines <Employee_id>.

The candidate key for this database is the because it's the only value that can't be used by another row.