Database normalisation (Cambridge International AS Computer Science)

Normalised design

What is a normalised design?

• A normalised design is a database structure that:

  • Organises data efficiently
  • Eliminates unnecessary duplication
  • Ensures data integrity
  • Supports easy updates and accurate relationships

• You may be asked to produce a normalised design based on:

  • A written description of a system
  • A table of unnormalised data
  • A set of existing flat tables

• You need to be able to:

  • Identify repeating groups or duplicated data
  • Break down data into separate related tables
  • Assign appropriate primary keys and foreign keys
  • Show the database in 1NF, 2NF, and 3NF
  • Clearly show relationships between the tables

Step-by-step process

Example

• A student is enrolled on multiple courses

• Each course is taught by a teacher

• The table includes: StudentName, StudentID, CourseID, CourseName, TeacherName

• You would:

  • Identify Entities: Student, Course, Teacher

  • Create tables:

    • Student (StudentID, StudentName)
    • Course (CourseID, CourseName, TeacherID)
    • Teacher (TeacherID, TeacherName)
    • A link table: StudentCourse (StudentID, CourseID) – handles many-to-many

Exam Hint

• Always check for repeating fields or duplicated values
• Watch for composite keys in link tables (especially many-to-many relationships)
• Check that all fields in a table depend only on the key, the whole key, and nothing but the key
• Label each stage of your work (1NF → 2NF → 3NF)

First Normal Form (1NF)

What is first normal form?

• 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

Table: Customers

• 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

Table: Customers

Second Normal Form (2NF)

What is second normal form?

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

  • Fulfil all 1NF requirements

  • Partial dependencies can only occur when the primary key is a composite key, so a 1NF table with a single-field primary key is already in 2NF

  • 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

Table: Course

• To turn this table into second normal form we will ensure:

  • Course Title is moved into its own Course table
  • A Session table is created to use the full key (Course, Date) for all time-specific info
  • No partial dependencies remain

Table: Course

Table: Session

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

    • 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, linked using a foreign key

• In this table below, Description depends on Certificate, which is not the primary key (FilmID → Certificate → Description)

• This is a transitive dependency, so this table is not in third normal form

• To turn this table into third normal form we will ensure:

  • Transitive dependency removed by separatingDescription into its own table
  • The Film table only stores fields that directly depend on the key (FilmID)
  • A foreign key (Certificate) is used to maintain the relationship

Table: Film

Table: Certificate

Worked Example Match each normal form to its definition:

First Normal Form (1NF)	All fields are fully dependent on the primary key.
Second Normal Form (2NF)	There are no repeating groups of attributes.
Third Normal Form (3NF)	There are no partial dependencies.

[1]

Final answer: Answer

First Normal Form (1NF)	There are no repeating groups of attributes.
Second Normal Form (2NF)	There are no partial dependencies.
Third Normal Form (3NF)	All fields are fully dependent on the primary key.