NivaarExam PrepOfficial exam papers ↗

25-Comp-B3 Data Bases and File Systems · December 2013

Question 3 of 8: ER diagram — Downtown Vancouver Records

Nivaar worked solution (AI-drafted; not reviewed by a licensed engineer)

Notes on this paper

98-Comp-B3, Data Bases & File Systems — National Exams, December 2013. 3 hours, closed book (calculators permitted). Candidates were instructed to answer five questions: one of Questions 1/2, one of Questions 3/4, and three of Questions 5–8 — only those five are marked. All 8 questions are answered below for completeness (this is a study resource covering the full syllabus).

Reference texts: Silberschatz, Korth & Sudarshan, Database System Concepts (7th ed.) — ER modelling, normal forms, transactions and serializability; Ramakrishnan & Gehrke, Database Management Systems (3rd ed.) — B+-tree indexing, SQL, and relational algebra.

Question 3: ER diagram — Downtown Vancouver Records (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.

Check: the source text lists an album's attributes as "a unique identification number... and an album identifier" — two apparently redundant ID fields. Treated as a single key attribute (AlbumID = the album identifier), since introducing two unrelated identifiers is unmotivated by anything else in the question.

Approach. Model each business rule as an entity, a relationship, or a cardinality/participation annotation, choosing entity vs. attribute so that every stated constraint becomes structurally enforced wherever possible.

MusicianSINNameAddressAddressPhoneInstrumentInstrIDNameMusicalKeyAlbumAlbumIDTitleCopyrightDateFormatSongTitle (partial key)LivesAtPlaysProducesPerformsContainsN1MNN1MN1N
Fig. Q3 — Downtown Vancouver Records conceptual schema. Double-bordered box/diamond = weak entity/identifying relationship; thick edge = total participation.

Design decisions. Address is modelled as its own entity (not a Musician attribute) with Phone as ITS attribute, connected via LivesAt (Musician N–1 Address). This is what actually captures "no address has more than one phone": since Phone belongs to the Address entity, every musician who shares that address automatically shares its one phone value — the constraint is enforced by the schema itself, not by a side-note. Song is modelled as a weak entity owned by Album through the identifying relationship Contains (total participation on Song, since a song cannot exist without its album), with partial key Title — this directly captures "no song appears on more than one album," because a weak entity has exactly one owner by construction. Plays is the stated many-to-many between Musician and Instrument. Performs is many-to-many between Song and Musician with total participation on Song (every song needs at least one performer). Produces is Album (N) to Musician (1) with total participation on Album (every album needs exactly one producer); a musician may produce zero or many albums, so no total-participation arrow points back at Musician.

Constraints the ER diagram cannot capture. A basic (Chen) ER diagram has no way to express a general, arbitrary-valued constraint that compares attribute values across relationship sets or bounds a many-relationship's multiplicity by an exact number — only the qualitative 1/many distinction is available. Concretely here: we cannot state "a musician may play at most 5 instruments" (only "many"), and we cannot enforce a cross-relationship business rule such as "the producer of an album must also be a performer on at least one of its songs" without additional constraint language (e.g. OCL) or an application-level check; the diagram can express each relationship's own cardinality but not a logical implication between two different relationships.