Normalising a table to 3NF step by step

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. A table stores StudentID, StudentName, Subject1, Subject2 and Subject3. Which normal form does it break?

A 3NF: StudentName depends on a non-key field

B 1NF: the repeating subject columns hold a repeating group

2. You normalise a table in order. Which order is correct?

A 3NF, then 2NF, then 1NF

B 1NF, then 2NF, then 3NF

3. Table OrderLine has the composite key (OrderID, ProductID). ProductName depends only on ProductID. What do you do to reach 2NF?

A Move ProductID and ProductName into a new Product table and keep ProductID in OrderLine as a foreign key

B Delete the ProductName field from the database

4. In a Student table, StudentID is the primary key. HouseName depends on HouseID, and HouseID is a non-key field. Which fix reaches 3NF?

A Create a House table (HouseID, HouseName) and keep HouseID in Student as a foreign key

B Make HouseName the primary key of the Student table

5. After splitting a table during normalisation, how do you keep the data connected?

A Put the primary key of the new table into the original table as a foreign key

B Copy all the fields into both tables so nothing is missed

Match the Pairs

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

Left column		Right column
1OrderLine(OrderID PK, ProductID PK, Qty)		ARepeating groups (Item1, Item2, Item3) break 1NF. Fix: one row per item, using a composite key of OrderID and ProductID.
2Product(ProductID PK, ProductName, Price)		B2NF result: Qty depends on the whole composite key, so it stays here while product details move to their own table.
3After splitting out a new table, the old table keeps the new table's key as a foreign key so the data can be linked back together.		CCaptain depends on House, not on StudentID (a transitive dependency), so this breaks 3NF. Move House and Captain to a House table linked by a HouseID foreign key.
4Checking for anomalies after normalising: a product's price change now needs only one row to be edited.		D2NF result: product details depend only on ProductID, so each is stored once instead of in every order row.
5Order(OrderID PK, Item1, Item2, Item3)		EUPDATE Product SET Price = 5 WHERE ProductID = 2;
6Student(StudentID PK, Name, House, Captain)		FStudent(StudentID PK, Name, HouseID FK)

Definitions

1NF, 2NF, 3NF

Fill in the Blanks

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

CREATE TABLE Customer ( CustID INT ___________, Name TEXT);

CREATE TABLE Orders (OrderID INT, CustID INT, ___________ (CustID) REFERENCES Customer(CustID));

OrderLine(OrderID, ProductID, Qty) Product(_________, ProductName, Price)

Suburb(PostCode, SuburbName) Customer(CustID, Name, ________)

UPDATE Product SET Price = 12.50 WHERE _________ = 3;

Spot the Error

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

Question 1

A Customer(CustID PK, Name)

B Product(ProdID PK, Name, Price)

C Orders(OrderID PK, CustID FK)

D Line(OrderID, ProdName, Qty)

Question 2

A 1NF: one value per field

B 2NF: remove partial dependency

C 3NF: remove repeating groups

D Link tables with foreign keys

Question 3

A Key: (StudentID, SubjectID)

B Teacher depends on both parts

C That is a partial dependency

D Move Teacher to Subject table

Question 4

A Student(ID PK, Name, HouseID)

B House(HouseID PK, Captain)

C HouseID is a PK in Student

D No data lost: join on HouseID

Question 5

A CREATE TABLE Student (

B ID INT PRIMARY KEY,

C HouseID INT

D );

Answer Key

Normalising a table to 3NF step by step

Section A: Quick Questions

B — 1NF: the repeating subject columns hold a repeating group

B — 1NF, then 2NF, then 3NF

A — Move ProductID and ProductName into a new Product table and keep ProductID in OrderLine as a foreign key

A — Create a House table (HouseID, HouseName) and keep HouseID in Student as a foreign key

A — Put the primary key of the new table into the original table as a foreign key

Section B: Match the Pairs

OrderLine(OrderID PK, ProductID PK, Qty) → 2NF result: Qty depends on the whole composite key, so it stays here while product details move to their own table.

Product(ProductID PK, ProductName, Price) → 2NF result: product details depend only on ProductID, so each is stored once instead of in every order row.

After splitting out a new table, the old table keeps the new table's key as a foreign key so the data can be linked back together. → Student(StudentID PK, Name, HouseID FK)

Checking for anomalies after normalising: a product's price change now needs only one row to be edited. → UPDATE Product SET Price = 5 WHERE ProductID = 2;

Order(OrderID PK, Item1, Item2, Item3) → Repeating groups (Item1, Item2, Item3) break 1NF. Fix: one row per item, using a composite key of OrderID and ProductID.

Student(StudentID PK, Name, House, Captain) → Captain depends on House, not on StudentID (a transitive dependency), so this breaks 3NF. Move House and Captain to a House table linked by a HouseID foreign key.

Section C: Definitions

1NF, 2NF, 3NF — Work through 1NF, 2NF, then 3NF, splitting data into new tables linked by foreign keys so no data is lost and anomalies are removed.

Section D: Fill in the Blanks

CREATE TABLE Customer (
  CustID INT PRIMARY KEY, Name TEXT);

CREATE TABLE Orders (OrderID INT,
  CustID INT, FOREIGN KEY (CustID)
  REFERENCES Customer(CustID));

OrderLine(OrderID, ProductID, Qty)
Product(ProductID, ProductName, Price)

Suburb(PostCode, SuburbName)
Customer(CustID, Name, PostCode)

UPDATE Product SET Price = 12.50
WHERE ProductID = 3;

Section E: Spot the Error

D — After 3NF the Line table links to Product through its key. It must hold ProdID, the foreign key, not ProdName. Otherwise the link to Product is not a key and the tables cannot be joined reliably.

C — Repeating groups are removed in 1NF. Third normal form removes transitive dependencies, where a non-key field depends on another non-key field.

B — Teacher depends only on SubjectID, which is part of the key. That is why it is a partial dependency that 2NF removes. It does not depend on both parts of the key.

C — HouseID is the primary key of House. In Student it is a foreign key that links each student to their house. It is not the primary key of Student.

C — The new link needs a foreign key. HouseID must say REFERENCES House so it points at the House table. Without it the tables are not linked and referential integrity is lost.