Overview
This lecture explains Third Normal Form (3NF) in database design, describing its rules, the concept of transitive dependencies, and how to transform tables to achieve 3NF using practical examples.
Third Normal Form (3NF) Rules
- A table is in 3NF if it is already in 2NF.
- All determinants of non-key attributes must be candidate keys (potential primary keys).
- Non-key attributes are columns that aren't part of the primary key or a composite key.
- 3NF eliminates transitive dependencies, where a non-key attribute depends on another non-key attribute instead of the primary key.
Identifying and Removing Transitive Dependencies
- A transitive dependency occurs when attribute X (primary key) determines Y, but Y is actually determined by Z (another non-key attribute).
- Example: If A is the primary key and determines B, C, D, and E, but D also determines E, then E depends transitively on A through D.
- To resolve, decompose the table: one table with A as primary key for B, C, D; another with D as primary key for E; use D as a foreign key in the first table.
Student Table Example
- Original table: ID (primary key), Student Name, Major, Adviser, Adviser's Office.
- Problem: Adviser's Office depends on Adviser, not directly on student ID (transitive dependency).
- Solution: Split into two tables—(1) Student info with Adviser as a foreign key, (2) Adviser info with Adviser's Office, Adviser as primary key; remove advisor duplicates.
Hotel Reservation Example
- Original table: Reservation Number (PK), Hotel Number, Hotel Address, Customer Number, Customer Rewards Number.
- Issues: Hotel Address depends on Hotel Number; Rewards Number depends on Customer Number.
- Solution: Create three tables—Reservations (Reservation Number, Hotel Number, Customer Number), Hotels (Hotel Number, Hotel Address), Customers (Customer Number, Rewards Number); use foreign keys to reference Hotels and Customers.
Summary of Normal Forms
- 1NF: Ensure each cell has a single value, no duplicate or blank rows.
- 2NF: Remove columns that can be derived or computed from others; eliminate partial dependencies.
- 3NF: Eliminate transitive dependencies by creating new tables for such relationships.
Key Terms & Definitions
- Candidate Key — An attribute or set of attributes that could serve as a primary key.
- Determinant — An attribute that determines another attribute’s value.
- Transitive Dependency — When a non-key attribute depends on another non-key attribute via the primary key.
- Foreign Key — An attribute in one table that refers to the primary key of another table.
- Primary Key — A unique identifier for table rows.
- Composite Key — A primary key made up of more than one attribute.
Action Items / Next Steps
- Review the next video for practice with real-world examples of tables not in 3NF.
- Practice identifying transitive dependencies and decomposing tables to achieve 3NF.