2NF and BCNF explained: the whole key, nothing but the key

‘Normal Forms’ are database normalization rules designed to prevent data duplication and editing anomalies in relational databases.

‘Second Normal Form’ (2NF) requires every column to depend on the entire table key, not just part of it, while ‘Boyce-Codd Normal Form’ (BCNF) tightens this further so no column depends on anything other than the key.

The classic mnemonic — “the key, the whole key, and nothing but the key” — captures all three normal forms in one line. This guide breaks down what each rule actually means, using worked examples you can apply to your own schema.

What are normal forms, and why do they matter?

Proper data modeling is crucial when designing a new database, and the first step in modeling is applying a set of table-structuring rules we call the ‘Normal Forms‘.

These rules promote referential integrity, remove data redundancy, allow efficient querying, and help prevent editing anomalies. We covered the first of these rules, the aptly-named ‘First Normal Form‘, in part one of this series.

This is part two of Lee Asher’s complete guide series to the ‘Normal Forms’ set of database design rules.

While ‘First Normal Form’ (1NF) is fairly easy to remember, the higher-order forms have formal definitions that seem designed to confuse.

The goal of this article is twofold: first, to give you an easy, unforgettable way to remember these rules, and – more importantly – to show specifically why these rules exist, and the problems you can avoid by following them.

To recall these normal-form rules, just remember a simple mnemonic phrase, one that will be immediately familiar to any fan of legal dramas:

The Key, the Whole Key, and Nothing But the Key

This phrase has three parts, representing three different rules. We’ll discuss each separately.

The Key (every key must depend on the table’s key)

‘The Key’ rule simply means that every table needs a key, and every column in the table must depend on that key. In other words, the key value determines the value of all other data in the row.

Consider this table in a simple POS (point of sale) database, recording purchases at a retail shop:

As you can see, sale_id is the table’s primary key, and sale_amount, tax_amount, and total depend directly on that key.

However, the tax_rate column does not depend on the key. Static facts like this should be removed into a separate fact table, rather than duplicating that data throughout the entire table.

This first rule is so simple it barely merits mention. The next one is a bit more complex.

The Whole Key: Second Normal Form (2NF) explained

The ‘Whole Key’ rule requires that every table column must depend on the entire key.

That, in turn, only makes sense when a key has multiple parts – which brings us to the subject of multi-part keys. We’ll illustrate this with the table below, representing college course enrollments and final grades:

Since a student can take the same course multiple times (once per term), the unique key for each row is the combination of three different columns: student_id, course_id, and term.

A student’s grade depends on all three of these, so it complies with our rule – but the instructor column is only dependent on two of them: course_id and term.

As you can see from the repeated instructor name, this causes duplication of data and generates what’s known as an update anomaly. To change one fact – the instructor for each class – multiple rows must be edited.

This impacts performance but, more importantly, raises the possibility that two rows referring to the same class might disagree on instructor.

The bottom line: a fact should only be stored in one location – referred to in data modeling as the ‘single source of truth’ (SSOT).

 We can solve this problem by decomposing the original table into two new ones:

This ‘Whole Key’ rule is formally known as ‘2NF: Second Normal Form’. Now, you’ll never forget it.

The three-part key for this table is rather cumbersome to use, so most shops would also add a surrogate key to identify each row (perhaps enrollment_id or something similar).

In fact, some shops add surrogates for all tables, even when they have a single-column natural key.

However, that doesn’t change the fundamental point that a surrogate key stands in for the natural key; it doesn’t alter the column dependencies.

Nothing But the Key: Bryce-Codd Normal Form (BCNF) explained

The ‘Nothing But the Key’ rule requires that table columns only depend on the table key itself.

This time, we’ll start with a table that does correctly conform to the rule. Our example is a medical research firm wishing to perform statistical analysis on the blood type of people who’ve been diagnosed with certain types of cancer.

For each case, they store the patient blood type:

Expressed in the functional dependencies of data modeling language, the rule for this table is simply:

case_id    >>>   blood_type

Don’t be put off by the notation here. The arrow simply means the value on the left determines the value on the right. So, if you know the case_id, you also know the patient’s age.

The firm later expands their database to include patient name and address to enable direct contact with the patient. They add two new columns to the table:

This adds two new functional dependencies to the table:

case_id    >>>   patient_name

case_id   >>>  patient_address

However, this list isn’t yet complete. Patient address and blood type also depend on the patient name:

