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

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

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

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

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

4. True or false: a table in 1NF must have a key that identifies each row.

A False

B True

5. Which column name suggests a repeating group that breaks 1NF?

A Hobby1, Hobby2, Hobby3

B HobbyName

Match the Pairs

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

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

Definitions

First normal form (1NF)

Fill in the Blanks

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

CREATE TABLE student ( studentId INT ___________, name VARCHAR(30));

INSERT INTO enrolment VALUES (1, 'Maths'); INSERT INTO enrolment VALUES (_, 'Art');

ALTER TABLE student ___________ phone2;

SELECT subject FROM enrolment WHERE _________ = 1;

CREATE TABLE phone ( studentId INT, number VARCHAR(10), PRIMARY KEY (studentId, ______));

Spot the Error

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

Question 1

A INSERT INTO Hobby VALUES

B (1, 'chess'),

C (2, 'soccer, chess'),

D (3, 'drawing');

Question 2

A CREATE TABLE Hobby (

B stuId INT,

C hobby VARCHAR(20),

D PRIMARY KEY (stuId)

E );

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 );

Question 4

A cell = 'chess,soccer'

B hobbies = cell.split(',')

C for h in hobbies:

D print(1, cell)

Question 5

A cell = 'chess;soccer;art'

B hobbies = cell.split(',')

C for h in hobbies:

D print(h)

Answer Key

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

Section A: Quick Questions

B — No, the cell holds more than one value

A — One row per student and subject, with one mark in each row

A — Split it so each item has its own row

B — True

A — Hobby1, Hobby2, Hobby3

Section B: 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.

Section C: Definitions

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.

Section D: Fill in the Blanks

CREATE TABLE student (
  studentId INT PRIMARY KEY,
  name VARCHAR(30));

INSERT INTO enrolment VALUES (1, 'Maths');
INSERT INTO enrolment VALUES (1, 'Art');

ALTER TABLE student
DROP COLUMN phone2;

SELECT subject FROM enrolment
WHERE studentId = 1;

CREATE TABLE phone (
  studentId INT, number VARCHAR(10),
  PRIMARY KEY (studentId, number));

Section E: Spot the Error

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.

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

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.

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

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