Skip to content

Relationship Types, Sets, Roles, and Structural Constraints

Whenever an attribute of one entity type refers to another entity type, a relationship exists.

The attribute Manager of DEPARTMENT refers to an EMPLOYEE.

The attribute Controlling_department of PROJECT refers to a DEPARTMENT.

In ER modeling, these references are modeled as relationships, not attributes. During initial design, they may be captured as attributes but are later refined into relationships.


Relationship Types, Sets, and Instances

A relationship type R among n entity types E1, E2, ..., En defines a set of associations (a relationship set) among entities from these entity types.

  • A relationship instance ri is a specific association between entities—one from each participating entity type.

  • The relationship type and its relationship set typically share the same name.

WORKS_FOR is a relationship type between EMPLOYEE and DEPARTMENT entity types.
Each instance of WORKS_FOR links one employee with one department.

ER Diagram Notation

  • Relationship types are shown as diamond-shaped boxes.

  • They are connected to the participating entity types (rectangles) with straight lines.

  • The relationship name is placed inside the diamond.


Relationship Degree, Role Names, and Recursive Relationships

Degree of a Relationship Type

The degree is the number of participating entity types:

  • Binary relationship: Degree 2 (e.g., WORKS_FOR)

  • Ternary relationship: Degree 3 (e.g., SUPPLY links a supplier, part, and project)

SUPPLY relates three entities:
a SUPPLIER (s), a PART (p), and a PROJECT (j).


Relationships as Attributes

Sometimes, binary relationships can be viewed as attributes.

  • EMPLOYEE.Department

  • DEPARTMENT.Employees

Department of EMPLOYEE or Employees of DEPARTMENT can represent the WORKS_FOR relationship type. If both are used, they must be inverse representations of the same relationship.


Role Names and Recursive Relationships

Each entity type in a relationship plays a role. The role name helps explain the meaning of that participation.

In WORKS_FOR:

  • EMPLOYEE plays the role of worker
  • DEPARTMENT plays the role of employer

When Role Names Are Required

  • If all participating entity types are distinct, role names are optional.

  • If the same entity type participates multiple times, role names are essential.

Same entity type participating more than once in a relationship type in different roles. Such relationships are called recursive or self-referencing relationships.

In SUPERVISION:

  • Both supervisor and supervisee are from the EMPLOYEE entity type.

  • The EMPLOYEE type participates twice with different roles.

Constraints on Binary Relationship Types

Binary relationship types typically include constraints that restrict the possible combinations of entities in a relationship set. These constraints are derived from the real-world (or miniworld) scenarios the relationships are intended to model.

Example: If a company requires that each employee must work for exactly one department, this constraint should be captured in the schema.

There are two main types of constraints:

  • Cardinality Ratio

  • Participation Constraint

Together, these are referred to as the structural constraints of a relationship type.


Cardinality Ratios for Binary Relationships

The cardinality ratio specifies the maximum number of relationship instances that an entity can participate in.

WORKS_FOR relationship between EMPLOYEE and DEPARTMENT, is a 1:N cardinality ration.

  • Each department can employ many employees (N)

  • Each employee works for only one department (1)

Common Cardinality Ratios

  • 1:1 – Each entity in both sets is related to at most one entity in the other set.

MANAGES — One employee manages one department; one department has one manager.

  • 1:N / N:1 – One entity is related to many entities in the other set.

WORKS_FOR — A department has many employees; each employee works for one department.

  • M:N – Many entities in one set can be related to many in the other.

WORKS_ON — An employee can work on multiple projects; a project can involve multiple employees.

ER Diagram Representation

  • Cardinality is shown using 1, N, or M near the connecting lines of the relationship diamond.

Participation Constraints and Existence Dependencies

The participation constraint determines the minimum number of relationship instances an entity must participate in. Also called the minimum cardinality constraint.

It whether the existence of an entity depends on its being related to another entity via the relationship type.

Types of Participation

  • Total Participation (Existence Dependency):
    Every entity in the set must participate in at least one relationship instance to exist. (minimum of one participation)

Every EMPLOYEE must work for some DEPARTMENT, only then an employee entity can exist.
→ Total participation of EMPLOYEE in WORKS_FOR.

  • Partial Participation:
    Only some entities participate in the relationship. (no minimum participation)

Not every EMPLOYEE manages a department.
→ Partial participation in MANAGES. Not full.

ER Diagram Representation

  • Total participation is shown with a double line.

  • Partial participation is shown with a single line between the entity and the relationship.


Attributes of Relationship Types

Relationship types, like entity types, can have attributes.

  • WORKS_ON has an attribute Hours (hours per week an employee works on a project).

  • MANAGES has an attribute Start_date (when an employee started managing a department).


Migrating Relationship Attributes to Entities

Whether a relationship attribute can be moved ("migrated") to an entity type depends on the cardinality of the relationship.

1:1 Relationships

  • Relationship attributes can be migrated to either of the participating entity types.

  • Conceptually, they belong to the relationship but can be stored with one entity.

Start_date in MANAGES can be stored in either EMPLOYEE or DEPARTMENT.

Value of the Start_date attribute can be determined separately, either by the participating department entity or by the participating employee (manager) entity.

1:N Relationships

  • Attributes can be migrated only to the entity type on the N-side of the relationship.

If WORKS_FOR has a Start_date attribute, it can be stored in EMPLOYEE, not DEPARTMENT, because a department has many employees, each with a unique start date.

M:N Relationships

  • Attributes must remain in the relationship type.

  • They depend on the combination of participating entities.

Hours in WORKS_ON is determined by the specific employee-project pair, and cannot be assigned to either entity individually.

Made with ❤️ for students, by a fellow learner.