'Coding has over 700 languages', '67% of programming jobs aren’t in the technology industry', 'Coding is behind almost everything that is powered by electricity'
Last Updated: Dec 31, 2023
Easy

Recursive Relationship in DBMS

gp-icon
Dbms - Database management systems
Free guided path
12 chapters
93+ problems
gp-badge
Earn badges and level up
recursive relationship in dbms

Introduction

If the same entity type participates more than once in a relationship type in a different role, it is called a recursive relationship.

Assume that one team is tasked with supervising the other teams. This supervisory team ensures that each team's data portion is recorded. The supervision relationship is a recursive relationship because the same entity, a particular team, participates in the relationship more than once, as both a supervisor and a supervisee. We draw two lines between team and supervision to depict the problem and name the two possible roles. A recursive relationship is when the same entity participates in the relationship more than once.

The ER diagram below shows recursive relationships.

We label two lines between team and supervision with the two possible roles to draw the relationship.

recursive relationship

What is a recursive relationship?

If an entity type appears more than once in a relationship, it is said to be recursive. The parent entity or table and the child entity or table in this kind of relationship are identical. Recursive relationships involve the same entity type participating in a relationship type multiple times while taking on a different role each time. An example of a typical business rule states, "An employee supervises other employees."

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

Introduction to Recursive Relationship in DBMS

In DBMS, a recursive connection is a particular kind of relationship between two entities of the same kind. We already know that a relationship is said to be recursive if an entity type appears more than once in it. A recursive connection in database management systems (DBMS) is a non-identifying link between two entities or tables that symbolises the possibility of one firm owning another.

Also read, File System vs DBMS

Recursive Relationship in ER Diagram

A recursive relationship is one that exists between two entities that belong to the same kind. This indicates that there is a relationship between many examples of the same entity type. In this case, the same entity type participates in a relationship type more than once, playing a different role in each instance. In other words, there has always been a connection between events that occur in two distinct things. An employee can oversee several employees as one form of a recursive relationship.

Illustration 1

For more clarity, let's look for another example -

A normal relationship looks like this : 
 

recursive relationship illustration


In this relationship, entity 1 participate once, and entity 2 also participates once.

But what if we create an entity that relates with itself?

Then that type of relationship is called a recursive relationship. It can be represented as: 

recursive relationship illustration

Let us consider there is an employee table :

It contains employee ID (empid) and Manager ID (mgrid)

empid

mgrid

11

2

22

3

33

4

44

x

 

In the above table employee 11 is managed by 2, 22 by 3, 33 by 4, and 44 by x.

We have to understand that in the relationship between employee and manager, there is only one entity, the employee, which is creating a relationship by itself.

So we can show the relation of this particular table like this : 
 

recursive relationship illustration

Illustration 2

Let assume an employee (as supervisor) entity, and we will call it on another side as an employee(supervisee). So the relationship between them is supervised.

Let us suppose employee E1 is the supervisor of E2 and E3. 

Under supervisor, many employees can work just like E1 is the supervisor of E2 and E3, but E2 cannot have two supervisors and same as E3. We can say that an employee cannot have two bosses in simple language. He can only have only one boss.  

Let assume that E4 is the manager of E1 and E6 is the CEO.

 

recursive relationship hierarchy

So, E1 has a manager above him, then E1 is now a supervisee.

So hierarchy can look something like  this:

 

Cardinality of Supervisor

Cardinality of Supervisor

Cardinality: N (Many)  ∵  supervisors can have many supervisees 

Cardinality of supervisee 

Cardinality: 1 ∵  supervisee can have only one supervisor.

Not every employee needs to be a supervisor, so not all the entity employees need to participate in supervisor. Also, not every employee needs a supervisor because the CEO (E6) cannot have a supervisor.

Let us see how this recursive relationship is shown in the ER diagram.

recursive relationship illustration

Note- While writing cardinality in ER diagram, the position is exchanged. As shown above, see positions of 1 and N.

Recursive Relationship in ER Diagram

In an ER diagram, a relationship between two entities of the same kind is known as a recursive relationship. This implies that an entity can have relationships with itself when modelling hierarchies or networks where one entity can relate to many other entities of a similar sort, recursive relationships are frequently employed.

We utilise a self-join to show a recursive relationship in an ER diagram. A join between a table and itself is known as a self-join. In other words, we establish a connection between entities of the same type. The above-mentioned examples are the illustrations of recursive relationships in ER diagrams. Now, we will see the examples of recursive relationships in DBMS.

Examples of Recursive Relationship in DBMS

The following are some examples of recursive relationships in DBMS:-

Employee-Manager Relationship

In an organization, employees can have managers who are also employees within the same organization, this means they will be a part of the employee table as well. This can be modeled as a recursive relationship because the foreign key for managerID will point to the employeeID or the primary key of the employee table.

Folder-Subfolder Relationship

In a file management system, folders can contain subfolders, and subfolders can further contain subfolders, this hierarchical relationship is represented using a recursive relationship.

Comment Replies

A commenting system that allows users to recursively reply to comments for eg. Reddit, is another example of a recursive relationship, where a comment can have a parent comment, which is denoted using a foreign key to the same comments table. 

Frequently Asked Questions

How are recursive relationships represented in ER Diagram?

We use a self-join, or a join between a table and itself, to depict a recursive relationship. We establish a connection between entities of the same type. We label the two lines between entity and relationship with the two possible roles to draw the situation.

What is the recursive relationship? 

The recursive relationship is the relationship among instances (rows) of the same entity type or, we can say, the relationship between two entities of the same type.

What is an ER diagram?

An Entity Relationship Diagram (ERD) represents the relationships between entities in a database. It is usually referred to as an ER Diagram.

What is an example of a recursive relationship set?

On an organizational chart, an employee may have relationships with other employees who are also in managerial positions. This is an example of a recursive relationship set. Similar to this, users of social networks can establish friendships with other users.

What is recursive relationship 1 to 1?

A recursive relationship of one-to-one means that two entities of the same entity type are related to one other. These relationship structures describe hierarchical lines at any level, including the lowest one. One employee, for instance, has just one employee ID.

Conclusion

In this blog, we have learned about Recursive relationships through some illustrations. Recursive relationships are the relationship between two entities of the same entity type.

Also, we learned about how the recursive relationship is shown in the ER diagram.

Visit here to learn more about different topics related to Database management systems.

Recommended Readings:

Also, try Coding Ninjas Studio to practice programming problems for your complete interview preparation. Ninja, don't stop here; check out the Top 100 SQL Problems to get hands-on experience with frequently asked interview questions and land your dream job.

Previous article
Minimization of ER Diagram
Next article
Impedance Mismatch
Guided path
Free
gridgp-icon
Dbms - Database management systems
12 chapters
93+ Problems
gp-badge
Earn badges and level up
Live masterclass