← QCE Digital Solutions Unit 2

First normal form (1NF) (Prototype Data Solutions)

First normal form: every field holds one atomic value, with no repeating groups or multi-valued fields, and the table has a primary key to identify each row.

Part of: QCE Digital Solutions Unit 2 — Prototype Data Solutions — Module 21

Understanding First normal form (1NF) (Prototype Data Solutions) in QCE Digital Solutions Unit 2

What it does: A table in 1NF has a primary key so each row can be told apart. PRIMARY KEY on studentId does this. Each phone number gets its own row, so every field holds one atomic value. This removes the multi-valued field. The second statement describes 2NF. 1NF is about atomic values and no repeating groups. Making the field larger does not make it atomic. Split the values into separate rows (often in a new table). Atomic means the smallest useful piece of data. Separate first and last names let you sort and search more easily. The second design repeats item and quantity columns, which is a repeating group. The first has one value per field and a primary key.

Common mistakes: phone1 and phone2 are repeating columns, a repeating group, so the table breaks 1NF. It should have one phone column holding one value per row. Without PRIMARY KEY nothing identifies each row, and 1NF needs a primary key. It should be bookId INTEGER PRIMARY KEY.

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) (Prototype Data Solutions) to a class →

This module's key terms are also in a printable crossword (Modules 305–336) 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 field Subjects containing 'Maths, English, Science' in one cell. Is it in 1NF?
No - the field holds several values, so it is not atomicYes - all the subjects are stored together neatly in one field
Matches
Students(studentId, name, subjects) where subjects = 'Maths, English, Art'
↓
The subjects field holds a list of values. It is not atomic, so the table is not in 1NF. Give each subject its own row.
Applying
Meanings
Term
1NF
Meaning
First normal form: every field holds one atomic value, with no repeating groups or multi-valued fields, and the table has a primary key to identify each row.
Practice
CREATE TABLE studentSubject (
Testing
Gaps
studentId INT ,
Check Answer
Spots
2 id INTEGER PRIMARY KEY,
3 phone1 TEXT, phone2 TEXT,

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

The 'Peregrine Falcon' badge — Operator Ace, 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 field Subjects containing 'Maths, English, Science' in one cell. Is it in 1NF?
A. No - the field holds several values, so it is not atomic ✓
B. Yes - all the subjects are stored together neatly in one field
1NF needs one atomic value per field. A list in one cell is a multi-valued field and must be split so each value has its own row.
2. Which table design follows 1NF?
A. One row per student with the subjects separated by commas in one cell
B. One row per student and subject, each cell holding a single value ✓
Each field must hold a single atomic value. Comma-separated lists in a cell break 1NF.
3. A table has columns Phone1, Phone2 and Phone3 for each customer. What does this show?
A. A repeating group, which breaks 1NF ✓
B. Good 1NF design, because every cell holds one value
Each cell is atomic, but repeating columns for the same kind of data are a repeating group. Move phone numbers to their own table linked by a foreign key.
4. Which statement about 1NF is correct?
A. Every non-key field depends on the whole primary key
B. Every field holds one atomic value and there are no repeating groups ✓
The second statement describes 2NF. 1NF is about atomic values and no repeating groups.
5. A table has no primary key, so two rows could be identical. Does it meet 1NF?
A. No - each row must be uniquely identified by a primary key ✓
B. Yes - 1NF only cares about the values in each field
In this course 1NF also needs a primary key present so each row can be identified.

Match the Pairs

phone = '0412 345 678, 0498 765 432' → A multi-valued field: two values are stored in one field, so the table breaks 1NF
subject1, subject2, subject3 → Repeating columns: the same kind of data is spread across numbered columns, a repeating group that breaks 1NF
To meet 1NF, every row must be identified by a primary key → CREATE TABLE students (studentId INTEGER PRIMARY KEY, firstName TEXT);
A 1NF fix for a multi-valued field: store one value per row, not a list in one field → INSERT INTO studentPhones VALUES (1, '0412 345 678'), (1, '0498 765 432');
Students(studentId, name, subjects) where subjects = 'Maths, English, Art' → The subjects field holds a list of values. It is not atomic, so the table is not in 1NF. Give each subject its own row.
Orders(item1, item2, item3, qty1, qty2, qty3) with no primary key → The table has repeating columns and no primary key. Move the items into rows with one item each, and add a primary key such as orderId.

Key Term

1NF
First normal form: every field holds one atomic value, with no repeating groups or multi-valued fields, and the table has a primary key to identify each row.

Fill in the Blanks

1. CREATE TABLE Student ( studentId INT PRIMARY KEY, firstName VARCHAR(30));
Hint: Type the two-word constraint that makes this field identify each row uniquely, which 1NF requires.
2. -- 1NF: one subject per row, not a list INSERT INTO Enrolment VALUES (1, 'Maths');
Hint: Type the keyword that introduces the single atomic value stored in the new row.
3. CREATE TABLE Phone ( phoneId INT PRIMARY KEY, phone VARCHAR(15));
Hint: Type the data type for a single text value, so one column holds one phone number instead of repeating columns.
4. SELECT * FROM Student WHERE subjects LIKE '%,%';
Hint: Type the pattern-matching operator used to find multi-valued fields that contain a comma-separated list.
5. CREATE TABLE Enrolment ( studentId INT, subjectId INT, PRIMARY KEY (studentId, subjectId));
Hint: Type the second field that combines with the student field to form a composite primary key.

Spot the Error

Question 1
A CREATE TABLE Student (
B id INTEGER PRIMARY KEY,
C phone1 TEXT, phone2 TEXT,
D name TEXT
E );
Line C — phone1 and phone2 are repeating columns, a repeating group, so the table breaks 1NF. It should have one phone column holding one value per row.
Question 2
A CREATE TABLE Book (
B bookId INTEGER,
C title TEXT,
D author TEXT
E );
Line B — Without PRIMARY KEY nothing identifies each row, and 1NF needs a primary key. It should be bookId INTEGER PRIMARY KEY.
Question 3
A INSERT INTO Pet VALUES
B (1, 'Rex', 'Ball'),
C (2, 'Mia', 'Rope'),
D (3, 'Bo', 'Bone, Ball');
Line D — The toy field holds two values ('Bone, Ball'), so it is not atomic. It should hold one value, such as 'Bone', with the second toy stored in its own row.
Question 4
A CREATE TABLE Player (
B s1 INT, s2 INT, s3 INT,
C name TEXT,
D playerId INT PRIMARY KEY
E );
Line B — s1, s2 and s3 are repeating columns, a repeating group, which breaks 1NF. It should be a single score column, with each score in its own row.
Question 5
A INSERT INTO Hobby VALUES
B (NULL, 'Ava', 'Chess'),
C (2, 'Liam', 'Surf');
Line B — The primary key value cannot be NULL because it must identify every row. The row should start with a real id, such as 1.

Student with a class code? Join your class →