Database Design and Normalization: Key Concepts and Forms

Database design

Is the process of defining the structure, relationships, and constraints of data to support efficient storage and retrieval.

Requirement Analysis

Understanding what data needs to be stored and how it will be used.

Conceptual Design

Creating high-level models (like ER diagrams) to visualize entities and relationships.

Local Design

Translating ER models into tables, keys, and constraints.

Normalization

Is the systematic process of organizing data in a database to reduce redundancy and ensure data integrity.

Physical Analysis

Deciding how data is physically stored and indexed for performance.

Data Redundancy

Same data repeated unnecessarily.

Update Anomalies

Inconsistent data after updates.

Insertion Anomalies

Inability to add data without other unnecessary data.

Deletion Anomalies

Deleting data causes loss of unrelated data.

1NF (First Normal Form)

Goal: Ensures that the table structure is tabular and atomic.

Rule of 1NF

Each column should contain only atomic values, and each record must be unique.

Key Points of 1NF

No Repeating groups or arrays; Each field contains only a single value; The table must have a primary key.

Example of 1NF

If a table has a column 'Phone Numbers' with multiple numbers separated by commas, it violates 1NF.

2NF (Second Normal Form)

Goal: Remove partial dependency of non-key attributes on a part of a composite key.

Rule of 2NF

It should be already in 1NF and every non-key attribute must depend on the entire primary key.

Key Points of 2NF

Applies only when the primary key is composite; No partial dependencies allowed.

Example of 2NF

If a table has a composite primary key (A,B) and a column depends only on A, that column violates 2NF.

3NF (Third Normal Form)

Goal: Removes transitive dependencies.

Rule of 3NF

It should be already in 2NF, and all non-key attributes must depend directly on the primary key.

Key Points of 3NF

No transitive dependency; Non-key attributes cannot depend on other non-key attributes.

Example of 3NF

If you have a column 'City' and another column 'ZipCode', and 'ZipCode' depends on 'City', this is a transitive dependency.