database-normalisation

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

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

  • 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

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, 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