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.
Criteria for a good structure
| Criterion | Question to ask |
|---|---|
| Completeness | Can every piece of data the scenario needs be stored, and every required output be produced? |
| Minimal duplication | Is each fact stored once (3NF)? Are there repeated groups or copied tables? |
| Data types and sizes | Is each field the right type (Currency for money, Date/Time for dates, Text for phone numbers)? |
| Keys | Does every table have a suitable primary key? Are composite keys the smallest unique set? |
| Relationships | Are the relationships the right type, with foreign keys on the many side and link tables for M:M? |
| Integrity and validation | Is referential integrity enforced? Do validation rules protect important fields? |
| Naming and usability | Are names consistent and meaningful, so queries and forms are easy to build? |
| Future needs | Could 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:
- What already exists that the requirement can use (name the tables and fields).
- What is missing: entities with no table, fields with no home, values that are not stored.
- Which values should be calculated, not stored (a fee that is 5% of the selling price is a calculated field in a query, not a stored field).
- Recommend new tables with primary keys, where each foreign key goes and the relationship type.
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.
- Weak: 'The structure is good because it has keys.'
- Strong: 'tblHireItem uses the composite key (HireID, BikeID), so the same bike cannot be entered twice on one hire, and several bikes per hire are stored without repeating hire details.'
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.
- 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.
- Missing: no staff entity, so add tblStaff (StaffID primary key, FirstName, Surname).
- 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.
- 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.
Check yourself
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
Sources
- Pearson Edexcel International Advanced Level in Information Technology specification (Issue 2, February 2021): Unit 4, Topics 17 to 19
- Pearson: WIT14/01 mark scheme, June 2024
- Pearson: WIT14/01 examiner report, June 2024
Last updated 8 November 2026. Spotted a mistake? Tell us.