Join Dependencies and fifth Normal form

Joint dependency 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 decomposition 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.

Let R is a relation schema R1, R2, R3……..Rn be the decomposition of R. r( R ) is said to satisfy join dependency if and only if

Example –

Table – R1

 COMPANY PRODUCT C1 pendrive C1 mic C2 speaker C2 speaker

Company->->Product

Table – R2

 AGENT COMPANY Aman C1 Aman C2 Mohan C1

Agent->->Company

Table – R3

 AGENT PRODUCT Aman pendrive Aman mic Aman speaker Mohan speaker

Agent->->Product

Table – R1⋈R2⋈R3

 COMPANY PRODUCT AGENT C1 pendrive Aman C1 mic Aman C2 speaker speaker C1 speaker Aman

Agent->->Product

Fifth Normal Form / Projected Normal Form (5NF):

A relation R is in 5NF if and only if every join dependency in R is implied by the candidate keys of R. A relation decomposed into two relations must have loss-less join Property, which ensures that no spurious or extra tuples are generated, when relations are reunited through a natural join.

Properties A relation R is in 5NF if and only if it satisfies following conditions:

1. R should be already in 4NF.
2. It cannot be further non loss decomposed (join dependency)

Example Consider the above schema, with a case as “if a company makes a product and an agent is an agent for that company, then he always sells that product for the company”. Under these circumstances, the ACP table is shown as:

Table ACP

 AGENT COMPANY PRODUCT A1 PQR Nut A1 PQR Bolt A1 XYZ Nut A1 XYZ Bolt A2 PQR Nut

The relation ACP is again decompose into 3 relations. Now, the natural Join of all the three relations will be shown as:

Table – R1

 AGENT COMPANY A1 PQR A1 XYZ A2 PQR

Table – R2

 AGENT PRODUCT A1 Nut A1 Bolt A2 Nut

Table – R3

 COMPANY PRODUCT PQR Nut PQR Bolt XYZ Nut XYZ Bolt

Result of Natural Join of R1 and R3 over ‘Company’ and then Natural Join of R13 and R2 over ‘Agent’and ‘Product’ will be table ACP.

Hence, in this example, all the redundancies are eliminated, and the decomposition of ACP is a lossless join decomposition. Therefore, the relation is in 5NF as it does not violate the property of lossless join.

2 thoughts on “Join Dependencies and fifth Normal form”

1. Student says:

This helped me understand conceptually better than some other typed, but the examples have a lot of typos so it’s harder to follow along on them. If those get edited, I think this would be perfect.

error: Content is protected !!