Home  Back Topics CD Orders  INDEX 
Data Normalisation

Normalisation

Normalisation is the process undertaken to make sure a database has no redundant data or inconsistencies.

Example:
A number of pupils are sitting some exams in a school.
The raw data is shown below in un-normalised form.

Pupil
Num 		Pupil Name DOB Exam Num Subject Level Date of
Exam 		Room
Code 		Room
Name
P99010 Jane Grey 12.03.86 CP101

EN004

AR075

Computing

English

Art

AS

GCSE

AS

15.05.01

24.05.01

12.06.01

UH

UG

UG

Hall

Gym

Gym

P99205 Tom Jones 05.11.86 CP101

MA110

PH190

Computing

Maths

Physics

AS

AS

AS

15.05.01

15.06.01

08.06.01

UH

UG

58

Hall

Gym

Science Lab

P99311 Sam Hill 16.08.86 CP101

EN004

Computing

English

AS

GCSE

15.05.01

24.05.01

UH

UG

Hall

Gym

 
 

First Normal Form

A table is in First Normal Form (1NF) if there are no repeating groups. In other words, each column must contain only a single value and each row must have an item in every column.

This can usually be done by putting the data into two tables ... separating the repeated data into a separate group.

The two tables are now...

Pupil
Num		 Name DOB Exam
Num
P99010 Jane Grey 12.03.86 CP101
P99010 Jane Grey 12.03.86 EN004
P99010 Jane Grey 12.03.86 AR075
P99205 Tom Jones 05.11.86 CP101
P99205 Tom Jones 05.11.86 MA110
P99205 Tom Jones 05.11.86 PH190
P99311 Sam Hill 16.08.86 CP101
P99311 Sam Hill 16.08.86 EN004
Exam
Num		 Subject Level Date of
Exam		 Room
Code		 Room
Name
CP101 Computing AS 15.05.01 UH Hall
EN004 English GCSE 24.05.01 UG Gym
AR075 Art AS 12.06.01 UG Gym
MA110 Maths AS 15.06.01 UG Gym
PH190 Physics AS 08.06.01 58 Science
Lab
The PUPIL Table The EXAM Table

A record of the pupil table can only be identified uniquely if we know the Pupil Number and the Exam Number. So, the keyfield of the Pupil Table is a combination of Pupil Num and Exam Num. 

The table definitions are...

PUPIL (PupilNum, Name, DOB, ExamNum)
EXAM (ExamNum, Subject, Level, Date, RoomCode, RoomName)

 

Second Normal Form (2NF)

A table is in Second Normal Form if it is in 1NF and it has no partial dependencies.

If you look at the PUPIL table above, the fields Name and DOB depend on the Pupil Number - which is only part of the keyfield. (They are not at all dependent on the Exam Number.). These are called partial dependencies.

To convert the tables into 2NF we may have to create another table which links the others.

Many-to-many relationships can be replaced by one-many-one relationships. 

can be converted to...

An extra table is created :

PUPIL (PupilNum, Name, DOB)
ENTRY (PupilNum,ExamNum)
EXAM (ExamNum, Subject, Level, Date, RoomCode, RoomName)

The tables would be as follows..

Pupil
Num		 Name DOB
P99010 Jane Grey 12.03.86
P99205 Tom Jones 05.11.86
P99311 Sam Hill 16.08.86
Pupil
Num		 Exam
Num
P99010 CP101
P99010 EN004
P99010 AR075
P99205 CP101
P99205 MA110
P99205 PH190
P99311 CP101
P99311 EN004
Exam
Num		 Subject Level Date Room
Code		 Room
Name
CP101 Computing AS 15.05.01 UH Hall
EN004 English GCSE 24.05.01 UG Gym
AR075 Art AS 12.06.01 UG Gym
MA110 Maths AS 15.06.01 UG Gym
PH190 Physics AS 08.06.01 58 Science
Lab
The PUPIL Table The ENTRY Table The EXAM Table

Put simply, any table which has a combined keyfield should not have any other fields in it.
 

Third Normal Form (3NF)

For a table to be in 3NF it must be in 2NF and have no transitive dependencies.

If you look at the EXAM table above, the Room Name field is dependent on the Room Code and not on the keyfield of the table (Exam Num). This is called a transitive dependency. These can be removed by transferring the fields into a separate table and leaving behind a foreign field (which is the keyfield of the new table).

So, the EXAM table would be split into these two tables..

Exam
Number		 Subject Level Date Room
Code
CP101 Computing AS 15.05.01 UH
EN004 English GCSE 24.05.01 UG
AR075 Art AS 12.06.01 UG
MA110 Maths AS 15.06.01 UG
PH190 Physics AS 08.06.01 58
Room
Code		 Room
Name
UH Hall
UG Gym
58 Science
Lab
The EXAM table The ROOM table

The table definitions are now...

PUPIL (PupilNum, Name, DOB)
ENTRY (PupilNum, ExamNum)
EXAM (ExamNum, Subject, Level, Date, RoomCode)
ROOM (RoomCode, RoomName)

The tables are now in 3NF.

For simple people (like me!) -

For Third Normal Form, make sure that...

  • the fields in a table are only related to that entity (ie only data about the pupil should be in the Pupil table)...except for foreign fields which are keyfields of other tables.
  • many-to-many relationships are eliminated by creating an extra table.