Third normal form (3NF)

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. Customer(CustomerID, Name, Postcode, Suburb) where Postcode determines Suburb. Which statement is correct?

A Suburb depends on CustomerID only, so the table is already in 3NF

B Suburb depends on Postcode, a non-key field, so the table is not in 3NF

3. Which phrase sums up 3NF?

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

B The key and only part of the key

4. What is a transitive dependency?

A Key → non-key field A → non-key field B

B Non-key field that depends on only part of a composite key

5. Student(StudentID, Name, HouseID, HouseColour) where HouseID determines HouseColour. How do you fix it to reach 3NF?

A Delete the HouseColour field and do not store it anywhere

B Move HouseID and HouseColour into a new House table and keep HouseID in Student as a foreign key

Match the Pairs

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

Left column		Right column
1StudentID → Postcode, Postcode → Suburb		ASuburb depends on Postcode, a non-key field, so this is a transitive dependency and the table is not in 3NF
2Student(StudentID, Name, Postcode) Postcode(Postcode, Suburb)		BPostcode → Suburb (determinant is not the key, so move it to a new table)
33NF means every field depends on the key, the whole key and nothing but the key		CThe Postcode to Suburb data is lost; the dependent field must move to a new table with Postcode as its primary key
4A table must already be in 2NF before it can be in 3NF		DThe result of removing the transitive dependency, so each non-key field depends only on its own table's key
5Customer(CustomerID, Name, Postcode, Suburb) -- already in 3NF		EA transitive dependency: Suburb depends on the key only through Postcode, a non-key field, so the table breaks 3NF
6Removing Suburb from Customer and deleting it, without creating a Postcode table		F1NF → 2NF (remove partial dependencies) → 3NF (remove transitive dependencies)

Definitions

3NF

Fill in the Blanks

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

Postcode __ Suburb -- postcode determines suburb

CREATE TABLE Suburb ( Postcode CHAR(4) ___________, SuburbName VARCHAR(30));

Key, the whole key and _______ but the key

StudentID -> Postcode -> Suburb This is a __________ dependency

SELECT Customer.Name, Suburb.SuburbName FROM Customer JOIN Suburb ON Customer.Postcode = _______________;

Spot the Error

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

Question 1

A Table: Customer

B Key: customerId

C Fields: name, postcode, suburb

D Moved to Postcode: suburb

Question 2

A studentId -> postcode

B postcode -> suburb

C suburb depends directly on key

D So the table is not in 3NF

Question 3

A SELECT postcode, suburb

B FROM Customer

C WHERE suburb = 'Fremantle';

Question 4

A SELECT c.name, p.suburb

B FROM Customer c, Postcode p

C WHERE c.postcode=p.suburb;

Question 5

A Table is in 2NF

B Check key fields for links

C postcode -> suburb is found

D Move suburb to a new table

E Table is now in 3NF

Answer Key

Third normal form (3NF)

Section A: Quick Questions

A — True

B — Suburb depends on Postcode, a non-key field, so the table is not in 3NF

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

A — Key → non-key field A → non-key field B

B — Move HouseID and HouseColour into a new House table and keep HouseID in Student as a foreign key

Section B: Match the Pairs

StudentID → Postcode, Postcode → Suburb → A transitive dependency: Suburb depends on the key only through Postcode, a non-key field, so the table breaks 3NF

Student(StudentID, Name, Postcode) Postcode(Postcode, Suburb) → The result of removing the transitive dependency, so each non-key field depends only on its own table's key

3NF means every field depends on the key, the whole key and nothing but the key → Postcode → Suburb (determinant is not the key, so move it to a new table)

A table must already be in 2NF before it can be in 3NF → 1NF → 2NF (remove partial dependencies) → 3NF (remove transitive dependencies)

Customer(CustomerID, Name, Postcode, Suburb) -- already in 3NF → Suburb depends on Postcode, a non-key field, so this is a transitive dependency and the table is not in 3NF

Removing Suburb from Customer and deleting it, without creating a Postcode table → The Postcode to Suburb data is lost; the dependent field must move to a new table with Postcode as its primary key

Section C: Definitions

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

Section D: Fill in the Blanks

Postcode -> Suburb
-- postcode determines suburb

CREATE TABLE Suburb (
  Postcode CHAR(4) PRIMARY KEY,
  SuburbName VARCHAR(30));

Key, the whole key and nothing but the key

StudentID -> Postcode -> Suburb
This is a transitive dependency

SELECT Customer.Name, Suburb.SuburbName
FROM Customer JOIN Suburb
ON Customer.Postcode = Suburb.Postcode;

Section E: Spot the Error

C — suburb depends on postcode, which is a non-key field. That is a transitive dependency, so suburb must not stay in Customer. Line 3 should be 'Fields: name, postcode'.

C — suburb depends on studentId only through postcode (studentId -> postcode -> suburb). That is a transitive dependency, not a direct one. Line 3 should say suburb depends via postcode.

B — After 3NF, suburb is stored in the Postcode table, not in Customer. The query must read FROM Postcode.

C — The foreign key Customer.postcode must be matched to the primary key Postcode.postcode. Comparing it with suburb gives wrong or no matches. It should be p.postcode.

B — 3NF removes dependencies between non-key fields. So you check the non-key fields for links to each other, not the key fields.