Play Store Application link – SQL in 18 steps – App on Google Play

Introduction to Database Design

Database design is like organizing a store’s inventory. If products are scattered, finding an item takes longer. A well-designed database helps in storing, retrieving, and managing data efficiently.

Key Aspects of Database Design:

1. Understand Requirements → What data needs to be stored?
2. Identify Entities & Attributes → Define tables and columns.
3. Set Relationships → How tables connect to each other.
4. Apply Normalization → Avoid redundant data.

Example Scenario:

Imagine a school database. We need tables for Students, Courses, and Enrollments.

CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

CREATE TABLE Courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100)
);

CREATE TABLE Enrollments (
    enrollment_id INT PRIMARY KEY,
    student_id INT,
    course_id INT,
    FOREIGN KEY (student_id) REFERENCES Students(student_id),
    FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);

Normalization

Normalization ensures data is structured efficiently by removing redundancy and dependency issues. Let’s break it down step by step:

1NF (First Normal Form) – Remove duplicate columns

• Each column should have atomic values (single values, not lists).
• Example:
  • ❌ Phone Numbers: "9876543210, 9123456789" (Multiple values in one column)
  • ✅ Separate into a new table:
  CREATE TABLE StudentPhones ( student_id INT, phone_number VARCHAR(15), FOREIGN KEY (student_id) REFERENCES Students(student_id) );

2NF (Second Normal Form) – Remove partial dependencies

• Every column must depend on the whole primary key, not just part of it.
• Example:
  • ❌ Enrollments table storing course_name (Course name depends only on course_id).
  • ✅ Move course details to Courses table.

3NF (Third Normal Form) – Remove transitive dependencies

• A column should depend only on the primary key, not another non-key column.
• Example:
  • ❌ Students table storing city and pincode (Pincode depends on city, not student).
  • ✅ Create a separate Cities table.

BCNF (Boyce-Codd Normal Form) – Remove overlapping candidate keys

• Ensures no functional dependencies except on primary keys.

4NF & 5NF – Handle multi-valued dependencies & complex joins

• Rarely used but ensure further optimization.

Denormalization

Denormalization is opposite of normalization. It improves performance by adding redundancy for faster queries.

When to Denormalize?

• Read-heavy systems (e.g., Reporting dashboards)
• Joins are too expensive
• Caching frequently used data

Example:

A Students table can include course details for quick lookup instead of joins.

ALTER TABLE Students ADD COLUMN course_name VARCHAR(100);

Conclusion

Normalization ensures efficiency, while denormalization improves performance for specific cases. A balance between both helps in designing scalable databases.