Join Dependency

Learn via video course
FREE
View all courses
DBMS Course - Master the Fundamentals and Advanced Concepts
DBMS Course - Master the Fundamentals and Advanced Concepts
by Srikanth Varma
1000
5
Start Learning
DBMS Course - Master the Fundamentals and Advanced Concepts
DBMS Course - Master the Fundamentals and Advanced Concepts
by Srikanth Varma
1000
5
Start Learning
Topics Covered

The concept of Join Dependency is directly based on the concept of 5NF, or Fifth Normal Form. Similar to the functional or multivalued dependency, the 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 R1R_1, R2R_2,..., RnR_n. In a database management system, join dependency is a generalization of the idea of multivalued dependency.

Let R be a relation schema and R1R_1, R2R_2,..., RnR_n be the decomposition of R, R is said to satisfy the join dependency (R1R_1, R2R_2,..., RnR_n), if and only if every legal instance r ( R ) is equal to join of its projections on R1R_1, R2R_2,..., RnR_n.

Example of Join Dependency

Suppose we have the following table R:

E_NameCompanyProduct
RohanComp1Jeans
HarpreetComp2Jacket
AnantComp3TShirt
  • 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_NameCompany
RohanComp1
HarpreetComp2
AnantComp3

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

E_NameProduct
RohanJeans
HarpreetJacket
AnantTShirt

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

CompanyProduct
Comp1Jeans
Comp2Jacket
Comp3TShirt

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

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

E_NameCompanyProduct
RohanComp1Jeans
HarpreetComp2Jacket
AnantComp3TShirt

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

E_NameCompanyProduct
RohanComp1Jeans
HarpreetComp2Jacket
AnantComp3TShirt

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 RiR_i in a join dependency (i.e. R1R_1, R2R_2,..., or RnR_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 RiR_i in a join dependency (i.e. R1R_1, R2R_2,..., or RnR_n) is equal to the original relation R, the join dependency is trivial.

Read More: