One Flat Schedule Sheet, Six Thousand Rows
A training company runs about 400 course sessions a year and has always tracked them in one worksheet named Schedule - roughly 6,000 rows with the columns CourseName, InstructorName, InstructorPhone, Room, StartDate, EndDate, StudentID, StudentName, StudentEmail and FeePaid. Every session repeats its course, instructor, room and student details on each row.
Symptoms have accumulated. When an instructor changed her phone number, an administrator used Find and Replace; 1,940 rows were updated and 40 were missed because the number had been typed with a space in the middle. The same student appears under three spellings of her name and two email addresses, with fees recorded against all three. A cancelled session was deleted, which also erased the only record that eight students had enrolled. Nobody can answer "which rooms were never used last term" or "how many seats does Room 4B hold" without opening a facilities document. The company wants to move this data into a relational database before the next enrolment cycle.
1. Name the update, insert and delete anomalies visible here and propose the table set, with primary and foreign keys.
Read the worked answer
Anomalies visible in the flat sheet:
- Update anomaly: the instructor's phone is stored on every session row. One change touched 1,940 rows and missed 40 - the attribute exists nearly 2,000 times and now disagrees with itself.
- Insert anomaly: a course cannot be scheduled until a student enrols, because student columns are part of the row.
- Delete anomaly: deleting a cancelled session deleted the only evidence that eight students had enrolled.
- Attribute dependency: a room's capacity depends on the room, not the session, so it is repeated on every row or - as here - kept outside the database altogether.
- Entity integrity: one student, three spellings. Nothing enforces that a person is a single row.
Proposed structure:
STUDENT(StudentID PK, FirstName, LastName, Email UNIQUE)
INSTRUCTOR(InstructorID PK, Name, Phone)
COURSE(CourseID PK, Title, Level)
ROOM(RoomID PK, Name, Capacity)
SESSION(SessionID PK, CourseID FK->COURSE, InstructorID FK->INSTRUCTOR,
RoomID FK->ROOM, StartDate, EndDate, Status)
ENROLLMENT(SessionID FK, StudentID FK, FeePaid, Result,
PRIMARY KEY(SessionID, StudentID))
ENROLLMENT resolves the many-to-many between sessions and students: one student attends many sessions, one session has many students, and fee and result belong to the pairing rather than to either side. The instructor's phone now exists once. A cancellation becomes a Status change, so history survives. Room capacity becomes a stored fact that answers both open questions directly. Add UNIQUE(RoomID, StartDate, StartTime) on SESSION if a room cannot host two sessions at once.
2. Choose the primary key strategy for STUDENT and justify why Email cannot serve as the key. How would you merge the existing duplicates?
Read the worked answer
Use a surrogate key: StudentID, auto-numbered or system-assigned.
Why Email cannot be the primary key:
- It changes. Students move between providers and institutional addresses; a mutating primary key forces cascading updates across every foreign key.
- It is not guaranteed unique or present. Applicants without email, minors on a family address, and shared departmental addresses all break a NOT NULL UNIQUE key.
- The data already disproves it. Two email addresses for one person means email does not identify the student; three spellings mean the name does not either.
- Format variance defeats comparison. Case, trailing spaces and plus-addressing make
[email protected],[email protected]and[email protected]three different strings.
Keep email as a UNIQUE, nullable, normalised attribute - trimmed and lower-cased on load - with a matching process for imports.
Merging the existing rows: normalise every email and name; match first on email, then on name plus date of birth; adjudicate the remaining ambiguous cases by hand rather than trusting a fuzzy rule. Point all enrolments at the surviving StudentID, move the fee records onto that ID, and keep the discarded IDs in a cross-reference table so historical exports can still be traced. Run the merge inside a transaction and check the totals before and after: SUM(FeePaid) must be identical. A name-based key would reproduce precisely the failure the company is escaping.
3. Write the query for "every room, including rooms never scheduled, with the number of sessions it ran between 1 September 2024 and 31 December 2024". Identify the two mistakes most likely to produce a wrong but plausible result.
Read the worked answer
SELECT r.RoomID,
r.Name,
r.Capacity,
COUNT(s.SessionID) AS SessionsRun
FROM ROOM r
LEFT JOIN SESSION s
ON s.RoomID = r.RoomID
AND s.StartDate >= '2024-09-01'
AND s.StartDate < '2025-01-01'
GROUP BY r.RoomID, r.Name, r.Capacity
ORDER BY SessionsRun ASC, r.Name;
Mistake 1 - putting the date filter in WHERE. WHERE s.StartDate >= '2024-09-01' tests a column that is NULL on the null-extended rows the LEFT JOIN created for unused rooms. NULL fails the comparison, those rows are discarded, and the outer join silently becomes an inner join - the unused rooms vanish, which is exactly the answer the question was asked to find. The predicate belongs in ON, where it limits which sessions match without limiting which rooms are returned.
Mistake 2 - using COUNT(*). A room with no sessions still contributes one row of NULLs, so COUNT(*) returns 1 and every unused room is reported as having run one session. COUNT(s.SessionID) counts non-NULL values only and correctly returns 0.
A subtler third: adding WHERE s.SessionID IS NULL to return only unused rooms is correct provided the date range stays in ON. Move the date to WHERE as well and the two clauses contradict each other, returning an empty result that looks like a data problem.