← WA Computer Science ATAR Unit 2

Second normal form (2NF)

A table in 1NF where no non-key field depends on only part of a composite key. Remove any partial dependency by splitting those fields into a new table.

Part of: WA Computer Science ATAR Year 11 — Data Management — Module 46

Understanding Second normal form (2NF) in WA Computer Science ATAR Unit 2

What it does: productName depends only on productId, so it goes in its own table whose primary key is productId. studentName can be found from studentId alone, so it depends on only part of the composite key. That is a partial dependency and breaks 2NF. A single-field key cannot be split into parts. A table in 1NF with a single-field primary key is therefore already in 2NF. Normal forms build on each other. A table must be in 1NF before it can be in 2NF.

Common mistakes: stuName depends only on stuId, which is part of the composite key. That is a partial dependency, so it breaks 2NF. Result should hold only mark, which depends on the whole key. The line should be ' mark INT,' and stuName should go in a Student table. A partial dependency needs a composite key. The key of Enrolment is the two fields together, so line 2 should be 'Key: (stuId, subId)'. With a single-field key, stuId -> name would not be partial.

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) to a class →

This module's key terms are also in a printable crossword (Modules 28–122) and word search (Modules 28–266).

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 key (studentId, subjectId) and the field studentName. Is studentName a partial dependency?
Yes - studentName depends on only studentId, which is part of the keyNo - studentName depends on the whole composite key
Matches
orderItems(orderId, productId, productName, quantity) is in 2NF
↓
productName depends only on productId, which is part of the composite key, so this is a partial dependency and the table is not in 2NF.
Applying
Meanings
Term
2NF
Meaning
A table in 1NF where no non-key field depends on only part of a composite key. Remove any partial dependency by splitting those fields into a new table.
Practice
SELECT results.studentId, subjects.name, results.mark
Testing
Gaps
This is a dependency
Check Answer
Spots
3 subId INT,
4 stuName VARCHAR(30),

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

The 'Sand Cat' badge — Precision Prowler, 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 key (studentId, subjectId) and the field studentName. Is studentName a partial dependency?
A. No - studentName depends on the whole composite key
B. Yes - studentName depends on only studentId, which is part of the key ✓
studentName can be found from studentId alone, so it depends on only part of the composite key. That is a partial dependency and breaks 2NF.
2. Which statement defines second normal form (2NF)?
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 holds many values
2NF builds on 1NF. It removes partial dependencies, where a non-key field depends on just part of a composite key.
3. A table has the single-field primary key orderId. Can it have a partial dependency?
A. No - a partial dependency needs a composite key ✓
B. Yes - any non-key field can depend on part of orderId
A single-field key cannot be split into parts. A table in 1NF with a single-field primary key is therefore already in 2NF.
4. Table: results(studentId, subjectId, mark) with key (studentId, subjectId). Does mark break 2NF?
A. No - mark depends on the whole key: one student in one subject ✓
B. Yes - mark depends on only studentId
A mark needs both the student and the subject to be identified. It depends on the full composite key, so it is not a partial dependency.
5. How do you fix a partial dependency to reach 2NF?
A. Delete the composite key and keep all fields in one table
B. Move the partly dependent fields into a new table keyed by the part they depend on ✓
Splitting into new tables is the fix. The part of the key that the fields depend on becomes the primary key of the new table. It stays in the original table as a foreign key.

Match the Pairs

PRIMARY KEY (orderId, productId) → A composite key. 2NF checks every non-key field against both parts of this key, not just one part.
productId → productName → A partial dependency when the key is (orderId, productId): productName depends on only part of the key.
To remove a partial dependency, split the dependent field into a new table keyed by the part of the key it depends on. → products(productId, productName)
After splitting to reach 2NF, the original table keeps the full composite key and only the fields that depend on all of it. → orderItems(orderId, productId, quantity)
orderItems(orderId, productId, productName, quantity) is in 2NF → productName depends only on productId, which is part of the composite key, so this is a partial dependency and the table is not in 2NF.
students(studentId, firstName, house) is not in 2NF because firstName depends on studentId → The key is a single field, so there is no partial dependency. Depending on the whole key is fine, and 2NF is not broken.

Key Term

2NF
A table in 1NF where no non-key field depends on only part of a composite key. Remove any partial dependency by splitting those fields into a new table.

Fill in the Blanks

1. Key: StudentID, SubjectID SubjectID -> SubjectName This is a partial dependency
Hint: Type the word for a non-key field that depends on only part of a composite key.
2. CREATE TABLE Subject ( SubjectID INT PRIMARY KEY, SubjectName VARCHAR(30));
Hint: Type the keyword that makes SubjectID the unique identifier of the new table.
3. CREATE TABLE Enrolment ( StudentID INT, SubjectID INT, Mark INT, PRIMARY KEY (StudentID, SubjectID));
Hint: Type the field that completes the composite key alongside StudentID.
4. 2NF needs the table to be in 1NF first and have no partial dependencies
Hint: Type the normal form a table must already meet before it can be in second normal form.
5. Mark INT, FOREIGN KEY (SubjectID) REFERENCES Subject(SubjectID));
Hint: Type the keyword that links the foreign key to the primary key of the new table.

Spot the Error

Question 1
A CREATE TABLE Result (
B stuId INT,
C subId INT,
D stuName VARCHAR(30),
E PRIMARY KEY (stuId, subId)
F );
Line D — stuName depends only on stuId, which is part of the composite key. That is a partial dependency, so it breaks 2NF. Result should hold only mark, which depends on the whole key. The line should be ' mark INT,' and stuName should go in a Student table.
Question 2
A Table: Enrolment, 1NF
B Key: (stuId)
C stuId -> name (partial)
D Move name to a Student table
Line B — A partial dependency needs a composite key. The key of Enrolment is the two fields together, so line 2 should be 'Key: (stuId, subId)'. With a single-field key, stuId -> name would not be partial.
Question 3
A CREATE TABLE Student (
B stuId INT PRIMARY KEY,
C name VARCHAR(30),
D mark INT
E );
Line D — mark depends on both the student and the subject, so it does not belong in the Student table. After splitting for 2NF, Student keeps only fields that depend on stuId, such as house. Line 4 should be ' house VARCHAR(10)'.
Question 4
A CREATE TABLE Subject (
B subId INT,
C name VARCHAR(30),
D teacher VARCHAR(30)
E );
Line B — The new table made by splitting needs a primary key. subId is the determinant that the moved fields depend on, so it must be the primary key. Line 2 should be ' subId INT PRIMARY KEY,'.
Question 5
A 2NF = 1NF + no transitive dep
B Partial: part of key -> non-key
C Fix: move fields to a new table
Line A — Second normal form means the table is in 1NF and has no partial dependencies. Removing transitive dependencies is the rule for 3NF. Line 1 should be '2NF = 1NF + no partial dep'.

Student with a class code? Join your class →