Mandatory and Optional Participation

In an ER model, each entity participates in a relationship either mandatorily or optionally. Participation is defined by business rules and directly influences the constraints you implement in tables (foreign keys, NULLability, and delete/update behavior).

1. Mandatory: at least one related instance must exist.
2. Optional: a related instance may or may not exist.

Although ERDs are conceptual, describing participation in table terms (rows and foreign keys) makes enforcement unambiguous.

• Crow’s Foot notation: a bar | at an end means mandatory; a circle O means optional. Example: Customer |--< Order means each Order must reference a Customer.
• Min–max labels: 1, 0..* convey minimum/maximum participation.

Business rule:

A customer may place many orders;
every order is placed by a customer.

As tables: an Orders row cannot exist without a matching Customers row. Enforce with a NOT NULL foreign key.

SQL pattern: mandatory parent → child (1:N)

-- Parent
CREATE TABLE Customers (
  customer_id   INT PRIMARY KEY,
  name          VARCHAR(100) NOT NULL
);

-- Child with mandatory participation: NOT NULL + FK
CREATE TABLE Orders (
  order_id      INT PRIMARY KEY,
  customer_id   INT NOT NULL,
  order_date    DATE NOT NULL,
  FOREIGN KEY (customer_id)
    REFERENCES Customers(customer_id)
    ON DELETE RESTRICT  -- choose RESTRICT/NO ACTION or CASCADE based on rules
);
  

Delete behavior: Use RESTRICT/NO ACTION when orders must never outlive their customer deletion; use CASCADE only when business policy allows deleting a customer and all their orders together.

A weak entity depends on a parent’s existence; it cannot exist unless a related parent exists. Typical examples: OrderLine depends on Order; InvoiceLine depends on Invoice.

• Foreign key is NOT NULL.
• Primary key often includes the parent key (e.g., (order_id, line_no)).
• ON DELETE CASCADE prevents orphans when the parent is removed.

-- Weak entity example
CREATE TABLE Orders (
  order_id   INT PRIMARY KEY,
  order_date DATE NOT NULL
);

CREATE TABLE OrderLine (
  order_id   INT     NOT NULL,
  line_no    INT     NOT NULL,
  product_id INT     NOT NULL,
  qty        INT     NOT NULL CHECK (qty > 0),
  PRIMARY KEY (order_id, line_no),
  FOREIGN KEY (order_id)
    REFERENCES Orders(order_id)
    ON DELETE CASCADE
);
  

Contrast with a deliberately optional relationship (covered next lesson), such as a Product that may or may not have a preferred Supplier assigned at creation time.

• Derive participation from business rules first (“every order has a customer”).
• Map mandatory to NOT NULL FKs and an explicit ON DELETE action.
• Use CHECK constraints or triggers if rules require “at least one” child (e.g., an order must have ≥ 1 line).
• Document optionality clearly on the ERD (Crow’s Foot | vs O).

Allowing a weak entity to exist without its parent (e.g., an order line without an order) breaks operational processes and data quality. Model these as mandatory and enforce with constraints. In contrast, some relationships are optional by design—for example, storing a new Merchandise item before a Supplier is selected—so the FK may be NULL until assigned.

The next lesson describes optional participation in relationships.