UN

First Normal Form: 1NF

A table is in First Normal Form if: (1) each column contains atomic values, (2) there are no repeating groups, and (3) each row is unique.

Rule 1 — each column contains atomic values

The following table is not in 1NF:

Student_IDStudent_NameCourses
101RahimDatabase, Programming
102KarimNetworking

…because the Courses column contains multiple values.

Fix — one value per cell, one course per row:

Student_IDStudent_NameCourse
101RahimDatabase
101RahimProgramming
102KarimNetworking

Rule 2 — there are no repeating groups

“No repeating groups” means the same type of information should not be stored in multiple columns.
Student_IDStudent_NameCourse_1Course_2Course_3
101RahimDatabaseProgrammingNetworking
102KarimDatabaseNULLNULL

Here Course_1, Course_2 and Course_3 are repeating groups, because they store the same type of data: course information. Note also the wasted NULLs — and the hard limit of three courses baked into the schema.

Fix:

Student_IDStudent_NameCourse
101RahimDatabase
101RahimProgramming
101RahimNetworking
102KarimDatabase

Now there is only one Course column, and each course is stored in a separate row.

Rule 3 — each row is unique

Each row in a table should be uniquely identifiable. There should not be duplicate rows.

Student_IDStudent_NameCourse
101RahimDatabase
101RahimDatabase
102KarimProgramming

The first two rows are exactly the same, so the rows are not unique.

Student_IDStudent_NameCourse
101RahimDatabase
102KarimProgramming
A better way to resolve the duplicity issue is to define a primary key. For this table the primary key can be (Student_ID, Course). This composite key ensures that the same student cannot be repeated for the same course.

Checklist for the exam

1NFsmallGiven a table, how do I show it is (or is not) in 1NF?

Next: Second Normal Form →