Join Dependency in DBMS - Scaler Topics

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.

Scope

This article explains join dependency with the help of a suitable example.
It also explains the relationship between the fifth normal form and the join dependency, however we wouldn't be discussing either 4NF, or 5NF in detail.

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.