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.

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

Practise this lesson

The supplied data

Imagine the exam gives you this extract of orders. Notice the repeated customer details and several items crammed into one cell.

OrderNoOrderDateCustNoCustNameCustPhoneItems (code, description, price, qty)
50012026-02-03C7Lena Park01632 960111P10 Chair 45.00 x2; P22 Desk 120.00 x1
50022026-02-04C9Omar Said01632 960222P10 Chair 45.00 x4
50032026-02-09C7Lena Park01632 960111P31 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:

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

Result (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.50

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

  1. Repeating facts: class and instructor details repeat for every booking; member names repeat for every class.
  2. Key of the original rows: (ClassID, MemberID, BookingDate) if a member can book a class more than once.
  3. Partial dependencies: ClassName and InstructorID depend on ClassID only; MemberName on MemberID only. Transitive: InstructorName depends on InstructorID.
  4. 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.

Key idea: For a supplied file: apply 1NF, 2NF and 3NF (from Unit 3) to find the entities, give each a table and key, add link tables for M:M, set relationships with referential integrity, then import through a holding table and append queries, and check the record counts. Never import an unnormalised extract as it is and link everything to it.

Check yourself

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

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

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

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

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

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

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

Practise with instant feedback

Sources

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