What is the difference between 3NF and BCNF?

Question

Can someone please explain the difference between 3NF and BCNF to me? It would be great if you could also provide some examples. Thanks.

Guizot

Answer 1

The difference between 3NF and BCNF is subtle.

3NF

Definition

A relation is in 3NF if it is in 2NF and no non-prime attribute transitively depends on the primary key. In other words, a relation R is in 3NF if for each functional dependency X ⟶ A in R, at least one of the following conditions are met:

1. X is a key or superkey in R
2. A is a prime attribute in R

Example

Given the following relation:

EMP_DEPT(firstName, employeeNumber, dateOfBirth, address, departmentNumber, departmentName)

An employee can only work in one department and each department has many employees.

The candidate key is employeeNumber.

Consider the following functional dependencies:

1. employeeNumber ⟶ firstName, dateOfBirth, address, departmentNumber
2. departmentNumber ⟶ departmentName

Given the definition above, it is possible to conclude that the relation EMP_DEPT is not in 3NF because the second functional dependency does not meet any of the 2 conditions of the 3NF:

1. departmentNumber is not a key or superkey in EMP_DEPT
2. departmentName is not a prime attribute in EMP_DEPT

BCNF

Definition

A relation R is in BCNF if it is in 3NF and for each functional dependency X ⟶ A in R, X is a key or superkey in R. In other words, the only difference between 3NF and BCNF is that in BCNF it is not present the second condition of the 3NF. This makes BCNF stricter than 3NF as any relation that is in BCNF will be in 3NF but not necessarily every relation that is in 3NF will be in BCNF.

Example

Given the following relation:

STUDENT_COURSE(studentNumber, socialSecurityNumber, courseNumber)

A student can assist to many courses and in a course there can be many students.

The candidate keys are:

1. socialSecurityNumber, courseNumber
2. studentNumber, courseNumber

Consider the following functional dependencies:

1. studentNumber ⟶ socialSecurityNumber
2. socialSecurityNumber ⟶ studentNumber

Given the definition above, it is possible to conclude that STUDENT_COURSE is not in BCNF as at least studentNumber is not a key or superkey in STUDENT_COURSE.

Unparalleled

how can you modify the two example so that they are 3NF and BCNF, respectively? – Intuitional

Remove (split into another table) departmentNumber ⟶ departmentName from first one which will make it 3NF and BCNF. In second one, both are equivalent so delete any one attribute studentNumber or sSnumber it'll be in BCNF – Messer

This is where I always get confused - when just the "key" is mentioned, is it the primary key or all the candidate keys or one of the candidate keys? – Bitthia

@Bitthia & Mosty Mostacho PKs are irrelevant to normalization. In this answer "key" means CK. The first sentence re 3NF, which mentions "the PK", is wrong. Otherwise the answer is clear about considering taking all CKs into account. – Chare

Can we say that the last example is in 3NF because both functional dependencies have a prime attribute of the relationship on the right-hand side? – Loving

@Loving A table is in 3NF "because" it meets the criterion for 3NF. That involves the set of all FDs that hold. We can reasonably say that a particular FD does or doesn't violate a particular condition of a particular definition. But if we only satisfy some condition(s) for some FD(s) individually then we are not justified in saying we have 3NF "because of" just that. – Chare