Join Dependencies and Fifth Normal Form

Join Dependencies and Fifth Normal Form                                 

Concept of Join Dependencies

• Join decomposition is a further generalization of  Multivalued dependencies.

• If the join of R1 and R2 over C is equal to relation R, then we can say that a Join Dependency (JD) exists.

• Where R1 and R2 are the decompositions R1(A, B, C) and R2(C, D) of a given relations R (A, B, C, D).

• Alternatively, R1 and R2 are a lossless decomposition of R.

• A JD  (R1, R2,..., Rn} is said to hold over a relation R if R1, R2,....., Rn is a lossless-join decomposition.

• The *(A, B, C, D), (C, D) will be a JD of R if the join of join's attribute is equal to the relation R.

• Here, *(R1, R2, R3) is used to indicate that relation R1, R2, R3 and so on are a JD of R.

Concept of Fifth Normal Form

The database is said to be in 5NF if -

i) It is in 4^th Normal Form

ii) If we can decompose table further to eliminate redundancy and anomalies and when we rejoin the table we should not be losing the original data or get a new record (join Dependency Principle)

The fifth normal form is also called as project join normal form

For example - Consider following table

Here we assume the keys as {Seller, Company, Product}

The above table has multivalued dependency as

Seller → {Company, Product). Hence table is not in 4^th Normal Form. To make the above table in 4^th normal form we decompose above table into two tables as

The above table is in 4^th Normal Form as there is no multivalued dependency. But it is not in 5th normal form because if we join the above two table we may get

To avoid the above problem we can decompose the tables into three tables as Seller_Company, Seller_Product, and Company Product table

Thus the table in in 5th normal form.

Review Question

1. Exemplify the join dependency and thew fifth normal form.  AU: Dec.-19, Marks 6