AS & A Level Computer Science 9618: Normalising a Table to 1NF, 2NF and 3NF

GreenTrail Outdoors records its customer orders in a single un-normalised table, ORDER_FORM. Each order can list more than one product, and all of that order's products are currently held together in the same record, as shown below.

ORDER_FORM (un-normalised)

OrderID	OrderDate	CustomerID	CustomerName	ProductID	ProductName	UnitPrice	Quantity
5001	2026-09-01	C12	J Alvarez	P01, P04	Trail Mix, Granola Bar	3.50, 2.20	2, 5
5002	2026-09-03	C07	R Novak	P04	Granola Bar	2.20	3

(a) Explain why ORDER_FORM is not in First Normal Form (1NF). Then show how the data for OrderID 5001 would be split into two separate rows once the table is in 1NF, and state the primary key needed for this resulting table. [3]

(b) The 1NF table from (a) contains a partial dependency, so it is not in Second Normal Form (2NF). Identify this partial dependency and describe how the table would be restructured (giving the resulting tables and their primary keys) to remove it and reach 2NF. [3]

(c) After the restructuring in (b), one of the resulting tables still contains a transitive dependency, so it is not yet in Third Normal Form (3NF). Identify this transitive dependency and describe the further restructuring needed to reach 3NF, giving the final set of tables and their keys. [3]

Part (a): Why ORDER_FORM is not in 1NF, and converting to 1NF

First Normal Form (1NF) requires every field in a record to hold a single, atomic value, with no repeating groups.

In ORDER_FORM, the record for OrderID 5001 breaks this rule: the ProductID, ProductName, UnitPrice and Quantity fields each hold two values in the one record, because order 5001 contains two products. This is a repeating group, so ORDER_FORM is not in 1NF.

To reach 1NF, order 5001 is split into one row per product:

OrderID	OrderDate	CustomerID	CustomerName	ProductID	ProductName	UnitPrice	Quantity
5001	2026-09-01	C12	J Alvarez	P01	Trail Mix	3.50	2
5001	2026-09-01	C12	J Alvarez	P04	Granola Bar	2.20	5
5002	2026-09-03	C07	R Novak	P04	Granola Bar	2.20	3

Now every field holds one value per row, so the table is in 1NF. However, neither OrderID alone (it repeats for order 5001) nor ProductID alone (it repeats for P04 across two different orders) is unique across all rows. The primary key of this 1NF table must therefore be the composite key (OrderID, ProductID).

[3 marks]: [1] for identifying the repeating group in the ProductID/ProductName/UnitPrice/Quantity fields, [1] for correctly splitting OrderID 5001 into two rows, [1] for the correct composite primary key (OrderID, ProductID).

Part (b): From 1NF to 2NF. Removing partial dependencies

Second Normal Form (2NF) requires every non-key field to depend on the whole of the primary key, not just part of it.

In the 1NF table, the primary key is (OrderID, ProductID), but:

OrderDate, CustomerID and CustomerName depend only on OrderID, they do not depend on ProductID at all. This is a partial dependency.
ProductName and UnitPrice depend only on ProductID, they do not depend on OrderID at all. This is also a partial dependency.

Only Quantity genuinely depends on the combination of OrderID and ProductID (how many of that particular product were ordered on that particular order).

To remove these partial dependencies, the fields are regrouped by what they actually depend on:

ORDERS(OrderID [PK], OrderDate, CustomerID, CustomerName)
PRODUCTS(ProductID [PK], ProductName, UnitPrice)
ORDER_LINES(OrderID [FK → ORDERS], ProductID [FK → PRODUCTS], Quantity), primary key (OrderID, ProductID)

Every non-key field in each of these three tables now depends on the whole of its own table’s primary key, so the design is in 2NF.

[3 marks]: [1] for correctly identifying the OrderDate/CustomerID/CustomerName partial dependency on OrderID (or the ProductName/UnitPrice one on ProductID), [1] for the correct three resulting tables, [1] for correctly stating each table’s primary/foreign keys.

Part (c): From 2NF to 3NF. Removing transitive dependencies

Third Normal Form (3NF) requires that no non-key field depends on another non-key field. Every non-key field must depend directly on the primary key only.

Looking at ORDERS(OrderID [PK], OrderDate, CustomerID, CustomerName): CustomerName does not depend directly on OrderID. It depends on CustomerID (which customer placed the order), and CustomerID in turn depends on OrderID. So CustomerName is transitively dependent on the primary key OrderID, via the non-key field CustomerID. This means ORDERS is not yet in 3NF.

(PRODUCTS and ORDER_LINES from part (b) have no such issue, every non-key field in each already depends directly on that table’s own primary key, so only ORDERS needs further work.)

To remove this transitive dependency, CustomerName is moved out into its own table, keyed on CustomerID:

CUSTOMERS(CustomerID [PK], CustomerName)
ORDERS(OrderID [PK], OrderDate, CustomerID [FK → CUSTOMERS])

Combined with the unchanged PRODUCTS and ORDER_LINES tables from part (b), the final 3NF design is:

CUSTOMERS(CustomerID [PK], CustomerName)
PRODUCTS(ProductID [PK], ProductName, UnitPrice)
ORDERS(OrderID [PK], OrderDate, CustomerID [FK → CUSTOMERS])
ORDER_LINES(OrderID [FK → ORDERS], ProductID [FK → PRODUCTS], Quantity), primary key (OrderID, ProductID)

[3 marks]: [1] for identifying the CustomerID → CustomerName transitive dependency on OrderID, [1] for correctly splitting off a CUSTOMERS table, [1] for the complete, correct final 3NF schema (all four tables with correct keys).

Final answers

(a) Not in 1NF due to the repeating ProductID/ProductName/UnitPrice/Quantity group; 1NF primary key is the composite (OrderID, ProductID).
(b) Partial dependencies removed by splitting into ORDERS, PRODUCTS and ORDER_LINES (2NF).
(c) Transitive dependency (OrderID → CustomerID → CustomerName) removed by splitting off CUSTOMERS; final 3NF schema: CUSTOMERS, PRODUCTS, ORDERS, ORDER_LINES.