Overview
This lecture explains the fourth normal form (4NF) violation in database normalization, focusing on multivalued dependencies and reviewing the key points of the first through fourth normal forms.
Fourth Normal Form (4NF) & Multivalued Dependencies
- 4NF violation occurs when one row of data points to multiple unrelated rows of information (multivalued dependency).
- Example: An employee table with skill and language columns has no implied relationship between specific skills and languages.
- Querying for a specific language-skill combination fails because the data is spread out across rows with no direct linking.
- Adding a new skill or language for an employee requires multiple new rows, indicating a 4NF issue.
- Another example: A restaurant with multiple pizza varieties and delivery areas requires multiple rows for every new delivery area or variety.
Examples of Normal Form Violations
- First Normal Form (1NF): All attributes must depend on the primary key; no columns should exist that are unrelated (e.g., "quantity on hand" in an employee table).
- Second Normal Form (2NF): All non-key attributes must be fully dependent on the entire composite primary key; if not, remove partial dependencies.
- Third Normal Form (3NF): No transitive dependencies—non-key attributes must not depend on other non-key attributes (e.g., department supervisor determined by department, not employee).
- Fourth Normal Form (4NF): No multivalued dependencies; data should not require inserting multiple rows for a single fact.
Key Terms & Definitions
- Fourth Normal Form (4NF) — A table is in 4NF if it has no multivalued dependencies except for a candidate key.
- Multivalued Dependency — When one attribute in a table determines multiple independent values of another attribute.
- First Normal Form (1NF) — Each attribute must contain only atomic (indivisible) values related to the primary key.
- Second Normal Form (2NF) — Non-key attributes must be fully functionally dependent on the entire primary key.
- Third Normal Form (3NF) — No transitive dependencies; non-key attributes cannot depend on other non-key attributes.
- Candidate Key — A minimal set of attributes that can uniquely identify a record.
Action Items / Next Steps
- Review your own tables for multivalued dependencies and check if adding one fact requires multiple new rows.
- Practice decomposing tables with multivalued dependencies into separate tables to achieve 4NF.
- Review definitions and examples of all four normal forms.