Overview
This lecture explains the concept of Second Normal Form (2NF) in database normalization, focusing on its criteria, the meaning of partial dependency, and how to eliminate it with practical table examples.
Criteria for Second Normal Form (2NF)
- For a table to be in 2NF, it must first be in First Normal Form (1NF).
- The table must not have any partial dependency for it to satisfy 2NF.
Understanding Dependency and Partial Dependency
- Dependency means all non-primary key columns rely on the table’s primary key.
- A primary key uniquely identifies each row and can be a single column or a combination of columns.
- Partial dependency occurs when a column depends only on part of a composite primary key, not the whole key.
Example: Student, Subject, and Score Tables
- Student table: primary key is student ID; other columns depend fully on it.
- Subject table: primary key is subject ID; subject name depends on it.
- Score table: composite primary key is (student ID, subject ID); marks depend on both.
- Teacher name in the Score table depends only on subject ID, not student ID, causing partial dependency.
Eliminating Partial Dependency
- Remove the teacher name from the Score table to eliminate partial dependency.
- Add teacher name to the Subject table where it logically fits with the subject information.
- Optionally, create a separate Teacher table and reference it using teacher ID.
Practical Tips
- Auto-increment primary keys simplify table design and row identification.
Key Terms & Definitions
- First Normal Form (1NF) — Table structure where each field contains only atomic values and each record is unique.
- Second Normal Form (2NF) — 1NF plus elimination of all partial dependencies.
- Primary Key — Column(s) that uniquely identify each row in a table.
- Composite Key — A primary key formed by combining two or more columns.
- Dependency — Relationship where non-key attributes rely on the primary key.
- Partial Dependency — Non-key attribute depends on part of a composite key.
Action Items / Next Steps
- Practice normalizing tables to 2NF using your own examples.
- Start using auto-increment primary keys in future table designs.
- Watch the upcoming video on Third Normal Form (3NF).