First normal form (1NF)

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. Which field value is atomic?

A phone = '0412 345 678'

B phones = '0412 345 678, 08 9123 4567'

2. A Students table has fields Subject1, Subject2 and Subject3 for each student's subjects. What does this show?

A A repeating group, so it is not in 1NF

B A table that is already in 1NF

3. A field called hobbies holds 'chess, netball, coding'. How is it fixed for 1NF?

A Make the hobbies field longer so it fits the list

B Split each hobby into its own record in a separate table

4. Which is a requirement of 1NF?

A Each non-key field depends on the whole key

B Each record is identified by a key

5. A table stores address as '12 King St, Perth, 6000' and the school needs to search by suburb. Which design is better for 1NF?

A Separate fields for street, suburb and postcode

B One address field holding all the parts

Match the Pairs

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

Left column		Right column
1phone: '0412 345 678, 0498 765 432'		AStudentPhone(StudentID, Phone)
2Student(StudentID, Name, Subject1, Subject2, Subject3)		BRepeating columns and no key to identify each record. Fix: add a key such as TeamID and move players into a Player table with one row each.
3A multi-valued field is split out into its own table, with one value per row		CA repeating group of columns, which breaks 1NF
4Each record in a 1NF table is identified by a key		DEnrolment(StudentID, SubjectCode, Mark) with the key (StudentID, SubjectCode)
5Order(OrderID, Customer, Items) with Items = 'Pen, Ruler, Book'		EItems holds several values in one field, so it is not atomic. Fix: put each item in its own row of an OrderItem table.
6Team(Player1, Player2, Player3) with no key field		FA field holding two values at once, so it is not atomic and breaks 1NF

Definitions

1NF

Fill in the Blanks

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

CREATE TABLE Phone ( studentId INT, phone VARCHAR(15), ___________ (studentId, phone));

INSERT INTO Phone VALUES (1, '0412111222'); INSERT INTO Phone VALUES (_, '0433999888');

SELECT studentId FROM Phone WHERE phone _ '0412111222';

SELECT studentId, _____(phone) FROM Phone GROUP BY studentId;

-- phone2 repeats the phone1 field ALTER TABLE Student ___________ phone2;

Spot the Error

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

Question 1

A CREATE TABLE Phone (

B sid INT,

C phone VARCHAR(15),

D PRIMARY KEY (sid)

E );

Question 2

A INSERT INTO Hobby VALUES

B (1, 'Ava', 'Chess'),

C (2, 'Liam', 'Art, Chess'),

D (3, 'Mia', 'Soccer');

Question 3

A CREATE TABLE Recipe (

B recipeId INT,

C ingredient VARCHAR(30)

D );

Question 4

A Table: Pet

B Fields: ownerId, pet1, pet2

C Key: ownerId + petName

Question 5

A id | name | subject

B 1 | Ava | Maths, Art

C 2 | Liam | English

D 3 | Liam | Maths

Answer Key

First normal form (1NF)

Section A: Quick Questions

A — phone = '0412 345 678'

A — A repeating group, so it is not in 1NF

B — Split each hobby into its own record in a separate table

B — Each record is identified by a key

A — Separate fields for street, suburb and postcode

Section B: Match the Pairs

phone: '0412 345 678, 0498 765 432' → A field holding two values at once, so it is not atomic and breaks 1NF

Student(StudentID, Name, Subject1, Subject2, Subject3) → A repeating group of columns, which breaks 1NF

A multi-valued field is split out into its own table, with one value per row → StudentPhone(StudentID, Phone)

Each record in a 1NF table is identified by a key → Enrolment(StudentID, SubjectCode, Mark) with the key (StudentID, SubjectCode)

Order(OrderID, Customer, Items) with Items = 'Pen, Ruler, Book' → Items holds several values in one field, so it is not atomic. Fix: put each item in its own row of an OrderItem table.

Team(Player1, Player2, Player3) with no key field → Repeating columns and no key to identify each record. Fix: add a key such as TeamID and move players into a Player table with one row each.

Section C: Definitions

1NF — A table is in 1NF when every field holds one atomic value, there are no repeating groups, and each record is identified by a key.

Section D: Fill in the Blanks

CREATE TABLE Phone (
  studentId INT, phone VARCHAR(15),
  PRIMARY KEY (studentId, phone));

INSERT INTO Phone VALUES (1, '0412111222');
INSERT INTO Phone VALUES (1, '0433999888');

SELECT studentId FROM Phone
WHERE phone = '0412111222';

SELECT studentId, COUNT(phone)
FROM Phone
GROUP BY studentId;

-- phone2 repeats the phone1 field
ALTER TABLE Student DROP COLUMN phone2;

Section E: Spot the Error

D — A student can have several phones, and each phone is stored in its own row. The key must be composite (sid, phone) so every record is identified. With only sid as the key, a student could have just one phone row. It should be PRIMARY KEY (sid, phone).

C — The hobby field holds two values, 'Art' and 'Chess', in one cell. That is not atomic, so the table breaks 1NF. Each field must hold a single value, so the second hobby should go in its own row or in a separate table.

B — 1NF needs every record to be identified by a key. Without PRIMARY KEY, recipeId does not uniquely identify each row. The line should be recipeId INT PRIMARY KEY,.

B — pet1 and pet2 are a repeating group of columns, which 1NF does not allow. The pets should be one field (petName) with one row per pet, and the table should use the composite key ownerId + petName.

B — The subject field holds two values, 'Maths' and 'Art', so it is not atomic. 1NF requires one value per field. Ava's second subject should go in a separate row, and this row should read 1 | Ava | Maths.