Database Normalisation

Overview

• Definition: Process of organizing database attributes to reduce or eliminate data redundancy
• Purpose: Improves database efficiency, consistency, and accuracy
• Benefits: Easier data management, maintenance, and adaptability to business changes
• Process: Involves splitting tables into multiple linked tables to eliminate redundancy

Key Concepts

• Data Redundancy: Same data repeated in multiple places
• Issues: Unnecessarily increases database size and causes inconsistency problems
• Normal Forms: Standard methods to quantify database efficiency
• Algorithms: Exist to convert databases into normal forms
• Trade-off: Split tables must be linked during queries

Why Normalization is Needed

Primary Objective

• Eliminate data anomalies that threaten data integrity
• Prevent issues that worsen as database grows
• Develop effective database structure through systematic procedures

Types of Anomalies

Insertion Anomalies

• Problem: Cannot record one fact without also supplying an unrelated fact, because both are stored in the same table
• Example: In a single Student_Course(StudentID, CourseID, CourseName, Instructor) table, a new course cannot be added until some student enrolls in it (StudentID is part of the key)

Deletion Anomalies

• Problem: Deleting a record causes unintentional loss of an unrelated fact stored in the same row
• Example: In the same Student_Course table, deleting the last student enrolled in a course also deletes the only record of that course’s name and instructor
• Note: ON DELETE CASCADE removing a customer’s orders is intended referential behaviour, not a deletion anomaly

Update Anomalies

• Problem: Modifying data causes inconsistencies or errors
• Example: Updating employee salary in one record but not related records

Visual Comparison

• Before Normalization: Table prone to redundancy and anomalies
• After Normalization: Data divided into logical tables ensuring consistency

Key Features

Data Redundancy Elimination

• Goal: Remove repetition of data across database parts
• Benefit: Improved efficiency and consistency

Data Consistency Assurance

• Goal: Ensure accurate and consistent data
• Method: Prevent contradictions from different data versions

Data Management Simplification

• Goal: Break complex structures into simpler tables
• Benefit: Easier management, updates, and retrieval

Database Design Improvement

• Goal: Structured and systematic data organization
• Benefit: Better design, maintenance, flexibility, and adaptability

Update Anomaly Prevention

• Goal: Avoid single record updates affecting multiple tables
• Method: Each table contains one data type with clear relationships

Standardization

• Goal: Consistent and uniform data storage
• Method: Organized tables with defined relationships

Normal Forms Overview

Database normalization follows a progressive series of normal forms, each building upon the previous:

Normal Form	Key Focus
First Normal Form (1NF)	Eliminates repeating groups - each attribute contains atomic values
Second Normal Form (2NF)	Eliminates partial dependencies - all non-key attributes fully depend on primary key
Third Normal Form (3NF)	Eliminates transitive dependencies - no non-key attribute depends on another non-key attribute
Boyce-Codd Normal Form (BCNF)	Stronger version of 3NF - every determinant must be a candidate key
Fourth Normal Form (4NF)	Eliminates multi-valued dependencies
Fifth Normal Form (5NF)	Eliminates join dependencies - no further lossless decomposition possible

Note: Each normal form addresses specific types of data anomalies and dependencies. Detailed explanations and examples for each form are covered in separate dedicated files.

Advantages of Normalization

Data Management Benefits

• Redundancy Elimination: Each data piece stored in only one place
• Consistency Assurance: Reduced risk of data inconsistency
• Accuracy Maintenance: Easier to maintain data accuracy

Storage and Structure Benefits

• Relevant Data Storage: Each table stores only relevant data
• Improved Data Integrity: Better overall database integrity
• Simplified Updates: Data changed in one place only

Query and Integration Benefits

• Flexible Querying: Various query criteria using joined tables
• Application Consistency: Consistent data across different applications
• Integration Support: Easier application integration
• Universal Access: All users access accurate and consistent data

Disadvantages of Normalization

Performance Considerations

• Join Overhead: Additional join operations required
• Query Performance: Potentially slower query execution times
• Complex Retrieval: Multiple table joins needed for data retrieval

Implementation Challenges

• Expert Knowledge Required: Proper implementation needs database design expertise
• Design Complexity: Can increase overall database design complexity
• Context Loss: Data context may be lost across split tables
• Learning Curve: Understanding normalization process requires expertise

Best Practices

When to Normalize

• OLTP Systems: Highly normalized for transaction processing
• Data Integrity Critical: When accuracy is paramount
• Frequent Updates: When data is frequently modified
• Storage Optimization: When minimizing redundancy is important

When to Consider Denormalization

• OLAP Systems: For analytical and reporting workloads
• Read-Heavy Applications: When query performance is critical
• Complex Joins: When normalization creates too many joins
• Performance Requirements: When speed outweighs storage concerns

Summary

• Core Goal: Organize data to eliminate redundancy and ensure integrity
• Progressive Process: Each normal form builds upon previous ones
• Trade-offs: Balance between data integrity and query performance
• Context Dependent: Choose normalization level based on use case requirements