3,200 club members in a single spreadsheet
The membership secretary of a 3,200-member club keeps everything in one Excel sheet with the columns MemberName, Phone, Email, Plan, StartDate, PaymentDate and Amount. Families share one phone number, so 412 rows share a phone with at least one other row. Renewals are added as new rows rather than as updates, so some members appear five times. When one member changed her number, the secretary edited 12 rows by hand and missed 3, which are now stale. Two members have the same name. No email address is stored for 47 people, which blocks the digital newsletter. The committee wants a reliable renewals list and a clean mailing list before the next season.
1. Identify the entities in this data and explain, with reference to the numbers given, why Phone cannot serve as the primary key and what you would use instead.
Read the worked answer
The data mixes at least three things: the member (identity and contact details), the subscription (plan, start and end period) and the payment (date, amount, method). Shared contact details belong to a household, which is a fourth, optional entity.
Phone fails as a key for two independent reasons. It is not unique: 412 rows share a number with another row, so it cannot identify one member, and joining on it would merge family members into one record. It is also not stable: it changed for one member, forcing 12 row edits, of which 3 were missed and now contradict the rest. A key must be unique and unchanging.
Name also fails, since two members share a name, and Email fails because 47 are blank and blanks are not permitted in a primary key. Use a surrogate MemberID: a sequential or auto-numbered value that never changes, with Phone and Email stored as ordinary attributes, and a HouseholdID to group family members who share a number. Add a unique constraint on MemberID plus period start so renewals cannot duplicate.
2. Give one update anomaly and one deletion anomaly that already exist in this sheet, using the details in the scenario.
Read the worked answer
Update anomaly. One member's phone change required 12 edits, and 3 rows still hold the old number. The same fact is stored in many places, so any change is only partly applied and the database now contradicts itself. Downstream, a mail merge or a call list will use a number that no longer exists, and nobody can tell which of the two versions is current without asking the member.
Deletion anomaly. If the secretary deletes what looks like a duplicate renewal row for a member whose only payment was that row, the member's plan, start date and contact details disappear with it. Deleting the last transaction removes the person, because member facts are stored on the transaction row rather than in their own record.
There is also an insertion problem: adding a new member means retyping name and phone on every renewal, which produces variants (initials, spacing, mobile formats) that no lookup will match, and the 47 blank emails are a data-quality gap that should be captured deliberately as a preference, not left as a null.
3. Describe a staged migration plan for a secretary with no database experience, including how you would know the new system is right before retiring the spreadsheet.
Read the worked answer
Stage 1, freeze and clean. Back up the file and keep it read-only. Add a MemberID column and fill sequential numbers. Use Remove Duplicates on name plus period start to collapse repeated renewals into one row per member per period, and record how many rows were removed.
Stage 2, split. Create three tables: Members (MemberID, Name, HouseholdID, Email, ContactPreference), Subscriptions (MemberID, Plan, StartDate, EndDate), Payments (PaymentID, MemberID, Amount, Date, Method). Keep the original sheet as a reference tab.
Stage 3, move. Load the tables into Access, LibreOffice Base or a hosted tool, set the keys and one-to-many relationships, and build a simple entry form so the secretary never types into a raw table.
Stage 4, prove it. Run old and new in parallel for one renewal cycle. Reconcile row counts, the sum of Amount and the count of active members against the spreadsheet; spot-check ten members end to end; confirm a restore from backup works.
Only when those checks agree do you retire the spreadsheet, and you keep one printed rota of who enters what, so the tidy structure survives contact with December.