FIFTH NORMAL FORM (5NF) - IBM Mainframe

The chances that you will ever get to use the fifth normal form are very few, because it requires semantically related multiple relationships, which are rare. Semantically related multiple relationships are two or more relationships among tables that are related closely enough so that they can be resolved into a single relationship. Fifth normal form specifies that they remain separate.

Consider the chemical analysis labs, which test products of various companies. Three tables exist, LAB, PRODUCT and COMPANY. Companies can offer one or more products, labs can test one or more products from one or more companies and a product can be tested in one or more labs and can be offered by one or more companies. Whenever a lab is equipped to test a product from any company, the lab is equipped to test the same product from all companies. If a lab is equipped to analyze a product from a given company, this can be recorded in a table with the following structure:

FIFTH NORMAL FORM (5NF)

The table contains three foreign keys expressing two relationships: the relationship between LABS and PRODUCTS and that between LABS and COMPANIES. The relationships are semantically related because they can be expressed using the same table. In other words, the relationship between PRODUCTS and COMPANIES is implied and expressed in this table.

There is nothing wrong with the above data structure, but it does not satisfy the fifth normal form and more than necessary entries are required in the table if the relationships are separated. Because a lab can test the same product for all companies who offer a given product, the following structure will be better:

FIFTH NORMAL FORM (5NF)

This allows the products that a given lab can test to be recorded and the companies whose products can be tested by a lab to be recorded with fewer entries. For example if lab A is newly equipped to do a spectroscopic analysis of products A, B and C offered by companies X and Y, the new structure requires five entries, 3 in the LAB_PRODUCT table and two in LAB_COMPANY table, to express this. To achieve the same, with the original structure we will require six entries.

The fifth normal form is not frequently invoked for the reason that the situation simply does not arise frequently.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

IBM Mainframe Topics