Now these relations are in 4NF because all multi valued attributes Course and

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