Recursive Relationship

Amisha Purswani
Last Updated: May 13, 2022

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.

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)

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 : 

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.

FAQs

  1. How are recursive relationships represented in ER Diagram?
    We label the two lines between entity and relationship with the two possible roles to draw the situation.
     
  2. 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.
     
  3. 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.
     
  4. What do you mean by cardinality in the ER diagram?
    Cardinality defines the maximum number of relationship instances in which an entity can participate.  
     
  5. Why are ER diagrams necessary in designing the database?
    ER diagrams analyze existing databases to identify and resolve logic or deployment issues. Drawing the diagram should reveal where the issue is. The diagrams are used to design and analyze relational databases used in business processes.
     
  6. Who introduced the Entity-relationship model?
    Entity-relationship modeling was developed for database and design by Peter Chen and introduced in 1976. An entity-relationship model (or ER model) describes interrelated things of interest in a specific knowledge domain. A basic ER model comprises entity types (which classify the things of interest) and specifies relationships between entities.

Key Takeaways

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.

Also, try CodeStudio 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.

Was this article helpful ?
1 upvote

Comments

No comments yet

Be the first to share what you think