# RELATIONSHIPS

## RELATIONSHIPS

94.Why are recursive relationships are bad? How do you resolve them?

A recursive relationship occurs when there is a relationship between an entity and itself. For example, a one-to-many recursive relationship occurs when an employee is the manager of other employeess.

The employee entity is related to itself, and there is a one-to-many relationship between one employee (the manager) and many other employees (the people who report to the manager).

•  A recursive relationship occurs in the case where an entity is related to itself.
• Talking about health care domain, it is a possibility that a health care provider (say, a doctor) is a patient to any other health care provider.
• Because, if the doctor himself falls ill and needs a surgery, he will have to visit some other doctor for getting the surgical treatment.
• So, in this case, the entity – health care provider is related to itself.
• A foreign key of the health insurance provider’s number will have to present in each member’s (patient) record

A relationship between two entities of 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, it is possible for the same entity to participate in the relationship. This is termed a recursive relationship.

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

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

• The minimum cardinality of Supervisor entity is ZERO since the lowest level employee may not be a manager for anyone.
• The maximum cardinality of 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.
• It maximum cardinality is ONE since a subordinate employee can have at most one supervisor

95.What is self-recursive relationship?

A standalone column in a table will be connected to the primary key of the same               table,    which is called as recursive relationship

96.What is identifying relationship?

• Usually, in a data model, parent tables and child tables are present. Parent table and child table are connected by a relationship line.
• If the referenced column in the child table is a part of the primary key in the child table, relationship is drawn by thick lines by connecting these two tables, which is called as identifying relationship.

97.What is non-identifying relationship?

• Usually, in a data model, parent tables and child tables are present. Parent table and child table are connected by a relationship line.
• If the referenced column in the child table is a not a part of the primary key and standalone column in the child table, relationship is drawn by dotted lines by connecting these two tables, which is called as non-identifying relationship.