4NF

Monday, 16 September 2002
The fourth normal form has annoyed me for almost 2 decennia already.

Thesis

4NF is nonsence
for those who apply
0NF, 1NF, 2NF and 3NF
well.

Do you have a convincing
example of 4NF?

contact me
immediately!

To me, Codd's three normal forms for logical database design seem complete.
  • You can't take anything out.
  • Additions are redundant.
A reader of the SUMit database design method sent me 2 URLs containing 4NF examples. Those examples did not convince me.

See my alternative solution below, fully in concert with my theses, to a correct third normal form.

1. Puppies with costumes

The first example, at gslis.utexas.edu/.../normover.html, descibes a 4NF solution for puppies that
  • know multiple tricks
  • can wear multiple costumes.
Alternative solution to correct 3NF
Start with the right set of data elements in UNF, including the costume that puppies can wear, but without redundant object-ids. Choose the right keys in FNF so for a given key value each data element can have only one value. Remove partkey dependencies in SNF. Puppie-costume automatically remains as link table. Create for costume_name a key-only table if the list of costumes provides added value. Solve the dependency between data elements kennel name and kennel location.
0NF 1NF 2NF 3NF
puppy number
puppy name
kennel name
kennel location
trick name
skill level
trick where learned
costume name
puppy number
trick name
costume name

kennel name
puppy name
kennel location
skill level
trick where learned
puppy number
puppy name
kennel name
kennel location
kennel name
kennel location

puppy number
puppy name
* kennel name

puppy number
trick name

skill level
trick where learned
puppy number
trick name

skill level
trick where learned
puppy number
costume name
puppy number
costume name
costume name costume name

2. Students attending courses

The second example, at www.cs.jcu.edu.au/.../node12.html, describes 4NF as a solution for dependencies between key elements.

Excuse me? Why would an element promote to be key in 1NF if it is dependent upon another? Apply the first normal form correctly and you won't need 4NF anymore.
Alternative solution to correct 3NF
Choose the complete set of dataelements in UNF. For a given student number and course number the orther data elements have only one possible value. student name is dependent on student number. course name is dependent on course number. 3NF equals 2NF in this example. There are no data elements with dependencies.
0NF 1NF 2NF 3NF
student number
student name
course number
course name
date of enrolment
student number
course number

student name
course name
date of enrolment
student number
student name
student number
student name
course number
course name

course number
course name

student number
course number

date of enrolment

student number
course number

date of enrolment
Till next week,
Nut

Special thanks to Denis Dougall (Kingston, Canada) for the discussion on 4NF by email.