One Spreadsheet for a Growing Tuition Institute
A tuition institute keeps all its records in one spreadsheet. Each row contains a student ID, student name, phone number, class name, teacher name, monthly fee, payment date, and receipt number. There are 480 students and about 3,200 payment rows because many students have paid for several months. A student who changes class is entered as a new row, and the old row remains. The same phone number appears with different spellings of a name. When a student leaves, the clerk deletes all rows for that student, which also removes the payment history. The monthly income report takes 20 minutes and sometimes shows different totals when run twice. The institute wants to move to a database before the next intake of 150 students.
1. Identify the insertion, update, and deletion anomalies in this spreadsheet. Give one concrete example of each from the description.
Read the worked answer
- Insertion anomaly: the institute cannot record a new class or a new teacher until at least one student is enrolled and a payment row exists. A new class with no students has nowhere to go.
- Update anomaly: a teacher's name is repeated in every row for every class and payment. If the teacher changes her name, the clerk must update many rows; a missed row leaves two versions. The same happens with a student's phone number and with the monthly fee.
- Deletion anomaly: deleting a student who leaves removes all payment rows, so income history and receipt numbers disappear. The report for a past month can no longer be reproduced.
- Duplicate identity: a class change creates a new row and leaves the old one, so one student can have two IDs and two phone spellings. The 480 student count and the 3,200 payment rows cannot be trusted.
- Unstable report: without a primary key and with duplicate rows, the income total can differ between runs if formulas or filters include different rows.
2. Propose a relational schema in third normal form. Name tables, primary keys, foreign keys, and at least three constraints.
Read the worked answer
Use separate tables so each fact is stored once.
Student(student_id PK, full_name, phone, address, status)Teacher(teacher_id PK, teacher_name)Class(class_id PK, class_name, teacher_id FK -> Teacher)Enrollment(student_id FK, class_id FK, start_date, end_date, PRIMARY KEY(student_id, class_id, start_date))FeeRate(class_id FK, effective_from, monthly_fee, PRIMARY KEY(class_id, effective_from))Payment(receipt_no PK, student_id FK, class_id FK, period_covered, amount, payment_date, method)
This is in third normal form: teacher name depends on teacher_id, fee depends on class and effective date, and payment facts depend on the receipt. Do not store the current fee only in Payment; store the amount actually paid and the period, and keep the rate history separately.
Constraints: UNIQUE(receipt_no), amount > 0, payment_date not in the future, end_date null or after start_date, phone in a fixed format, and foreign keys that prevent deleting a student with payments. A student changing class gets a new enrollment row, not a new student ID.
3. Describe a migration and validation plan, including how to detect duplicates and reconcile totals.
Read the worked answer
- Profile the spreadsheet: count rows, find blank student IDs, duplicate receipt numbers, and rows with the same normalised phone and similar names. Produce an exception list for the clerk, not an automatic merge.
- Create the new tables in a staging database. Assign or preserve student IDs; map each class name and teacher to one ID; load payments with the original receipt number.
- Reconcile before switching: 480 students (or the corrected number), about 3,200 payment rows, and the same total income by month. Compare
SUM(amount)and counts per month, and investigate every difference. - Detect duplicates with grouped queries on normalised phone and name; resolve them manually against admission forms. Check orphan payments and class enrollments without a student.
- Run the old spreadsheet and the new database in parallel for one month. Train the clerk, restrict access, and take a backup before go-live.
- After sign-off, make the spreadsheet read-only for audit. Keep the legacy IDs in a cross-reference column so old receipts can still be found.