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
ofDEPARTMENT
refers to anEMPLOYEE
.The attribute
Controlling_department
ofPROJECT
refers to aDEPARTMENT
.
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 betweenEMPLOYEE
andDEPARTMENT
entity types.
Each instance ofWORKS_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:
aSUPPLIER
(s), aPART
(p), and aPROJECT
(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 workerDEPARTMENT
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
andsupervisee
are from theEMPLOYEE
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 betweenEMPLOYEE
andDEPARTMENT
, 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
, orM
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 someDEPARTMENT
, only then an employee entity can exist.
→ Total participation ofEMPLOYEE
inWORKS_FOR
.
- Partial Participation:
Only some entities participate in the relationship. (no minimum participation)
Not every
EMPLOYEE
manages a department.
→ Partial participation inMANAGES
. 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 attributeHours
(hours per week an employee works on a project).
MANAGES
has an attributeStart_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
inMANAGES
can be stored in eitherEMPLOYEE
orDEPARTMENT
.
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 aStart_date
attribute, it can be stored inEMPLOYEE
, notDEPARTMENT
, 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
inWORKS_ON
is determined by the specific employee-project pair, and cannot be assigned to either entity individually.