Now these relations are in 4NF, because all multi-valued attributes (Course and
Committee) have been placed in relations by themselves. Moreover this approach
overcomes the problem of various approaches discussed in MVD section.
4.8 Join Dependency and Fifth Normal Form
5NF is of little practical use to the database designer, but may be important from
theoretical point of view. For a relation to be in a particular normal form, we generally
decompose the relation into two separate relations in such a way that there should not be
any loss of information. No-loss decomposition is possible because of the availability of
the join operator as part of the relational model.
More technically, in 5NF, we use the concept of Join Dependency, which is a generalized
form of Multi-valued dependency. A join dependency (JD), denoted by JD [R1, R2,….Rn
specified on relation scheme R, specifies a constraint on the states r of R. the constraint states
that every legal state r of R should have a loss-less join decomposition into R1, R2, ….Rn.
A relation is in 5Nf if it is in 4NF and cannot be further non-loss decomposed.
No loss decomposition is possible because of the availability of the join operator in
Relational model. Such decomposition can only be achieved by decomposition into three
or more separate tables. Such decomposition is not always possible as in some cases
we can loose some information if we do the decomposition. Taking the following example,
we can show this:
Consider the relation COMPANY_PRODUCT_SUPPLIER
COMPANY
PRODUCT
SUPPLIER
Godrej
Soap
Mr. X
Godrej
Shampoo
Mr. X
Godrej
Shampoo
Mr. Y
Godrej
Shampoo
Mr. Z
H.Lever
Soap
Mr. X
H. Lever
Soap
Mr. Y
H. Lever
Shampoo
Mr. Y
Fig. 7.16
The table is in fourth normal form as there is no multi-valued dependency. It does,
however, have a lot of redundancy. For example Mr. X is a supplier for Godrej for twice
and Mr. Y is also for twice for Hindustan Lever Company. But if we decompose the table
then we will loose information, which can be shown as follows:

66
Data Base Management systems
Notes
Amity Directorate of Distance & Online Education
COMPANY_PRODUCT
COMPANY
PRODUCT
Godrej
Soap
Godrej
Shampoo
H.Lever
Soap
H. Lever
Shampoo
COMPANY_SUPPLIER
COMPANY
SUPPLIER
Godrej
Mr. X
Godrej
Mr. Y
Godrej
Mr. Z
H.Lever
Mr. X
H. Lever
Mr. Y
Fig. 7.17
The above said redundancy has been eliminated but we have lost the information. For
example if we want to display the products and their suppliers, then we will have to use
the join based on the company attribute. The result will display some spurious records.
For Mr. Z, it will display both the products, soap and shampoo as the company for which
Mr. Z is the supplier (Godrej) is producing soap and shampoo, which is correct.
Now suppose that the original tables were to be decomposed in three parts, company_
product, company_supplier and one more product_supplier, which is as shown
PRODUCT_SUPPLIER
COMPANY
SUPPLIER
Soap
Mr. X
Soap
Mr. Y
Shampoo
Mr. X
Shampoo
Mr. Y
Shampoo
Mr. Z
Fig. 7.17
If a join is taken of all the projections, again we will get wrong results. So it is not
possible to decompose the original table without loosing information. Thus using the

#### You've reached the end of your free preview.

Want to read all 19 pages?

- Winter '17
- DR Mubashir
- Relational Database