All subjects · Information Technology · WIT14 3 hour practical exam on database solutions

Evaluating data and relational structures

Edexcel IAL Information Technology · WIT14 3 hour practical exam on database solutions

Specifications 18.1.4 and 18.2.3 ask you to evaluate how well a data structure and a relational structure meet requirements. In the exam this appears in typed responses: justifying your structure, or analysing what an existing structure cannot store and recommending changes.

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

Practise this lesson

Criteria for a good structure

CriterionQuestion to ask
CompletenessCan every piece of data the scenario needs be stored, and every required output be produced?
Minimal duplicationIs each fact stored once (3NF)? Are there repeated groups or copied tables?
Data types and sizesIs each field the right type (Currency for money, Date/Time for dates, Text for phone numbers)?
KeysDoes every table have a suitable primary key? Are composite keys the smallest unique set?
RelationshipsAre the relationships the right type, with foreign keys on the many side and link tables for M:M?
Integrity and validationIs referential integrity enforced? Do validation rules protect important fields?
Naming and usabilityAre names consistent and meaningful, so queries and forms are easy to build?
Future needsCould new requirements be added without redesigning everything?

Analysing what a structure cannot do

A common extended question gives a new requirement (for example, a form showing a salesperson's fee) and asks why the current database cannot meet it and what should change. Work through it methodically:

Pearson's mark scheme rewards analysis that is 'sound and developed' and a solution that is workable.

Writing the evaluation

Make each point specific and linked: criterion, evidence from the structure, effect, improvement.

Finish with a judgement on overall fitness for purpose.

Worked example

Harbour Bike Hire now wants to record which member of staff handled each hire and the commission they earn (5% of the hire value). Analyse the current structure and recommend changes.

  1. Exists: tblHire (HireID, CustomerID, HireDate, Days), tblHireItem and tblBike (DailyRate), so the hire value can already be calculated as DailyRate x Days for each bike.
  2. Missing: no staff entity, so add tblStaff (StaffID primary key, FirstName, Surname).
  3. Link: each hire is handled by one member of staff, so add StaffID as a foreign key in tblHire (one staff, many hires), with referential integrity.
  4. Commission should not be stored; calculate it in a query as 0.05 x the hire value, so it always matches the data.

Answer: Add tblStaff and a StaffID foreign key in tblHire; calculate commission in a query.

Key idea: Evaluate structures for completeness, minimal duplication, types, keys, relationships, integrity, naming and future needs. For a new requirement: say what exists, what is missing, what should be calculated, then recommend tables, keys and relationships.

Check yourself

  1. A new requirement asks for each order's VAT, always 20% of the order value. What should the structure do?
    • Add a VAT field to every table
    • Create a tblVAT table with one row per order
    • Store VAT as Short Text
    • Calculate VAT in a query when needed rather than storing it
    Show answer

    Calculate VAT in a query when needed rather than storing it. Values derived from stored data should be calculated, so they cannot become inconsistent.

  2. Which is the strongest evaluation point about a structure?
    • The structure is good because it has tables
    • tblBooking uses (MemberID, ClassID, BookingDate) as its key, so a member can book the same class on different dates but not twice on the same date
    • The tables look neat
    • It has a lot of fields
    Show answer

    tblBooking uses (MemberID, ClassID, BookingDate) as its key, so a member can book the same class on different dates but not twice on the same date. Specific: criterion, evidence and effect.

  3. A database has tblArt and tblSale. A new form needs each work's artist's name and phone. What is the best recommendation?
    • Add artist name and phone to tblSale
    • Type the artist on the form each time
    • Add ArtID to a new tblArtist
    • Add tblArtist (ArtistID, name, phone) and an ArtistID foreign key in tblArt
    Show answer

    Add tblArtist (ArtistID, name, phone) and an ArtistID foreign key in tblArt. One artist has many works: the foreign key goes in the many table, tblArt.

  4. A candidate creates a new table that copies customer names and emails from tblCustomer to store newsletter invitations. Why is this weak?
    • It duplicates data already stored, risking inconsistency; a query should select from tblCustomer instead
    • Customer emails cannot be stored in Access
    • New tables cannot have primary keys
    • Invitations must be stored in a report
    Show answer

    It duplicates data already stored, risking inconsistency; a query should select from tblCustomer instead. Pearson's 2024 examiner report noted candidates proposing new tables that duplicated data.

  5. A structure stores DateOfBirth as Short Text. Which consequence is most likely?
    • The primary key stops working
    • The table cannot be related to others
    • Queries cannot reliably find members born before a given date
    • Records cannot be deleted
    Show answer

    Queries cannot reliably find members born before a given date. Text dates do not compare or sort as dates.

  6. Explain why storing a customer's three most recent purchases as fields Purchase1, Purchase2, Purchase3 in tblCustomer is a poor structure.
    Show answer

    It is a repeating group, so a customer with a fourth purchase cannot be stored without changing the design; purchases should be in a separate table linked by CustomerID.. Numbered fields are a repeating group: limited, wasteful and hard to query. Use a separate Purchase table with a foreign key.

Practise with instant feedback

Sources

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