Overview
This lecture explains Fourth Normal Form (4NF) in database normalization, focusing on multivalued dependencies and the differences between 1NF, 2NF, 3NF, and 4NF.
Fourth Normal Form (4NF) & Multivalued Dependency
- 4NF deals with cases where a single row points to multiple independent sets of data, known as multivalued dependencies.
- Example: An employee table with skills and languages where each can have multiple values and are not directly related.
- Problem: Queries combining independent attributes (e.g., "Spanish-speaking plumber") may return incorrect results.
- Inserting one new piece of information often requires adding multiple rows, indicating a 4NF violation.
- In 4NF, all multivalued facts in a table must be independent of one another.
Comparison with Other Normal Forms
- First Normal Form (1NF): Every attribute must be directly related to the key; uses functional dependency.
- Second Normal Form (2NF): Table with a compound key; all nonkey attributes must depend on the whole key.
- Third Normal Form (3NF): Attributes must depend only on the key, not on other nonkey attributes (no transitive dependency).
- Fourth Normal Form (4NF): Removes multivalued dependencies not addressed by earlier forms.
Examples
- Employee table: Employee ID, skill, and language — inserting one skill or language forces redundant row creation.
- Restaurant table: Restaurant, pizza variety, delivery area — adding a new delivery area requires duplicating each pizza variety.
Fixing 4NF Violations
- To resolve, break the table into separate tables for each multivalued attribute (e.g., EmployeeSkills and EmployeeLanguages).
Key Terms & Definitions
- Multivalued Dependency — When one attribute in a table determines multiple independent attributes.
- Functional Dependency — Relationship where one attribute uniquely determines another.
- Primary Key — Unique identifier for a table row.
- Compound Primary Key — A key composed of more than one column.
Action Items / Next Steps
- Review examples of tables with multivalued dependencies.
- Practice decomposing tables to resolve 4NF violations.