Optional Participation in Relationships

Participation answers a simple question: “Must an instance on this side of the relationship be linked to something on the other side?”

• Mandatory participation ⇒ minimum cardinality is 1 (must participate).
• Optional participation ⇒ minimum cardinality is 0 (may participate, but doesn’t have to).

In table terms, optionality affects whether a foreign key may be NULL (in 1:N) and whether the absence of a row in an intersection table is allowed (in M:N).

1. Find the minimums on each end of a relationship (e.g., 0..* or 1..*). A 0 means optional; a 1 means mandatory.
2. Translate by relationship type:
   • 1:N: Optional on the N-side ⇒ the child’s foreign key may be NULL. Mandatory ⇒ the foreign key is NOT NULL.
   • M:N: Optional participation on either side is represented by the absence of a row in the intersection table. Mandatory on a side means each parent must have at least one row in the intersection.

Use surrogate keys for entities and intersection tables for the M:N relationships. Optional participation is the default-do not insert rows unless the relationship actually exists.

-- Entities
CREATE TABLE Bars (
  bar_id       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name         VARCHAR(200) NOT NULL,
  address      VARCHAR(300),
  license_no   VARCHAR(50),
  CONSTRAINT uq_bars_name UNIQUE (name)
);

CREATE TABLE Beers (
  beer_id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name         VARCHAR(150) NOT NULL,
  manufacturer VARCHAR(150),
  CONSTRAINT uq_beers_name UNIQUE (name)
);

CREATE TABLE Drinkers (
  drinker_id   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name         VARCHAR(200) NOT NULL,
  address      VARCHAR(300)
);

-- Intersections (M:N). Optional participation: absence of rows = no relationship.
CREATE TABLE Sells (
  bar_id  BIGINT NOT NULL,
  beer_id BIGINT NOT NULL,
  price   DECIMAL(7,2) CHECK (price >= 0),
  PRIMARY KEY (bar_id, beer_id),
  FOREIGN KEY (bar_id) REFERENCES Bars(bar_id),
  FOREIGN KEY (beer_id) REFERENCES Beers(beer_id)
);

CREATE TABLE Likes (
  drinker_id BIGINT NOT NULL,
  beer_id    BIGINT NOT NULL,
  rating     SMALLINT CHECK (rating BETWEEN 1 AND 5),
  PRIMARY KEY (drinker_id, beer_id),
  FOREIGN KEY (drinker_id) REFERENCES Drinkers(drinker_id),
  FOREIGN KEY (beer_id)    REFERENCES Beers(beer_id)
);

CREATE TABLE Frequents (
  drinker_id      BIGINT NOT NULL,
  bar_id          BIGINT NOT NULL,
  since_date      DATE,
  visits_per_week SMALLINT CHECK (visits_per_week BETWEEN 0 AND 21),
  PRIMARY KEY (drinker_id, bar_id),
  FOREIGN KEY (drinker_id) REFERENCES Drinkers(drinker_id),
  FOREIGN KEY (bar_id)     REFERENCES Bars(bar_id)
);

1:N relationships

• Optional child: child’s foreign key is NULL-able.
• Mandatory child: child’s foreign key is NOT NULL and has a valid parent row.

-- Example: Department (1) -- (N) Employee
CREATE TABLE Department (
  dept_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name    VARCHAR(120) NOT NULL UNIQUE
);

-- Optional participation of Employee in Department (employee may be unassigned)
CREATE TABLE Employee_Optional (
  emp_id  BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name    VARCHAR(120) NOT NULL,
  dept_id BIGINT NULL,                    -- optional
  FOREIGN KEY (dept_id) REFERENCES Department(dept_id)
);

-- Mandatory participation (every employee must belong to a department)
CREATE TABLE Employee_Mandatory (
  emp_id  BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name    VARCHAR(120) NOT NULL,
  dept_id BIGINT NOT NULL,                -- mandatory
  FOREIGN KEY (dept_id) REFERENCES Department(dept_id)
);

M:N relationships

Optionality is modeled by the presence or absence of rows in the intersection table. To make participation mandatory for an entity, enforce “at least one” via constraints, triggers, or periodic checks.

-- Pattern: enforce that each Bar must sell at least one Beer.
-- Option A (transactional trigger - pseudo code):
CREATE TRIGGER bar_must_sell
AFTER INSERT ON Bars
DEFERRABLE INITIALLY DEFERRED
BEGIN
  -- At commit, verify each new bar_id appears at least once in Sells.
  IF EXISTS (
    SELECT 1
    FROM Bars b
    WHERE NOT EXISTS (SELECT 1 FROM Sells s WHERE s.bar_id = b.bar_id)
      AND b.created_in_this_tx = TRUE
  ) THEN RAISE EXCEPTION 'Each bar must sell at least one beer';
END;

Note: Strict “at least one” constraints are often handled at the application/service layer because enforcing them transactionally can complicate bulk loads and edits.

Use LEFT JOIN to include entities that currently do not participate.

-- Bars and the beers they sell (include bars that currently sell none)
SELECT b.name AS bar, be.name AS beer, s.price
FROM Bars b
LEFT JOIN Sells s ON s.bar_id = b.bar_id
LEFT JOIN Beers be ON be.beer_id = s.beer_id
ORDER BY b.name, be.name;

-- Drinkers with no recorded preferences (optional Likes)
SELECT d.drinker_id, d.name
FROM Drinkers d
LEFT JOIN Likes l ON l.drinker_id = d.drinker_id
WHERE l.drinker_id IS NULL;

-- Count how many bars each drinker frequents (0 allowed)
SELECT d.name, COUNT(f.bar_id) AS bars_count
FROM Drinkers d
LEFT JOIN Frequents f ON f.drinker_id = d.drinker_id
GROUP BY d.name
ORDER BY bars_count DESC;

• State the business rule with a clear minimum (0 vs 1) on each side.
• For 1:N, set the child FK NULL-able (optional) or NOT NULL (mandatory).
• For M:N, use an intersection table; optionality = absence of rows.
• Index foreign keys and intersection PKs for join performance.
• If you need “at least one” semantics, decide where to enforce it (DB triggers, constraints, or application logic).

• Using NULL-able FKs by habit. If the business rule is mandatory, make it NOT NULL.
• Embedding attributes of the relationship in one entity table (e.g., storing price on Beer or Bar). Relationship attributes belong on the intersection table (Sells.price).
• Forgetting unique constraints on real-world identifiers (e.g., Bars.name, Beers.name).

Classify each side of the three relationships (sells, likes, frequents) in the ERD as optional or mandatory given these scenarios:

1. New bars can be entered before their product lists are known.
2. Drinkers may choose not to share preferences.
3. Only active customers are allowed to record frequented bars.

Explain how your answers change the DDL (NULL vs NOT NULL FKs, presence/absence rules in intersection tables).

Mandatory & Optional Participation – Exercise

Glossary

• Optional participation: Minimum cardinality = 0.
• Mandatory participation: Minimum cardinality = 1.
• Intersection table: Table that implements an M:N relationship.
• Relationship attribute: Data that belongs to the link itself (e.g., price on Sells).