Overview
This lecture introduces the rules and methods for identifying database normal forms, focusing on how to evaluate and correct violations in poorly designed tables.
Relational Model & Relation Rules
- A relation is a table meeting eight specific rules in database design.
- Each table should contain information about only one type of thing (entity).
- Each row represents a single instance of that thing (e.g., a student).
- Each column must relate directly to the entity type of the table.
- All values in a column must have the same data type.
- Each cell should hold only one value (no multi-valued cells).
- Row and column order cannot be depended on; order doesn't matter.
- No duplicate rows are permitted.
- Most important: all columns must contain data about a single entity.
Violations Example
- Having mixed data types in a column is a violation.
- Storing multiple values in one cell or splitting related data across rows violates rules.
- Duplicate records are not allowed.
First Normal Form (1NF)
- A table must satisfy all relation rules and have a primary key (unique identifier).
- All non-key columns must show a functional dependency on the primary key.
- Functional dependency: Knowing the key lets you determine all column values.
- Data unrelated to the key should be moved to another table.
Second Normal Form (2NF)
- Applies only to tables with compound (multi-attribute) primary keys.
- All non-key columns must be fully dependent on the entire primary key, not just part.
- Partial dependency (when a column is determined by part of the key) violates 2NF.
Third Normal Form (3NF)
- No transitive dependencies: non-key columns cannot depend on other non-key columns.
- Every non-key attribute must depend only on the key, the whole key, and nothing but the key.
- Transitive dependency example: Winner’s date of birth linked via Winner, not directly by primary key.
Boyce-Codd Normal Form (BCNF)
- BCNF tightens the 3NF rule to handle overlapping candidate keys.
- Every determinant (any attribute on which others depend) must be a candidate key.
- Overlapping compound primary keys and candidate keys indicate a potential BCNF problem.
Key Terms & Definitions
- Relation — A table that satisfies eight foundational database rules.
- Primary Key — Unique identifier for each row in a table.
- Functional Dependency — When a column’s value is determined by the primary key.
- Compound Primary Key — Primary key composed of more than one column.
- Transitive Dependency — When a non-key column depends on another non-key column via the primary key.
- Candidate Key — An attribute or set of attributes that could serve as a primary key.
Action Items / Next Steps
- Review these normal form rules and practice identifying violations in sample tables.
- Prepare for part two of the tutorial.