Understanding Normalization
First Normal Form (1NF):
Eliminate Repeating Groups: Ensure each table has a primary key and that all attributes are atomic (indivisible).
Example
In a library database, separate borrower information from book details.
Second Normal Form (2NF):
Remove Partial Dependencies: Ensure that all non-key attributes are fully dependent on the primary key.
Example
In a transaction table, separate item details from customer information.
Third Normal Form (3NF):
Eliminate Transitive Dependencies: Ensure that non-key attributes depend only on the primary key.
Example
In an e-commerce database, separate artist information from item details.
Real-World Scenario: E-Commerce Database
- Unnormalized Data:
- Table: Transactions
- Attributes: Customer ID, Customer Email, Item ID, Item Description, Artist Name, Artist Email
- First Normal Form (1NF):
- Separate Tables:
- Customer: Customer ID, Customer Email
- Item: Item ID, Item Description, Artist Name, Artist Email
- Transaction: Customer ID, Item ID, Quantity, Date
- Separate Tables:
- Second Normal Form (2NF):
- Remove Partial Dependencies:
- Item: Item ID, Item Description, Artist ID
- Artist: Artist ID, Artist Name, Artist Email
- Transaction: Customer ID, Item ID, Quantity, Date
- Remove Partial Dependencies:
- Third Normal Form (3NF):
- Eliminate Transitive Dependencies:
- Transaction: Customer ID, Item ID, Quantity, Date
- Item: Item ID, Item Description, Artist ID
- Artist: Artist ID, Artist Name, Artist Email
- Eliminate Transitive Dependencies:
Example
Real-World Scenario: Library Database
- Unnormalized Data:
- Table: Borrowing Records
- Attributes: Library Name, Borrower ID, Book Name, Date Borrowed, Fees
- First Normal Form (1NF):
- Separate Tables:
- Library: Library Name, Location
- Loan: Borrower ID, Book Name, Date Borrowed, Fees
- Separate Tables:
- Second Normal Form (2NF):
- Remove Partial Dependencies:
- Book: Book Number, Book Name, Fiction
- Loan: Borrower ID, Book Number, Date Borrowed, Fees
- Remove Partial Dependencies:
- Third Normal Form (3NF):
- Eliminate Transitive Dependencies:
- Borrower: Borrower ID, Borrower Name, Fees
- Loan: Borrower ID, Book Number, Date Borrowed
- Book: Book Number, Book Name, Fiction
- Eliminate Transitive Dependencies:
Key Principles of Normalization
- Data Integrity: Ensures that data is accurate and consistent across the database.
- Elimination of Redundancy: Reduces duplicate data, saving storage space and minimizing update anomalies.
- Improved Query Performance: Simplifies queries by organizing data into logical, related tables.
Tip
- Identify entities and relationships: Use ERDs to visualize the structure of the database.
- Focus on functional dependencies: Ensure that each attribute depends on the key, the whole key, and nothing but the key.
- Iterate through normal forms: Progressively refine the database from 1NF to 3NF, addressing anomalies at each stage.
