← QCE Digital Solutions Unit 2

Evaluating a database against the normal forms

Test a table against 1NF, then 2NF, then 3NF, naming each dependency problem found (repeating group, partial or transitive) and backing it up with an example row.

Part of: QCE Digital Solutions Unit 2 — Prototype Data Solutions — Module 24

Understanding Evaluating a database against the normal forms in QCE Digital Solutions Unit 2

What it does: HouseCaptain depends on House, and House depends on StudentID. A non-key field determined by another non-key field is a transitive dependency, which fails 3NF. Quoting a real row shows the repeated data caused by the dependency problem. This backs up the normal form verdict with evidence. 1NF needs one value per field in every row. Two phone numbers in one cell is a repeating group, so check 1NF first. Each normal form builds on the one before it. A table must pass 1NF before 2NF can be checked, and 2NF before 3NF. Partial dependency means depending on only part of a composite key. With a one-field key, there is no 'part' to depend on.

Common mistakes: StuName depends on only part of the composite key (StuID). That is what makes it a partial dependency. The line should read 'StuName needs StuID only'. The normal forms are checked in order: 1NF, then 2NF, then 3NF. Step 2 should be 'check 2NF'.

Class: _____________________  ·  Name: _____________________  ·  Date: _________

Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.

🖨 Print worksheet + answer key Add Evaluating a database against the normal forms to a class →

This module's key terms are also in a printable crossword (Modules 305–336) and word search (Modules 234–409).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning
Duo
A table has a Phones field holding '0412 111, 0433 222' in one row. Which normal form does it break?
1NF: the field holds a repeating group, not one atomic value3NF: the field depends on a non-key field
Matches
Check 3NF first, then 2NF, then 1NF
↓
The normal forms must be checked in order: 1NF, then 2NF, then 3NF, because each form builds on the one before.
Applying
Meanings
Term
normal form check
Meaning
Test a table against 1NF, then 2NF, then 3NF, naming each dependency problem found (repeating group, partial or transitive) and backing it up with an example row.
Practice
Table: OrderLine(orderId, productId, productName, qty)
Testing
Gaps
-- Row 1: Phone = 0412, 0498 breaks
Check Answer
Spots
2Key: (StuID, SubjID)
3StuName needs both key fields

Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.

The 'Pufferfish' badge — Error Handler, one of Cloodit's 106 collectible badges
Students earn collectable badges

Over 100 of them, across seven natural biomes.

Study Buddy
Students build a real study resource

Turns their work into revision material ready for Flash Cards and Exam study.

Diamond Dash — one of Cloodit's live, competitive Sprint games
Students lock in with Sprints

Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.

A Cloodit certificate of achievement, ready to personalise and print
Custom printable certificates

Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.

Add to class →

Quick Questions

1. A table has a Phones field holding '0412 111, 0433 222' in one row. Which normal form does it break?
A. 3NF: the field depends on a non-key field
B. 1NF: the field holds a repeating group, not one atomic value ✓
1NF needs one value per field in every row. Two phone numbers in one cell is a repeating group, so check 1NF first.
2. Which order should you check a table against the normal forms?
A. 1NF, then 2NF, then 3NF ✓
B. 3NF, then 2NF, then 1NF
Each normal form builds on the one before it. A table must pass 1NF before 2NF can be checked, and 2NF before 3NF.
3. A table has the composite key (OrderID, ProductID). ProductName depends only on ProductID. What is the problem?
A. A transitive dependency, so the table breaks 3NF
B. A partial dependency, so the table breaks 2NF ✓
ProductName depends on only part of the composite key. That is a partial dependency, which is a 2NF problem.
4. In a Students table with key StudentID, the row (7, Ava, Red, Ms Lee) has HouseHead depending on House, not on StudentID. What is this called?
A. A transitive dependency, which breaks 3NF ✓
B. A partial dependency, which breaks 2NF
StudentID gives House, and House gives HouseHead. A non-key field depending on another non-key field is a transitive dependency, a 3NF problem.
5. A table has a single-field primary key and passes 1NF. Can it have a partial dependency?
A. No, a partial dependency needs a composite key, so it passes 2NF ✓
B. Yes, any non-key field can be partially dependent
Partial dependency means depending on only part of a composite key. With a one-field key, there is no 'part' to depend on.

Match the Pairs

Student_ID, Subject_1, Subject_2, Subject_3 → Fails 1NF: repeating group of columns
Primary key (OrderID, ProductID) with ProductName in the table → Fails 2NF: partial dependency on part of the key
Transitive dependency: a non-key field depends on another non-key field, so the table fails 3NF → StudentID -> House -> HouseCaptain
Justify a failed check with an example row from the table → Row (1, Ava, Red, Ms Lee) repeats Red and Ms Lee for every Red student
Check 3NF first, then 2NF, then 1NF → The normal forms must be checked in order: 1NF, then 2NF, then 3NF, because each form builds on the one before.
Table fails 3NF because it has a repeating group → A repeating group is a 1NF problem, not a 3NF problem. 3NF problems are transitive dependencies.

Key Term

normal form check
Test a table against 1NF, then 2NF, then 3NF, naming each dependency problem found (repeating group, partial or transitive) and backing it up with an example row.

Fill in the Blanks

1. -- Row 1: Phone = 0412, 0498 breaks 1NF
Hint: Type the abbreviation of the first normal form, which bans several values in one field.
2. -- ProductName depends on ProductId only -- This is a partial dependency (2NF)
Hint: Type the word for a field that depends on only part of a composite key.
3. -- Suburb depends on Postcode, not the key -- This is a transitive dependency (3NF)
Hint: Type the word for a non-key field that depends on another non-key field.
4. CREATE TABLE Product ( ProductId INT PRIMARY KEY, Name VARCHAR(30));
Hint: Type the constraint keywords that make ProductId the identifier in the new 2NF table.
5. SELECT Postcode, Suburb, COUNT(*) FROM Customer GROUP BY Postcode, Suburb;
Hint: Type the other non-aggregated column from the SELECT line that must also be grouped.

Spot the Error

Question 1
A Enrol(StuID, SubjID, StuName)
B Key: (StuID, SubjID)
C StuName needs both key fields
D Fails 2NF: partial dependency
Line C — StuName depends on only part of the composite key (StuID). That is what makes it a partial dependency. The line should read 'StuName needs StuID only'.
Question 2
A Step 1: check 1NF
B Step 2: check 3NF
C Step 3: check 3NF
Line B — The normal forms are checked in order: 1NF, then 2NF, then 3NF. Step 2 should be 'check 2NF'.
Question 3
A Row 3: Ava | Art
B Cell holds two values
C Repeating group: fails 1NF
Line A — The example row must show two values in one cell to justify a 1NF failure. 'Art' alone is a single value. It should be 'Row 3: Ava | Art, Maths'.
Question 4
A Staff(ID, DeptID, DeptName)
B Key: ID
C DeptID -> DeptName
D Transitive: fails 2NF
Line D — A transitive dependency (a non-key field depending on another non-key field) breaks 3NF, not 2NF. It should be 'Transitive: fails 3NF'.
Question 5
A 2NF: key is (OrdID)
B CustName depends on OrdID
C Partial dependency found
Line A — A partial dependency can only exist when the primary key is composite. The key must be (OrdID, Item) for CustName depending on OrdID alone to be partial.

Student with a class code? Join your class →