One Spreadsheet, 3,140 Rows
Anjali Coaching Centre in Jaipur has tracked admissions in a single Excel sheet for four years. The sheet now has 3,140 rows and these columns: Student Name, Father Name, Mobile, Course, Batch, Fee Total, Fee Paid, Admission Date, Remarks. The problems have piled up. A student who left and rejoined appears twice. Two brothers in different batches share their father's mobile number. Numbers are typed as 98xxxxxxxx, +91-98xxxxxxxx and 098xxxxxxxx. The same course appears as 'X', 'x', 'Class X' and 'Xth'. Fee Paid holds values like 12000, '12000/-', 'paid', 'partial' and blanks. There are two different students named Rahul Sharma in the same batch. The centre must send a scholarship report to a sponsor by Friday, and the accountant's fee collection total differs from the bank statement by about ₹18,000. The owner wants to keep using Excel and asks whether a database is really needed.
1. The owner proposes making Mobile the primary key so that duplicate students disappear. Evaluate that choice and propose a key design for the data.
Read the worked answer
Test the candidate key against the actual data, not against intuition. A primary key must be unique, never blank and stable.
- Unique? No. Two brothers share their father's mobile number, so the key immediately collides on perfectly valid rows.
- Clean? No. 98xxxxxxxx, +91-98xxxxxxxx and 098xxxxxxxx are the same number written three ways, so the key both rejects valid rows and accepts duplicates until the data is normalised.
- Stable? No. People change numbers; a primary key that changes forces updates across every table that references it.
- Never blank? Not guaranteed - some rows will have no number at all.
So Mobile is a useful attribute, not a key. Even after cleaning to a 10-digit text field validated to start with 6-9, the siblings case remains, which is why a separate Guardian table keyed on GuardianID is the honest solution.
Proposed design. StudentID as a surrogate primary key (auto number), with AdmissionNo as a candidate business key if the centre actually issues one. Then Course(CourseID, CourseName), Batch(BatchID, CourseID, Timing), Enrolment(EnrolmentID, StudentID, BatchID, AdmissionDate), Payment(PaymentID, EnrolmentID, PaidOn, Amount, Mode, ReceiptNo), and Guardian(GuardianID, Name, Mobile) linked to Student.
Judge the trade-off. A natural composite key of Name + Father Name + Date of Birth could work, but name alone fails immediately here - two Rahul Sharmas in one batch - and date of birth is often missing. A surrogate key costs one extra column and removes the whole argument.
2. Name the tables the sheet should become, and give one update, one insert and one delete anomaly that is visible in this data.
Read the worked answer
Why the sheet fails. It mixes four different things in one row: a person, a course, a batch and a payment. That is a partial dependency plus a repeating group, so the sheet is not even in second normal form.
Tables. Student, Guardian, Course, Batch, Enrolment, Payment. Each row of Payment is one instalment, which is the change that matters most because Fee Total and Fee Paid as two columns cannot hold a student who pays in three parts.
Update anomaly. The course is stored as text in every student row, which is exactly why it appears as 'X', 'x', 'Class X' and 'Xth'. Renaming or correcting a course means editing hundreds of rows, and missing one splits the scholarship report into four separate courses. With a Course table, one row changes and everything follows.
Insert anomaly. The centre cannot record a new course, say Class XII Commerce, until at least one student is admitted to it, because a course exists only as a value inside a student row. A Course table lets them create it in advance.
Delete anomaly. If the only student in 'Batch B-7 Morning' leaves and that row is deleted, the fact that the batch, its timing and its teacher ever existed disappears too.
Also state the cost of migrating. The clean-up is the expensive part, not the software: one afternoon of standardising course names and numbers, a decision on how to treat the rejoined student, and a reconciliation before anything is copied across.
3. The accountant's fee figure is about ₹18,000 below the bank statement. Outline how you reconcile it in Excel and one rule that stops it recurring.
Read the worked answer
Reason about the direction of the error first. A duplicate student row would push the total up, not down. A figure that is lower than the bank suggests amounts are present in the sheet but not being counted - and SUM ignores text.
Likely causes, in order of probability.
- '12000/-', 'paid' and 'partial' sit in the Fee Paid column as text. SUM silently skips them, so real money vanishes from the total.
- Blanks where a payment was received but entered only in Remarks.
- The rejoined student's fee split across two rows, with one row's amount typed as text.
Reconciliation steps. Add a helper column: =IFERROR(VALUE(SUBSTITUTE(SUBSTITUTE(D2,"/-",""),",","")),0), and a flag column ISNUMBER(D2) to filter every row where the flag is FALSE. Fix those rows by reading Remarks. Then use COUNTIF on Name and Father Name to list suspected duplicates and decide, with the owner, which rows are one enrolment. Compare month by month against the bank's credit total, matching on date and receipt number, and list unmatched receipts on both sides. Chase the missing receipts rather than adjusting the sheet to make it balance.
One rule to prevent recurrence. After migration, amounts can enter only through a validated payment form - Amount stored as a decimal, ReceiptNo unique, Mode from a fixed list - with no free-text entry in a money field. Then reconcile the Payment table against the bank every month, so the gap is one month old and findable instead of four years old and ₹18,000 deep.