# Join Dependency

**Challenge Inside! :**Find out where you stand! Try quiz, solve problems & win rewards!

## Overview

The concept of **Join Dependency** is directly based on the concept of **5NF**, or Fifth Normal Form. Similar to functional or multivalued dependency, join dependency is a constraint. It is satisfied only if and only if the relation concerned is the **joining of a set of projections**.

## What are Join Dependencies in DBMS?

A Join Dependency on a relation schema R, specifies a constraint on states, r of R that every legal state r of R should have a **lossless join decomposition** into **$R_1$, $R_2$,..., $R_n$**.
In a database management system, join dependency is a generalization of the idea of multivalued dependency.

Let R be a relation schema and $R_1$, **$R_2$,..., $R_n$** be the decomposition of R, R is said to satisfy the join dependency **($R_1$, $R_2$,..., $R_n$)**, if and only if every legal instance r ( R ) is equal to join of its projections on **$R_1$, $R_2$,..., $R_n$**.

## Example of Join Dependency

Suppose we have the following table R:

E_Name | Company | Product |
---|---|---|

Rohan | Comp1 | Jeans |

Harpreet | Comp2 | Jacket |

Anant | Comp3 | TShirt |

- We can break, or decompose the above table into three tables, this would mean that the table
**is not in 5NF!** - The
**three decomposed tables**would be:

**1. R1: The table with columns E_Name and Company.**

E_Name | Company |
---|---|

Rohan | Comp1 |

Harpreet | Comp2 |

Anant | Comp3 |

**2. R2: The table with columns E_Name and Product.**

E_Name | Product |
---|---|

Rohan | Jeans |

Harpreet | Jacket |

Anant | TShirt |

**3. R3: The table with columns Company and Product.**

Company | Product |
---|---|

Comp1 | Jeans |

Comp2 | Jacket |

Comp3 | TShirt |

**Note:** If the natural join of all three tables yields the relation table R, the relation will be said to have join dependency.

Let's try to figure out whether or not R has join dependency.

**Step 1-** First, the natural join of R1 and R2:

E_Name | Company | Product |
---|---|---|

Rohan | Comp1 | Jeans |

Harpreet | Comp2 | Jacket |

Anant | Comp3 | TShirt |

**Step 2-** Next, let's perform the natural join of the above table with R3:

E_Name | Company | Product |
---|---|---|

Rohan | Comp1 | Jeans |

Harpreet | Comp2 | Jacket |

Anant | Comp3 | TShirt |

In the above example, we do get the same table R after performing the natural joins at both steps, luckily.

Therefore, our join dependency comes out to be: {(E_Name, Company ), (E_Name, Product), (Company, Product)}

Because the above-mentioned relations are joined dependent, they are not 5NF. That is, a join relation of the three relations above is equal to our initial relation table R.

## Join Dependencies and Fifth Normal Form (5NF)

- If a relation is in 4NF and
**does not contain any join dependencies**, it is in 5NF. - To avoid
**redundancy**, 5NF is satisfied when all tables are divided into as many tables as possible.

**Conclusion**: if a relation has join dependency, it won't be in 5NF.

## When is a Join Dependency trivial?

A Join Dependency is **trivial**, if one of the relation schemas $R_i$ in a join dependency **(i.e. $R_1$, $R_2$,..., or $R_n$)** is equal to the original relation R.

## Conclusion

Let us now conclude what we studied in the article:

- The table is in
**Join Dependency**if it can be reproduced by connecting numerous tables and each of these tables has a subset of the table's attributes. - The relation between 5NF and Join Dependency is that a relation is in 5NF if it is in 4NF and does not have any join dependencies.
- If one of the relation schemas $R_i$ in a join dependency
**(i.e. $R_1$, $R_2$,..., or $R_n$)**is equal to the original relation R, the join dependency is trivial.