5. Examples of Normalization
Recap: What is Normalization?
- First Normal Form (1NF):
- Each cell must contain only only one value
- Each row must be unique
- Second Normal Form (2NF):
- Data must depend on the entire primary key, and not just part of the primary key
- Third Normal Form (3NF):
- There can be no transitive dependencies
- A column cannot depend on another column, unless that other column is a primary key
- There can be no transitive dependencies
Example 1: Annual Tennis Tournament Winners
- Original Table
Annual Tournament Winners Table (Composite Key) tournament_name year winner winner_dob St. John's Invitational 2000 Maxwell House July 21, 1990 Toronto Open 2010 George Albertson May 1, 1987 St. John's Invitational 2011 Tiffany Masterson November 19, 1988 Vancouver Memorial 2003 Steve Miller January 30, 1980 Steps to Normalize
- 1NF: Ensure Atomic Values
Step 1: Identify and Remove Non-Atomic Values
- The table is in 1NF as each column contains atomic values. The primary key is a composite key of tournament_name and year.
- 2NF: Remove Partial Dependencies
Step 2: Verify Partial Dependencies
- The table is in 2NF as all non-key attributes are fully functionally dependent on the composite primary key (tournament_name and year).
- 3NF: Remove Transitive Dependencies
Step 3: Remove Transitive Dependencies
- The table is not in 3NF due to the transitive dependency between winner and winner_dob. To resolve this:
Normalization Steps
- Create a separate table for winners:
Winners Table winner_id winner winner_dob 1 Maxwell House July 21, 1990 2 George Albertson May 1, 1987 3 Tiffany Masterson November 19, 1988 4 Steve Miller January 30, 1980 - Update the original table to use a foreign key:
Updated Annual Tournament Winners Table tournament_name year winner St. John's Invitational 2000 Maxwell House Toronto Open 2010 George Albertson St. John's Invitational 2011 Tiffany Masterson Vancouver Memorial 2003 Steve Miller
- 1NF: Ensure Atomic Values
- Original Table
Example 2: Student Courses
- Original Table:
Student Courses Table student_id student_name courses 1 John Doe Math, Science, History 2 Jane Smith Science, English 3 Emily Johnson Math, History 4 Michael Brown English, History Steps to Normalize
- 1NF: Ensure Atomic Values
Step 1: Identify and Remove Non-Atomic Values
- The table is not in 1NF because the courses column contains a list of values. Each course should be in its own row.
- Normalized Table (1NF):
Student Courses Table (1NF) student_id student_name course 1 John Doe Math 1 John Doe Science 1 John Doe History 2 Jane Smith Science 2 Jane Smith English 3 Emily Johnson Math 3 Emily Johnson History 4 Michael Brown English 4 Michael Brown History
- 2NF: Remove Partial Dependencies
Step 2: Identify and Remove Partial Dependencies
- The table is now in 1NF but not in 2NF. The student_name attribute is dependent only on student_id and not on the full composite key (student_id, course).
Normalization Steps
- Create a separate table for students:
Students Table student_id student_name 1 John Doe 2 Jane Smith 3 Emily Johnson 4 Michael Brown - Update the courses table to use a foreign key:
Courses Table student_id course 1 Math 1 Science 1 History 2 Science 2 English 3 Math 3 History 4 English 4 History
- 3NF: Remove Transitive Dependencies
Step 3: Remove Transitive Dependencies
- There are no transitive dependencies in the students and courses tables, so we are already in 3NF.
- 1NF: Ensure Atomic Values
- Original Table:
Example 3: Customer Orders Table
- Original Table:
Orders Table Order_ID Customer_Name Customer_Address Order_Date Product Product_Price 1001 John Smith 123 Elm St 2024-01-15 Laptop $1200 1002 Jane Doe 456 Oak St 2024-01-17 Smartphone $800 1003 Emily Johnson 789 Pine St 2024-01-20 Tablet $600 1004 Michael Brown 101 Maple St 2024-01-22 Monitor $200 - 1NF: Ensure Atomic Values
Step 1: Identify and Remove Non-Atomic Values
- The table is already in 1NF as each cell contains only atomic values.
- 2NF: Remove Partial Dependencies
Step 2: Verify and Remove Partial Dependencies
- The table is in 2NF because all non-key attributes are fully dependent on the primary key, Order_ID.
- 3NF: Remove Transitive Dependencies
Step 3: Remove Transitive Dependencies
- Product_Price depends on Product, not directly on Order_ID. As well, Customer_Address depends on Customer_Name. Thus, there are transitive dependencies.
Normalization Steps
- Create a separate table for Customers:
Customers Table Customer_ID Customer_Name Customer_Address 1001 John Smith 123 Elm St 1002 Jane Doe 456 Oak St 1003 Emily Johnson 789 Pine St 1004 Michael Brown 101 Maple St - Create a separate table for Products:
Products Table Product_ID Product Product_Price 2001 Laptop $1200 2002 Smartphone $800 2003 Tablet $600 2004 Monitor $200 - Update the Orders table to use foreign keys:
Orders Table (3NF) Order_ID Order_Date Customer_ID Product_ID 1001 2024-01-15 1001 2001 1002 2024-01-17 1002 2002 1003 2024-01-20 1003 2003 1004 2024-01-22 1004 2004
- Original Table:
Example 4: Library Book Loans (Normalization)
- Original Table (Not in 1NF):
Library Book Loans Table Loan_ID Borrower_Name Books_Borrowed Loan_Date 2001 Emily Clark "Introduction to Programming", "Data Structures" 2024-02-01 2002 John Doe "Advanced Algorithms", "Computer Networks" 2024-02-05 2003 Sophie Adams "Database Systems", "Operating Systems" 2024-02-10 2004 Michael Lee "Web Development", "Software Engineering" 2024-02-15 - 1NF: Ensure Atomic Values
Step 1: Identify and Remove Non-Atomic Values
- The table is not in 1NF because the Books_Borrowed column contains multiple values in a single cell.
Normalization Steps
- Normalize to 1NF: Separate each book into its own row.
Library Book Loans Table (1NF) Loan_ID Borrower_Name Book_Title Loan_Date 2001 Emily Clark Introduction to Programming 2024-02-01 2001 Emily Clark Data Structures 2024-02-01 2002 John Doe Advanced Algorithms 2024-02-05 2002 John Doe Computer Networks 2024-02-05 2003 Sophie Adams Database Systems 2024-02-10 2003 Sophie Adams Operating Systems 2024-02-10 2004 Michael Lee Web Development 2024-02-15 2004 Michael Lee Software Engineering 2024-02-15
- 2NF: Remove Partial Dependencies
Step 2: Verify and Remove Partial Dependencies
- The table is not in 2NF because the Borrower_Name is dependent only on part of the composite key (Loan_ID) and not on the entire composite key (Loan_ID, Book_Title).
Normalization Steps
- Create a separate table for Borrowers:
Borrowers Table Loan_ID Borrower_Name 2001 Emily Clark 2002 John Doe 2003 Sophie Adams 2004 Michael Lee - Update the Loans table to use foreign keys:
Loans Table (2NF) Loan_ID Book_Title Loan_Date 2001 Introduction to Programming 2024-02-01 2001 Data Structures 2024-02-01 2002 Advanced Algorithms 2024-02-05 2002 Computer Networks 2024-02-05 2003 Database Systems 2024-02-10 2003 Operating Systems 2024-02-10 2004 Web Development 2024-02-15 2004 Software Engineering 2024-02-15
- 3NF: Remove Transitive Dependencies
Step 3: Remove Transitive Dependencies
- There are no transitive dependencies in the borrowers or the loans tables, so we are already in 3NF.
- Original Table (Not in 1NF):