KevsRobots Learning Platform

Create Databases with Python and SQLite3

75% complete

Reset my progress
Overview
Getting Started
Advanced topics
Summary and Conclusion
bonus

Third Normal Form (3NF)

3NF helps eliminate data redundancy and improves data integrity in relational databases

By Kevin McAleer,    3 Minutes

The Third Normal Form (3NF) builds on First Normal Form (1NF) and Second Normal Form (2NF). It helps eliminate data redundancy and improves data integrity in relational databases.

To achieve 3NF, the following conditions must be met:

  1. The table must be in 2NF.
  2. There should be no transitive dependencies: a non-key column must not depend on another non-key column.

In other words, every column should describe the key, not some other column.

By eliminating transitive dependencies, 3NF helps prevent update, insertion and deletion anomalies.

Example

Suppose we have a table called “Students” with the columns Student_ID, Student_Name, Tutor_ID and Tutor_Room:

Student_ID Student_Name Tutor_ID Tutor_Room
1 Alice T1 Lab 3
2 Bob T2 Room 12
3 Charlie T1 Lab 3
4 Dana T1 Lab 3

The primary key is Student_ID. Tutor_ID depends on the student, which is fine. But Tutor_Room depends on Tutor_ID, not on the student. That’s a transitive dependency: Student_ID → Tutor_ID → Tutor_Room.

This causes problems:

  • Update anomaly: if tutor T1 moves room, we must change three rows, and if we miss one the data contradicts itself
  • Insertion anomaly: we can’t record a new tutor’s room until they have a student
  • Deletion anomaly: if Bob leaves, we lose the fact that T2 is in Room 12

To normalise to 3NF, move the tutor details into their own table:

Table: Students

Student_ID Student_Name Tutor_ID
1 Alice T1
2 Bob T2
3 Charlie T1
4 Dana T1

Table: Tutors

Tutor_ID Tutor_Room
T1 Lab 3
T2 Room 12

Now each tutor’s room is stored once. Moving a tutor means changing one row, and a tutor can exist without any students. You can still get the original view back with a JOIN.

Quick quiz

In the original table, why is Tutor_Room a problem?

< Previous Next >

You can use the arrows  ← → on your keyboard to navigate between lessons.

What are you looking for?
Watch Videos Get Ideas Learn Something Read a Review Read the Blog Search
... Z Z z