← WA Computer Science ATAR Unit 2

First normal form (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.

Part of: WA Computer Science ATAR Year 11 — Data Management — Module 45

Understanding First normal form (1NF) in WA Computer Science ATAR Unit 2

What it does: Moving each phone number into its own row gives one atomic value per field and removes the multi-valued field. This moves the table towards 1NF. 1NF requires that every record can be identified by a key. Here the composite key StudentID + SubjectCode identifies each row uniquely. Columns that repeat the same kind of data are a repeating group. Move subjects to their own table linked by a key. A key to identify each record is part of 1NF. Depending on the whole key is the rule for 2NF.

Common mistakes: 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). 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.

Class: _____________________ · Name: _____________________ · Date: _________

Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.

🖨 Print worksheet + answer key Add First normal form (1NF) to a class →

This module's key terms are also in a printable crossword (Modules 28–122) and word search (Modules 28–266).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning

Duo

Which field value is atomic?

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

Matches

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.

Applying

Meanings

Term

1NF

Meaning

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.

Practice

SELECT studentId, subjectId, mark

Testing

Gaps

(studentId, phone));

Check Answer

Spots

3 phone VARCHAR(15),

4 PRIMARY KEY (sid)

Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.

Students earn collectable badges

Over 100 of them, across seven natural biomes.

Study Buddy

Students build a real study resource

Turns their work into revision material ready for Flash Cards and Exam study.

Diamond Dash — one of Cloodit's live, competitive Sprint games

Students lock in with Sprints

Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.

A Cloodit certificate of achievement, ready to personalise and print

Custom printable certificates

Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.

Add to class →

Quick Questions

1. Which field value is atomic?

A. phone = '0412 345 678' ✓

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

An atomic value is a single piece of data. A list of two phone numbers in one field breaks 1NF.

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

Columns that repeat the same kind of data are a repeating group. Move subjects to their own table linked by a key.

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 ✓

A multi-valued field is split out so each row holds one hobby. Making the field longer keeps the list in one field.

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 ✓

A key to identify each record is part of 1NF. Depending on the whole key is the rule for 2NF.

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

Splitting the address into atomic fields lets each part be searched and sorted on its own.

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.

Key Term

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.

Fill in the Blanks

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

Hint: Type the two-word keyword that makes each record identifiable, which 1NF requires. Here it covers a combination of fields.

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

Hint: Type the studentId value. The same student's second phone number goes in its own record rather than sharing a field.

3. SELECT studentId FROM Phone WHERE phone = '0412111222';

Hint: Type the comparison operator for an exact match. It works because each phone field holds one atomic value.

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

Hint: Type the aggregate function that counts the phone numbers per student. It is easy once each number is a separate record.

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

Hint: Type the clause that removes a column. This removes a repeating group from the table.

Spot the Error

Question 1

A CREATE TABLE Phone (

B sid INT,

C phone VARCHAR(15),

D PRIMARY KEY (sid)

E );

Line 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).

Question 2

A INSERT INTO Hobby VALUES

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

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

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

Line 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.

Question 3

A CREATE TABLE Recipe (

B recipeId INT,

C ingredient VARCHAR(30)

D );

Line 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,.

Question 4

A Table: Pet

B Fields: ownerId, pet1, pet2

C Key: ownerId + petName

Line 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.

Question 5

A id | name | subject

B 1 | Ava | Maths, Art

C 2 | Liam | English

D 3 | Liam | Maths

Line 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.

Student with a class code? Join your class →