← QCE Digital Solutions Unit 2

Second normal form (2NF) (Data and Programming Techniques)

Second normal form: the table is in 1NF and every non-key field depends on the WHOLE primary key, so no field depends on only part of a composite key. Move partial fields to their own table.

Part of: QCE Digital Solutions Unit 2 — Data and Programming Techniques — Module 101

Understanding Second normal form (2NF) (Data and Programming Techniques) in QCE Digital Solutions Unit 2

What it does: subjectName depends only on subjectId, so it moves to a Subjects table where subjectId is the primary key. A mark is decided by both the student and the subject together, so it depends on the whole key and the table meets 2NF. 2NF builds on 1NF and removes partial dependencies, so every non-key field must depend on the WHOLE primary key. A composite key uses two or more fields together to identify a record. Student has a single-field key. productName is determined by productId alone, which is only part of the key. quantity needs both fields, so it is fine.

Common mistakes: A table must already be in 1NF before it can be in 2NF. The line should say 2NF: in 1NF, no partial deps. 3NF comes after 2NF. A composite key is made of two or more fields combined. A key with one field is a simple key, not a composite key.

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) (Data and Programming Techniques) to a class →

This module's key terms are also in a printable crossword (Modules 234–285) 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 is in 2NF when it is in 1NF and every non-key field depends on the whole primary key.

FalseTrue

Matches

Results(studentId, subjectId, mark, studentName)

↓

studentName depends only on studentId, which is part of the key. This partial dependency means the table is not in 2NF.

Applying

Meanings

Term

2NF

Meaning

Second normal form: the table is in 1NF and every non-key field depends on the WHOLE primary key, so no field depends on only part of a composite key. Move partial fields to their own table.

Practice

SELECT results.studentId, subjects.name, results.mark

Testing

Gaps

(orderId, productId)

Check Answer

Spots

12NF: in 3NF, no partial deps

2Key: (stuId, subId)

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

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 is in 2NF when it is in 1NF and every non-key field depends on the whole primary key.

A. False

B. True ✓

2NF builds on 1NF and removes partial dependencies, so every non-key field must depend on the WHOLE primary key.

2. Which table has a composite primary key?

A. OrderItem(orderId, productId, quantity) with key (orderId, productId) ✓

B. Student(studentId, firstName, house) with key studentId

A composite key uses two or more fields together to identify a record. Student has a single-field key.

3. OrderItem has the key (orderId, productId). Which field is a partial dependency?

A. productName, because it depends only on productId ✓

B. quantity, because it depends on both orderId and productId

productName is determined by productId alone, which is only part of the key. quantity needs both fields, so it is fine.

4. Can a table with a single-field primary key have a partial dependency?

A. Yes, any non-key field can depend on part of a single-field key

B. No, there is no part of the key to depend on ✓

Partial dependency only happens with composite keys. A table in 1NF with a single-field key is automatically in 2NF.

5. Results(studentId, subjectId, mark, teacher) has the key (studentId, subjectId). How do you fix the partial dependency of teacher on subjectId?

A. Move subjectId and teacher into a new Subject table ✓

B. Add more rows to Results so teacher repeats less

Move the partially dependent field to its own table with the part of the key it depends on (subjectId). Results keeps subjectId as a foreign key.

Match the Pairs

PRIMARY KEY (studentId, subjectId) → A composite key made of two fields. 2NF checks every other field against the whole key, not just one part.

subjectName depends on subjectId only → A partial dependency: the field relies on part of the composite key, so the table breaks 2NF.

To fix a partial dependency, move the partly dependent field into a new table keyed by that part of the key → Subjects(subjectId, subjectName)

A table in 2NF has every non-key field depending on the whole composite key, such as a mark for one student in one subject → Results(studentId, subjectId, mark)

Results(studentId, subjectId, mark, studentName) → studentName depends only on studentId, which is part of the key. This partial dependency means the table is not in 2NF.

Orders(orderId, productId, quantity, productName) PK: orderId, productId → productName depends only on productId, not on the whole key of orderId and productId, so the table breaks 2NF.

Key Term

2NF

Second normal form: the table is in 1NF and every non-key field depends on the WHOLE primary key, so no field depends on only part of a composite key. Move partial fields to their own table.

Fill in the Blanks

1. OrderItem(orderId, productId, qty, name) PRIMARY KEY (orderId, productId)

Hint: Type the two-word SQL keyword that declares the key, which here is made of two fields (a composite key).

2. Key: (orderId, productId) productId -> name

Hint: Type the one part of the composite key that on its own decides the name. This is a partial dependency.

3. OrderItem(orderId, productId, qty) Product(productId, name)

Hint: Type the partially dependent field that gets moved out into the new table.

4. FOREIGN KEY (productId) REFERENCES product(productId)

Hint: Type the keyword that links the foreign key to the primary key of the new table.

5. 2NF = 1NF + no partial dependency

Hint: Type the word for a field that depends on only part of a composite key.

Spot the Error

Question 1

A 2NF: in 3NF, no partial deps

B Key: (stuId, subId)

C mark needs studentId+subjectId

Line A — A table must already be in 1NF before it can be in 2NF. The line should say 2NF: in 1NF, no partial deps. 3NF comes after 2NF.

Question 2

A A composite key has 1 field

B PRIMARY KEY (orderId, itemId)

C Example: orderId + itemId

Line A — A composite key is made of two or more fields combined. A key with one field is a simple key, not a composite key.

Question 3

A CREATE TABLE Result (

B stuId INT, subId INT,

C PRIMARY KEY (stuId),

D mark INT

E );

Line C — A mark belongs to one student in one subject, so the key must be the composite (stuId, subId). Using only stuId would not identify each row uniquely.

Question 4

A Key: (stuId, subId)

B subName depends on stuId only

C That is a partial dependency

D Move subName to Subject table

Line B — The subject name describes the subject, so it depends on subId, which is only part of the composite key. It does not depend on stuId.

Question 5

A Result(stuId,subId,mark,sName)

B sName depends on subId only

C New: Subject(stuId, sName)

D Result drops sName

Line C — The partially dependent field moves to a new table whose primary key is the part of the key it depends on. That is subId, so the table is Subject(subId, sName).

Student with a class code? Join your class →