Open In App
Related Articles

Recursive Relationships in ER diagrams

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Report issue
Report

Prerequisite – ER Model 

A relationship between two entities of a similar entity type is called a recursive relationship. Here the same entity type participates more than once in a relationship type with a different role for each instance. In other words, a relationship has always been between occurrences in two different entities. However, the same entity can participate in the relationship. This is termed a recursive relationship. 

Recursive relationships are often used to represent hierarchies or networks, where an entity can be connected to other entities of the same type. For example, in an organizational chart, an employee can have a relationship with other employees who are also in a managerial position. Similarly, in a social network, a user can have a relationship with other users who are their friends.

To represent a recursive relationship in an ER diagram, we use a self-join, which is a join between a table and itself. In other words, we create a relationship between the same entity type. The self-join involves creating two instances of the same entity and connecting them with a relationship. One instance is considered the parent, and the other instance is considered the child.

We use cardinality constraints to specify the number of instances of the entity that can participate in the relationship. For example, in an organizational chart, an employee can have many subordinates, but each subordinate can only have one manager. This is represented as a one-to-many (1:N) relationship between the employee entity and itself.

Overall, recursive relationships are an important concept in ER modeling, and they allow us to represent complex relationships between entities of the same type. They are particularly useful in modeling hierarchical data structures and networks.

Example: Let us suppose that we have an employee table. A manager supervises a subordinate. Every employee can have a supervisor except the CEO and there can be at most one boss for each employee. One employee may be the boss of more than one employee. Let’s suppose that REPORTS_TO is a recursive relationship on the Employee entity type where each Employee plays two roles.

  1. Supervisor
  2. Subordinate


 Supervisors and subordinates are called “Role Names”. Here the degree of the REPORTS_TO relationship is 1 i.e. a unary relationship.  

  • The minimum cardinality of the Supervisor entity is ZERO since the lowest level employee may not be a manager for anyone.
  • The maximum cardinality of the Supervisor entity is N since an employee can manage many employees.
  • Similarly, the Subordinate entity has a minimum cardinality of ZERO to account for the case where CEO can never be a subordinate.
  • Its maximum cardinality is ONE since a subordinate employee can have at most one supervisor.

Note – Here none of the participants have total participation since both minimum cardinalities are Zero. Hence, the relationships are connected by a single line instead of a double line in the ER diagram. 

To implement a recursive relationship, a foreign key of the employee’s manager number would be held in each employee record. A Sample table would look something like this:- 

Emp_entity( Emp_no,Emp_Fname, Emp_Lname, Emp_DOB, Emp_NI_Number, Manager_no);

Manager no - (this is the employee no of the employee's manager)

Example

CREATE TABLE employee (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT,
    FOREIGN KEY (manager_id) REFERENCES employee(id)
);

Here, the employee table has a foreign key column called manager_id that references the id column of the same employee table. This allows you to create a recursive relationship where an employee can have a manager who is also an employee.

 

Secure upto 50% Scholarship for our newly launched GATE Classes! Register for the Free Scholarship Test today 
Rewards-
1. Top 3: 50% Scholarship
2. Rank 4-10: 30% Scholarship
3. Rank 11-50: 10% Scholarship

Contest starting from 17th Feb, 2024. Register today and get ready to smash GATE 2025!


Last Updated : 24 Mar, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads