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

8.1 Database Concepts: 3NF Overview 🚀

What is 3NF?

In database design, Third Normal Form (3NF) is a rule that helps us keep tables tidy and avoid confusing data. A table is in 3NF when:

1. It is already in Second Normal Form (2NF) (no partial dependencies).
2. There are no transitive dependencies – a non‑key column should not determine another non‑key column.

Think of 3NF like a clean room: every item has its own spot, and nothing is left hanging on something that could change later.

Why 3NF matters

• 🔄 Reduces data duplication – less wasted space.
• 🛠️ Improves update performance – changes happen in one place.
• 🧹 Prevents data anomalies – no “stale” or inconsistent information.

Example Tables – Are They in 3NF?

Example 1: Students & Courses (Not 3NF)

Imagine a table that keeps track of which student is taking which course, and also stores the course name in the same row.

Here, StudentID → CourseID and CourseID → CourseName. The non‑key column CourseName depends on CourseID, which is itself dependent on StudentID. This is a transitive dependency, so the table is not in 3NF.

Example 2: Enrollments (3NF)

Now we split the data into two tables: one for students, one for courses, and a linking table that only records the relationship.

All non‑key attributes (Grade) depend on the whole composite key (StudentID, CourseID), and there are no transitive dependencies. Therefore, this table is in 3NF.

Example 3: Orders (Not 3NF)

Consider an Orders table that stores product details inside the same row.

Here, ProductID → ProductName creates a transitive dependency: OrderID → ProductID → ProductName. The table is not in 3NF.

Example 4: OrderDetails (3NF)

We separate product information into its own table and keep only the essential fields in the order table.

All non‑key attributes depend on the entire composite key (OrderID, ProductID), and there are no transitive dependencies. This table is in 3NF.

Quick Checklist for 3NF

1. Is the table already in 2NF? (No partial dependencies.)
2. Do any non‑key columns determine other non‑key columns? (No transitive dependencies.)
3. If both answers are yes, the table is in 3NF.

Remember the Library Analogy 📚

Think of each table as a shelf in a library:

• Each shelf (table) holds books (rows) that belong together.
• Every book has a unique identifier (primary key) – like a library card number.
• All information about a book should be on that shelf; if you need to know the author’s address, put it on a separate shelf.
• When you move a book to a new shelf, you only need to update one place, keeping the library tidy.

Your Turn!

Try designing a small database for a school cafeteria. List the tables you would create, decide on primary keys, and check if each table meets the 3NF criteria. Good luck, future database designers! 🎓