What are database normal forms: 1NF, 2NF, 3NF, BCNF? Difference between 3NF and BCNF.

Assumed reader is already acquainted with relational databases theory.
Definitions of 2 NF and 3 NF is given asusming only one candidate key, which is thus the primary key.

1 NF

A relation is in 1NF if and only if all underlying domains contain scalar values only.

2 NF

A relation is in 2NF if and only if it is in 1NF and every nonkey attribute is irreducibly dependent on the primary key.

3NF

A relation is in 3NF if and only if it is in 2NF and every nonkey attribute is nontransitively dependent on the primary key.

BCNF

A relation R is in BCNF if and only if for every one of its functional dependencies X → Y, at least one of the following conditions hold:

• X → Y is a trivial functional dependency (Y ⊆ X)
• X is a superkey for relation R

Superkey is a set of attributes of a relation schema upon which all attributes of the schema are functionally dependent. For example, if we have relation schema with attributes {X, Y, Z, Value}, then superkey is a subset  {X, Y, Z}→ Value.

Difference between 3NF and BCNF

Difference between 3NF and BCNF forms could be only if relation has at least two potential keys which is composite and two or more potential keys are overlapped  (i.e. has one or more common attributes).
Let suppose we had relation variable SST with attributes  Student, Subject, Teacher.
There is two constraints for this relation:

• Every student is learning the subject only in the one teacher;
• Every teacher teaches only one subject, but each subject could be teached by several teachers.

Below is the example of relation variable value:

Functional dependencies which determinated by constraints:
{ Student, Subject} → Teacher
{Teacher} → {Subject}

Candidate keys: {Student, Subject}, {Student, Teacher}.
As you see we have two candidate keys which is composite and it are overlapped, because attribute Student is common attribute.

Is relation variable SST in 3NF?

Answer is yes, because in both cases non-key attribute has irreducibly non-transitive dependency from key: {Student, Subject} → Teacher or {Student, Teacher} → Subject.

Is relation variable SST in BCNF?

Answer is no, because for dependency {Teacher} → Subject we don't have potential key where determinant of it is Teacher.


Refrences
[1]. Normal forms on trumpetpower.com
[2]. Date, C. J. (1999), An Introduction to Database Systems (8th ed.). Addison-Wesley Longman. ISBN 0-321-19784-4.
[3]. Candidate key, the free encyclopedia