Download the BYJU'S Exam Prep App for free GATE/ESE preparation videos & tests - Download the BYJU'S Exam Prep App for free GATE/ESE preparation videos & tests -

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?

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

Q1

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.

Q2

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.

Q3

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.

Q4

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 CriteriaGATE 2023GATE Admit CardGATE Syllabus for CSE (Computer Science Engineering)GATE CSE NotesGATE CSE Question Paper, and more.

Also Explore,

Comments

Leave a Comment

Your Mobile number and Email id will not be published.

*

*