Overview
This lecture outlines a step-by-step method for completing a normalization exercise, involving the identification of primary keys, table creation, and linking tables using keys.
Step 1: Identify Potential Primary Keys
- Review the variable list and highlight those that could serve as primary keys (unique identifiers like SSN, reservation ID, email).
- The number of potential primary keys suggests a maximum number of tables needed.
- Consider that some candidate keys may belong in the same table.
Step 2: Transfer Keys to Proposed Tables
- Move highlighted primary keys into candidate tables on paper, grouping related keys together.
- Strike through variables in your original list once transferred to avoid duplication.
- Name each table (e.g., student table, reservation table) for clarity.
Step 3: Identify Functional Dependencies
- Go through the variable list to spot functional dependencies (values that can be computed from others, like price after tax).
- Eliminate columns that do not need to be stored because they're derivable.
- Strike through these dependent variables in your list.
Step 4: Assign Variables to Tables
- Assign each remaining variable to a table based on the appropriate primary key.
- Continue striking through variables as they're allocated to avoid missing any.
Step 5: Balance Table Sizes
- Expect some tables (like reservation or transaction tables) to be larger, containing most variables.
- Master data tables (e.g., customer or supplier) will have fewer, more permanent entries.
Step 6: Link Tables with Foreign Keys
- Link tables by connecting primary keys from "peripheral" tables as foreign keys in larger tables.
- Apply the "number of tables minus one" rule for the number of foreign key connections needed.
- Ensure adding foreign keys does not violate third normal form (3NF).
Key Terms & Definitions
- Primary Key — A unique identifier for each record in a table.
- Functional Dependency — A relationship where one value is determined by another.
- Foreign Key — A field in a table that is a primary key in another table, used to link tables.
- Third Normal Form (3NF) — A database normal form where no non-prime attribute is transitively dependent on a primary key.
Action Items / Next Steps
- Practice following these steps on normalization exercises.
- Prepare for the next video, which covers normalizing data for students.