Join dependency or JD is a constraint that is similar to FD (functional dependency) or MVD (multivalued dependency). JD is satisfied only when the concerned relation is a join of a specific number of projections. Thus, such a type of constraint is known as a join dependency.

In this article, we will take a look at the Join Dependency in DBMS according to the GATE Syllabus for CSE (Computer Science Engineering). Read ahead to learn more.

**Table of Contents**

- What is Join Dependency in DBMS?
- Characteristics of Join Dependency in DBMS?
- Practice Questions on Join Dependency in DBMS
- FAQs

## What is Join Dependency in DBMS?

Whenever we can recreate a table by simply joining various tables where each of these tables consists of a subset of the table’s attribute, then this table is known as a Join Dependency. Thus, it is like a generalization of MVD. We can relate the JD to 5NF. Herein, a relation can be in 5NF only when it’s already in the 4NF. Remember that it cannot be further decomposed.

## Examples of Join Dependency in DBMS

### Example 1

<Student>

Stu_Name |
Stu_Skills |
Stu_Job (Assigned Work) |

Tag | Marketing | GK001 |

Barry | PR | GK002 |

Paulo | Graphic Designing | GK003 |

We can decompose the table given above into these three tables given below. And thus, it is not in the Fifth Normal Form.

<Student_Skills>

Stu_Name |
Stu_Skills |

Tag | Marketing |

Barry | PR |

Paulo | Graphic Designing |

<Student_Job>

Stu_Name |
Stu_Job |

Tag | GK001 |

Barry | GK002 |

Paulo | GK002 |

<Job_Skills>

Stu_Skills |
Stu_Job |

Marketing | GK001 |

PR | GK002 |

Graphic Designing | GK003 |

Our Join Dependency would be:

{(Stu_Name, Stu_Skills ), ( Stu_Name, Stu_Job), (Stu_Skills, Stu_Job)}

The relations given above have join dependency. Thus, they do not happen to be in 5NF. It means that the join relation of the three relations given above is equal to the very original relation <Student>.

### Example 2

Let us consider some special classes of join dependencies that help us in capturing data dependencies that are present in a data structure that is hierarchical in nature.

This hierarchical organisation informs the reader about the rooms, and the students currently living in the room depend only on the hostel but not the utilities present in that hostel. Since hostels have multiple rooms, FDs are NOT adequate when we want to describe the data dependency among hostels and rooms or utilities.

In such a case, the multivalued dependencies,

Hostel ->-> room or

Hostel ->-> utilities hold

Thus, using the first-order hierarchical decomposition, one would be enabled to represent data dependencies that are present in a hierarchical data structure in a natural way. Thus, one can store the hostel database as a lossless join of the following:

Hostel_utility (hostel, utilities),

Hostel_room (hostel, room, student, syllabus, classes, teacher)

### Example 3

The relation X would satisfy join dependency whenever X is equal to the join of X1, X2, ….. Xn, where Xi happens to be a subset of a set of attributes of X.

Relation X

Sec |
Language |
Name |

Sec_A | Java | Rimona |

Sec_B | C++ | Zelda |

Sec_C | HTML | Jack |

Sec_D | Java | Tia |

Here,

sec ->-> name

sec ->-> language

The relation given above is in 4NF. These anomalies can occur in the relations that are in 4NF if the primary key contains three or more fields. Thus, the primary key is (sec, language, name). Sometimes, when we decompose a relation into two of the smaller relations, the redundancy isn’t removed here. In such cases, decomposing the relation is possible in three or more than three relations using the Fifth Normal Form.

Thus, the relation given above says that sec offers many elective languages that are taken by a combination of their students. These students have their individual opinion to choose their languages. Thus, all three fields are required to represent this data and information.

This relation does not display non-trivial MVDs. It is because the attributes, language and name, are dependent. Thus, these are related to one another (A Functional Dependency subject -> the existing name). This relation cannot be decomposed into two relations (sec, language) and (sec, name).

Thus, we cannot decompose this relation into the following relations:

X1(sec, language)

X2(sec, name) and

X3(language, name), and we can show here that this decomposition is lossless in nature.

X1

Sec |
Language |

Sec_A | Java |

Sec_B | C++ |

Sec_C | HTML |

Sec_D | Java |

X2

Sec | Name |

Sec_A | Rimona |

Sec_B | Zelda |

Sec_C | Jack |

Sec_D | Tia |

X3

Language |
Name |

Java | Rimona |

C++ | Zelda |

HTML | Jack |

Java | Tia |

## Characteristics of Join Dependency in DBMS?

- The join decomposition is like a further generalization of the Multivalued dependencies.
- In case the join of X1 and X2 over C is equal to relation X, then one can say that there exists a join dependency (JD).
- Where X1 and X2 are the decompositions X1(A, B, C) and X2(C, D) of a given relation X (A, B, C, D).
- Alternatively, X1 and X2 are lossless forms of decomposition of X.
- A JD â‹ˆ {X1, X2,…, Xn} holds over a relation X if X1, X2,….., Xn is a lossless-join type of decomposition.
- The *(A, B, C, D), (C, D) happen to be a Join Dependency of X if the join of the join’s attribute happens to be equal to the relation X.
- Here, we use the *(X1, X2, X3) to indicate that relation X1, X2, X3 and so on are a Join Decomposition of X.

## Practice Questions on Join Dependency in DBMS

**1. **What is the other name of the project-join normal form?

**A. **Fifth Normal Form

**B. **Sixth Normal Form

**C. **Trivial Normal Form

**D. **Join Normal Form

**Answer: A. **Fifth Normal Form

**2. **We use the preceding inclusion dependencies when we want to represent which of these?

**A. **Algorithmic Constraint

**B. **Referential Integrity Constraint

**C. **Trivial Constraint

**D. **Algorithmic Constant

**Answer: B. **Referential Integrity Constraint

## FAQs

### What is join dependency? Give examples.

Whenever we can recreate a table by simply joining various tables where each of these tables consists of a subset of the tableâ€™s attribute, then this table is known as a Join Dependency.

Thus, it is like a generalization of MVD. We can relate the JD to 5NF. Herein, a relation can be in 5NF only when itâ€™s already in the 4NF. Remember that it cannot be further decomposed.

### What is multivalued and join dependency in DBMS?

A multivalued dependency is a case in which a determinant gets associated with some set of values. Whenever isolated, the MVD does not have modification anomalies.

Join dependency or JD is a constraint that is similar to FD (functional dependency) or MVD (multivalued dependency). JD is satisfied only when the concerned relation is a join of a specific number of projections. Thus, such a type of constraint is known as a join dependency.

### What is join dependency in 5NF?

Some relations can be in 5NF whenever it satisfies the 4NF and there exists no join dependency. Thus, a relation can have join dependency in case it can be recreated when we join multiple sub relations, while each of the sub relations contains a subset of the original relationâ€™s attributes.

### What is a multivalued dependency, and how do they affect the normalization process?

A multivalued dependency is a case in which a determinant gets associated with some set of values. Whenever isolated, the MVD does not have modification anomalies. Thus, the tables that have these isolated dependencies can be considered to be in the 4NF (fourth normal form).

Keep learning and stay tuned to get the latest updates onÂ GATE ExamÂ along withÂ GATE Eligibility Criteria,Â GATE 2023,Â GATE Admit Card,Â GATE Syllabus for CSE (Computer Science Engineering),Â GATE CSE Notes,Â GATE CSE Question Paper, and more.

**Also Explore,**

## Comments