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

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

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

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

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

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

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

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'		ARepeating columns: the same kind of data is spread across numbered columns, a repeating group that breaks 1NF
2subject1, subject2, subject3		BCREATE TABLE students (studentId INTEGER PRIMARY KEY, firstName TEXT);
3To meet 1NF, every row must be identified by a primary key		CThe 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.
4A 1NF fix for a multi-valued field: store one value per row, not a list in one field		DThe subjects field holds a list of values. It is not atomic, so the table is not in 1NF. Give each subject its own row.
5Students(studentId, name, subjects) where subjects = 'Maths, English, Art'		EA multi-valued field: two values are stored in one field, so the table breaks 1NF
6Orders(item1, item2, item3, qty1, qty2, qty3) with no primary key		FINSERT INTO studentPhones VALUES (1, '0412 345 678'), (1, '0498 765 432');

Definitions

1NF

Fill in the Blanks

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

CREATE TABLE Student ( studentId INT ___________, firstName VARCHAR(30));

-- 1NF: one subject per row, not a list INSERT INTO Enrolment ______ (1, 'Maths');

CREATE TABLE Phone ( phoneId INT PRIMARY KEY, phone _______(15));

SELECT * FROM Student WHERE subjects ____ '%,%';

CREATE TABLE Enrolment ( studentId INT, subjectId INT, PRIMARY KEY (studentId, _________));

Spot the Error

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

Question 1

A CREATE TABLE Student (

B id INTEGER PRIMARY KEY,

C phone1 TEXT, phone2 TEXT,

D name TEXT

E );

Question 2

A CREATE TABLE Book (

B bookId INTEGER,

C title TEXT,

D author TEXT

E );

Question 3

A INSERT INTO Pet VALUES

B (1, 'Rex', 'Ball'),

C (2, 'Mia', 'Rope'),

D (3, 'Bo', 'Bone, Ball');

Question 4

A CREATE TABLE Player (

B s1 INT, s2 INT, s3 INT,

C name TEXT,

D playerId INT PRIMARY KEY

E );

Question 5

A INSERT INTO Hobby VALUES

B (NULL, 'Ava', 'Chess'),

C (2, 'Liam', 'Surf');

Answer Key

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

Section A: Quick Questions

A — No - the field holds several values, so it is not atomic

B — One row per student and subject, each cell holding a single value

A — A repeating group, which breaks 1NF

B — Every field holds one atomic value and there are no repeating groups

A — No - each row must be uniquely identified by a primary key

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

Section C: Definitions

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.

Section D: Fill in the Blanks

CREATE TABLE Student (
  studentId INT PRIMARY KEY,
  firstName VARCHAR(30));

-- 1NF: one subject per row, not a list
INSERT INTO Enrolment VALUES (1, 'Maths');

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

SELECT * FROM Student
WHERE subjects LIKE '%,%';

CREATE TABLE Enrolment (
  studentId INT, subjectId INT,
  PRIMARY KEY (studentId, subjectId));

Section E: Spot the Error

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.

B — Without PRIMARY KEY nothing identifies each row, and 1NF needs a primary key. It should be bookId INTEGER PRIMARY KEY.

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.

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.

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.