Join Dependency

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:

• 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.

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

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

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

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

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

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.

Read More:

• Functional dependency in DBMS.
• Inclusion Dependency in DBMS.
• Join in DBMS.
• Generalization in DBMS.