Question 3 of 8

Question 3 (20 marks)

Question text not reproduced: the examination questions are © Engineers and Geoscientists BC. Open the official past paper (linked at the top of this page) to read the question, then follow the worked solution below.

Given. Shows are released as seasons of episodes (an episode belongs to exactly one season; a show may have several seasons); each episode has its own unique ID, title, copyright date, plus a season ID and show ID; each show has a show ID and a format; actors have an actor ID, SIN, name, address, and phone, and "no address has more than one phone"; a role (e.g. "Moses") has a name, gender, and type, and is portrayed in a given episode by exactly one actor, though a role may recur across many episodes/seasons and the same actor may portray the same role many times.

Find. A complete Chen-notation ER diagram with keys and cardinality/participation constraints, plus any constraint the diagram cannot express.

Approach. Read "a season has a season ID" alongside "an episode appears in only one season" and "a show may have one or more seasons": nothing in the prose says a SeasonID is unique ACROSS different shows (only that each show numbers its own seasons), so Season is modelled as a weak entity owned by Show. Episode, by contrast, IS given "a unique identification number" of its own, so it is a strong entity related to Season by an ordinary (if mandatory) 1:N relationship. The trickiest rule — "the role for a single episode is played by exactly one actor" — is a functional (key) constraint on a THREE-way relationship among Role, Episode, and Actor, drawn as a ternary relationship with an arrow into Actor.

Entities. Show (ShowID key, Format). Season (weak; partial key SeasonID, Title — owned by Show). Episode (EpisodeID key, Title, CopyrightDate). Role (RoleName key, Gender, Type). Actor (ActorID key, SIN, Name, Address, Phone).

Relationships. Has (identifying): Show (1) – Season (N, total — a season cannot exist without its owning show). Contains (identifying): Season (1) – Episode (N, total — "an episode appears in only one season" makes this both mandatory and single-valued). Portrays (ternary): Episode (M) – Role (N) – Actor, with a functional (key) constraint drawn as an arrow into Actor: for any given (Role, Episode) combination, at most one Actor participates — exactly the constraint "the role for a single episode is played by exactly one actor." Neither Role nor Episode is required to participate in every combination (a role need not appear in every episode), so participation on those two sides is partial.

Constraint the ER diagram cannot capture. "Poorly paid actors often share the same address, and no address has more than one phone" encodes a functional dependency BETWEEN TWO ATTRIBUTES of the same entity, Address → Phone: every Actor tuple sharing a given Address value must also carry the same Phone value. Chen ER diagrams express cardinality/participation constraints on relationships BETWEEN entities (or key uniqueness within one entity); they have no notation for "attribute X of an entity determines attribute Y of that SAME entity." Capturing this properly would require pulling Address out into its own entity (with Phone as an attribute of THAT entity, and a relationship from Actor to it) — a normalization step, not something expressible by annotating the diagram above. By contrast, "an episode appears in only one season" and "the role for a single episode is played by exactly one actor" ARE both fully captured, by Episode's ordinary participation in Contains and by the functional arrow on Portrays.