Produce a normalised database design for a description of a database, a given set of data, or a given set of tables

8.1 Database Concepts

Learning Objective

Produce a normalised database design for a description of a database, a given set of data, or a given set of tables.

Key Terminology

• Data – Raw facts and figures.
• Database – An organised collection of related data.
• DBMS (Database Management System) – Software that creates, maintains and controls access to a database.
• Relation (Table) – A set of tuples (records) sharing the same attributes (fields).
• Primary Key (PK) – A field (or combination of fields) that uniquely identifies each record.
• Foreign Key (FK) – A field that links to the primary key of another table, establishing a relationship.
• Functional Dependency (FD) – \$X \rightarrow Y\$ means that attribute(s) \$X\$ uniquely determine attribute(s) \$Y\$.
• Normalization – Process of organising tables to reduce redundancy and improve integrity.

Normalization Overview

1. First Normal Form (1NF): Eliminate repeating groups; each field contains atomic values.
2. Second Normal Form (2NF): Achieve 1NF and remove partial dependencies (non‑key attributes depend on part of a composite key).
3. Third Normal Form (3NF): Achieve 2NF and remove transitive dependencies (non‑key attributes depend on other non‑key attributes).
4. Boyce‑Codd Normal Form (BCNF): Every determinant is a candidate key.

Step‑by‑Step Normalisation Procedure

1. Identify all attributes and the primary key of the original (un‑normalised) table.
2. Check for repeating groups – if present, split them to achieve 1NF.
3. List all functional dependencies (FDs) that hold in the data.
4. Apply 2NF: For each composite key, move attributes that depend on only part of the key to a separate table.
5. Apply 3NF: Remove attributes that are dependent on other non‑key attributes.
6. Verify BCNF: Ensure every determinant is a candidate key; if not, decompose further.
7. Define primary keys and foreign keys for the resulting tables.

Illustrative Example

Consider a university’s student‑course registration system. The following un‑normalised data have been collected:

Functional Dependencies for the Example

• \$\text{StudentID} \rightarrow \text{StudentName}\$
• \$\text{CourseCode} \rightarrow \text{CourseTitle, Lecturer}\$
• \$\{\text{StudentID},\text{CourseCode},\text{Semester}\} \rightarrow \text{Grade}\$
• \$\text{CourseCode},\text{Semester} \rightarrow \text{Lecturer}\$ (if lecturer can change each semester)

Normalisation Process

1. First Normal Form (1NF)

The table already stores atomic values; there are no repeating groups. The table is in 1NF.

2. Second Normal Form (2NF)

The composite key is \$\{\text{StudentID},\text{CourseCode},\text{Semester}\}\$. The attribute StudentName depends only on StudentID, and CourseTitle, Lecturer depend only on CourseCode. These partial dependencies are removed.

3. Third Normal Form (3NF)

In the Offering table, Lecturer depends on the combination {CourseCode, Semester}. No transitive dependencies remain, so all tables are in 3NF.

4. Boyce‑Codd Normal Form (BCNF)

Each determinant in every table is a candidate key, satisfying BCNF.

Resulting Normalised Design

The final schema consists of four tables, each with clearly defined primary and foreign keys. This design eliminates redundancy (e.g., student names and course titles are stored once) and ensures data integrity through referential constraints.

Checklist for Exam Questions

• Identify all attributes and the candidate key(s).
• Write down the functional dependencies that can be justified from the description.
• Show the table in 1NF (atomic values).
• Apply 2NF – create separate tables for attributes that depend on part of a composite key.
• Apply 3NF – remove transitive dependencies.
• Confirm BCNF – ensure every determinant is a candidate key.
• Label primary keys (PK) and foreign keys (FK) in each resulting table.
• Optionally, sketch a simple ER diagram to illustrate relationships.

Common Pitfalls

• Forgetting to check for transitive dependencies when moving from 2NF to 3NF.
• Assuming a single attribute is always the primary key; composite keys are common in junction tables.
• Over‑normalising – creating too many tables can make queries unnecessarily complex.
• Missing functional dependencies that arise from business rules (e.g., “a lecturer teaches a specific course in a given semester”).

Practice Question

Given the following un‑normalised table of library loans, normalise it to BCNF and list the primary and foreign keys for each resulting table.

Use the steps outlined above to produce the final design.