Entity Relationships: Concept, Notation, and Implementation

An entity relationship (ER) describes how two entities are associated in the data model. In practice, relationships (1) communicate business meaning in the ER diagram and (2) drive the foreign keys and junction tables in the relational schema. A good relationship statement reads naturally as a verb phrase between two nouns, e.g., Customer places Order.

A relationship specifies:

1. Cardinality — how many instances can/must relate (1:1, 1:M, M:N).
2. Participation (Optionality) — whether the relationship is mandatory or optional for each side.
3. Business semantics — the verb phrase that explains the association.

These details appear in the ERD and are later enforced with foreign keys, unique constraints, and (when needed) an associative/junction table.

• One-to-One (1:1): One instance relates to at most one on the other side (often used to isolate rarely populated attributes or segregate sensitive data). Participation may be optional or mandatory on either side.
• One-to-Many (1:M): One parent instance relates to many child instances (e.g., Department has many Employees). Implement with a foreign key in the “many” side.
• Many-to-Many (M:N): Many instances on each side relate to many on the other (e.g., Student ↔ Course). Implement via a junction (associative) table that holds the two foreign keys and any relationship attributes.

Participation: If the association is mandatory for a child, the child’s foreign key is NOT NULL (and often given a suitable referential action). If it’s optional, the foreign key may be nullable.

1. 1:M → add a foreign key column to the child table referencing the parent’s primary key. Consider indexing the foreign key for join performance.
2. M:N → create a junction table with a composite primary key (FK_A, FK_B) or a surrogate key plus a unique constraint on (FK_A, FK_B). Add any attributes that belong to the relationship itself (e.g., AssignedDate, Role).
3. 1:1 → use a shared primary key or a unique foreign key; document why the split exists (privacy, volatility, or sparsity).

Business rule: Employees are assigned to projects; projects have many employees. This is an M:N relationship modeled with an associative entity.

-- EMPLOYEE (parent)
CREATE TABLE EMPLOYEE (
  EmpID      INT PRIMARY KEY,
  LastName   VARCHAR(50) NOT NULL,
  FirstName  VARCHAR(50) NOT NULL
);

-- PROJECT (parent)
CREATE TABLE PROJECT (
  ProjID     INT PRIMARY KEY,
  ProjName   VARCHAR(100) NOT NULL,
  ProjDept   VARCHAR(100)
);

-- Junction table to resolve M:N
CREATE TABLE EMPLOYEE_PROJECT (
  EmpID        INT NOT NULL,
  ProjID       INT NOT NULL,
  AssignedDate DATE,
  HoursWorked  DECIMAL(7,2),
  CONSTRAINT pk_employee_project PRIMARY KEY (EmpID, ProjID),
  CONSTRAINT fk_emp FOREIGN KEY (EmpID) REFERENCES EMPLOYEE(EmpID),
  CONSTRAINT fk_proj FOREIGN KEY (ProjID) REFERENCES PROJECT(ProjID)
);

Why this works: The composite primary key prevents duplicate assignments; foreign keys preserve referential integrity; optional attributes (e.g., HoursWorked) live where the relationship occurs.

• Name relationships with clear verbs (e.g., places, contains, assigned to).
• State cardinality and participation explicitly; avoid ambiguity.
• Put foreign keys on the “many” side; use junction tables for M:N.
• Keep relationship attributes (e.g., quantities, dates, roles) on the junction table, not on either parent.
• Enforce integrity with NOT NULL, UNIQUE, and foreign keys; index foreign keys for performance.

Before moving on to the next lesson, click the Exercise link below to check your understanding of entity relationships.
Entity Relationships - Exercise
The next lesson introduces the three basic relationship types in more detail.

junction table: Also called an associative or bridge table; resolves a many-to-many relationship by holding the primary keys from each related entity (and any attributes of the association).