Join Dependency in DBMS

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?
  • Examples of Join Dependency in DBMS
    • Example 1
    • Example 2
    • Example 3
• 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>

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>

<Student_Job>

<Job_Skills>

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

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

X2

X3

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

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,

• Functional Dependency in DBMS
• Partial Dependency in DBMS
• Transitive Dependency in DBMS
• Join Dependency in DBMS
• Introduction to DBMS
• Lossless Decomposition in DBMS
• Lossy Decomposition in DBMS
• Multivalued Dependency in DBMS
• Decomposition in DBMS
• First Normal Form in DBMS