One spreadsheet for the summer school
The organisers of a village summer school keep bookings in one spreadsheet sheet named Bookings. Columns are: BookingRef, ChildName, GuardianPhone, SessionCode, SessionDay, CoachName, CoachPhone, Fee. A child may book several sessions; a session has one coach; the fee depends on the session, not on the child. There are 312 booking rows for 96 children and 18 sessions. Staff have noticed that when Coach Maria's phone number changes, someone must edit every row for her sessions, and last year one row kept the old number. When a child cancels their only booking, the row is deleted and the session's coach details disappear until another booking is entered. Two children with the same name and different guardians were merged by mistake. The committee wants a small relational database and asks whether to keep a 'reporting' table with CoachPhone repeated to make printing easier.
1. Identify the functional dependencies in the spreadsheet and decompose it into relations in third normal form. Name the tables, primary keys and foreign keys, and explain the normalisation steps.
Read the worked answer
The obvious dependencies are: BookingRef → ChildName, GuardianPhone, SessionCode; and SessionCode → SessionDay, CoachName, CoachPhone, Fee. ChildName alone is not a key because two children share a name. There is also a transitive dependency SessionCode → CoachName → CoachPhone, and SessionCode → Fee.
A 3NF design is:
Child(ChildID PK, ChildName, GuardianPhone) Coach(CoachID PK, CoachName, CoachPhone) Session(SessionCode PK, SessionDay, CoachID FK, Fee) Booking(BookingRef PK, ChildID FK, SessionCode FK)
ChildID is a surrogate key because names are not unique; date of birth or another identifier should be captured to distinguish the two children. In the flat sheet, if the key were taken as (BookingRef, SessionCode), ChildName and GuardianPhone would depend on BookingRef alone, a partial dependency. In the decomposed design every non-key attribute depends on the key, the whole key and nothing but the key. CoachPhone now depends only on CoachID, and Fee only on SessionCode, so changing Maria's number is one update.
2. Use concrete examples from this summer school to explain an update anomaly, a deletion anomaly and an insertion anomaly, and show how the new schema removes each one.
Read the worked answer
Update anomaly: Maria's phone number appears on every booking row for her sessions. Staff must find and edit all of them; last year one row kept the old number, so the spreadsheet held two different values for the same fact. In the new schema the number is stored once in Coach and changes once.
Deletion anomaly: when a child cancels their only booking, the row is deleted and the session's coach details disappear with it. The session still exists and its coach still exists, but the spreadsheet loses that information. In the new schema deleting a Booking row does not touch Session or Coach rows.
Insertion anomaly: a new session cannot be recorded until at least one child books it, because there is no row to hold SessionCode, SessionDay, CoachName and Fee. In the new schema a Session row can be inserted with its CoachID before any booking exists.
The merged-children problem is a separate identity issue: because the sheet has only names, two different children were treated as one. A ChildID plus a second identifier such as date of birth prevents that.
3. Advise the committee on the proposal to keep a reporting table with CoachPhone repeated for easier printing.
Read the worked answer
For 312 booking rows, a reporting table is unnecessary. A view that joins Booking, Session and Coach can present CoachPhone beside each booking without storing it twice, so the printed report is always current. A view also avoids the update anomaly the committee is trying to escape.
A materialised reporting table would be faster for very large reports, but here the join is tiny. It also introduces a refresh problem: if Maria's number changes and the table is not rebuilt, the report is wrong, and nobody may notice. If a materialised copy is used, it needs a documented refresh trigger, a timestamp, and a rule that it is never used for operational decisions. For this summer school, keep the normalised tables as the source of truth and use a view; denormalisation would be justified only by a measured performance problem, not by convenience.