Applied Class 4: Entity Relationship Mapping

exercises
tutorial
databases
relational-model
er-mapping

Practice for 2026-03-16-er-to-relational-mapping. See relational-mapping-notation and er-diagram-notation for reference.

Section A — Basic mapping

Question 1 — Select the correct mapping

A diagram combining several mapping rules at once: H (strong entity, key i, attribute j) and A (strong entity, key b, attributes c, d) participate in a ternary relationship K (roles: N for H, M for A, 1 for P, own attribute l) together with P (strong entity, key q, attribute r). A —1:N R1→ E (weak entity, partial key f, attribute g). A —1:1 R2— P. P —1:N S→ M (weak entity, partial key o).

H [i, j]

A [b, c, d]

E [b, f, g]
E.b references A.b

P [q, b, r]
P.b references A.b

K [b, i, q, l]
K.b references A.b
K.i references H.i
K.q references P.q

M [q, o]
M.q references P.q
  • E is a weak entity owned by A — its key is (b, f) (A’s key
    • its own partial key f).
  • R2 is a plain 1:1 relationship between A and P — merge it into one side rather than creating a separate relation for it; P is extended with A’s key as a foreign key here.
  • K is a ternary relationship — its own relation gets a foreign key to all three participants, but only the foreign keys from the many-cardinality sides (H’s N and A’s M) form the primary key; P’s foreign key (q, the 1 side) is a plain attribute, not part of the key — same pattern as the lecture’s N-ary “1:1:1 & N:1:1” worked cases.
  • M is a second weak entity, this time owned by P — key (q, o).

Question 2 — Subclasses and weak entities

F (key a, composite attribute b → c/d) has a recursive 1:N relationship R (attribute r), and disjointly (d) specialises into G (attributes i, j) and H (attribute k). H —1:N S→ X (weak entity, partial key x, attributes y, z).

(a) What’s the relationship between F, G, H, and how are G/H mapped? (b) Interpret S/X with respect to H.

(a) G and H are disjoint subclasses of F (an instance is at most one of G/H, never both). Standard subclass mapping — each subclass’s primary key is the superclass’s primary key, plus a foreign key back to it:

F [a, c, d, superF, r]
F.superF references F.a

G [a, i, j]
G.a references F.a

H [a, k]
H.a references F.a

(F.superF/r come from R, F’s own recursive 1:N relationship — mapped the same way as any binary 1:N relationship, just back onto F itself.)

(b) X is a weak entity owned by H via the identifying relationship S — each X belongs to exactly one H, but one H can own many Xs. X’s real key is (a, x) (H’s key, inherited from F, plus X’s own partial key x):

X [a, x, y, z]
X.a references H.a

Question 3 — Olympics database

Map the ER diagram: Athlete (id, sex, name, age, country) —M:N Participates (placement)→ Event (id, name, category) —N:1 Venue (id, name, address).

Athlete [id, sex, name, age, country]

Venue [id, name, address]

Event [id, name, category, venue]
Event.venue references Venue.id

Participates [athleteID, eventID, placement]
Participates.athleteID references Athlete.id
Participates.eventID references Event.id

Event—Venue is 1:N, so Event (the N side) gets a plain foreign key. Athlete—Event is M:N, so Participates is its own new relation, carrying both foreign keys plus the relationship’s own attribute placement.

Section B — Advanced mapping

Question 1 — Cinema

Map: Movie (name, description, runningTime) —1:N Viewing (date, time, multivalued Snack)←1:N Customer (id, name, email), each Viewing overseen by exactly one Attendant (staffId, name, phone).

Movie [name, description, runningTime]

Customer [id, name, email]

Attendant [staffID, name, phone]

Viewing [name, id, date, time, attendant]
Viewing.name references Movie.name
Viewing.id references Customer.id
Viewing.attendant references Attendant.staffID

ViewingSnack [name, id, date, time, snack]
ViewingSnack.{name, id, date, time} references Viewing.{name, id, date, time}

Viewing’s own key is the composite (name, id, date, time) — the movie + customer + timestamp jointly identify one viewing. Snack is multivalued, so it gets its own relation (ViewingSnack) with a composite foreign key back to the full Viewing key.

Question 2 — Student peer evaluation

Map: Student (sid, firstName, secondName) submits Assessments (sid+number); students peer-evaluate each other’s assessment submissions, recording a mark.

Student [sid, firstName, secondName]

Assessment [sid, number]
Assessment.sid references Student.sid

PeerEvaluation [studentSid, assessmentSid, number, evaluationId, sidGrades, mark]
PeerEvaluation.studentSid references Student.sid
PeerEvaluation.sidGrades references Student.sid
PeerEvaluation.{assessmentSid, number} references Assessment.{sid, number}

PeerEvaluation has two separate foreign keys to Student playing different roles — studentSid (the evaluator submitting the mark) and sidGrades (whose assessment is being marked) — plus a composite foreign key to the specific Assessment being evaluated.

Question 3 — Spot the mistakes

A junior developer mapped question 1’s Cinema diagram as:

Movie [name, description, runningTime]

Customer [id, name, email]

Attendant [staffID, name, phone]

Viewing [name, id, date, time, attendant]
Viewing.name references Movie.name
Viewing.id references Customer.id
Viewing.attendant references Attendant.staffID

ViewingSnack [name, id, date, time, snack]
ViewingSnack.name references Movie.name
ViewingSnack.id references Viewing.id
ViewingSnack.date references Viewing.date
ViewingSnack.time references Viewing.time

Find the mistakes.

ViewingSnack should reference Viewing’s composite key as one foreign key constraint (ViewingSnack.{name, id, date, time} references Viewing.{name, id, date, time}), not four separate, independently-named foreign key lines pointing at different tables (Movie for name, Viewing for id, and Viewing again for date/time, inconsistently) — splitting a composite foreign key up like this breaks the link between a ViewingSnack and the specific Viewing it belongs to (nothing actually forces all four columns to jointly match one real Viewing row), and pointing name back at Movie instead of Viewing is simply the wrong target relation for a foreign key that’s supposed to identify which viewing the snack belongs to.

Section C — EER diagram mapping

Question 1 — Medical clinic

Map: Doctor (licenceNo) splits into GP/Specialist (specialisationArea); Doctor —M:N WorksIn→ Clinic (regNo); Clinic has a subclass SpecialistClinic, run by exactly one Specialist (Runs, 1:N); Patient (patientId) —M:N Appointment (dateTime, fee)→ Doctor.

Doctor [licenceNo]

Patient [patientId]

Clinic [regNo]

GP [licenceNo]
GP.licenceNo references Doctor.licenceNo

Specialist [licenceNo, specialisationArea]
Specialist.licenceNo references Doctor.licenceNo

SpecialistClinic [regNo]
SpecialistClinic.regNo references Clinic.regNo

Appointment [licenceNo, dateTime, patientId, fee]
Appointment.licenceNo references Doctor.licenceNo
Appointment.patientId references Patient.patientId

WorksIn [licenceNo, regNo]
WorksIn.licenceNo references Doctor.licenceNo
WorksIn.regNo references Clinic.regNo

Runs [licenceNo, regNo]
Runs.licenceNo references Specialist.licenceNo
Runs.regNo references SpecialistClinic.regNo

GP/Specialist are standard disjoint-subclass mappings of Doctor. SpecialistClinic is a subclass of Clinic with no extra attributes of its own — just a foreign key back to Clinic. Runs is a 1:N relationship between Specialist and SpecialistClinic specifically (not the general Doctor/Clinic pair), so its foreign keys target the subclass relations, not the superclasses.