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

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

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

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

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

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

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

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)		AA single-field primary key cannot have a partial dependency, because there is no part of the key to depend on
2productName depends on productId only		BCREATE TABLE products (productId INT PRIMARY KEY, productName VARCHAR(50));
3To reach 2NF, move a field that depends on only part of the composite key into its own table		CCREATE TABLE orderItems (orderId INT, productId INT, quantity INT, PRIMARY KEY (orderId, productId));
4A field that depends on the whole composite key can stay in the original table		DA partial dependency, which breaks 2NF when the key is (orderId, productId)
5orderItems(orderId, productId, quantity, productName) with PRIMARY KEY (orderId, productId)		EproductName depends only on productId, which is part of the key, so this table is not in 2NF
6A table with the single primary key studentId is checked for partial dependencies, and one is found		FA composite primary key made of two fields, so 2NF must be checked for partial dependency

Definitions

2NF

Fill in the Blanks

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

CREATE TABLE OrderItem ( orderId INT, productId INT, qty INT, ___________ (orderId, productId));

CREATE TABLE Enrolment ( studentId INT, subjectId INT, mark INT, PRIMARY KEY (studentId, _________));

SELECT ________ productId, productName FROM OrderItem;

CREATE TABLE Product ( productId INT, productName VARCHAR(30), ___________ (productId));

-- subjectName depends on subjectId only -- This is a _______ dependency -- Move it to the Subject table

Spot the Error

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

Question 1

A CREATE TABLE OrderItem (

B ordId INT,

C prodId INT,

D PRIMARY KEY (ordId),

E qty INT

F );

Question 2

A INSERT INTO OrderItem

B VALUES (1, 2, 5),

C (1, 2, 2),

D (2, 2, 1);

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

Question 4

A ALTER TABLE OrderItem

B DELETE COLUMN prodName;

C SELECT * FROM OrderItem;

Question 5

A INSERT INTO Product

B VALUES (1, Pencil);

C SELECT * FROM Product;

Answer Key

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

Section A: Quick Questions

B — A partial dependency, so the table is not in 2NF

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

B — No, a partial dependency needs a composite key, so it is in 2NF

A — subjectName, because it depends only on subjectId

A — Move the partly dependent field into its own table with the part of the key it depends on

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

Section C: Definitions

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.

Section D: Fill in the Blanks

CREATE TABLE OrderItem (
  orderId INT, productId INT, qty INT,
  PRIMARY KEY (orderId, productId));

CREATE TABLE Enrolment (
  studentId INT, subjectId INT, mark INT,
  PRIMARY KEY (studentId, subjectId));

SELECT DISTINCT productId, productName
FROM OrderItem;

CREATE TABLE Product (
  productId INT,
  productName VARCHAR(30), PRIMARY KEY
 (productId));

-- subjectName depends on subjectId only
-- This is a partial dependency
-- Move it to the Subject table

Section E: Spot the Error

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.

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

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,

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.

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.