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
- Supervisor
- 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.
