Normalize data to third normal form (3NF)

10 Database and File Concepts 🚀

Objective: Normalize data to Third Normal Form (3NF) 📊

Normalisation is like organising a messy backpack. You want to keep everything in its own compartment so you can find it quickly and avoid duplication. In databases we use Normal Forms to decide how to split data into tables.

1️⃣ First Normal Form (1NF) – No Repeating Groups

• Each column holds a single value. • No lists or arrays inside a cell. • Think of each row as a single, unique item (like a single Lego brick).

2️⃣ Second Normal Form (2NF) – Remove Partial Dependencies

• The table must already be in 1NF. • Every non‑key attribute must depend on the whole primary key, not just part of it. • If a key is made of two columns (e.g., OrderID + ProductID), nothing should depend only on OrderID or only on ProductID.

3️⃣ Third Normal Form (3NF) – Remove Transitive Dependencies

• The table must already be in 2NF. • No non‑key attribute should depend on another non‑key attribute. • In other words, every attribute is directly linked to the primary key, not indirectly through another attribute.

📚 Example: From Messy to Clean

Suppose we have a single table that stores orders and customer details:

Issues: • Customer details repeat for every product in the same order (violates 1NF). • Customer details depend on OrderID but not on ProductID (violates 2NF). • CustomerAddress depends on CustomerName (transitive dependency, violates 3NF).

?? Normalised Tables (3NF)

1. Orders – OrderID (PK), CustomerID, OrderDate
2. OrderItems – OrderID (FK), ProductID (FK), Quantity
3. Customers – CustomerID (PK), Name, Address, Phone
4. Products – ProductID (PK), Name, Price

Now each table has a single primary key and all non‑key data depends only on that key. No duplication, no hidden dependencies – just clean, organised data.

🔑 Quick Checklist for 3NF

• ✔️ Table is in 1NF (atomic values)
• ✔️ Table is in 2NF (no partial dependencies)
• ✔️ No transitive dependencies: if A → B and B → C, then A → C must be removed
• ✔️ Each non‑key attribute is fully functionally dependent on the whole primary key

🧩 Practice Problem

You have a table StudentGrades with columns: StudentID, CourseID, CourseName, Grade, InstructorName. Task: Normalise this table to 3NF. Hint: Think about which attributes belong to students, courses, and instructors separately.

Happy normalising! 🎉 Remember: a tidy database is like a tidy classroom – everything has its place and you can find it fast.