Relationships in ER Models and Types

In implementation of objects and people as entities, attribute of one entity type may associate with attribute of another entity type. These associations are called Relationships in ER Models. In other words any relation between two entities which associates each other is called relationship. For example, there are two entities Employee and Department. An employee works in a department. Here Employee and Department are entities and works for is the relationship.

Types of Relationships in ER Models

A relationship type R among n entity types E1, E2, E3, …., En defines a set of associations or a relationship set-among entities from these entity types. Mathematically, the relationship set R is a set of relationship instances r_i, where each r_i associates n individual entities (e₁,e₂,……..,e_n) and each entity e_j in r_i is s member of entity type E_J, 1<=j<=n. It means that each relationship instance r_i in R is an association of entities, where the association includes exactly one entity from each participating entity type.

For example, in the Works For relationship type between two entity types Employee and Department, associates each employee with a department in which the employee works. Each relationship instance in the relationship set Works For associates one employee entity and one department entity.

Relationship Degree

Degree of Relationships in ER Models is the count of entity sets involved in the relationship. In this example Works For relationship has degree two.  A relationship type of degree two is called Binary Relationship. A relationship type in which three entity sets participate and have degree three is called Ternary Relationship.

Here in the figure there is a Ternary Relationship between Employee, Department and Project entity sets. Here employee E1 works for department D1 on project P1. Employee E2 works for department d2 on project P2 and so on.

Role Names

Each entity that participates in the in a relationship type plays a particular Role in Relationships in ER Models. In the example being discussed, entity type Employee plays the role of employee and Department entity plays the role of Department in Works For relationship type.

Role names can be same as the entity name but sometimes same entity plays different role in a relationship. For example, an employee in an organization can be a Supervisor of one employee and at the same time she is the Subordinate of another employee. So, if we have a relationship Manages or Supervises in this relationship, employee entity is playing the role of Supervisor and also of Supervisee. In these kinds of relations it becomes necessary to give different role names to entities.

Recursive Relationship

Recursive relationship is different type of Relationships in ER Models. It is a relationship between two entities belonging to same entity set. For example, there is one entity Employee and a relationship, say, supervises.  An employee, except at the top management, always have a superior who supervises her who herself is an employee. In this relationship both the participating entities belong to same entity set.

Here in the above example E2 and E3 are reporting to E1 i.e. E2 and E3 are the subordinates and E1 is the supervisor and E1 is reporting to E4 i.e. E1 is subordinate to E4. This kind of relation between entities of same entity set is called Recursive Relationship.

Constraints on Relationships in ER Models

To implement the real world dependencies, Relationships in ER Models can be defined with limit on the associations of entities in a relationship set. We can identify two types of relationship constraints: Cardinality ratio and Participation.

Cardinality Ratios

The cardinality ratio for a relationship specifies the maximum number of relationship instances that an entity can participate in. for example, in Works _for  relationship type, Department and  Employee are in a relationship of cardinality ratio 1: N. It means that every employee can work as employee under one department and a department can have many employees.

Types of Cardinality Ratios

1. One-to-One (1:1)
2. One-to-Many (1:N)
3. Many-to-One (N:1)
4. Many-to-Many (M:N)

One to One

When one entity in an entity set have association with at most one entity in another entity set. We can say that for each entity in first entity set there exists one or zero entity in the second entity set. For example, in a university every department can have only one Head of Department. Here at most is important since it means that there can be entities that are not related to any other entity. Like there can be some new Professors who only teach and do not manage any Academic Department of university.

One to Many

There is One to Many cardinality ratio when one entity of an entity set is related to more than one entities of the other entity set. Entities of 2^nd set can relate to at most one entity of the 1^st entity set. A Teaching department of a college can have more than one professors but one professor can belong to  only one department.

Many to One

Cardinality ratio between two relations is many to one when any number of entities of one entity set can have association with one entity of another entity set. One entity of the other set can relate to any number of entities of the 1^st set.

 Many to Many

Cardinality ratio between two relations is many to many when any number of entities in one entity set can be related to any number of entities of the other set and vice versa. For example, many students can be taught by one Professor and one students attends lectures of many professors. So it is many to many ratio.

Participation Constraints

Participation in Relationships in ER Models  are limited by constraints. It is a technique to specify existential dependency of one entity upon another entity via the relationship type. Through this an ER diagram designer can specify the minimum count of relationship instances through which the entities are associated. It is defined as minimum cardinality constraint.

Participation constraint can be of two types: total and partial. Let us take the example of employee and department entities.  An employee in an organization must work for a department so the employee entity participation in works_for relationship with department entity is total participation. It is also called existence dependency. There is always an employee who manages a department but every employee is not a manager so employee entity participation in manages relationship is partial participation.