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

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

4.2 Relational Schema Derived from the E‑R Diagram

4.3 Functional Dependencies for Each Relation

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

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.)