Recursive Relationship in ER Diagrams

ER Diagrams

ER Diagram stands for Entity Relationship Diagram. When we draw the relationships between entities using a diagram, then it is called an entity relationship diagram. The ER diagram is just for understanding the purpose of the database administrator. We cannot use the ER diagram directly on the computer. ER Diagrams are converted into the tabular form then they are inserted into the computer using any query language. In ER Diagrams, we use attributes, entities, and the relationships between entities. We use an oval shape to represent the entity and a diamond shape to represent the relationships between entities.

Cardinality

The number of the relationship between two entity sets can have, is known as Cardinality. The quantity of items in a particular set is referred to mathematically as its Cardinality. Cardinality can be used by Database Administrators to count values and tables. Cardinality in a Database typically illustrates the relationship between the data in two distinct tables by emphasizing how frequently one object occurs in comparison to another.

Because it establishes organized linkages between different tables or entities, Cardinality is crucial in Databases. The query execution plan-a series of actions users might take to find and access data in a Database System-is significantly impacted by this. A well-organized query execution strategy can make it simpler for consumers to find the information they require fast. The Cardinality model can be used in Databases by Database Managers for a variety of purposes, but corporations often use it to evaluate customer or inventory data.

The Mapping Cardinality, also known as the Cardinality Ratio, in a database refers to the number of entities that can be connected to another entity through a specific relation set. Although they can help with the description of relation sets with more than two entity sets, Mapping Cardinality is most beneficial when defining binary relation sets.

Types of Cardinality in DBMS:

• One to one cardinality
• Many to one cardinality
• One to many cardinality
• Many to many cardinality

Recursive Relationship

If there is a relationship between two entities and they both belong to the same entity type, then this is called a recursive relationship. We can say a relationship between the same entity set is called a recursive relationship. In the ER diagram, we can use one entity that will enter and exit from itself will denote the recursive relationship. While mapping the ER diagram into the table, we must consider the foreign key in the table for mapping the recursive relationship.

There can be a lot of examples of recursive relationships.

For Example, if a boy marries a girl and both belong to the human entity set, then this is called the recursive relationship.

So, we can indirectly say that if an entity participates more than once in a relationship for different roles, then this is called a recursive relationship. In the above example, the person entity will be used for a boy, and then, on the other hand, it will be used for a girl, and there will be a relation between them.

Examples:

We can understand the recursive relationships using the case study of an organization. In an organization, we have employees and a CEO. Any successful organization follows the hierarchy of employees where employees are arranged at their levels, and they report to their supervisors. We can have two roles of relationships in an organization which are: supervisor and subordinate. In an organization, each employee can have a maximum of only one supervisor, but one supervisor can supervise more than one subordinate. Let's suppose we have one relationship named 'reporting' between supervisors and subordinates.

So, ER diagram will look like this:

The degree of the relationship is one, which means it is a recursive relationship, and a recursive relationship is also called a unary relationship. We can discuss the cardinalities of different roles in the below points:

• The minimum cardinality of a subordinate can be zero because the CEO of a company cannot be the subordinate.
• The maximum cardinality of a subordinate can be ONE because, at max, one supervisor is allowed to be a subordinate.
• The minimum cardinality of a supervisor can be ZERO because subordinates of the companies cannot be supervisors of any other employees.
• The maximum cardinality of a supervisor can be N, where one supervisor can be allowed to N employees and N can be the total working employees of the organization.

To manage the relationships, there would be a foreign key as the manager number in each record. So, the sample table's entities would look like this:

We will see a lot of examples of the unary or recursive relationships, which are the following:

Example 1:

One person can have many children, and the relationship between person and child is unary because they both belong to the same entity type as humans.

Example 2:

One monitor can handle multiple students in class, and he is itself a student. So the relationship handling is a unary or recursive relationship because the monitor and other students belong to the same entity set.

Example 3:

When the match between team A and team B is organized, then this is also an example of a recursive relationship because both teams belong to the same entity set team, and the relation is 'match'.

Example 4:

In a machine, one part is built by combining different small parts, so it is also one kind of recursive relationship.