Overview
This lecture explains the rules of Second Normal Form (2NF) in database normalization, provides key definitions, and demonstrates 2NF through practical examples.
Second Normal Form (2NF) Rules
- A table is in 2NF if it is already in First Normal Form (1NF).
- Every non-candidate column must depend on the entire candidate key (no partial dependencies).
- A non-candidate column is one that is not part of any candidate (primary or composite) key.
Identifying Composite Keys and Dependencies
- Composite keys are formed when no single column can serve as a unique primary key.
- Any attribute that only depends on part of a composite key violates 2NF.
- Such attributes should be separated into a new table.
Example: Invoice Table
- Table: invoice ID, line ID, product number, quantity, invoice date.
- Composite key: (invoice ID, line ID) determines product number and quantity.
- Invoice date depends only on invoice ID, not on the full composite key.
- Solution: Move invoice date to a separate table with invoice ID as the key.
Example: Housing Table
- Table: city, street, house number, house color, city population.
- Composite key: (city, street, house number) determines house color.
- City population depends only on city.
- Solution: Move city population to a separate table keyed by city.
Example: Retailer Product Data
- Attributes: product name, manufacturer, product type, country.
- Product name is unique; country depends on manufacturer, not product name.
- Solution: Create a separate table: manufacturer and country.
Example: Hotel Invoice
- Table: invoice number, check-in date, checkout date, length of stay.
- Length of stay can be calculated from check-in and checkout dates (derived dependency).
- Solution: Remove length of stay from the table entirely.
General 2NF Approach
- Move attributes that do not depend on the whole key into separate tables or remove if redundant.
- Main goal: eliminate partial dependencies to achieve 2NF.
Key Terms & Definitions
- First Normal Form (1NF) — all table entries are atomic, with no repeating groups.
- Second Normal Form (2NF) — table is in 1NF, and all non-candidate columns depend on the whole candidate key.
- Candidate Key — a column or set of columns that can uniquely identify a row.
- Composite Key — a primary key consisting of multiple columns.
- Partial Dependency — a non-key attribute dependent on part of a composite key.
- Non-candidate column — a column not part of any candidate key.
Action Items / Next Steps
- Practice decomposing tables to achieve 2NF using additional examples.
- Prepare for the next lecture on Third Normal Form (3NF).