Stationery shop moving from spreadsheets to a database
A stationery shop in Windhoek has three tills and 14 staff. It records sales in a different spreadsheet for each till and emails the three files to the owner each evening. The owner then combines them by hand. There are about 1,400 regular customers and 600 products. The customer list has grown to 1,900 rows because names and phone numbers were typed differently at each till; some customers receive three copies of the same promotion. Stock records often disagree with the shelves: last month the system showed 240 blue pens but only 168 were found. Two staff sometimes edit the same file, and there is no history of who changed a price. The owner wants a relational database but has no IT staff and only one old laptop for administration.
1. Identify three data integrity problems in the current spreadsheet system and explain how each could affect the shop's decisions or customer trust.
Read the worked answer
Three problems:
- Duplicate customers: the same person is keyed as 'Maria N.', 'M. Nangolo' and 'Maria Nangolo' with different phone formats. The shop cannot count customers accurately or send one promotion per household; it wastes money and annoys customers.
- Inconsistent stock: each till's spreadsheet is a separate copy, so sales from till 2 are invisible to till 1 until the owner merges files. The 240-versus-168 pen discrepancy is likely unrecorded sales, returns or keying errors; the shop may over-order or promise stock it does not have.
- No audit trail or concurrency control: two staff can edit the same file and the last save wins. There is no record of who changed a price, so the owner cannot investigate a suspicious discount or a pricing error.
2. Design a small relational schema (table names, primary keys, foreign keys, and at least two other fields per table) to replace the spreadsheets. Explain how it prevents the duplicate-customer and stock problems.
Read the worked answer
Schema: Customer(CustomerID PK, FullName, Phone, Email) Product(ProductID PK, Description, UnitPrice, StockQty, ReorderLevel) Sale(SaleID PK, SaleDate, TillID, CustomerID FK) SaleItem(SaleID PK/FK, ProductID PK/FK, Quantity, UnitPrice) Primary keys are underlined in a diagram; SaleItem uses a composite key of SaleID + ProductID. CustomerID in Sale is a foreign key; SaleID and ProductID in SaleItem are foreign keys. A separate Till table is optional. This prevents duplicates because each customer has one CustomerID and phone or email can be set to unique where appropriate; a sale refers to that ID rather than retyping a name. Stock is a single value in Product, updated inside a database transaction when a SaleItem is inserted, so all tills see the same figure. The composite key prevents the same product being added twice to one sale without a quantity. Price history can be kept by copying UnitPrice into SaleItem at the time of sale, so later price changes do not rewrite old receipts.
3. The owner has one old laptop and no IT staff. Recommend a practical migration and backup plan, including how to avoid losing sales data during the changeover.
Read the worked answer
Practical migration: first clean the data in a copy - standardise phone numbers, merge duplicates, agree product codes and opening stock. Build the database on the old laptop or a low-cost cloud database, with validation: phone format, positive quantity, stock not negative, and a lookup list for products. Run the new system in parallel with the spreadsheets for two weeks, entering sales in both and comparing daily totals. Train staff on one till at a time to limit disruption. Before switchover, take a full backup and record opening balances. After switchover, restrict price and stock adjustments to the owner's login and give staff sales-only rights. Back up automatically each evening to an external drive and a cloud folder, keep weekly copies off-site, and test a restore before deleting the old spreadsheets. Keep the old files read-only for at least one year.