A relation R is in Fifth Normal Form (5NF) if and only if the following conditions are satisfied simultaneously:
1. R is already in 4NF.
2. It cannot be further non-loss decomposed.
5NF is of little practical use to the database designer, but it is of interest from a theoretical point of view and a discussion of it is included here to complete the picture of the further normal forms.
In all of the further normal forms discussed so far, no loss decomposition was achieved by the decomposing of a single table into two separate tables. No loss decomposition is possible because of the availability of the join operator as part of the relational model. In considering 5NF, consideration must be given to tables where this non-loss decomposition can only be achieved by decomposition into three or more separate tables. Such decomposition is not always possible as is shown by the following example.Consider the table below.
agent_company_product (Agent, Company, Product _Name)This table lists agents, the companies they work for and the products they sell for those companies. ‘The agents do not necessarily sell all the products supplied by the companies they do business with. An example of this table might be:
The table is necessary in order to show all the information required. Suneet, for example, sells ABC’s Nuts and Screws, but not ABC’s Bolts. Raj is not an age it for CDE and does not sell ABC’s Nuts or Screws. The table is in 4NF because it contains no multi-valued dependency. It does, however, contain an element of redundancy in that it records the fact that Suneet is an agent for ABC twice. But there is no way of eliminating this redundancy without losing information. Suppose that the table is decomposed into its two projections, PI and P2.
The redundancy has been eliminated, but the information about which companies make which products and which of these products they supply to which agents has been lost. The natural join of these projections over the ‘agent’ columns is:
The table resulting from this join is spurious, since the asterisked row of the table contains incorrect information. Now suppose that the original table were to be decomposed into three tables, the two projections, P I and P2 which have already shown, and the final, possible projection, P3.
If a join is taken of all three projections, first of PI and P2 with the (spurious) result shown above, and then of this result with P3 over the ‘Company’ and ‘Product name’ column, the following table is obtained:
This still contains a spurious row. The order in which the joins are performed makes no difference to the final result. It is not simply possible of decompose the ‘AGENT_COMPANY_PRODUCT’ table, populated as shown, without losing information. Thus, it has to be accepted that it is not possible· to eliminate all redundancies using normalization techniques, because it cannot be assumed that all decompositions will be non-loss.
But now consider the different case where, if an agent is an agent for a company and that company makes a product, then he always sells that product for the company. Under these circumstances, the ‘agent company product’ table as shown below:
The assumption being that ABC makes both Nuts and Bolts and that CDE makes Bolts only. This table can be decomposed into its three projections without loss of information as demonstrated below:
All redundancy has been removed, if the natural join of PI and P2 IS taken, the result is:
The spurious row as asterisked. Now, if this result is joined with P3 over the column ‘company ‘product_name’ the following table is obtained:
This is a correct re-composition of the original table and no loss decomposition into the three projections was achieved. Again, the order in which the joins are performed does not affect the final result. The original table, therefore, violated 5NF simply because it was non-loss decomposable into its three projections.
In the first case exemplified above, non-loss decomposition of the ‘agent_company -product’ table was not possible. In the second it was. If a table is non-loss decomposable as in the second case, it is said to be in violation of 5NF. The difference, of course, lay in certain semantic properties of the information being represented. These properties were not understandable simply by looking at the table, but had to be supplemented by further information about the relationship between products, agents and companies.
Detecting that a table violates 5 NF is very difficult in practice and for this reason this normal form has little if any practical application. The theoretical concept of fifth normal form is discussed in the following paragraphs.
Suppose that the statement, ‘The agent50mpany -product’ table is equal to the join of its three projections is to hold true, this is another way of saying that it Can be non-loss decomposed into its three projections and is equivalent to saying.
IF the tuple ‘agent X, company Y’ appears in P1
AND the tuple ‘agent X, product Z’ appears in P2
AND the tuple ‘company Y, product Z’ appears in P3
Then the row ‘agent X, company Y, product Z’ must have appeared in ‘agent_company _product’.
If the reader cares to re-examine the projections PI, P2, and P3 from the two versions of ‘ the table which were illustrated earlier, then, it will be seen that the earlier version which was in 5NF does not confirm to the above rule, whereas the later version, which violated 5NF does.
The rule is referred to as a Join. Dependency, because it holds good only if a table can be reconstituted without loss of information from the join of certain specified projections it.
The notation used for a join dependency on table Tips:
*(X, Y, Z)
Where X, Y … Z are projections of T.
Table T is said to satisfy the above join dependency, if it is equal to the join of the projections X, Y, Z.
Thus, the second example given of the table ‘agent_company product’ can be said to satisfy the join dependency:
*(PI, P2, P3)
In the discussion of the other further normal forms use was made of the concepts of functional and multi-valued dependencies. In dealing with 5NF the concept of join dependency has been introduced (in a very informal way).
5NF is defined by the statement
A table T is in fifth normal form if every join dependency in T is a consequence only of the candidate keys of T.
The second version of the table ‘agent_company product’ illustrated earlier’ violated
5NF, because the join dependency *(agent, company, product_name) was not a consequence only of the primary key for the table, but also a consequence of the tuple formation rule which was given earlier,
In the first, example of ‘agent_company Product’ there was no application of this rule, hence no join dependency other than that on the primary key. Thus, the table was in 5NF.
It can be shown that if a table is in 5NF, then, because join dependencies are the ‘ultimate’ form of dependency, it must also be in 4NF and thus confirm to all the further normal forms. The problem with this is that detecting join dependencies is, in practice, very difficult. For this reason, 5NF is largely of academic interest.
Dinesh Thakur holds an B.C.A, MCDBA, MCSD certifications. Dinesh authors the hugely popular