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

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

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

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

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

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

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

Match the Pairs

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

Left column		Right column
1PRIMARY KEY (studentId, subjectId)		AA composite key made of two fields. 2NF checks every other field against the whole key, not just one part.
2subjectName depends on subjectId only		BResults(studentId, subjectId, mark)
3To fix a partial dependency, move the partly dependent field into a new table keyed by that part of the key		CSubjects(subjectId, subjectName)
4A table in 2NF has every non-key field depending on the whole composite key, such as a mark for one student in one subject		DA partial dependency: the field relies on part of the composite key, so the table breaks 2NF.
5Results(studentId, subjectId, mark, studentName)		EstudentName depends only on studentId, which is part of the key. This partial dependency means the table is not in 2NF.
6Orders(orderId, productId, quantity, productName) PK: orderId, productId		FproductName depends only on productId, not on the whole key of orderId and productId, so the table breaks 2NF.

Definitions

2NF

Fill in the Blanks

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

OrderItem(orderId, productId, qty, name) ___________ (orderId, productId)

Key: (orderId, productId) _________ -> name

OrderItem(orderId, productId, qty) Product(productId, ____)

FOREIGN KEY (productId) __________ product(productId)

2NF = 1NF + no _______ dependency

Spot the Error

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

Question 1

A 2NF: in 3NF, no partial deps

B Key: (stuId, subId)

C mark needs studentId+subjectId

Question 2

A A composite key has 1 field

B PRIMARY KEY (orderId, itemId)

C Example: orderId + itemId

Question 3

A CREATE TABLE Result (

B stuId INT, subId INT,

C PRIMARY KEY (stuId),

D mark INT

E );

Question 4

A Key: (stuId, subId)

B subName depends on stuId only

C That is a partial dependency

D Move subName to Subject table

Question 5

A Result(stuId,subId,mark,sName)

B sName depends on subId only

C New: Subject(stuId, sName)

D Result drops sName

Answer Key

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

Section A: Quick Questions

B — True

A — OrderItem(orderId, productId, quantity) with key (orderId, productId)

A — productName, because it depends only on productId

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

A — Move subjectId and teacher into a new Subject table

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

Section C: Definitions

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.

Section D: Fill in the Blanks

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

Key: (orderId, productId)
productId -> name

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

FOREIGN KEY (productId)
REFERENCES product(productId)

2NF = 1NF + no partial dependency

Section E: Spot the Error

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.

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.

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.

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.

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