Normalising a table step by step to 3NF

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. A table has a Phone1, Phone2, Phone3 group of columns for each student. Which normal form step removes this?

A 1NF: remove the repeating group

B 3NF: remove the transitive dependency

2. A table has the composite key (OrderID, ProductID) and ProductName depends only on ProductID. Which step fixes this?

A 1NF: remove the repeating group

B 2NF: remove the partial dependency

3. In a Student table, StudentID determines TeacherID and TeacherID determines TeacherName. Which problem is this?

A A transitive dependency, fixed in 3NF

B A partial dependency, fixed in 2NF

4. What is the correct order to normalise a table?

A 1NF, then 2NF, then 3NF

B 3NF, then 2NF, then 1NF

5. When you split TeacherName out into a new Teacher table, what must the new table have?

A No key, because it is only a lookup list

B Its own primary key, such as TeacherID

Match the Pairs

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

Left column		Right column
1Order(OrderID, Item1, Item2, Item3)		AProduct(ProductID, ProductName, Price) OrderLine(OrderID, ProductID, Qty)
2OrderLine(OrderID, ProductID, ProductName, Qty)		BHouseName depends on HouseID, which is not the key. This transitive dependency means the table is not in 3NF.
3Step 3 to reach 3NF: remove a transitive dependency by moving the non-key determinant into its own table		CAn unnormalised table with a repeating group of item columns that must be removed to reach 1NF
4Choosing keys for new tables: the attribute that determined the moved fields becomes the new table's primary key and stays as a foreign key in the old one		DProductName depends only on ProductID, part of the composite key, so this has a partial dependency and fails 2NF
5Student(StudentID, Name, HouseID, HouseName) so the table is now in 3NF		EStudent(StudentID, Name, HouseID) House(HouseID, HouseName, HeadOfHouse)
6Normalise to 3NF: remove transitive dependencies first, then repeating groups, then partial dependencies		FThe steps are in the wrong order. Normalisation removes repeating groups (1NF), then partial dependencies (2NF), then transitive dependencies (3NF).

Definitions

Normalise to 3NF

Fill in the Blanks

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

Removing repeating groups gives ___

Remove partial dependencies to reach ___

OrderID -> CustID -> CustName is __________

OrderItem(OrderID, ProductID, Qty) PRIMARY KEY (OrderID, _________)

Customer(CustID, CustName) Order(OrderID, OrderDate, ______)

Spot the Error

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

Question 1

A UNF: Ord(ID, Item1, Item2)

B 1NF: remove transitive deps

C OrderItem(OrderID, Item, Qty)

D PK: OrderID + Item

Question 2

A Key: (OrderID, ItemID)

B ItemID -> ItemName, Price

C 2NF: remove transitive deps

D Item(ItemID, ItemName, Price)

Question 3

A Stu(SID, Name, TutID, TutName)

B SID -> TutID -> TutName

C TutID is a non-key field

D New: Tutor(SID, TutName)

Question 4

A Student(SID, Name, TutID)

B Tutor(TutID, TutName)

C ERD: Tutor many --- 1 Student

D FK: Student.TutID -> Tutor

Question 5

A Student(SID, Name, Phones)

B Problem: Name holds a list

C 1NF: one value per field

Answer Key

Normalising a table step by step to 3NF

Section A: Quick Questions

A — 1NF: remove the repeating group

B — 2NF: remove the partial dependency

A — A transitive dependency, fixed in 3NF

A — 1NF, then 2NF, then 3NF

B — Its own primary key, such as TeacherID

Section B: Match the Pairs

Order(OrderID, Item1, Item2, Item3) → An unnormalised table with a repeating group of item columns that must be removed to reach 1NF

OrderLine(OrderID, ProductID, ProductName, Qty) → ProductName depends only on ProductID, part of the composite key, so this has a partial dependency and fails 2NF

Step 3 to reach 3NF: remove a transitive dependency by moving the non-key determinant into its own table → Student(StudentID, Name, HouseID) House(HouseID, HouseName, HeadOfHouse)

Choosing keys for new tables: the attribute that determined the moved fields becomes the new table's primary key and stays as a foreign key in the old one → Product(ProductID, ProductName, Price) OrderLine(OrderID, ProductID, Qty)

Student(StudentID, Name, HouseID, HouseName) so the table is now in 3NF → HouseName depends on HouseID, which is not the key. This transitive dependency means the table is not in 3NF.

Normalise to 3NF: remove transitive dependencies first, then repeating groups, then partial dependencies → The steps are in the wrong order. Normalisation removes repeating groups (1NF), then partial dependencies (2NF), then transitive dependencies (3NF).

Section C: Definitions

Normalise to 3NF — Take a table through 1NF, 2NF, then 3NF by removing repeating groups, then partial dependencies, then transitive dependencies, giving each new table its own key.

Section D: Fill in the Blanks

Removing repeating groups gives 1NF

Remove partial dependencies to reach 2NF

OrderID -> CustID -> CustName is transitive

OrderItem(OrderID, ProductID, Qty)
PRIMARY KEY (OrderID, ProductID)

Customer(CustID, CustName)
Order(OrderID, OrderDate, CustID)

Section E: Spot the Error

B — The first step, 1NF, removes repeating groups such as Item1 and Item2. Transitive dependencies are removed later, in 3NF. The line should read '1NF: remove repeating groups'.

C — 2NF removes partial dependencies. Here ItemName and Price depend on only part of the composite key (ItemID). Transitive dependencies are removed in 3NF. The line should read '2NF: remove partial deps'.

D — TutName depends on TutID, a non-key field, so it moves to a new table. The key of the new table must be the determinant TutID, not SID. The line should be 'New: Tutor(TutID, TutName)'.

C — One tutor looks after many students, and each student has one tutor. The 'one' side is Tutor and the 'many' side is Student. The line should read 'ERD: Tutor 1 --- many Student'.

B — The field that holds a list of values is Phones, which breaks 1NF. Name holds a single value. The line should read 'Problem: Phones holds a list'.