We're sorry but this app doesn't work properly without JavaScript enabled. Please enable it to continue.

This lesson's interactive features are locked, please to keep using them

Boyce-Codd Normal Form (BCNF)

A table in Boyce-Codd normal form (created by Raymond Boyce and Edgar Codd) follows all the rules of third normal form, plus one additional rule:

  • A column that's part of a primary key can not be entirely dependent on a column that's not part of that primary key.

This only comes into play when there are multiple possible primary key combinations that overlap. Another name for this is "overlapping candidate keys."

Only in rare cases does a table in third normal form not meet the requirements of Boyce-Codd normal form!

Example of 3NF but Not Boyce-Codd

release_yearrelease_datesalesname
20012001-01-02100Kiss me tender
20012001-01-02200Bloody Mary
20022002-04-14100I wanna be them
20022002-04-14200He got me

The interesting thing here is that there are three possible primary keys:

  • release_year + sales

  • release_date + sales

  • name

This means that by definition, this table is in the second and third normal forms because those forms only restrict how dependent a column that is not part of a primary key can be.

However, this table is not in Boyce-Codd's normal form because release_year is entirely dependent on release_date.

Example of BCNF

The easiest way to fix the table in our example is to remove the duplicate data from release_date. Let's make that column release_month_and_day.

release_yearrelease_month_and_daysalesname
200101-02100Kiss me tender
200101-02200Bloody Mary
200204-14100I wanna be them
200204-14200He got me

BCNF Is Usually a Good Idea

The same exact rule of thumb applies to the second, third, and Boyce-Codd normal forms. That said, it's unlikely you'll see BCNF-specific issues in practice.

Optimize for data integrity and data de-duplication first by adhering to Boyce-Codd normal form. If you have speed issues, de-normalize accordingly.