All subjects · Information Technology · WIT14 3 hour practical exam on database solutions
Why relational structures? Redundancy and anomalies
Edexcel IAL Information Technology · WIT14 3 hour practical exam on database solutions
Specification 18.2.1 asks you to understand the need for and function of relational data structures. Unit 3 covers redundancy and anomalies as theory; here you see them in the Harbour Bike Hire data, by putting everything in one flat table.
One flat table
Here is the Harbour Bike Hire data squeezed into a single table, one row for each bike on each hire:
| HireID | HireDate | CustomerName | CustomerTown | BikeID | Model | DailyRate |
|---|---|---|---|---|---|---|
| 101 | 2026-03-02 | Amira Hassan | Portview | B01 | Trail 500 | 18.00 |
| 101 | 2026-03-02 | Amira Hassan | Portview | B02 | City Glide | 12.50 |
| 102 | 2026-03-05 | Chen Li | Portview | B04 | Volt E2 | 35.00 |
| 103 | 2026-03-14 | Ben Okafor | Millbrook | B03 | Speedster | 20.00 |
| 104 | 2026-03-20 | Amira Hassan | Portview | B04 | Volt E2 | 35.00 |
| 105 | 2026-04-02 | Dana Kowalski | Eastfield | B05 | Ridge Pro | 22.00 |
| 105 | 2026-04-02 | Dana Kowalski | Eastfield | B06 | Commuter | 10.00 |
| 106 | 2026-04-11 | Ellis Brown | Millbrook | B01 | Trail 500 | 18.00 |
| 107 | 2026-04-18 | Chen Li | Portview | B02 | City Glide | 12.50 |
| 108 | 2026-05-01 | Amira Hassan | Portview | B03 | Speedster | 20.00 |
| 108 | 2026-05-01 | Amira Hassan | Portview | B04 | Volt E2 | 35.00 |
One flat table holding the same hire data
What goes wrong
- Data redundancy: Amira Hassan's name and town appear on 5 rows; the Volt E2's model and rate appear on 3 rows. Repeated data wastes space and typing time.
- Update anomaly: if the Volt E2's rate changes, all 3 rows must be changed. Miss one and the data becomes inconsistent: two different rates for the same bike.
- Insertion anomaly: a new bike that has never been hired cannot be recorded without inventing a hire.
- Deletion anomaly: deleting the only hire of a bike also deletes the only record of that bike's model and rate.
The relational solution
Split the data into tables, one per entity, and link them with keys:
- tblCustomer stores each customer once; tblBike stores each bike once.
- tblHire refers to the customer by CustomerID; tblHireItem refers to the hire and the bike by their IDs.
- A rate change is made in one row of tblBike, and every query that joins to it sees the new value.
- A bike can be added before it is ever hired, and deleting a hire leaves the bike's details intact.
The original flat view can always be rebuilt when needed with a query that joins the tables, so nothing is lost.
Worked example
A school stores one table: StudentID, StudentName, TutorGroup, TutorName, TutorRoom, for every student. The tutor of group 12B moves room. What anomaly can occur, and how does a relational structure prevent it?
- TutorName and TutorRoom are repeated on every row of a student in 12B.
- Changing the room means updating every one of those rows; missing one gives inconsistent data (an update anomaly).
- Relational design: tblTutorGroup (TutorGroup, TutorName, TutorRoom) and tblStudent (StudentID, StudentName, TutorGroup). The room is stored once, so one change updates it for everyone.
Answer: An update anomaly; fixed by storing tutor group details once in their own table.
Check yourself
- In the flat table of 11 rows, on how many rows is Amira Hassan's name stored?
Show answer
5. Amira made three hires, two of them with two bikes, giving 5 rows. In tblCustomer it is stored once.
- In a flat table, a product's price is changed on some rows but not others. What is this problem called?
- An insertion anomaly
- An update anomaly leading to inconsistent data
- A referential integrity rule
- A composite key
Show answer
An update anomaly leading to inconsistent data. Changing repeated data in some places but not all is an update anomaly.
- A garden centre's flat table records sales with product details. Why can a new product not be recorded before its first sale?
- It is a deletion anomaly
- Products need an input mask first
- The table has no Currency field
- It is an insertion anomaly: a product can only be stored as part of a sale row
Show answer
It is an insertion anomaly: a product can only be stored as part of a sale row. When facts about one entity can only exist inside rows about another, inserting them alone is impossible.
- Deleting the only order for a customer also removes the customer's address. What is this called?
- A deletion anomaly
- An update anomaly
- Data verification
- A one-to-one relationship
Show answer
A deletion anomaly. Losing unrelated facts when a row is deleted is a deletion anomaly.
- Explain one benefit of storing bikes in a separate tblBike table rather than repeating bike details on every hire.
Show answer
Each bike's details are stored once, so if the daily rate changes it is updated in one place and every hire query uses the new rate.. Storing each fact once avoids redundancy, so updates happen in one place and data stays consistent.
- After splitting a flat file into related tables, how can staff still see the combined view of each hire with customer and bike details?
- Run a query that joins the tables
- Merge the tables back into one
- Copy and paste each table into a spreadsheet
- It is no longer possible
Show answer
Run a query that joins the tables. A join query recombines the data on demand without storing it twice.
Sources
- Pearson Edexcel International Advanced Level in Information Technology specification (Issue 2, February 2021): Unit 4, Topics 17 to 19
- Pearson: IAL Information Technology scheme of work for Unit 4
Last updated 8 November 2026. Spotted a mistake? Tell us.