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

8.1 Database Concepts – 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).

Key Concepts

• Functional Dependency (FD): For attributes X and Y in a relation R, X → Y means that if two tuples have the same values for X, they must also have the same values for Y.
• Candidate Key: A minimal set of attributes that uniquely identifies a tuple in a relation.
• Prime Attribute: An attribute that is part of any candidate key.
• Third Normal Form (3NF): A relation R is in 3NF if, for every non‑trivial FD X → Y in R, at least one of the following holds:

  1. X is a superkey of R, or
  2. Y is a prime attribute (i.e., part of some candidate key).

Example Scenario

Consider a university database that stores information about courses, lecturers, and the rooms in which courses are taught.

Table 1 – COURSE_OFFERING

Functional Dependencies

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

Analysis of COURSE_OFFERING

1. Candidate Key: {CourseID, Semester} (minimal set that determines all other attributes).
2. FD 1: {CourseID, Semester} → LecturerID, RoomNumber, Capacity.

   The left‑hand side is a candidate key → satisfies 3NF.

3. FD 2: RoomNumber → Capacity.

   Here, RoomNumber is not a superkey, and Capacity is not a prime attribute (it does not belong to any candidate key).

   Therefore this FD violates 3NF.

Conclusion: COURSE_OFFERING is NOT in 3NF. To achieve 3NF we would decompose the relation into:

• COURSE_OFFERING(CourseID, Semester, LecturerID, RoomNumber)
• ROOM(RoomNumber, Capacity)

Table 2 – LECTURER

Functional Dependencies

• LecturerID → Name, Department, OfficePhone

Analysis of LECTURER

1. Candidate Key: LecturerID.
2. The only non‑trivial FD has a left‑hand side that is a candidate key, so the relation satisfies 3NF.

Conclusion: LECTURER is in 3NF.

Table 3 – ENROLLMENT

Functional Dependencies

• {StudentID, CourseID, Semester} → Grade

Analysis of ENROLLMENT

1. Candidate Key: {StudentID, CourseID, Semester}.
2. The only FD has the candidate key on the left‑hand side, so the table is in 3NF.

Conclusion: ENROLLMENT is in 3NF.

Summary Checklist for 3NF

• Identify all functional dependencies.
• Determine all candidate keys.
• For each FD X → Y, verify that X is a superkey or Y is a prime attribute.
• If any FD violates the rule, decompose the table until all resulting tables satisfy 3NF.