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.

Practise this in the app: instant marking and a fix for every mistake. Free, no email needed to start.

Practise this lesson

One flat table

Here is the Harbour Bike Hire data squeezed into a single table, one row for each bike on each hire:

HireIDHireDateCustomerNameCustomerTownBikeIDModelDailyRate
1012026-03-02Amira HassanPortviewB01Trail 50018.00
1012026-03-02Amira HassanPortviewB02City Glide12.50
1022026-03-05Chen LiPortviewB04Volt E235.00
1032026-03-14Ben OkaforMillbrookB03Speedster20.00
1042026-03-20Amira HassanPortviewB04Volt E235.00
1052026-04-02Dana KowalskiEastfieldB05Ridge Pro22.00
1052026-04-02Dana KowalskiEastfieldB06Commuter10.00
1062026-04-11Ellis BrownMillbrookB01Trail 50018.00
1072026-04-18Chen LiPortviewB02City Glide12.50
1082026-05-01Amira HassanPortviewB03Speedster20.00
1082026-05-01Amira HassanPortviewB04Volt E235.00

One flat table holding the same hire data

What goes wrong

The relational solution

Split the data into tables, one per entity, and link them with keys:

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?

  1. TutorName and TutorRoom are repeated on every row of a student in 12B.
  2. Changing the room means updating every one of those rows; missing one gives inconsistent data (an update anomaly).
  3. 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.

Key idea: Flat files repeat data (redundancy) and suffer update, insertion and deletion anomalies, which lead to inconsistent data. Relational structures store each fact once in its own table, linked by keys, and joins rebuild the combined view.

Check yourself

  1. 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.

  2. 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.

  3. 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.

  4. 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.

  5. 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.

  6. 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.

Practise with instant feedback

Sources

Last updated 8 November 2026. Spotted a mistake? Tell us.