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. Four entity classes described in prose — musicians (SIN, name, address, phone), instruments (ID, name, musical key), albums (ID, title, copyright date, format), songs (title, author) — plus five business rules connecting them (plays, one-album-per-song, performs, exactly-one-producer, and the address/phone sharing rule).

Find. A complete Chen-notation ER diagram: entities with keys, relationships with cardinalities and participation, and any constraint the diagram cannot express.

Approach. Read each bullet as either an entity attribute or a relationship cardinality; the two trickiest bullets ("no address has more than one phone" and "no song may appear on more than one album") get special attention because one is a plain 1:N/weak-entity cardinality (expressible) and the other is a cross-attribute functional dependency (NOT expressible in a basic ER diagram).

Musician (SIN key, Name, Address, Phone) — SIN is the natural key since names are not guaranteed unique. Instrument (ID key, Name, MusicalKey). Album (AlbumID key, Title, CopyrightDate, Format) — the source text lists both "a unique identification number" and "an album identifier" for Album; these read as the same key attribute named twice, so only one key attribute is modelled (flagged below). Song is a weak entity (Title, Author, no attribute of its own is globally unique) identified by its owning Album via the identifying relationship Includes, with a partial key of Title — this matches the rule "no song may appear on more than one album" exactly, since a weak entity can only ever be linked to ONE owner.

Four relationships complete the model: Plays (Musician M:N Instrument, "a musician may play several instruments, and a given instrument may be played by several musicians" — no participation constraint stated, so partial on both sides); Includes (Album 1:N Song, identifying, total participation of Song — every song belongs to exactly one album); Performs (Song M:N Musician, total participation on the Song side because "each song is performed by one or more musicians" requires at least one); Produces (Album N:1 Musician, total participation on the Album side because "each album has exactly one musician who acts as its producer" is mandatory, partial on the Musician side since not every musician need produce).

Constraint the ER diagram cannot capture. The rule "poorly paid musicians often share the same address, and no address has more than one phone" is a functional dependency between two ATTRIBUTES of the same entity (Address → Phone): every Musician tuple with a given Address value must carry the same Phone value. Chen-notation ER diagrams (and the cardinality/participation constraints they express) only constrain relationships BETWEEN entities, or the uniqueness of a whole key; they have no notation for "attribute X of an entity determines attribute Y of the same entity." Capturing this rule structurally would require pulling Address out into its own entity (with Phone as ITS attribute, and a relationship from Musician to Address) — a NORMALIZATION step that changes the schema rather than something expressible by decorating the diagram above. By contrast, "no song may appear on more than one album" IS fully captured, simply by making Song a weak entity with Album as its one identifying owner.