The fifth normal form deals with join-dependencies which is a generalization of the MVD (multi-valued dependency). The aim of fifth normal form is to have relations that cannot be decomposed further. A relation in 5NF cannot be constructed from several smaller relations.
A relation R satisfies join dependency (R1, R2, ..., Rn) if and only if R is equal to the join of R1, R2, ..., Rn where Ri are subsets of the set of attributes of R.
A relation R is in 5NF (or project-join normal form, PJNF) if for all join dependencies at least one of the following holds.
(a) (R1, R2, ..., Rn) is a trivial join-dependency (that is, one of Ri is R)
(b) Every Ri is a candidate key for R.
An example of 5NF can be provided by the example below that deals with departments, subjects and students.
| dept | subject | student |
|
Comp. Sc. Mathematics Comp. Sc. Comp. Sc. Physics Chemistry |
CP1000 MA1000 CP2000 CP3000 PH1000 CH2000 |
John Smith John Smith Arun Kumar Reena Rani Raymond Chew Albert Garcia |
The above relation says that Comp. Sc. offers subjects CP1000, CP2000 and CP3000 which are taken by a variety of students. No student takes all the subjects and no subject has all students enrolled in it and therefore all three fields are needed to represent the information.
The above relation does not show MVDs since the attributes subject and student are not independent; they are related to each other and the pairings have significant information in them. The relation can therefore not be decomposed in two relations
(dept, subject), and
(dept, student)
without losing some important information. The relation can however be decomposed in the following three relations
(dept, subject), and
(dept, student)
(subject, student)
and now it can be shown that this decomposition is lossless.