patient_name   >>>   patient_address

patient_name  >>>    blood_type

Now we have columns that are not only dependent on the table key, but also something else – so our rule is violated. To correct that violation, we must decompose patient data into a separate table.

This rule is formally known as Boyce-Codd Normal Form (BCNF). The latter name refers to Dr. Edgar Codd, who first fabricated these normalization rules and is immortalized in the full version of our mnemonic phrase: “the key, the whole key, and nothing but the key — so help me Codd”.

The update and delete anomalies BCNF prevents

Not correcting the violation would result in us having both an update anomaly and a delete anomaly.

The update anomaly would involve a patient whose cancer recurs appear twice in the table (potentially with different data each time).

The delete anomaly, on the other hand, means that deleting a patient’s case would also remove the association between the patient name, address, and blood type.

What about ‘Third Normal Form’?

You’ll notice we covered 2NF and BCNF, but what happened to ‘Third Normal Form’ (3NF)? Well, BCNF is essentially a slightly stricter version of 3NF’s big brother.

The two forms actually function identically except in the unusual case where a table has multiple overlapping composite keys. In brief: a table in BCNF is by definition in 3NF, and nearly every table in 3NF is also BCNF.

What are the key differences between BCNF and 3NF?

The difference between the two forms is really very simple (though you’d never know it from most college courses on the subject).

In 3NF, a column isn’t required to depend on the table key if that column itself is part of another key.

Let’s visualize this.

A company has a skill-building program it encourages its employees to join, based across various teams. While an employee can join multiple teams, they’re assigned just one mentor, and that mentor can only be assigned to one team – despite a team potentially having multiple mentors.

Here’s some sample data:

Expressed in the functional dependencies of data modeling language, the constraints for this table are:

(employee, team)  >>>   mentor

(mentor)   >>>   team

The second one is the problem: the team column depends on mentor, which is not ‘the whole key’. Thus, the table is not in BCNF.

It is, however, in 3NF, since the team itself is part of a key (a ‘prime attribute’ in modeling terms.) So what exactly is the problem with this form, and why would we want to move past it to BCNF?

Well, the example table has the dreaded delete anomaly. If employee “Schrute, D.” drops from the team “Office Conflict Resolution”, the fact that Smith is a mentor for this team is lost.

Decomposing the table into two BCNF-compliant tables solves that problem.

Can every table always reach BCNF? (No – here’s the exception)

A table can always be structured to at least the 3NF level, but a table in 3NF can’t always be converted to BCNF without losing a functional dependency.

Here’s an example hospital shift roster of nurses assigned to patients:

Each nurse can have multiple patients, but is assigned to one shift only. Each patient has a single nurse for each shift. In modeling terms, these two functional dependencies are represented as:

nurse   >>>   shift

(patient, shift) >>>   nurse

The possible candidate keys are (patient, nurse) and (patient, shift). See the overlap?

This is the only time you’ll see a table in 3NF but not in BCNF.

Why is this table in 3NF but not in BCNF?

This table isn’t BCNF because the shift column depends on nurse. If you know the value of nurse, you also know their shift – but nurse is not a possible key for the table.

3NF, however, does allows this, because shift is part of the (patient, shift) key – a prime attribute.

Now, watch what happens when we decompose this 3NF table into two separate BCNF-compliant tables:

Now, the restriction that a patient can only have one nurse per shift is lost.

You’ll notice I’ve changed the same data slightly, so that patient “Mallon, Mary” now has two nurses assigned to the same shift – something that wasn’t possible in the original table.

There are limitations – you can only recover and enforce the lost constraint by joining the two tables, for example.

Still, though, that’s better better than the redundancy and delete anomaly caused by leaving the table at 3NF.

To see this, in the original 3NF table, let’s assign a nurse assigned to three different patients:

If Nurse Barton changes shifts, three rows will require editing, not just one. This violates the ‘single source of truth’ rule as a nurse’s shift should only be stored in one location.

And, if a nurse has no patients, there’s no way to represent what shift they’re assigned to in the first place.

What’s next: ‘Fourth Normal Form’ and ‘Fifth Normal Form’

The last two rules are ‘Fourth Normal Form’ and ‘Fifth Normal Form’.

These are rarely taught in college data modeling classes, which is a shame because the situations they address crop up quite often in real-world databases.

We’ll cover their use in Part 3 of the series.

Have any thoughts or comments? Feel free to share them down below!