← QCE Digital Solutions Unit 2

First normal form (1NF) (Data and Programming Techniques)

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

Part of: QCE Digital Solutions Unit 2 — Data and Programming Techniques — Module 100

Understanding First normal form (1NF) (Data and Programming Techniques) in QCE Digital Solutions Unit 2

What it does: Each hobby now sits in its own row as one atomic value, so the field is no longer multi-valued. Instead of Phone1, Phone2 and Phone3 columns, each phone number gets its own row linked back to the student by studentId. Repeating columns such as Subject1, Subject2 and Subject3 are a repeating group. 1NF replaces them with one row for each subject. Commas inside a cell do not make the values atomic. Each item needs its own row, linked to the order by a key. One part of 1NF is that each row is identified by a key. This could be a primary key or a combination of fields.

Common mistakes: This cell holds two values, 'soccer' and 'chess', so it is not atomic. In 1NF each hobby needs its own row, for example (2, 'soccer') and a separate (2, 'chess') row. A student can have many hobbies, so stuId repeats and cannot identify a row by itself. After splitting the hobbies into rows, the key must be (stuId, hobby).

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) (Data and Programming Techniques) to a class →

This module's key terms are also in a printable crossword (Modules 234–285) and word search (Modules 234–409).

Take this worksheet further — free with Cloodit

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

Learning
Duo
A Students table has a Phone column holding '0412 345 678, 0498 765 432' in one cell. Is this table in 1NF?
Yes, it is all stored in one columnNo, the cell holds more than one value
Matches
Student(studentId, name, Subject1, Subject2, Subject3)
↓
Subject1, Subject2 and Subject3 are repeating columns, so the table is not in 1NF. Use a separate table with one row per subject.
Applying
Meanings
Term
First normal form (1NF)
Meaning
A table is in 1NF when every field holds one atomic value, there are no repeating groups or repeating columns, and each row is identified by a key.
Practice
SELECT studentId, subjectId, mark
Testing
Gaps
studentId INT ,
Check Answer
Spots
2(1, 'chess'),
3(2, 'soccer, chess'),

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

The 'Spider' badge — Web Weaver, one of Cloodit's 106 collectible badges
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. A Students table has a Phone column holding '0412 345 678, 0498 765 432' in one cell. Is this table in 1NF?
A. Yes, it is all stored in one column
B. No, the cell holds more than one value ✓
1NF needs every field to hold a single atomic value. Two phone numbers in one cell break that rule, so they must be split into separate rows.
2. Which table design follows 1NF?
A. One row per student and subject, with one mark in each row ✓
B. One row per student with Subject1, Subject2 and Subject3 columns
Repeating columns such as Subject1, Subject2 and Subject3 are a repeating group. 1NF replaces them with one row for each subject.
3. A cell in an Orders table contains 'Pencil, Ruler, Eraser'. What does 1NF say to do?
A. Split it so each item has its own row ✓
B. Leave it, because commas separate the items
Commas inside a cell do not make the values atomic. Each item needs its own row, linked to the order by a key.
4. True or false: a table in 1NF must have a key that identifies each row.
A. False
B. True ✓
One part of 1NF is that each row is identified by a key. This could be a primary key or a combination of fields.
5. Which column name suggests a repeating group that breaks 1NF?
A. Hobby1, Hobby2, Hobby3 ✓
B. HobbyName
Numbered columns that store the same kind of data are repeating columns. A single HobbyName column with one value per row is fine.

Match the Pairs

Hobbies = 'Chess, Soccer, Art' → A multi-value cell that breaks 1NF because one field holds several values instead of one atomic value
Phone1, Phone2, Phone3 → Repeating columns (a repeating group) that break 1NF and should be moved to their own rows
To fix a multi-value cell, split it so each value gets its own row and the table has a primary key → StudentHobby(studentId, hobby) (1, 'Chess') (1, 'Soccer') (1, 'Art')
To remove repeating columns, move them into a separate table with one row per value, linked by a key → StudentPhone(studentId, phone)
Student(studentId, name, Subject1, Subject2, Subject3) → Subject1, Subject2 and Subject3 are repeating columns, so the table is not in 1NF. Use a separate table with one row per subject.
Order(orderId, items) (1, 'Pen, Ruler, Book') → The items field holds several values in one cell, so it is not atomic. Each item should have its own row.

Key Term

First normal form (1NF)
A table is in 1NF when every field holds one atomic value, there are no repeating groups or repeating columns, and each row is identified by a key.

Fill in the Blanks

1. CREATE TABLE student ( studentId INT PRIMARY KEY, name VARCHAR(30));
Hint: Type the two-word constraint that makes each row identified by a unique key, as 1NF requires.
2. INSERT INTO enrolment VALUES (1, 'Maths'); INSERT INTO enrolment VALUES (1, 'Art');
Hint: Type the student ID. The same student gets a second row instead of two subjects in one cell.
3. ALTER TABLE student DROP COLUMN phone2;
Hint: Type the keywords that remove a repeating column from the table.
4. SELECT subject FROM enrolment WHERE studentId = 1;
Hint: Type the column name that picks out one student's rows, now that each subject has its own row.
5. CREATE TABLE phone ( studentId INT, number VARCHAR(10), PRIMARY KEY (studentId, number));
Hint: Type the second column that, together with the student ID, identifies each row.

Spot the Error

Question 1
A INSERT INTO Hobby VALUES
B (1, 'chess'),
C (2, 'soccer, chess'),
D (3, 'drawing');
Line C — This cell holds two values, 'soccer' and 'chess', so it is not atomic. In 1NF each hobby needs its own row, for example (2, 'soccer') and a separate (2, 'chess') row.
Question 2
A CREATE TABLE Hobby (
B stuId INT,
C hobby VARCHAR(20),
D PRIMARY KEY (stuId)
E );
Line D — A student can have many hobbies, so stuId repeats and cannot identify a row by itself. After splitting the hobbies into rows, the key must be (stuId, hobby).
Question 3
A CREATE TABLE Result (
B stuId INT,
C m1 INT, m2 INT, m3 INT,
D subId INT,
E PRIMARY KEY (stuId, subId)
F );
Line C — m1, m2 and m3 are repeating columns, which is a repeating group and breaks 1NF. Use one mark column and put each mark on its own row, identified by subId.
Question 4
A cell = 'chess,soccer'
B hobbies = cell.split(',')
C for h in hobbies:
D print(1, cell)
Line D — This prints the whole multi-value cell again. To store atomic values, each row should use the single value h, so it should be print(1, h).
Question 5
A cell = 'chess;soccer;art'
B hobbies = cell.split(',')
C for h in hobbies:
D print(h)
Line A — The cell uses semicolons but split(',') splits on commas, so the cell is never separated into single values. The delimiter in the cell must match the one passed to split().

Student with a class code? Join your class →