Six Years of Admissions Data in One Spreadsheet

Entities

• Student(StudentID PK, FirstName, LastName, Phone, GuardianPhone, CreatedOn)
• Course(CourseID PK, CourseName, FullFee)
• Teacher(TeacherID PK, TeacherName, Phone, Email)
• Batch(BatchID PK, CourseID FK, TeacherID FK, Timing, Room)
• Enrollment(EnrollmentID PK, StudentID FK, BatchID FK, AdmissionDate, TotalFee, FeePaid, Status)
• Payment(PaymentID PK, EnrollmentID FK, Amount, PaidOn, Mode) if receipts must be tracked

Why AdmNo cannot be the key yet

A primary key must be unique and not null, and AdmNo fails both tests: 12 blanks and 5 duplicates. A key that is sometimes absent and sometimes shared cannot identify a row, so nothing can safely reference it. The duplicates are worse than the blanks because they look valid - two different students sharing one number will merge silently in any join, and the merge will look like a correct answer.

Repair steps

1. Do not delete duplicates on sight. Match candidate pairs on normalised name plus phone plus admission date; true duplicates are merged with a note, genuinely different students get a new number.
2. Fill blanks only after the duplicate question is closed, so a new number is not issued to a row that is about to disappear.
3. Keep the old AdmNo as a legacy column for reconciliation with receipts and certificates.

Why a generated key is safer

A surrogate key such as an auto-increment integer has no meaning, so it never needs correcting when a student's details change, and it cannot be reused or mistyped by a clerk who thinks they remember it. Human-entered numbers also carry meaning that tempts reuse (the same number for a re-admission), which quietly reintroduces the duplicate problem. Keep the legacy AdmNo as an alternate key with a unique constraint once cleaned, so the office still has the number it prints on receipts.

Update anomaly

TeacherPhone is stored once per student row. Priya's teacher appears on perhaps 300 rows; when her number changed in March, either all 300 were edited or none were. In fact a partial edit happened, which is the worst outcome: the file now contains two different numbers for one teacher, and nothing in the data says which is current. Anyone phoning from the older rows reaches a stranger. In a normalised design the phone appears once in Teacher, so one update is correct by construction and the multi-valued mess cannot exist.

Insertion anomaly

A new teacher cannot be recorded until at least one student enrols with them, because a teacher row does not exist - the teacher only exists as an attribute of a student. The centre cannot keep a staff list, cannot assign the teacher to a new batch, and cannot contact them about a batch that has not yet filled.

Deletion anomaly

When the last student of a departing teacher leaves, deleting or cleaning those rows also erases the teacher and their contact details, even though the centre still needs them for salary records, references or re-hiring. Unrelated facts are destroyed by an unrelated deletion.

Migration cost of doing nothing

The cost compounds in three places. Every SMS campaign built on a stale phone list wastes credit and reaches the wrong people. Every staff communication depends on someone's memory of which number is current. And each new term adds rows carrying the same contradictions, so the eventual cleanup grows with the file: 300 rows today becomes 900 in two years, and the March ambiguity becomes five or six competing numbers with no way to date them.

What to do first

Extract a distinct teacher list from the file, reconcile it by hand with the office's staff records to settle which number is current and when it changed, assign TeacherIDs, and only then update the student rows to reference the IDs. Do not attempt a full normalisation in one weekend: build Teacher first because it is small, high-value and immediately removes the ambiguity the centre actually feels.

The list

SELECT s.FirstName, s.Phone, c.CourseName, e.TotalFee - e.FeePaid AS Due
FROM Enrollment e
JOIN Student s ON s.StudentID = e.StudentID
JOIN Batch b   ON b.BatchID   = e.BatchID
JOIN Course c  ON c.CourseID  = b.CourseID
WHERE COALESCE(e.FeePaid, 0) < e.TotalFee
  AND e.Status = 'Active';

The silent trap

NULL means unknown, not zero. NULL < TotalFee evaluates to unknown, not true, so every row where FeePaid is blank - exactly the students who have paid nothing - is excluded and never appears in the SMS list. The query returns a confident, wrong list. COALESCE(e.FeePaid, 0) handles it, and the same care is needed for blank TotalFee. A second trap sits on the raw sheet: fees typed as 'Rs 4,500/-' or as text compare as strings, so the 'less than' test is meaningless until the column is numeric.

Before sending, also normalise Phone to ten digits, drop numbers with 9 digits or obvious placeholders, decide how to treat two students sharing a guardian's number, and check consent or do-not-disturb status - a technically correct list can still be a compliance problem.

Why filter and sort fails

Sorting by name changes row order and nothing else. It does not merge Priya Sharma with Sharma, Priya or priya sharma, so she remains three students. It does not resolve the five duplicate admission numbers or the twelve blanks. It does not unify the six spellings of CCC, so any count by course splits into six small groups. And sorting on a column where numbers are stored as text orders 100 before 20. The junior has proposed a way of looking at the problem more comfortably, not a way of removing it.

De-duplication procedure

1. Standardise first: trim spaces, fix case, split name into parts, strip non-digits from phone.
2. Block on exact match of normalised phone plus surname - phone is the strongest identifier available here.
3. Review candidate pairs by hand; a student may legitimately have two enrolments, which is not a duplicate.
4. Merge only with a written record: which ID survives, which rows are relinked, which are archived.
5. Never hard-delete; keep an archive with the reason and the date.
6. Prevent recurrence with a unique constraint on phone plus course plus enrolment date, and form validation that rejects a 9-digit phone or a missing admission number at entry.