Relationships in the ER Model

In the Entity-Relationship (ER) model, a relationship is an association between two or more entities. whenever an attribute of one entity type refers to another entity type, a relationship exists.

Instead of using attributes to refer to other entities (e.g., a Manager attribute in a DEPARTMENT entity), we model these connections explicitly as relationships.

During initial design, they may be captured as attributes but are later refined into relationships.

---

Relationship Types, Sets, and Instances

• A Relationship Type (e.g., WORKS_FOR) defines the blueprint for an association between entity types (e.g., EMPLOYEE and DEPARTMENT).

• A Relationship Set is the current collection of all individual associations (instances) of a particular relationship type.

• A Relationship Instance is a single, specific link between entities (e.g., linking employee 'John Smith' to the 'Sales' department).

ER Diagram Notation

• Relationship types are shown as diamond-shaped boxes connected to the participating entity types (rectangles) with straight lines.

Characteristics of a Relationship

Relationship Degree

The degree of a relationship is the number of participating entity types.

• Binary (Degree 2) relationships: The most common type, linking two entity types (e.g., EMPLOYEE WORKS_FOR DEPARTMENT).

• Ternary (Degree 3) relationships: Links three entity types (e.g., a SUPPLY relationship linking SUPPLIER, PART, and PROJECT).

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 participates in a relationship in a specific role. The role name clarifies the meaning of its participation.

• In a WORKS_FOR relationship, EMPLOYEE plays the worker role, and DEPARTMENT plays the employer role.

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

Role names are essential for recursive relationships.

• Example: A SUPERVISION relationship on the EMPLOYEE entity type. One EMPLOYEE participates in the supervisor role, and another participates in the supervisee role.

Structural Constraints: The Rules of Association

Structural constraints are rules that limit how entities can participate in a relationship.

There are two main types of constraints:

• Cardinality Ratio
• Participation Constraint

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

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

---

1. Cardinality Ratios (The Maximum Number)

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

• One-to-One (1:1): An entity in one set can be related to at most one entity in the other.

  • Example: An employee MANAGES at most one department, and a department is managed by at most one employee.

• One-to-Many (1:N): An entity in one set can be related to many entities in the other, but not vice-versa.

  • Example: In WORKS_FOR, One DEPARTMENT can have many employees, but each EMPLOYEE works for only one department.

• Many-to-Many (M:N): Many Entities in a set can be related to many entities in the other.

  • Example: In WORKS_ON, An EMPLOYEE can work on many projects, and a PROJECT can have many employees.

ER Diagram Representation

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

2. Participation Constraints (The Minimum Number)

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

The participation constraint specifies whether an entity's existence depends on its being related to another entity.

• Total Participation (Existence Dependency): Every entity in the set must participate in the relationship to exist. This is shown with a double line in ER diagrams.

  • Example: Every EMPLOYEE must work for a DEPARTMENT. An employee cannot exist in the database without being assigned to a department. So Employee has Total participation in WORKS_FOR relationship.

• Partial Participation: An entity in the set is not required to participate in the relationship. This is shown with a single line.

  • Example: Not every EMPLOYEE manages a department; therefore, EMPLOYEE has partial participation in the MANAGES relationship.

Attributes on Relationships

Relationships can have their own attributes, especially in M:N relationships.

• The WORKS_ON (M:N) relationship between EMPLOYEE and PROJECT can have an Hours attribute to record the hours a specific employee works on a specific project.

• MANAGES has an attribute Start_date forwhen an employee started managing a department.

Where this attribute is stored depends on the cardinality:

• 1:1 or 1:N: The attribute can be "migrated" to one of the entity types (specifically, the N-side in a 1:N relationship).

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

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

• M:N: The attribute must belong to the relationship itself, as its value depends 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.

Weak Entity Types

A weak entity type is one that cannot be uniquely identified by its own attributes alone. It depends on another entity type, called the owner entity, for its existence and identification.

No Primary Key: It lacks enough attributes to form a full primary key on its own.

Owner Entity Type: (also called the identifying entity type or strong entity set) is the entity type upon which the existence and identity of a weak entity type depend. The relationship associating the weak entity type with the owner entity type is called the identifying relationship.

Existence Dependent: Weak entity has total participation in an identifying relationship with its owner. A weak entity cannot exist without its owner (identifying entity).

Partial Key (Discriminator): A weak entity has a partial key, which is an attribute that can uniquely identify it within the context of its owner entity. The primary key of the weak entity is formed by combining the owner's primary key with its own partial key.

Example: DEPENDENT is a weak entity type owned by EMPLOYEE. The partial key for DEPENDENT might be Name. A dependent is uniquely identified only by combining their Name with the EmployeeID of the employee they belong to.

Two dependents (from different employees) may share the same values for attributes like Name, Birth_date, Sex, and Relationship. However, they are considered distinct because they are connected to different EMPLOYEE entities.

In ER diagrams, weak entity types are shown in a double rectangle, their identifying relationship in a double diamond, and their partial key is underlined with a dashed line.

ER Design Guidance and Best Practices

Proper Naming Conventions

• Entity Types & Relationship Types: Use singular, uppercase nouns (EMPLOYEE, WORKS_ON).

• Attributes: Use CamelCase or snake_case (StartDate, first_name).

• Role Names: Use lowercase nouns (supervisor, worker).

Common Design Choices: Attribute vs. Entity vs. Relationship

• Attribute to Relationship: If an attribute is a reference to another entity type, it should be modeled as a relationship.

• Attribute to Entity: If an attribute (like DepartmentName) is used in multiple entity types and has its own properties (like DepartmentHead), it's better to promote it to its own DEPARTMENT entity type and link to it via relationships.