All subjects · Information Technology · WIT13 2 hour written exam on data, enabling technologies and systems development

Normalising data to 1NF, 2NF and 3NF

Edexcel IAL Information Technology · WIT13 2 hour written exam on data, enabling technologies and systems development

Exam questions give you a set of unnormalised data (often a form or flat table) and ask you to normalise it to third normal form, showing tables, primary keys and foreign keys (spec 12.2.3).

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

Practise this lesson

Unnormalised form (UNF) to 1NF

Start from a flat list of all attributes, including repeating groups (several values for one key, such as many products on one order).

First normal form (1NF):

Method: move the repeating group into its own table and give it a composite key of the original key plus the repeating group's key.

Example UNF: ORDER(OrderID, OrderDate, CustomerID, CustomerName, CustomerTown, {ProductID, Description, Price, Quantity}).

1NF: ORDER(OrderID, OrderDate, CustomerID, CustomerName, CustomerTown) and ORDER_ITEM(OrderID, ProductID, Description, Price, Quantity).

1NF to 2NF

Second normal form (2NF): in 1NF, and no partial dependencies: every non-key attribute depends on the whole of a composite primary key, not just part of it.

(A table with a single-attribute primary key that is in 1NF is automatically in 2NF.)

In ORDER_ITEM(OrderID, ProductID, Description, Price, Quantity): Description and Price depend only on ProductID; Quantity depends on both.

2NF: PRODUCT(ProductID, Description, Price) and ORDER_ITEM(OrderID, ProductID, Quantity).

2NF to 3NF

Third normal form (3NF): in 2NF, and no transitive (non-key) dependencies: no non-key attribute depends on another non-key attribute.

A handy check: every non-key attribute depends on 'the key, the whole key and nothing but the key'.

In ORDER(OrderID, OrderDate, CustomerID, CustomerName, CustomerTown): CustomerName and CustomerTown depend on CustomerID, which is not the key.

3NF: CUSTOMER(CustomerID, CustomerName, CustomerTown) and ORDER(OrderID, OrderDate, CustomerID*), where * marks the foreign key.

Final 3NF design

TablePrimary keyOther attributesForeign key(s)
CUSTOMERCustomerIDCustomerName, CustomerTownnone
ORDEROrderIDOrderDateCustomerID
PRODUCTProductIDDescription, Pricenone
ORDER_ITEMOrderID + ProductIDQuantityOrderID, ProductID

Four tables in 3NF. Exam tip: underline primary keys, mark foreign keys, and keep every original attribute somewhere.

Worked example

A gym records: MemberID, MemberName, {ClassID, ClassName, InstructorID, InstructorName, DateAttended}. Normalise to 3NF (assume each class has one instructor).

  1. 1NF: MEMBER(MemberID, MemberName); ATTENDANCE(MemberID, ClassID, DateAttended, ClassName, InstructorID, InstructorName). Key: MemberID + ClassID + DateAttended (a member attends the same class on many dates).
  2. 2NF: ClassName, InstructorID and InstructorName depend only on ClassID, so CLASS(ClassID, ClassName, InstructorID, InstructorName); ATTENDANCE(MemberID, ClassID, DateAttended).
  3. 3NF: InstructorName depends on InstructorID (non-key), so INSTRUCTOR(InstructorID, InstructorName); CLASS(ClassID, ClassName, InstructorID*).

Answer: MEMBER(MemberID, MemberName); ATTENDANCE(MemberID*, ClassID*, DateAttended); CLASS(ClassID, ClassName, InstructorID*); INSTRUCTOR(InstructorID, InstructorName)

Key idea: 1NF: atomic values, no repeating groups, a primary key. 2NF: no partial dependency on part of a composite key. 3NF: no non-key attribute depending on another non-key attribute. The key, the whole key and nothing but the key.

Check yourself

  1. Which feature shows a table is NOT in first normal form?
    • The table has a composite primary key
    • A cell holds several phone numbers separated by commas
    • A non-key attribute depends on another non-key attribute
    • The table has a foreign key
    Show answer

    A cell holds several phone numbers separated by commas. 1NF needs atomic values and no repeating groups.

  2. ENROL(StudentID, CourseID, StudentName, Grade) has key StudentID + CourseID. Why is it not in 2NF?
    • Grade depends on the whole key
    • It has a composite key
    • It contains a repeating group
    • StudentName depends only on StudentID, part of the key
    Show answer

    StudentName depends only on StudentID, part of the key. That is a partial dependency.

  3. BOOK(ISBN, Title, PublisherID, PublisherName) has key ISBN. Which change puts it into 3NF?
    • Make ISBN + Title the key
    • Move PublisherName to a PUBLISHER table keyed on PublisherID, keeping PublisherID in BOOK as a foreign key
    • Move Title into a separate table
    • Delete PublisherID
    Show answer

    Move PublisherName to a PUBLISHER table keyed on PublisherID, keeping PublisherID in BOOK as a foreign key. PublisherName depends on PublisherID, a non-key attribute: a transitive dependency.

  4. A table in 1NF has a single-attribute primary key. What can you say about 2NF?
    • It can never be in 2NF
    • It must be split into two tables
    • It is already in 2NF, because partial dependency needs a composite key
    • It is automatically in 3NF
    Show answer

    It is already in 2NF, because partial dependency needs a composite key. Partial dependency can only exist with a composite key.

  5. The gym example in the worked example ends with how many tables in 3NF?
    Show answer

    4. MEMBER, ATTENDANCE, CLASS and INSTRUCTOR: 4.

  6. In the 3NF design CUSTOMER(CustomerID, ...) and ORDER(OrderID, OrderDate, CustomerID), what is CustomerID in ORDER?
    • The primary key of ORDER
    • A repeating group
    • A foreign key linking each order to one customer
    • A partial dependency
    Show answer

    A foreign key linking each order to one customer. It is the primary key of CUSTOMER placed in ORDER.

  7. Which composite key suits ATTENDANCE(MemberID, ClassID, DateAttended) if a member can attend the same class each week?
    • MemberID + ClassID + DateAttended
    • MemberID alone
    • ClassID alone
    • MemberID + ClassID
    Show answer

    MemberID + ClassID + DateAttended. Without the date, repeat attendances would duplicate the key.

Practise with instant feedback

Sources

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