Second normal form (2NF)

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

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

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

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

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

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

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

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

Definitions

2NF

Fill in the Blanks

Complete each line of code by filling in the missing parts.

Key: StudentID, SubjectID SubjectID -> SubjectName This is a _______ dependency

CREATE TABLE Subject ( SubjectID INT ___________, SubjectName VARCHAR(30));

CREATE TABLE Enrolment ( StudentID INT, SubjectID INT, Mark INT, PRIMARY KEY (StudentID, _________));

2NF needs the table to be in ___ first and have no partial dependencies

Mark INT, FOREIGN KEY (SubjectID) __________ Subject(SubjectID));

Spot the Error

Shade in the letter of the line that contains an error.

Question 1

A CREATE TABLE Result (

B stuId INT,

C subId INT,

D stuName VARCHAR(30),

E PRIMARY KEY (stuId, subId)

F );

Question 2

A Table: Enrolment, 1NF

B Key: (stuId)

C stuId -> name (partial)

D Move name to a Student table

Question 3

A CREATE TABLE Student (

B stuId INT PRIMARY KEY,

C name VARCHAR(30),

D mark INT

E );

Question 4

A CREATE TABLE Subject (

B subId INT,

C name VARCHAR(30),

D teacher VARCHAR(30)

E );

Question 5

A 2NF = 1NF + no transitive dep

B Partial: part of key -> non-key

C Fix: move fields to a new table

Answer Key

Second normal form (2NF)

Section A: Quick Questions

B — Yes - studentName depends on only studentId, which is part of the key

A — The table is in 1NF and no non-key field depends on only part of a composite key

A — No - a partial dependency needs a composite key

A — No - mark depends on the whole key: one student in one subject

B — Move the partly dependent fields into a new table keyed by the part they depend on

Section B: 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.

Section C: Definitions

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.

Section D: Fill in the Blanks

Key: StudentID, SubjectID
SubjectID -> SubjectName
This is a partial dependency

CREATE TABLE Subject (
  SubjectID INT PRIMARY KEY,
  SubjectName VARCHAR(30));

CREATE TABLE Enrolment (
  StudentID INT, SubjectID INT, Mark INT,
  PRIMARY KEY (StudentID, SubjectID));

2NF needs the table to be in 1NF first
and have no partial dependencies

  Mark INT,
  FOREIGN KEY (SubjectID)
    REFERENCES Subject(SubjectID));

Section E: Spot the Error

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.

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.

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)'.

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,'.

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'.