Database Management System
Addressing One-To-One Recursive Relationships
A recursive relationship is a connection for a class type that serves a particular role in itself (in serving a different function) (Singh, 2011). Consider a team gets designated the work of managing other teams. The manager team has to ensure that every team does its proportion of task. In another way, consider an organization, for example, within a company there are departments, employees, and managers. Every single employee is employed to work only within a single department, and in a single department, it is expected that it contains many employees. Similarly, every department is assigned a single manager who is expected to manage it solely. Finally, it can be concluded that a manager is in control of many employees working in a single department, while every employee gets put under a single manager. In this way, it gets noted that a single entity participates several times within the relationship.
In the one-to-one relationship, every single row within a database relation gets connected to one other row in another relation. Therefore considering that we have two relations A and B, the number of rows in relation A should be the same as the number of rows in relation B. The number of attributes is usually the same or even different if possible. Therefore in a recursive one-to-one relationship, we have two tables with the same number of rows (International Conference on Conceptual Modeling & Akoka, 1999).
Let us provide the supervisor-employee relationship to demonstrate one-to-one recursive relationship for a team containing a single supervisor and a single employee (a good example is the freelancing jobs and personal project where a person is his supervisor). The relationship between both of them is a one-to-one relationship, and the model can get described as shown.
Supervised by (1)
In the class diagram shown above, the foreign key has not been shown for easy understanding. However in our relational model, the relationship between supervisor and supervisees is clearly understood (the Employee- id is our primary key while supervisor-id is the foreign key).
(Primary key, PK)
In this type of relationship, since a single supervisor manages a single employee, there is high inflexibility, simply because it does not allow working with more than one supervisor. Within this model, we can also decide to define the tasks allocated to the employee over time by revising on our class diagram. By extending the tables, we will note that the supervisor and employee id appear at the tasks table as shown in the relational model below.
Our model’s characteristics include: the manager inherits the properties of the employee (that is, the manager can be an ordinary employee), and every team has a single manager (or a manager supervises zero or one teams).
When retrieving the record for both the supervisor and employees, we create a join for the employee table to itself, by using two aliases for the table. The outer join includes an employee who is under such supervision.
Select EMP.first_name as ‘Employee’, SUP.first_name as ‘Supervisor’ from Employee EMP LEFT OUTER JOIN Employee SUP on EMP.supervisor_id=SUP.employee_id order by EMP.first_name.
From the SQL query, it will certainly work as if there are two duplicates of the employee table as shown below:
|manager as an employee|
|manager as an employee|
International Conference on Conceptual Modeling, & Akoka, J. (1999). Conceptual modeling–ER’99: 18th International Conference on Conceptual Modeling, Paris, France, November 15-18, 1999: proceedings. Berlin: Springer.
Singh, S. K. (2011). Database systems: Concepts, design and applications. Delhi: Dorling Kindersley