All subjects · Information Technology · WIT14 3 hour practical exam on database solutions
Building a relational structure from a given data set (to 3NF)
Edexcel IAL Information Technology · WIT14 3 hour practical exam on database solutions
Exam question 1 typically supplies a data file (a text file or spreadsheet extract) and asks you to extend the database to store it with minimal duplication, correct keys and referential integrity (specification 19.1.2). Normalisation, covered in Unit 3, is the method; this lesson applies it step by step to a supplied data set.
The supplied data
Imagine the exam gives you this extract of orders. Notice the repeated customer details and several items crammed into one cell.
| OrderNo | OrderDate | CustNo | CustName | CustPhone | Items (code, description, price, qty) |
|---|---|---|---|---|---|
| 5001 | 2026-02-03 | C7 | Lena Park | 01632 960111 | P10 Chair 45.00 x2; P22 Desk 120.00 x1 |
| 5002 | 2026-02-04 | C9 | Omar Said | 01632 960222 | P10 Chair 45.00 x4 |
| 5003 | 2026-02-09 | C7 | Lena Park | 01632 960111 | P31 Lamp 18.50 x3 |
Unnormalised order data
Applying 1NF, 2NF and 3NF
Unit 3 teaches the normal forms in full; in Unit 4 you apply them quickly to the file you are given:
- 1NF: one value per field, so split the items cell into one row per product per order, with key (OrderNo, ProductCode).
- 2NF: OrderDate and CustNo depend only on OrderNo, and Description and Price only on ProductCode, so they move to tblOrder and tblProduct. tblOrderLine keeps OrderNo, ProductCode and Qty.
- 3NF: CustName and CustPhone depend on CustNo, not on OrderNo, so they move to tblCust.
A quick check on the result: one table for each entity (customer, order, product), plus a link table for the many-to-many between orders and products.
The result, built and checked
Four tables in 3NF, with keys and referential integrity:
CREATE TABLE tblCust (CustNo TEXT PRIMARY KEY, CustName TEXT, CustPhone TEXT);
CREATE TABLE tblOrder (OrderNo INTEGER PRIMARY KEY, OrderDate TEXT, CustNo TEXT REFERENCES tblCust (CustNo));
CREATE TABLE tblProduct (ProductCode TEXT PRIMARY KEY, Description TEXT, Price REAL);
CREATE TABLE tblOrderLine (OrderNo INTEGER REFERENCES tblOrder (OrderNo), ProductCode TEXT REFERENCES tblProduct (ProductCode),
Qty INTEGER, PRIMARY KEY (OrderNo, ProductCode));
INSERT INTO tblCust VALUES ('C7', 'Lena Park', '01632 960111'), ('C9', 'Omar Said', '01632 960222');
INSERT INTO tblOrder VALUES (5001, '2026-02-03', 'C7'), (5002, '2026-02-04', 'C9'), (5003, '2026-02-09', 'C7');
INSERT INTO tblProduct VALUES ('P10', 'Chair', 45.00), ('P22', 'Desk', 120.00), ('P31', 'Lamp', 18.50);
INSERT INTO tblOrderLine VALUES (5001, 'P10', 2), (5001, 'P22', 1), (5002, 'P10', 4), (5003, 'P31', 3);Nothing is lost: a join rebuilds every original item line, with a calculated line total:
SELECT o.OrderNo, c.CustName, p.Description, l.Qty, p.Price * l.Qty AS LineTotal
FROM tblOrderLine l
JOIN tblOrder o ON o.OrderNo = l.OrderNo
JOIN tblCust c ON c.CustNo = o.CustNo
JOIN tblProduct p ON p.ProductCode = l.ProductCode
ORDER BY o.OrderNo, p.DescriptionResult (4 rows):
OrderNo | CustName | Description | Qty | LineTotal
--------+-----------+-------------+-----+----------
5001 | Lena Park | Chair | 2 | 90.00
5001 | Lena Park | Desk | 1 | 120.00
5002 | Omar Said | Chair | 4 | 180.00
5003 | Lena Park | Lamp | 3 | 55.50In Access you would create the tables, import the text file into a holding table, and use append queries (or careful imports) to fill each table, then set the relationships. Examiner reports note two slips: leaving out the link table (in June 2024 fewer candidates recognised the need for a sale details table, the equivalent of tblOrderLine) and importing the extract unchanged and linking the other tables to it (June 2025).
Worked example
A gym's data file has: ClassID, ClassName, InstructorID, InstructorName, MemberID, MemberName, BookingDate, one row per member per class. Normalise it to 3NF.
- Repeating facts: class and instructor details repeat for every booking; member names repeat for every class.
- Key of the original rows: (ClassID, MemberID, BookingDate) if a member can book a class more than once.
- Partial dependencies: ClassName and InstructorID depend on ClassID only; MemberName on MemberID only. Transitive: InstructorName depends on InstructorID.
- 3NF tables: tblInstructor (InstructorID, InstructorName); tblClass (ClassID, ClassName, InstructorID); tblMember (MemberID, MemberName); tblBooking (ClassID, MemberID, BookingDate) with a composite key.
Answer: Four tables: instructor, class, member and a booking link table.
Check yourself
- A field holds 'P10, P22' for an order with two products. Which normal form does this break?
- Second normal form
- First normal form, because values must be atomic
- Third normal form
- None, as text fields can hold lists
Show answer
First normal form, because values must be atomic. Several values in one field is a repeating group, which 1NF forbids.
- A candidate imports a supplied class extract unchanged as one table, keeps all its fields and links the other tables to it. Why did the June 2025 examiner report call this inappropriate?
- Imported tables cannot take part in relationships
- Access cannot import spreadsheet files
- Linking tables makes queries return no results
- The imported table still holds data that belongs in separate tables, so the duplication is copied into the database
Show answer
The imported table still holds data that belongs in separate tables, so the duplication is copied into the database. A supplied extract is usually unnormalised: split it into the proper tables (often through a holding table and append queries).
- After importing the order extract into a holding table, which fields should an append query copy into tblOrderLine?
- OrderNo, OrderDate and CustNo
- OrderNo, ProductCode and Qty
- ProductCode, Description and Price
- CustNo, CustName and CustPhone
Show answer
OrderNo, ProductCode and Qty. tblOrderLine holds one row per product on an order: the composite key and the quantity, which depends on both.
- After running the normalisation script, how many tables does this count?
```sql
SELECT COUNT(*) FROM sqlite_master WHERE type = 'table' AND name IN ('tblCust', 'tblOrder', 'tblProduct', 'tblOrderLine')
```
Show answer
4. The normalised design has 4 tables.
- Using the normalised tables, what is the total value of order 5001?
```sql
SELECT SUM(p.Price * l.Qty) FROM tblOrderLine l JOIN tblProduct p ON p.ProductCode = l.ProductCode WHERE l.OrderNo = 5001
```
Show answer
210.00. Order 5001: 2 chairs and 1 desk, total 210.00.
- In the normalised design, how many rows does tblCust have?
Show answer
2. Lena Park appears on two orders but is stored once, so there are 2 customer rows.
- When extending a database to store sales where each sale can include several items, examiners say many candidates omit a table. Which table, and why is it needed?
Show answer
A sale details (sale item) table, because each sale has many items and each item can appear on many sales, so a link table is needed to resolve the many-to-many relationship.. Without a sale details/link table, several items per sale cannot be stored without repeating groups or duplication.
Sources
- Pearson Edexcel International Advanced Level in Information Technology specification (Issue 2, February 2021): Unit 4, Topics 17 to 19
- Pearson: WIT14/01 examiner report, June 2024
- Pearson: WIT14/01 examiner report, June 2025
- Pearson: WIT14/01 mark scheme, June 2024
Last updated 8 November 2026. Spotted a mistake? Tell us.