Third normal form (3NF) (Prototype Data Solutions)

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. A table is in 3NF when it is in 2NF and no non-key field depends on another non-key field.

A True

B False

2. Students(StudentID, Name, HomeroomID, HomeroomTeacher). HomeroomTeacher depends on HomeroomID. Which describes this?

A Transitive dependency, so it breaks 3NF

B Every field depends on the key, so it meets 3NF

3. What is a transitive dependency?

A A non-key field depends on the primary key

B A non-key field depends on another non-key field

4. Which phrase sums up 3NF?

A The key, the whole key and nothing but the key

B The key and anything else that is handy

5. Orders(OrderID, ProductID, ProductName, Price). ProductName depends on ProductID. How do you fix this to reach 3NF?

A Add more rows to the Orders table

B Move ProductName and Price into a Products table

Match the Pairs

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

Left column		Right column
13NF		A3NF: every non-key field depends on the key, and house details are held in a separate House table
2Student(studentId, name, houseId)		BhouseCaptain depends on houseId (a non-key field), not on studentId. This is a transitive dependency, so the table is not in 3NF.
3A transitive dependency is when a non-key field depends on another non-key field, not directly on the primary key		CstudentId -> houseId -> houseCaptain
4To reach 3NF, move the fields that depend on a non-key field into their own table, linked by a foreign key		DA table in 2NF where no non-key field depends on another non-key field
5Student(studentId, name, houseId, houseCaptain)		EHouse(houseId, houseName, houseCaptain)
6Product(productId, name, categoryId, categoryName)		FcategoryName depends on categoryId, which is a non-key field. Storing it here repeats the category name and breaks 3NF.

Definitions

3NF

Fill in the Blanks

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

CREATE TABLE House ( houseId INTEGER ___________, houseHead TEXT);

CREATE TABLE Student ( studentId INTEGER PRIMARY KEY, houseId INT __________ House(houseId));

SELECT Student.firstName, House.houseHead FROM Student JOIN House ON Student.houseId = _____________;

ALTER TABLE Student ___________ houseHead;

Student(studentId, firstName, houseId) House(_______, houseHead)

Spot the Error

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

Question 1

A CREATE TABLE Student (

B houseName TEXT PRIMARY KEY,

C name TEXT,

D houseId INT

E );

Question 2

A CREATE TABLE Customer (

B custId INT PRIMARY KEY,

C suburb TEXT,

D postcode TEXT

E );

Question 3

A SELECT c.name, p.suburb

B FROM Customer c

C JOIN Customer p

D ON c.postcode = p.postcode;

Question 4

A CREATE TABLE Student (

B studentId INT PRIMARY KEY,

C houseId INT,

D FOREIGN KEY (houseName)

E REFERENCES House(houseId)

F );

Question 5

A CREATE TABLE Product (

B productId INT PRIMARY KEY,

C categoryName TEXT,

D price REAL

E );

Answer Key

Third normal form (3NF) (Prototype Data Solutions)

Section A: Quick Questions

A — True

A — Transitive dependency, so it breaks 3NF

B — A non-key field depends on another non-key field

A — The key, the whole key and nothing but the key

B — Move ProductName and Price into a Products table

Section B: Match the Pairs

3NF → A table in 2NF where no non-key field depends on another non-key field

Student(studentId, name, houseId) → 3NF: every non-key field depends on the key, and house details are held in a separate House table

A transitive dependency is when a non-key field depends on another non-key field, not directly on the primary key → studentId -> houseId -> houseCaptain

To reach 3NF, move the fields that depend on a non-key field into their own table, linked by a foreign key → House(houseId, houseName, houseCaptain)

Student(studentId, name, houseId, houseCaptain) → houseCaptain depends on houseId (a non-key field), not on studentId. This is a transitive dependency, so the table is not in 3NF.

Product(productId, name, categoryId, categoryName) → categoryName depends on categoryId, which is a non-key field. Storing it here repeats the category name and breaks 3NF.

Section C: Definitions

3NF — A table in 2NF where no non-key field depends on another non-key field (no transitive dependency). Every field depends on the key, the whole key and nothing but the key.

Section D: Fill in the Blanks

CREATE TABLE House (
  houseId INTEGER PRIMARY KEY,
  houseHead TEXT);

CREATE TABLE Student (
  studentId INTEGER PRIMARY KEY,
  houseId INT REFERENCES House(houseId));

SELECT Student.firstName, House.houseHead
FROM Student JOIN House
ON Student.houseId = House.houseId;

ALTER TABLE Student DROP COLUMN houseHead;

Student(studentId, firstName, houseId)
House(houseId, houseHead)

Section E: Spot the Error

B — The primary key must identify each student. houseName is a house detail, not a student detail. Every field must depend on the key, so the key should be studentId.

C — suburb depends on postcode, which is a non-key field. That is a transitive dependency, so the table breaks 3NF. The suburb belongs in a separate Postcode table, so this line should be name TEXT.

C — After moving to 3NF, suburb is stored in the Postcode table. The query must join Postcode p, not Customer again.

D — The Student table only has a houseId column. houseName was moved to the House table in 3NF, so the foreign key must be (houseId).

C — categoryName depends on the category, not on productId, so it is a transitive dependency. Keep only the categoryId foreign key here and store the name in a Category table.