Data Normalisation (OCR A Level Computer Science): Revision Note

What is normalisation?

• In A Level Computer Science, normalisation is the process of:

  • Organising a database to reduce data duplication

  • Improve data accuracy and consistency

• Achieved by applying a set of guidelines (forms), each with specific rules and requirements

• Enhances database efficiency and maintainability

• Provides consistency within the database

First Normal Form (1NF)

What is first normal form?

• For a table to be in first normal form it must:

  • Contain only atomic values

  • Have no repeating groups

  • Have a primary key

• An atomic value is a single, indivisible piece of data, so a field must not hold two items of data such as a full name

• A repeating group is when a field contains multiple values, such as a list of phone numbers in one field

• Every record must be uniquely identified by a primary key

Example

• This table is not in first normal form

• The name field is not atomic because it stores the forename and surname together

• There is no primary key, so two customers with the same details could not be told apart

• This table is in first normal form

• The name is now split into forename and surname, so every value is atomic

• customer_id is the primary key, so every record is uniquely identified

Second Normal Form (2NF)

What is second normal form?

• For a table to be in second normal form it must:

  • Fulfil all 1NF requirements

  • Have no partial dependencies

• A partial dependency can only happen when the primary key is made up of two or more fields

• A partial dependency is when an attribute depends on only part of the primary key rather than the whole key

• Every attribute that is not part of the primary key must depend on the whole primary key

• Attributes that depend on only part of the primary key should be moved into a separate table

• If the primary key is a single field, a table in 1NF is automatically in 2NF

Example

• In this table the primary key is made up of two fields, Course and Date

• Course Title depends only on Course, because SQL101 is always "SQL Basics" regardless of the Date

• This is a partial dependency, so the table is not in second normal form

• Room depends on the whole primary key, because SQL101 runs in different rooms on different dates

• To put the table into 2NF, Course Title would move to a new Course table with Course as its primary key

Third Normal Form (3NF)

What is third normal form?

• For a table to be in third normal form it must:

  • Fulfil all 2NF requirements

  • Have no transitive dependencies

• A transitive dependency is when an attribute depends on another attribute that is not the primary key

• Every attribute that is not part of the primary key must depend on the primary key and nothing else

• Attributes with a transitive dependency should be moved into a separate table

• The new table is linked to the original table using a foreign key

Example

• In this table the primary key is FilmID

• Description depends on Certificate, because a certificate of 18 always means "Eighteen and over" whatever the film is

• This is a transitive dependency, because Description depends on FilmID only indirectly, through Certificate

• This means the table is not in third normal form

• To put the table into 3NF, Certificate and Description would move to a new Certificate table, with Certificate staying in the Film table as a foreign key