Three Staff, One Spreadsheet, Two Rahul Sharmas
A coaching centre in Panchkula teaches 300 students in 6 batches. All records are kept by the owner, Mr Verma, in a single spreadsheet with the columns: Student Name, Father's Name, Mobile, Batch, Admission Date, Total Fee, Fee Paid, Fee Due and Remarks. Three staff members take turns editing the same file from a shared folder.
Problems in the last month: there are two students named "Rahul Sharma" in Batch B2 and staff cannot tell which fee row belongs to which; mobile numbers appear as 98123-45678, 9812345678 and +91 9812345678, so sorting and searching give different results; one student moved from B2 to B3 but her Fee Due is still listed under B2 in the monthly report; and two staff members saved the file at the same time, losing a day's entries.
1. Propose a design with two tables that removes the duplicate-name and batch-change problems. Name the tables, list at least four fields in each, and state the primary and foreign keys. Explain why Student Name is a poor primary key.
Read the worked answer
Table 1 - Students: StudentID (AutoNumber, primary key), StudentName, FatherName, Mobile, AdmissionDate, BatchID (foreign key to the batch list). Table 2 - Fees: FeeID (AutoNumber, primary key), StudentID (foreign key), FeePeriod, TotalFee, FeePaid, FeeDue, PaymentDate.
Relationship: one student to many fee rows; every fee row belongs to exactly one student. The monthly report takes the student's current BatchID from the Students table, so moving her from B2 to B3 is a single edit and the next report is automatically correct - the old spreadsheet duplicated the batch in every fee row, which is why it went stale.
Why StudentName is a poor primary key:
- It is not unique - B2 already has two Rahul Sharmas.
- It can be misspelt and later corrected, and a key must never change.
- Two students may share a name and even a father's name.
- A mobile number is also poor: it changes, and siblings may share one.
A primary key must be unique, never blank and never edited; a system-generated ID satisfies all three and lets staff identify the right Rahul Sharma by roll number or admission date.
2. Recommend a data type for StudentID, Mobile, Admission Date, Fee Due and Remarks, justifying each. Explain how to handle the three formats of mobile numbers.
Read the worked answer
- StudentID: AutoNumber / Long Integer - generated by the system, unique, never typed by staff.
- Mobile: Short Text, not Number. It is an identifier, never added or multiplied; a Number field drops a leading zero and cannot hold "+91" or a space; text preserves the exact 10-digit string.
- Admission Date: Date/Time - needed for sorting, ageing and month-wise reports. Text dates sort alphabetically (01/10 before 02/01) and cannot be used in date arithmetic.
- Fee Due: Currency or Decimal Number - arithmetic and totals; set two decimal places and a validation rule
FeeDue >= 0. - Remarks: Short Text or Long Text/Memo - free writing, no calculation.
Handling the mobile formats: clean once, then enforce. Decide the standard (10 digits, no +91, no spaces or hyphens), strip spaces, hyphens and any leading +91 or 0 using an update query or expression, then add an input mask or validation rule such as Like "##########" plus a uniqueness check, so new entries cannot reintroduce the variation. Tell the three staff the rule and update the entry form so the field is formatted for them - variation is an entry problem, not a search problem.
3. Write the criteria a staff member would use for a "B3 students with Fee Due greater than 0" report, and explain two reasons why the database is safer than the shared spreadsheet for this centre.
Read the worked answer
Criteria: BatchID = "B3" AND FeeDue > 0. As a query:
SELECT StudentName, Mobile, FeeDue FROM Students INNER JOIN Fees ON Students.StudentID = Fees.StudentID WHERE BatchID = 'B3' AND FeeDue > 0 ORDER BY StudentName;
For non-technical staff, build a form with two filter boxes and save it as a named report so everyone runs the same definition and no one hand-edits the output.
Reason 1 - concurrency: a spreadsheet locks the whole file; two people saving at once overwrite each other, which is exactly how a day's entries were lost. A database writes one record at a time, so two staff entering different students is normal and safe.
Reason 2 - controlled entry: data types, validation rules and lookup lists stop "98123-45678" or a batch code typed as "b3" from ever being stored, required fields stop a fee row being saved without a student, and referential integrity stops a fee entry pointing at a deleted student.
Two further benefits worth mentioning: the report always re-reads current data, so a batch change appears immediately, and access can be limited (one staff member read-only) with a scheduled backup - neither is possible in the shared file.