Evaluating a database against the normal forms

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. A table has a Phones field holding '0412 111, 0433 222' in one row. Which normal form does it break?

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

B 1NF: the field holds a repeating group, not one atomic value

2. Which order should you check a table against the normal forms?

A 1NF, then 2NF, then 3NF

B 3NF, then 2NF, then 1NF

3. A table has the composite key (OrderID, ProductID). ProductName depends only on ProductID. What is the problem?

A A transitive dependency, so the table breaks 3NF

B A partial dependency, so the table breaks 2NF

4. In a Students table with key StudentID, the row (7, Ava, Red, Ms Lee) has HouseHead depending on House, not on StudentID. What is this called?

A A transitive dependency, which breaks 3NF

B A partial dependency, which breaks 2NF

5. A table has a single-field primary key and passes 1NF. Can it have a partial dependency?

A No, a partial dependency needs a composite key, so it passes 2NF

B Yes, any non-key field can be partially dependent

Match the Pairs

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

Left column		Right column
1Student_ID, Subject_1, Subject_2, Subject_3		AA repeating group is a 1NF problem, not a 3NF problem. 3NF problems are transitive dependencies.
2Primary key (OrderID, ProductID) with ProductName in the table		BRow (1, Ava, Red, Ms Lee) repeats Red and Ms Lee for every Red student
3Transitive dependency: a non-key field depends on another non-key field, so the table fails 3NF		CStudentID -> House -> HouseCaptain
4Justify a failed check with an example row from the table		DFails 2NF: partial dependency on part of the key
5Check 3NF first, then 2NF, then 1NF		EThe normal forms must be checked in order: 1NF, then 2NF, then 3NF, because each form builds on the one before.
6Table fails 3NF because it has a repeating group		FFails 1NF: repeating group of columns

Definitions

normal form check

Fill in the Blanks

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

-- Row 1: Phone = 0412, 0498 breaks ___

-- ProductName depends on ProductId only -- This is a _______ dependency (2NF)

-- Suburb depends on Postcode, not the key -- This is a __________ dependency (3NF)

CREATE TABLE Product ( ProductId INT ___________, Name VARCHAR(30));

SELECT Postcode, Suburb, COUNT(*) FROM Customer GROUP BY Postcode, ______;

Spot the Error

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

Question 1

A Enrol(StuID, SubjID, StuName)

B Key: (StuID, SubjID)

C StuName needs both key fields

D Fails 2NF: partial dependency

Question 2

A Step 1: check 1NF

B Step 2: check 3NF

C Step 3: check 3NF

Question 3

A Row 3: Ava | Art

B Cell holds two values

C Repeating group: fails 1NF

Question 4

A Staff(ID, DeptID, DeptName)

B Key: ID

C DeptID -> DeptName

D Transitive: fails 2NF

Question 5

A 2NF: key is (OrdID)

B CustName depends on OrdID

C Partial dependency found

Answer Key

Evaluating a database against the normal forms

Section A: Quick Questions

B — 1NF: the field holds a repeating group, not one atomic value

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

B — A partial dependency, so the table breaks 2NF

A — A transitive dependency, which breaks 3NF

A — No, a partial dependency needs a composite key, so it passes 2NF

Section B: Match the Pairs

Student_ID, Subject_1, Subject_2, Subject_3 → Fails 1NF: repeating group of columns

Primary key (OrderID, ProductID) with ProductName in the table → Fails 2NF: partial dependency on part of the key

Transitive dependency: a non-key field depends on another non-key field, so the table fails 3NF → StudentID -> House -> HouseCaptain

Justify a failed check with an example row from the table → Row (1, Ava, Red, Ms Lee) repeats Red and Ms Lee for every Red student

Check 3NF first, then 2NF, then 1NF → The normal forms must be checked in order: 1NF, then 2NF, then 3NF, because each form builds on the one before.

Table fails 3NF because it has a repeating group → A repeating group is a 1NF problem, not a 3NF problem. 3NF problems are transitive dependencies.

Section C: Definitions

normal form check — Test a table against 1NF, then 2NF, then 3NF, naming each dependency problem found (repeating group, partial or transitive) and backing it up with an example row.

Section D: Fill in the Blanks

-- Row 1: Phone = 0412, 0498 breaks 1NF

-- ProductName depends on ProductId only
-- This is a partial dependency (2NF)

-- Suburb depends on Postcode, not the key
-- This is a transitive dependency (3NF)

CREATE TABLE Product (
  ProductId INT PRIMARY KEY,
  Name VARCHAR(30));

SELECT Postcode, Suburb, COUNT(*)
FROM Customer
GROUP BY Postcode, Suburb;

Section E: Spot the Error

C — StuName depends on only part of the composite key (StuID). That is what makes it a partial dependency. The line should read 'StuName needs StuID only'.

B — The normal forms are checked in order: 1NF, then 2NF, then 3NF. Step 2 should be 'check 2NF'.

A — The example row must show two values in one cell to justify a 1NF failure. 'Art' alone is a single value. It should be 'Row 3: Ava | Art, Maths'.

D — A transitive dependency (a non-key field depending on another non-key field) breaks 3NF, not 2NF. It should be 'Transitive: fails 3NF'.

A — A partial dependency can only exist when the primary key is composite. The key must be (OrdID, Item) for CustName depending on OrdID alone to be partial.