Recursive Relationship in DBMS

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 : 
 

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: 

Let us consider there is an employee table :

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

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 : 
 

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.

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

So hierarchy can look something like  this:

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.

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

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:

• What is Recursion
• Super Keys in DBMS
• Aggregation in DBMS

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.