Data Normalisation (OCR A Level Computer Science)

First Normal Form (1NF)

What is Normalisation?

• Normalisation is the process of organising a database to reduce data duplication and 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)

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

• Contain atomic values

  • Each column in a table must contain single, indivisible values

• Have no repeating groups

  • Columns must not contain arrays or lists of values

• Have unique column names

  • Each column must have a unique name within the table

• Have a unique identifier (primary key)

  • Each row must have a unique identifier to distinguish it from other rows

This customers table below has no primary key and the name is stored in one field so is not atomic. This table is not in first normal form

This customers table below has a primary key and the name is stored in two fields so it is atomic 

This table is in first normal form

Second Normal Form (2NF)

Second Normal Form (2NF)

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

• Fulfil all 1NF requirements

• Only apply to tables with a compound primary key

• Have full functional dependency

  • All non-prime attributes (attributes not part of the primary key) must be fully dependent on the primary key

• Have no partial dependencies

  • Non-prime attributes must not depend on only part of the primary key (in case of a composite primary key)

  • Separate tables should be created for partially dependent attributes

In this table below, Course Title only depends on part of the compound primary key (the course code) and not the Date so this table is not in second normal form

Third Normal Form (3NF)

Third Normal Form (3NF)

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

• Fulfil all 2NF requirements

• Have no transitive dependencies

  • Non-prime attributes must not depend on other non-prime attributes

• Have each non-prime attribute dependent solely on the primary key, not on other non-prime attributes

• Have separate tables for attributes with transitive dependencies, and the tables should be linked using a foreign key

In this table below, the certificate depends on the title - this a transitive dependency and so this table is not in third normal form