DATABASE DESIGN & NORMALIZATION

 

Database Design and Normalization

Introduction

• Good database design is crucial for efficient data management and retrieval
• Normalization is a technique used to design good table structures
• Goals of normalization:
  1. Minimize data redundancy
  2. Ensure data consistency
  3. Simplify data maintenance
  4. Avoid anomalies in data operations (insertion, deletion, update)

Problems with Unnormalized Tables

1. Repetition Anomaly

• Definition: Unnecessary repetition of data across multiple rows
• Example: Repeating project details (Project_id, Project_name) for each employee working on the project
• Consequences:
  • Wastes storage space
  • Slows down data retrieval
  • Increases risk of data inconsistencies

2. Insertion Anomaly

• Definition: Difficulty in inserting certain data without including other, potentially unavailable data
• Example: Unable to add a new employee without assigning them to a project
• Consequences:
  • Limits flexibility in data entry
  • May lead to incomplete or inaccurate data

3. Deletion Anomaly

• Definition: Unintended loss of data when deleting a record
• Example: Deleting an employee's project assignment also removes their personal details
• Consequences:
  • Risk of losing important, unrelated data
  • Complicates data management and historical record-keeping

4. Updating Anomaly

• Definition: Inconsistency when updating repeated data
• Example: Updating an employee's salary requires changes in multiple rows
• Consequences:
  • Increases risk of data inconsistencies
  • Makes data maintenance more time-consuming and error-prone

Normalization Process

Normalization involves organizing data into progressively more structured forms:

First Normal Form (1NF)

• Objectives:
  1. Eliminate repeating groups
  2. Create separate tables for each set of related data
  3. Identify each record with a unique field (primary key)
• Rules:
  1. Each column should contain atomic (indivisible) values
  2. Each column should have a unique name
  3. The order of data stored doesn't matter
  4. Each record should be unique
• Example:
  • Before 1NF:

    | Emp_no | Emp_name | Project_ids      | Project_names           |
    |--------|----------|------------------|-------------------------|
    | E1     | John     | P1, P2           | MERCURY, VENUS          |

  • After 1NF:

    | Emp_no | Emp_name | Project_id | Project_name |
    |--------|----------|------------|--------------|
    | E1     | John     | P1         | MERCURY      |
    | E1     | John     | P2         | VENUS        |

Second Normal Form (2NF)

• Objectives:
  1. Meet all 1NF requirements
  2. Remove partial dependencies
• Rules:
  1. Be in 1NF
  2. All non-key attributes must depend on the entire primary key
• Example:
  • Before 2NF:

    | Emp_no | Project_id | Emp_name | Project_name | Grade | Salary |
    |--------|------------|----------|--------------|-------|--------|
    | E1     | P1         | John     | MERCURY      | G3    | 50000  |
    | E1     | P2         | John     | VENUS        | G3    | 50000  |

  • After 2NF:

    Employee Table:
    | Emp_no | Emp_name | Grade | Salary |
    |--------|----------|-------|--------|
    | E1     | John     | G3    | 50000  |
    
    Project Table:
    | Project_id | Project_name |
    |------------|--------------|
    | P1         | MERCURY      |
    | P2         | VENUS        |
    
    Employee_Project Table:
    | Emp_no | Project_id |
    |--------|------------|
    | E1     | P1         |
    | E1     | P2         |

Third Normal Form (3NF)

• Objectives:
  1. Meet all 2NF requirements
  2. Remove transitive dependencies
• Rules:
  1. Be in 2NF
  2. No non-key attribute should depend on another non-key attribute
• Example:
  • Before 3NF:

    | Emp_no | Emp_name | Grade | Salary |
    |--------|----------|-------|--------|
    | E1     | John     | G3    | 50000  |

  • After 3NF:

    Employee Table:
    | Emp_no | Emp_name | Grade |
    |--------|----------|-------|
    | E1     | John     | G3    |
    
    Grade_Salary Table:
    | Grade | Salary |
    |-------|--------|
    | G3    | 50000  |

Benefits of Normalization

1. Reduced data redundancy
2. Improved data consistency
3. Greater flexibility in data queries
4. Easier data maintenance and updates
5. Better support for data integrity constraints
6. More accurate representation of real-world entities and relationships

Key Concepts

• Primary Key: A unique identifier for each record in a table
• Foreign Key: A field that refers to the primary key in another table
• Composite Key: A primary key that consists of two or more attributes
• Atomic Values: Values that cannot be further divided
• Partial Dependency: When a non-key attribute depends on only part of a composite key
• Transitive Dependency: When a non-key attribute depends on another non-key attribute

Practical Considerations

• While full normalization (up to 3NF or beyond) is theoretically ideal, practical implementations may sometimes denormalize for performance reasons
• Always consider the specific requirements of your application and the nature of data access patterns
• Normalization is an iterative process; start with 1NF and progressively refine the structure

Conclusion

• Normalization is a powerful technique for creating efficient, consistent, and flexible database designs
• Understanding and applying normalization principles is crucial for database designers and administrators
• Always balance theoretical ideals with practical considerations in real-world applications