Diagramming Types of Participation

Reading the diagrams

Always read the symbols at the end of the line nearest each entity.

• || next to an entity means “exactly one” (mandatory one).
• O| means “zero or one”.
• O< means “zero or many”.
• |< means “one or many”.

Examples with participation

Implementation patterns

1) One-to-one

Shared primary key (tight coupling):

CREATE TABLE employee (
  emp_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  last_name  VARCHAR2(60) NOT NULL,
  first_name VARCHAR2(60) NOT NULL
);

CREATE TABLE office (
  emp_id   NUMBER PRIMARY KEY,             -- shared PK enforces 1:1
  room_no  VARCHAR2(20) NOT NULL,
  CONSTRAINT fk_office_emp
    FOREIGN KEY (emp_id) REFERENCES employee(emp_id)
);

Unique foreign key (looser coupling):

CREATE TABLE office_uq (
  office_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  emp_id    NUMBER UNIQUE NOT NULL,        -- one office per employee
  room_no   VARCHAR2(20) NOT NULL,
  CONSTRAINT fk_office_uq_emp
    FOREIGN KEY (emp_id) REFERENCES employee(emp_id)
);

2) One-to-many

CREATE TABLE supplier (
  supplier_id   NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  supplier_code VARCHAR2(20) UNIQUE NOT NULL,
  name          VARCHAR2(120) NOT NULL
);

CREATE TABLE product (
  product_id  NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  supplier_id NUMBER NOT NULL,             -- mandatory one
  sku         VARCHAR2(40) UNIQUE NOT NULL,
  title       VARCHAR2(120) NOT NULL,
  CONSTRAINT fk_product_supplier
    FOREIGN KEY (supplier_id) REFERENCES supplier(supplier_id)
);

3) Many-to-many (resolved)

CREATE TABLE student (
  stud_id     NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  student_no  VARCHAR2(20) UNIQUE NOT NULL,
  last_name   VARCHAR2(60) NOT NULL
);

CREATE TABLE class (
  class_id    NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  class_code  VARCHAR2(20) UNIQUE NOT NULL,
  title       VARCHAR2(120) NOT NULL
);

CREATE TABLE enrollment (
  stud_id     NUMBER NOT NULL,
  class_id    NUMBER NOT NULL,
  enrolled_on DATE DEFAULT SYSDATE,
  grade       VARCHAR2(2),
  CONSTRAINT pk_enrollment PRIMARY KEY (stud_id, class_id),
  CONSTRAINT fk_enr_stud  FOREIGN KEY (stud_id)  REFERENCES student(stud_id),
  CONSTRAINT fk_enr_class FOREIGN KEY (class_id) REFERENCES class(class_id)
);

Participation vs. identifying relationships

Don’t conflate mandatory with identifying. Mandatory means the minimum is at least one; identifying means the parent’s PK participates in the child’s PK. You can have mandatory relationships that are non-identifying (child has its own PK) and optional relationships that are identifying (rare, but notation allows it).

Referential actions on delete

Standard SQL options (availability varies by RDBMS):

• NO ACTION / RESTRICT: prevent parent delete if children exist.
• CASCADE: delete children automatically.
• SET NULL: set the FK to NULL (FK must allow NULLs).
• SET DEFAULT: set the FK to its DEFAULT value.

ALTER TABLE product
  ADD CONSTRAINT fk_product_supplier
  FOREIGN KEY (supplier_id)
  REFERENCES supplier(supplier_id)
  ON DELETE SET NULL;  -- example: product may temporarily have no supplier

Quick self-check

Given SUPPLIER O-< PRODUCT, answer:

1. Is a product required to have a supplier? (Yes: minimum near PRODUCT is a bar, so mandatory.)
2. Can a supplier have zero products? (Yes: the circle near SUPPLIER means optional.)

ER Diagram Participation - Exercise