One Table Behind a Growing Tuition Centre
A tuition centre with four branches has kept enrolment and payment records in a single database table since 2015, exported monthly to a spreadsheet for the manager. The table contains: StudentID, StudentName, StudentPhone, ClassCode, ClassName, TutorName, TutorPhone, BranchName, BranchPhone, MonthlyFee, PaymentMonth, AmountPaid, ReceiptNo. Recent problems: when a tutor changed her phone number, a clerk updated 300 rows but 17 rows kept the old number and parents were given the wrong contact; the centre raised the fee for one class in January, and February receipts generated from older rows show the new fee; two students named 'Nur Aisyah' with different phone numbers were given the same StudentID, so their payments merged; and when a clerk deleted a withdrawn student's rows, the only record of two receipt numbers disappeared. The centre has 620 students, 48 classes and 19 tutors, producing about 7,000 payment rows a year.
1. Which normal form does the table violate, and why? List the functional dependencies you can infer and propose third-normal-form relations with primary and foreign keys.
Read the worked answer
Diagnosis. The natural key of a row is roughly (StudentID, ClassCode, PaymentMonth, ReceiptNo). Because one receipt can cover several months and one student can take several classes, the table also embeds a repeating group; treat it as not in 1NF until each payment is one atomic row.
Functional dependencies:
- StudentID → StudentName, StudentPhone (partial dependency on part of the key → violates 2NF)
- ClassCode → ClassName, MonthlyFee, TutorName, TutorPhone (partial dependency → 2NF)
- TutorName → TutorPhone (so TutorPhone is transitively dependent → 3NF)
- ClassCode → BranchName and BranchName → BranchPhone (transitive → 3NF)
3NF schema:
| Relation | Columns | Keys |
|---|---|---|
| Branch | BranchID, BranchName, BranchPhone | PK BranchID |
| Tutor | TutorID, TutorName, TutorPhone | PK TutorID |
| Student | StudentID, StudentName, StudentPhone | PK StudentID; UNIQUE(StudentPhone, StudentName) |
| Class | ClassCode, ClassName, MonthlyFee, TutorID, BranchID | PK ClassCode; FK TutorID, BranchID |
| Enrolment | StudentID, ClassCode, StartDate, EndDate | PK (StudentID, ClassCode) |
| Payment | ReceiptNo, StudentID, ClassCode, PaymentMonth, AmountPaid, FeeCharged | PK ReceiptNo; FK StudentID+ClassCode |
The FeeCharged column is deliberate: the fee must be copied into the payment when it is taken, not looked up from Class at report time. That single decision fixes the February problem, because old receipts keep the old price while new receipts carry the new one.
2. The manager proposes using the student's name plus phone number as StudentID so that records are 'easier to read'. Evaluate this and recommend an identifier strategy.
Read the worked answer
Reject it. Name-plus-phone fails as an identifier on four counts:
- Stability. Names change (marriage, spelling correction), and phone numbers change when a family switches plan. A key that changes forces updates across every referencing table, and any update that is missed recreates the 17-row problem.
- Uniqueness. The collision already happened: two different 'Nur Aisyah' records received the same derived ID and their payments merged. Siblings may share a parent's phone number, which creates the opposite failure, a duplicate student under one ID.
- Reuse. A deleted identifier may be reissued to a new student, silently attaching old payment history to a new person.
- Coupling. It exposes personal data in every foreign key, printout and export, which is poor practice for a centre holding minors' data.
Recommendation: a surrogate key, an auto-increment integer or a short code such as STU-2024-0001, with no meaning and never reused. Enforce uniqueness separately with a UNIQUE constraint on a combination such as (StudentName, GuardianPhone, DateOfBirth). Keep a duplicate-suspicion report that lists name similarity plus matching phone or address for clerical review before enrolment. When two records must merge, merge them by moving child rows under a transaction and marking the retired surrogate key as inactive rather than deleting it, so receipt history stays traceable.
3. Outline a migration plan and the integrity rules you would enforce. What could go wrong, and how would you detect it?
Read the worked answer
Plan.
- Build the new schema alongside the old table; do not edit the production table in place.
- Extract the distinct Branch, Tutor, Class and Student sets, assign surrogate keys, and fix duplicates manually before loading (name plus phone matching finds the merged Nur Aisyah records).
- Load Enrolment and Payment, writing FeeCharged from the fee that applied on the payment date, not from the current fee.
- Reconcile: row counts per year (about 7,000), total AmountPaid per month and per class must match the old extract to the sen before cutover.
- Run both systems in parallel for one billing cycle, then freeze the old table read-only.
Integrity rules: NOT NULL on all keys and amounts; PK on each table; FK on Class and Payment with ON DELETE RESTRICT; CHECK (AmountPaid > 0); CHECK (MonthlyFee >= 0); UNIQUE(ReceiptNo); UNIQUE(StudentID, ClassCode, PaymentMonth) on Payment to block double-charging in a month.
What could go wrong: orphaned payments when a class code has no match; silent duplicate branches created by spelling variants ('Taman Maju' versus 'Tmn Maju'); loss of historical fee values if you copy the current fee; deletion of receipts during deduplication. Detection: a pre-load exception report listing every unmatched code, an orphan-count query of zero before cutover, a totals reconciliation, and a post-migration audit query for payments whose FeeCharged differs from the class fee on the payment date, which the manager must sign off row by row.