Overview
This lecture introduces the concept of the First Normal Form (1NF) in database normalization, focusing on keys, atomicity, and the structure of tables.
Key Principles of Normalization
- Every table must have a unique key to identify each row.
- Candidate keys are potential unique identifiers; the primary key is selected from them.
- Composite (compound) keys are combinations of columns used when no single unique attribute exists.
- Surrogate keys are system-generated unique identifiers, like auto-increment numbers.
- No two rows in a table can be identical due to the unique key.
Rules of First Normal Form (1NF)
- Rows must contain data about a single entity (e.g., only employees in an employee table).
- Columns must represent attributes of that entity only.
- Each cell in the table must hold a single value (atomicity).
- All values in a column must be of the same type (no mixing data types).
- Each column must have a unique name.
- The order of rows and columns does not affect the table’s meaning.
- No duplicate rows are allowed.
Examples of 1NF Violations
- A column containing mixed data types (e.g., email, fax, and home number in one column).
- Cells holding multiple values (e.g., listing multiple degrees in one cell).
- Solutions include decomposing data into separate tables for different contact types or degrees.
Fixing 1NF Violations
- Separate mixed data into new tables (e.g., a table for fax numbers, a table for degrees).
- Use a surrogate key or composite key for tables where a single attribute doesn’t provide uniqueness.
- Deconstruct multi-valued columns so each value appears in a separate row.
Key Terms & Definitions
- Primary Key — The unique identifier for a row in a table.
- Candidate Key — An attribute, or set of attributes, that could serve as a unique identifier.
- Composite Key — A key formed by combining multiple columns to ensure uniqueness.
- Surrogate Key — A system-generated unique identifier, often auto-incremented.
- Atomicity — Each cell must contain a single, indivisible value.
- First Normal Form (1NF) — The minimal set of requirements a table must meet: unique key, atomic values, consistent columns.
Action Items / Next Steps
- Review your tables to ensure they conform to 1NF rules.
- Practice decomposing tables that violate 1NF (e.g., those with multi-valued cells).
- Prepare for the next lecture on Second Normal Form (2NF).