Database Normalization Lecture Notes

Introduction to Normalization

• Normalization is a database design technique.
• It involves structuring a database to reduce redundancy and improve data integrity.
• Normal forms range from First Normal Form (1NF) to Fifth Normal Form (5NF).
• Normalization is crucial for preventing bad database designs.
• Goals of normalization:
  • Avoid redundant information.
  • Ensure data consistency.
  • Simplify database structures.
  • Protect against anomalies (insertion, update, deletion).

First Normal Form (1NF)

• Requirements:
  1. No using row order to convey information.
  2. No mixing data types within a single column.
  3. Must have a primary key.
  4. No repeating groups.
• Examples:
  • A table with mixed data types violates 1NF.
  • A table without a primary key or with repeating groups is not in 1NF.

Second Normal Form (2NF)

• Builds on 1NF: Every non-key attribute must be dependent on the entire primary key.
• Identifies issues like:
  • Deletion anomaly: losing data unintentionally upon deletion.
  • Update anomaly: inconsistencies that arise when updating data.
  • Insertion anomaly: inability to add new data without additional information.
• Example: Splitting Player_Rating into a separate table from Player Inventory.

Third Normal Form (3NF) and Boyce-Codd Normal Form (BCNF)

• 3NF: Every non-key attribute must depend on the key, the whole key, and nothing but the key (no transitive dependencies).
• BCNF: Slightly stronger; every attribute must depend on the whole key.
• Problems resolved by creating separate tables for attributes that transitively depend on a primary key.

Fourth Normal Form (4NF)

• Focuses on eliminating multivalued dependencies that are not on the primary key.
• Example: Splitting combinations of models, colors, and styles into separate tables.

Fifth Normal Form (5NF)

• Ensures that tables cannot be logically deduced from joining multiple tables.
• Example: Separate tables are created for brand preferences, flavor preferences, and brand-flavor offerings.

Summary

• Normalization from 1NF to 5NF involves progressively stricter criteria to organize data.
• Ensures structural integrity, data consistency, and reduces redundancy.
• Follow guidelines for each normal form to achieve fully normalized databases.

Conclusion

• Importance of adhering to normalization principles.
• Encouragement to ask questions and suggest topics for further clarification.