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 ri, where each ri associates n individual entities (e1,e2,……..,en) and each entity ej in ri is s member of entity type EJ, 1<=j<=n. It means that each relationship instance ri 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.
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.
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 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.
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
- One-to-One (1:1)
- One-to-Many (1:N)
- Many-to-One (N:1)
- 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 2nd set can relate to at most one entity of the 1st 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 1st 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 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.