Lecture 7: Database Normalization Tutorial

1. The Scenario: An Unnormalized Table

The lecture practically demonstrates normalization using a deeply flawed Student-Course spreadsheet. One massive table attempts to track Student IDs, Names, Skills, multiple courses taken simultaneously (Course1, Course2, Course3), Instructors, and Section details.

• The Flaws:
  • Repeating Groups: "Course1, Course2, Course3" represent the exact same entity duplicated horizontally across columns.
  • Multi-valued Attributes: The "Student Skills" cell is stuffed with comma-separated values like "SQL, C#", violating the most basic rule of database atomicity.

2. Achieving First Normal Form (1NF)

The Rule: Every single cell must contain one atomic value, and repeating column groups must be utterly eliminated to guarantee every row is unique. The Fix: The multi-valued "SQL, C#" cell is split vertically into distinct rows. The horizontal repeating groups (Course1, Course2) are collapsed into a single CourseID column. Because a student can have many skills, a brand new StudentSkills table is created containing StudentID and Skill.

3. Achieving Second Normal Form (2NF)

The Rule: The table must be in 1NF, and all Partial Dependencies must be destroyed. The Fix: The main table now utilizes a Composite Primary Key: StudentID + CourseID. However, attributes like Course Name and Instructor depend entirely on the CourseID alone, completely ignoring the StudentID. This is a textbook partial dependency. To fix this, a dedicated Courses table is born. A junction table named StudentCourses is left behind, containing only the foreign keys to resolve the Many-to-Many relationship.

4. Achieving Third Normal Form (3NF)

The Rule: The table must be in 2NF, and all Transitive Dependencies must be annihilated. The Fix: In the refined Students table, we notice the SectionName column. However, SectionName describes the SectionID, not the StudentID directly. This indirect reliance is a transitive dependency. Consequently, a standalone Sections table is created. The Students table keeps the SectionID merely as a Foreign Key.

5. The Final Normalized Architecture

Through rigorous mathematical division, the chaotic initial table is beautifully decomposed into five highly efficient, zero-redundancy tables:

1. Students Table: Contains student details and a foreign key linking to their Section.
2. Sections Table: Contains specific section metadata (1-to-1 relationship with the student).
3. Courses Table: Contains core course data and the instructor's name.
4. StudentCourses Table: A bridge table containing a composite PK to resolve the massive Many-to-Many relationship between students and courses.
5. StudentSkills Table: A table to resolve the 1-to-Many relationship of a student holding multiple skills.