

0 / 2 embers
0 / 3000 xp
click for more info
Complete a lesson to start your streak
click for more info
Difficulty: 4
click for more info
Not enough gems
Cost: 6 gems
1: Table Relationships
incomplete
2: One to Many
incomplete
3: Many to Many
incomplete
4: Database Normalization
incomplete
5: Normal Forms
incomplete
6: Primary Keys
incomplete
7: Functional Dependencies
incomplete
8: First Normal Form (1NF)
incomplete
9: Second Normal Form (2NF)
incomplete
10: Third Normal Form (3NF)
incomplete
11: Boyce-Codd Normal Form (BCNF)
incomplete
12: Normalization Review
incomplete
13: Query Practice – Users & Banks
incomplete
Back
ctrl+,
Next
ctrl+.
This lesson's interactive features are locked, please to keep using them
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!
| release_year | release_date | sales | name |
|---|---|---|---|
| 2001 | 2001-01-02 | 100 | Kiss me tender |
| 2001 | 2001-01-02 | 200 | Bloody Mary |
| 2002 | 2002-04-14 | 100 | I wanna be them |
| 2002 | 2002-04-14 | 200 | He 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.
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_year | release_month_and_day | sales | name |
|---|---|---|---|
| 2001 | 01-02 | 100 | Kiss me tender |
| 2001 | 01-02 | 200 | Bloody Mary |
| 2002 | 04-14 | 100 | I wanna be them |
| 2002 | 04-14 | 200 | He got me |
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.