Lesson 32: Database Design Methodology Overview

Designing a robust database is a systematic process, not an accident. Good design minimizes storage space and ensures data integrity and speed.

Phases of Database Design

Database design typically moves through three conceptual phases:

1. Conceptual Design (E-R Model)

• Goal: Understanding the real-world data requirements, independent of any specific DBMS.
• Tool: Entity-Relationship (E-R) Diagram. We identify entities (tables), attributes (columns), and the relationships (1:1, 1:N, M:N) between them.

2. Logical Design (Normalization)

• Goal: Translating the E-R Model into a formal relational model (tables and keys).
• Tool: Normalization. Applying a set of rules (Normal Forms) to ensure tables are structured correctly, eliminating data redundancy and minimizing anomalies.

3. Physical Design

• Goal: Implementing the logical model using a specific DBMS.
• Steps: Choosing data types, defining indexes, setting storage parameters, and enforcing performance considerations.

The Importance of Good Design

Poor design leads to:

1. Insertion Anomalies: Difficult to add new data without also adding irrelevant data.
2. Deletion Anomalies: Deleting one piece of data accidentally removes other, vital data.
3. Update Anomalies: Having to update the same information in multiple places (leading to inconsistency).

Normalization (covered next) is the primary technique used to prevent these anomalies.