← QCE Digital Solutions Unit 2

Second normal form (2NF) (Prototype Data Solutions)

Second normal form: a table is in 1NF and no non-key field depends on only part of a composite primary key. Removing partial dependencies means each field relies on the whole key.

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

Understanding Second normal form (2NF) (Prototype Data Solutions) in QCE Digital Solutions Unit 2

What it does: productName depends on productId alone, so it moves to a table keyed by productId, removing the partial dependency. quantity depends on both orderId and productId together, so it relies on the whole key and is fine for 2NF. studentName relies on only part of the composite key (studentId), so it is a partial dependency. This breaks 2NF. 2NF builds on 1NF and removes partial dependencies, so each non-key field relies on the whole primary key. A single-field key has no 'part' to depend on. A 1NF table with a single-field primary key is automatically in 2NF. Split the table. For example, move subjectName into a subjects table keyed by subjectId, and keep the rest in the original table.

Common mistakes: 2NF is about composite keys. Qty depends on the whole key (order AND product), so the key must be PRIMARY KEY (ordId, prodId). With only ordId, two products in the same order would clash. The composite key (ordId, prodId) must be unique. Row (1, 2) already exists on line 2, so this duplicate pair is rejected. It should use a different pair such as (1, 3, 2).

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 Second normal form (2NF) (Prototype Data Solutions) 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 the composite primary key (studentId, subjectId). The field studentName depends only on studentId. What is this called?
A full dependency, so the table is in 2NFA partial dependency, so the table is not in 2NF
Matches
orderItems(orderId, productId, quantity, productName) with PRIMARY KEY (orderId, productId)
↓
productName depends only on productId, which is part of the key, so this table is not in 2NF
Applying
Meanings
Term
2NF
Meaning
Second normal form: a table is in 1NF and no non-key field depends on only part of a composite primary key. Removing partial dependencies means each field relies on the whole key.
Practice
CREATE TABLE studentMark (
Testing
Gaps
(orderId, productId));
Check Answer
Spots
3 prodId INT,
4 PRIMARY KEY (ordId),

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

The 'Cosmic Owl' badge — Comprehension Guru, 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 the composite primary key (studentId, subjectId). The field studentName depends only on studentId. What is this called?
A. A full dependency, so the table is in 2NF
B. A partial dependency, so the table is not in 2NF ✓
studentName relies on only part of the composite key (studentId), so it is a partial dependency. This breaks 2NF.
2. Which statement describes 2NF correctly?
A. The table is in 1NF and no non-key field depends on only part of a composite key ✓
B. The table has no primary key and every field depends on every other field
2NF builds on 1NF and removes partial dependencies, so each non-key field relies on the whole primary key.
3. A table uses a single-field primary key, orderId, and is already in 1NF. Can it have a partial dependency?
A. Yes, a field can depend on part of a single-field key
B. No, a partial dependency needs a composite key, so it is in 2NF ✓
A single-field key has no 'part' to depend on. A 1NF table with a single-field primary key is automatically in 2NF.
4. In the table results(studentId, subjectId, mark, subjectName) with key (studentId, subjectId), which field breaks 2NF?
A. subjectName, because it depends only on subjectId ✓
B. mark, because it depends on both studentId and subjectId
mark needs both studentId and subjectId, so it depends on the whole key. subjectName depends only on subjectId, which is a partial dependency.
5. How do you fix a partial dependency to reach 2NF?
A. Move the partly dependent field into its own table with the part of the key it depends on ✓
B. Add more duplicate rows so the field depends on the whole key
Split the table. For example, move subjectName into a subjects table keyed by subjectId, and keep the rest in the original table.

Match the Pairs

PRIMARY KEY (orderId, productId) → A composite primary key made of two fields, so 2NF must be checked for partial dependency
productName depends on productId only → A partial dependency, which breaks 2NF when the key is (orderId, productId)
To reach 2NF, move a field that depends on only part of the composite key into its own table → CREATE TABLE products (productId INT PRIMARY KEY, productName VARCHAR(50));
A field that depends on the whole composite key can stay in the original table → CREATE TABLE orderItems (orderId INT, productId INT, quantity INT, PRIMARY KEY (orderId, productId));
orderItems(orderId, productId, quantity, productName) with PRIMARY KEY (orderId, productId) → productName depends only on productId, which is part of the key, so this table is not in 2NF
A table with the single primary key studentId is checked for partial dependencies, and one is found → A single-field primary key cannot have a partial dependency, because there is no part of the key to depend on

Key Term

2NF
Second normal form: a table is in 1NF and no non-key field depends on only part of a composite primary key. Removing partial dependencies means each field relies on the whole key.

Fill in the Blanks

1. CREATE TABLE OrderItem ( orderId INT, productId INT, qty INT, PRIMARY KEY (orderId, productId));
Hint: Type the two-word keyword that declares the key made from more than one field (a composite key).
2. CREATE TABLE Enrolment ( studentId INT, subjectId INT, mark INT, PRIMARY KEY (studentId, subjectId));
Hint: Type the second field that joins studentId to form the composite key, so mark depends on the whole key.
3. SELECT DISTINCT productId, productName FROM OrderItem;
Hint: Type the keyword that removes repeated rows, used to pull product details out into their own table.
4. CREATE TABLE Product ( productId INT, productName VARCHAR(30), PRIMARY KEY (productId));
Hint: Type the keyword that makes productId the single key of the new table, so no part of a key exists.
5. -- subjectName depends on subjectId only -- This is a partial dependency -- Move it to the Subject table
Hint: Type the word describing a field that depends on only part of a composite key.

Spot the Error

Question 1
A CREATE TABLE OrderItem (
B ordId INT,
C prodId INT,
D PRIMARY KEY (ordId),
E qty INT
F );
Line D — 2NF is about composite keys. Qty depends on the whole key (order AND product), so the key must be PRIMARY KEY (ordId, prodId). With only ordId, two products in the same order would clash.
Question 2
A INSERT INTO OrderItem
B VALUES (1, 2, 5),
C (1, 2, 2),
D (2, 2, 1);
Line C — The composite key (ordId, prodId) must be unique. Row (1, 2) already exists on line 2, so this duplicate pair is rejected. It should use a different pair such as (1, 3, 2).
Question 3
A CREATE TABLE OrderItem (
B ordId INT NOT NULL
C prodId INT NOT NULL,
D qty INT,
E PRIMARY KEY (ordId, prodId)
F );
Line B — A comma is missing at the end of the field definition. Each field in CREATE TABLE is separated by a comma, so it should be ordId INT NOT NULL,
Question 4
A ALTER TABLE OrderItem
B DELETE COLUMN prodName;
C SELECT * FROM OrderItem;
Line B — SQL removes a field with DROP COLUMN, not DELETE COLUMN. Once prodName is moved to the Product table (it depends only on prodId), it is dropped from OrderItem to remove the partial dependency.
Question 5
A INSERT INTO Product
B VALUES (1, Pencil);
C SELECT * FROM Product;
Line B — Text values in SQL must be in quotes. Pencil should be 'Pencil'. Product name now lives in the Product table, so it is stored once per product.

Student with a class code? Join your class →