1 200 Bookings in One Flat Spreadsheet
A private tuition centre keeps its class bookings in a single spreadsheet with the columns BookingID, StudentName, StudentPhone, ClassName, TeacherName, TeacherPhone and ClassFee. It now holds 1 200 booking rows and has three visible symptoms. First, when teacher Ms Perera changes her phone number, the clerk must edit about 40 rows, and last time two rows were missed, so the centre now has two different numbers for her. Second, a new class cannot be recorded until at least one student books it, because a row without a booking would have no BookingID. Third, when the last booking for 'Maths B' was cancelled, every detail of that class, its teacher and its fee disappeared from the centre's records. The clerk has also twice merged records for two different students who are both named K. Fernando. The clerk wants to keep working in a spreadsheet.
1. Identify the primary key of the current table, explain why StudentName cannot serve as an identifier, and propose what should replace it.
Read the worked answer
Primary key: BookingID. Each row describes one booking event, and each booking has exactly one student, one class and one fee, so BookingID is unique and minimal for the current table.
Why StudentName fails as an identifier
- It is not unique: two different students are both K. Fernando, and merging their rows mixed one student's fees and phone number into the other's record.
- It is not stable: names change through marriage or deed poll, spelling is entered inconsistently (K. Fernando, K Fernando, Kamal Fernando), and any change would have to be made in every one of that student's booking rows.
- It identifies a text string, not a person. Two people can share a name, and one person can appear under several spellings.
Replacement: issue each student a StudentID (for example the admission number), which is unique, never reused, and never changes. Do the same for teachers with TeacherID and for classes with ClassID, and store the names and phone numbers once against those IDs.
2. Name the three types of data anomaly present and match each one to a specific symptom in the case.
Read the worked answer
1. Update anomaly - Ms Perera's phone number is stored in about 40 rows. Changing it requires 40 edits, two were missed, and the database now contradicts itself. In a properly split design the number is stored once in the Teacher table and every class refers to it.
2. Insertion anomaly - a new class cannot be entered until somebody books it, because a booking row needs a BookingID and there is nowhere else to hold a class. The centre is forced to either invent a fake booking or lose the information until the first enrolment.
3. Deletion anomaly - cancelling the final booking for Maths B erased the class, its teacher and its fee. Deleting a booking should remove a booking, nothing more.
Underlying cause (worth stating): redundancy. StudentName, StudentPhone, TeacherName, TeacherPhone and ClassFee are each repeated in many rows, so the same fact is stored many times. Repetition is what makes all three anomalies possible, and it also raises the risk that ClassFee differs between rows for the same class. Splitting the data so each fact is stored once removes the anomalies.
3. Propose a set of related tables with keys that removes the anomalies, and explain how the clerk can keep working in a familiar spreadsheet environment.
Read the worked answer
Proposed tables
- Student (StudentID PK, StudentName, StudentPhone)
- Teacher (TeacherID PK, TeacherName, TeacherPhone)
- Class (ClassID PK, ClassName, TeacherID FK, ClassFee)
- Booking (BookingID PK, StudentID FK, ClassID FK, BookingDate)
How this fixes each symptom
- Ms Perera's number exists once in Teacher. One edit updates everything, and no row can disagree.
- A class is created in Class before anyone books it, so the insertion anomaly disappears.
- Cancelling a booking deletes one row in Booking; the Class and Teacher rows remain, so the deletion anomaly disappears.
- Students with the same name are distinguished by StudentID, so the two K. Fernandos can never be merged.
- Booking resolves the many-to-many link: one student takes many classes and one class has many students.
Keeping the spreadsheet The clerk can use one worksheet per table, linked by ID, with data validation drop-downs so that a booking picks an existing StudentID and ClassID instead of retyping a name. Lookup formulas then display names and fees on a printable register. At 1 200 rows and growing, however, moving the four tables into a simple relational DBMS with forms and queries is the safer step, and the sheet can be imported directly.