Explain why a given set of database tables are, or are not, in 3NF

8.1 Database Concepts – Normalisation to Third Normal Form (3NF)

Learning Objective

Explain, using functional dependencies, why a given set of database tables are, or are not, in Third Normal Form (3NF). In doing so you must be able to:

  • Identify the limitations of a file‑based approach.

  • Define the core relational‑DB terminology.

  • Interpret an Entity‑Relationship (E‑R) diagram and translate it into a relational schema.

  • Apply the normalisation process (1NF → 2NF → 3NF).

1. Why Relational Databases Replace File‑Based Systems

  • Redundancy & Inconsistency – The same data may be stored in several independent files. Updating one copy but not the others creates contradictory information.

  • Update Anomalies – Insertion, deletion or modification of a record often requires changes in multiple files, increasing the chance of errors.

  • Limited Query Capability – File‑based programs must read whole files sequentially; relational DBMSs provide set‑based queries (SQL) that retrieve exactly the data required.

  • Data Integrity & Security – Relational DBMSs enforce primary‑key, foreign‑key and other integrity constraints automatically, and they offer fine‑grained access control.

2. Glossary of Core Relational‑Database Terminology

TermOne‑sentence definition
EntityA real‑world object or concept that can be described by a set of attributes (e.g., a Student).
AttributeA property of an entity; each attribute has a domain of permissible values (e.g., StudentID).
TupleA single row in a relation, representing one instance of an entity.
Relation (Table)A set of tuples that all have the same attributes.
DomainThe set of all possible values that an attribute may take.
Primary Key (PK)A minimal set of attributes that uniquely identifies each tuple in a relation.
Foreign Key (FK)An attribute (or group) that references the primary key of another relation, creating a link between tables.
Candidate KeyAny minimal set of attributes that could serve as a primary key.
Composite KeyA candidate key that consists of two or more attributes.
Super‑keyA set of attributes that uniquely identifies tuples; it may contain extra (non‑essential) attributes.
Prime AttributeAn attribute that is part of at least one candidate key.
Functional Dependency (FD)For attributes X and Y in a relation R, X → Y means that any two tuples with the same X‑values must also have the same Y‑values.
Transitive DependencyAn FD X → Z where X → Y and Y → Z hold and Y is not a prime attribute.
Referential IntegrityThe rule that a foreign‑key value must match an existing primary‑key value in the referenced table (or be NULL).
IndexA data structure that speeds up retrieval of rows based on key values.

3. Normalisation Process Overview

The aim of normalisation is to organise data so that:

  • Redundancy is minimised.

  • Update anomalies are eliminated.

  • Logical data integrity is enforced.

Step‑by‑step Flowchart

  1. First Normal Form (1NF) – Eliminate repeating groups; ensure every attribute contains an atomic (indivisible) value and each record is uniquely identifiable.

  2. Second Normal Form (2NF) – After achieving 1NF, remove partial dependencies: every non‑prime attribute must depend on the *whole* of every candidate key (not just part of a composite key).

  3. Third Normal Form (3NF) – After 2NF, remove transitive dependencies: for every non‑trivial FD X → Y, either X is a super‑key or each attribute in Y is a prime attribute.

4. Worked Example – University Course Management

4.1 Entity‑Relationship Diagram

E‑R diagram showing entities Course, Lecturer, Room and relationships Offers and Assigned‑to

Figure 1 – E‑R diagram for the university scenario. Primary keys are underlined; foreign keys are italicised.

4.2 Relational Schema Derived from the E‑R Diagram

RelationAttributes (PK underlined, FK italicised)
COURSE_OFFERING

CourseID, Semester, LecturerID, RoomNumber, Capacity

LECTURER

LecturerID, Name, Department, OfficePhone

ENROLLMENT

StudentID, CourseID, Semester, Grade

ROOM

RoomNumber, Capacity

4.3 Functional Dependencies for Each Relation

RelationNon‑trivial Functional Dependencies
COURSE_OFFERING

{CourseID, Semester} → LecturerID, RoomNumber, Capacity

RoomNumber → Capacity

LECTURER

LecturerID → Name, Department, OfficePhone

ENROLLMENT

{StudentID, CourseID, Semester} → Grade

ROOM

RoomNumber → Capacity

4.4 Normalisation Checks

4.4.1 COURSE_OFFERING

  1. Candidate Key: {CourseID, Semester} (minimal set that determines all other attributes).

  2. FD 1: {CourseID, Semester} → LecturerID, RoomNumber, Capacity – left‑hand side is a candidate key → satisfies 3NF.

  3. FD 2: RoomNumber → Capacity – RoomNumber is not a super‑key and Capacity is not a prime attribute → violates 3NF (transitive dependency).

  4. Result: Not in 3NF.

  5. Decomposition to achieve 3NF:

    • COURSE_OFFERING(CourseID, Semester, LecturerID, RoomNumber)

    • ROOM(RoomNumber, Capacity)

4.4.2 LECTURER

  1. Candidate Key: LecturerID.

  2. Only FD has the candidate key on the left‑hand side → satisfies 3NF.

  3. Result: In 3NF.

4.4.3 ENROLLMENT

  1. Candidate Key: {StudentID, CourseID, Semester}.

  2. The sole FD uses the whole candidate key on the left‑hand side → satisfies 3NF.

  3. Result: In 3NF.

4.4.4 ROOM (after decomposition)

  1. Candidate Key: RoomNumber.

  2. FD: RoomNumber → Capacity – left‑hand side is the primary key → satisfies 3NF.

  3. Result: In 3NF.

5. Checklist for Evaluating 3NF

  1. List all functional dependencies in the relation.

  2. Identify all candidate keys; from them determine the set of prime attributes.

  3. For each non‑trivial FD X → Y, verify that either:

    1. X is a super‑key of the relation, or

    2. Every attribute in Y is a prime attribute.

  4. If any FD fails the test, decompose the relation into two (or more) tables that each satisfy the rule, while preserving:

    • Lossless‑join – the original data can be reconstructed by joining the decomposed tables.

    • Dependency preservation – all original functional dependencies are still enforceable.

6. Quick Reference – Normal Forms at a Glance

Normal FormKey RequirementTypical Violation
1NFAll attributes atomic; no repeating groups.Multi‑valued or composite fields.
2NFEvery non‑prime attribute fully depends on all parts of any composite candidate key.Partial dependency (e.g., A,B → C and A → D).
3NFFor every FD X → Y, X is a super‑key or Y is a prime attribute.Transitive dependency (e.g., X → Y and Y → Z where Y is non‑prime).

7. Practice Question (Exam‑style)

Relation: STUDENT_COURSE with attributes {StudentID, CourseID, Semester, LecturerID, LecturerName, RoomNumber, Capacity}

Functional Dependencies:

  • {StudentID, CourseID, Semester} → LecturerID, RoomNumber, Capacity

  • LecturerID → LecturerName

  • RoomNumber → Capacity

Tasks:

  1. State whether STUDENT_COURSE is in 3NF. Show your reasoning using the checklist.

  2. If it is not in 3NF, decompose the relation step‑by‑step until every resulting table satisfies 3NF. List the final set of tables and their primary keys.

(Use the checklist in Section 5 to guide your solution.)