Flat-file stock spreadsheet at a family hardware shop
A family-run hardware shop in Hamrun records every sale in one spreadsheet sheet. Each row contains InvoiceNo, SaleDate, CustomerID, CustomerName, CustomerAddress, ProductCode, Description, UnitPrice, QuantitySold, SupplierName and SupplierPhone. The shop has about 4,000 rows from three years of trading. The owner has noticed three problems: when a customer moves house, staff must find and edit every past row for that customer; when a supplier changes its phone number, some rows still show the old number; and when the shop stops stocking a product, deleting its rows also destroys the only record of which supplier provided it. Staff also occasionally type the same customer with two different IDs. The owner wants to keep using the spreadsheet because 'it is simple and I can filter it'.
1. Identify the update, insert and delete anomalies present, using a specific example from the scenario for each.
Read the worked answer
Update anomaly. CustomerAddress is stored on every invoice row. If customer C017 moves, the address appears in perhaps 40 rows across three years. Updating some rows but not others leaves contradictory addresses, and the shop cannot tell which is current. The same applies to SupplierPhone and UnitPrice: a price change should affect future sales only, but the flat sheet stores price per row with no history table, so an edit can silently rewrite historical values.
Insert anomaly. A new supplier cannot be recorded until the shop buys something from it, because SupplierName and SupplierPhone exist only as attributes of a sale row. If the shop wants to keep a supplier's contact details before the first order, there is nowhere to put them without inventing a fake invoice.
Delete anomaly. Removing the last row for a discontinued product also removes the supplier details that were stored only on those rows. Losing a product should not lose the supplier relationship.
Duplication problem. Two IDs for one customer indicate that CustomerID is not being controlled. In a properly normalised design, CustomerID would be a primary key with a uniqueness constraint, and a lookup or validation rule would prevent re-entry under a slightly different spelling.
2. Propose a set of tables in third normal form. For each table, list its primary key, any foreign keys, and explain in one sentence what the table represents.
Read the worked answer
One workable 3NF design is five tables.
| Table | Primary key | Foreign keys | Represents |
|---|---|---|---|
| Customer | CustomerID | - | One row per customer, holding name and current address. |
| Supplier | SupplierID | - | One row per supplier, holding name and phone. |
| Product | ProductCode | SupplierID | One row per stocked product, with description, current unit price and its supplier. |
| Sale | InvoiceNo | CustomerID | One row per invoice, holding the date and the customer. |
| SaleLine | InvoiceNo + ProductCode (composite) | InvoiceNo → Sale; ProductCode → Product | One row per product line on an invoice, with quantity and the price charged at the time. |
Reasoning. Each non-key attribute depends on the key, the whole key, and nothing but the key. CustomerAddress depends on CustomerID, not on InvoiceNo, so it moves to Customer. SupplierPhone depends on SupplierID, not on ProductCode, so it moves to Supplier. UnitPrice is deliberately kept in two places for different purposes: Product.UnitPrice is the current list price, while SaleLine records the price actually charged, because prices change and historic invoices must not change with them. SaleLine has a composite key because one invoice can contain many products and one product can appear on many invoices - a many-to-many relationship resolved by the junction table.
3. The owner argues that filters and sorting make normalisation unnecessary. Evaluate this argument and recommend a practical migration approach.
Read the worked answer
Filters and sorting are presentation tools; they do not enforce data integrity. A filter can hide the duplicate customer rows, but it cannot stop a member of staff creating a third ID tomorrow. A spreadsheet cell accepts any text, so nothing prevents a phone number being typed into the QuantitySold column. The three anomalies the owner has already observed are exactly the failures that normalisation prevents.
The owner's concern is legitimate, though: a relational database has a learning curve, and staff may resist. A reasonable compromise is to migrate to a small relational database (for example LibreOffice Base or a hosted low-cost system) but give staff a data-entry form and a saved query that looks like the old sheet. Filters become parameter queries; the owner still gets a familiar grid view.
Migration steps:
- Clean the existing data first - standardise customer IDs, resolve duplicate customers, and check that every ProductCode maps to exactly one supplier.
- Create the five tables and import cleaned data in dependency order: Customer and Supplier, then Product, then Sale, then SaleLine.
- Add validation: required fields, a uniqueness constraint on CustomerID, and a lookup list for ProductCode.
- Run parallel for one month, comparing totals (row counts, total sales value per month) between the old sheet and the new database.
- Keep the old sheet read-only as an archive rather than deleting it.
Trade-off: the owner loses the freedom to type anything anywhere. That loss is the point.