# DBMS/U4 Topic 8 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:

- R should be already in 4NF.
- 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.

## 1 Comment »