Representing Unary Relationship in DBMS:
Unary or recursive relationship is a relationship
between the instances of a single entity type. Recursive relationship is also
further divided into 1:1, 1:N, and M:N.
(1) Representing One-to-One (1:1) Unary
The one-to-one (1:1) unary relationship is
represented in the similar way as 1:1 for binary relationship. For example,
recursive relationship is shown below.
In the above relationship, an employee may work
under another employee. Each employee may or may not work under another
employee, and each employee can not work under more than one employee.
Therefore, cardinality is optional zero or one in both directions. For example:
- Peon works under Clerk.
- Clerk works under Assistant Manager.
- Assistant Manager works under Manager.
- Manager works under General Manager and so
There is one-to-one relationship in the recursive
entity "EMPLOYEE". The 1:1 relationship of the above entity type can be
converted into a relation. The primary key of the "EMPLOYEE" relation is the
same as for the entity type, but a foreign key is added to the relation that
references the primary key values. Suppose foreign key is taken as "work-under".
Domain of primary key and foreign key is same. This foreign key is known as
recursive foreign key. The "EMPLOYEE" relation is represented as:
EMPLOYEE (Emp-ID, Name, Designation,
The above table shows that "H" is working
(2) Representing One-to-Many (1:N) Unary
The one-to-many (1:N) recursive relationship is
shown in figure below. This relationship is named 'Manages' that associates
employees of an organization with another employee who is their manager.
The 1 :N relationship of the above entity type
(i.e. EMPLOYEE) can be converted into relation. The primary key of the EMPLOYEE
relation is the same as for the entity type, but a foreign key is added to the
relation that references the primary key values. The EMPLOYEE relation is
EMPLOYEE (Emp-ID, Name, Address, Manager-ID)
In the EMPLOYEE relation, Manager-ID is the
recursive foreign key that has same domain as primary key. When the data is
stored into the relation, one row represents the manager, and the other rows
represent those who have been managed by manager. The manager row takes the
role of the parent, and the managed rows take the role of the child. We place
the key of the parent in the child.
(3) Representing Many-to-Many (M:N) Unary
Now consider M:N unary relationship. The
Treated-By relationship in the figure below represents the situation in which
doctors give treatments to each other. Although this entity has one relation.
After converting this entity into a relation, we must have to create a separate
relations to represent the M:N relationship. It means that we have to create a
new intersection relation to show the related rows. The name of the doctor in
the first relation is one who provided the treatment, and the name of the doctor
in the second relation is one who received the treatment.
The Doctor and Treatment relations are:
DOCTOR (Doctor-ID, Name, Address)
TREATMENT (Doctor-ID, Patient)
The primary key of the DOCTOR relation is
"Doctor-ID" and the primary key of the new relation is a composite key that
consists (Doctor-ID, Patient) attributes. These attributes have the same domain
as primary key domain.
Representing Binary Relationship
Representing Unary Relationship
Representing Ternary Relationship