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 Relationship

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

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, Work-under)

The above table shows that "H" is working independently.

(2) Representing One-to-Many (1:N) Unary Relationship

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 written as:

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 Relationship

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

Representing IS-A Relationship

Relevant Articles: Steps to Convert E-R Model into Relational database design Representing Entities Types of Relationships in Database and Representing Relationship Normalize the Relation Representing Entities Case Study