[Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 706
  • Last Modified:

fifth normalization form

I like to  know what would cause someone to use the fifth normalization? I can't really understand why someone would break a table down like that just to put it back together with a "join". I can see how it is done, but what logic would someone have... what reason is there?
1 Solution
Normalisation is a subject open to interpretation. It is generally accepted that 3nf is usually the most efficient level of normalisation, however this in not always the case and is always open to developer opinion. Someone taking a design to 5nf would generally be regarded as a pedantic w**ker, doing it for kicks not for any practical purpose.

Muhammad Ahmad ImranDatabase DeveloperCommented:
The fifth normal form deals with join-dependencies which is a generalisation of the MVD. 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.  CP1000  John Smith
Mathematics MA1000  John Smith
Comp. Sc.  CP2000 Arun Kumar
Comp. Sc. CP3000 Reena Rani
Physics PH1000 Raymond Chew
Chemistry CH2000  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 loosing 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.

Someone who thinks the higher the number, the better it must be! A VP recently asked what it would take to get one of our databases to fifth normal form.... I'll let you imagine the reaction of the DBA.

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now