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
|