Data Modeling
Conceptual → Logical → Physical
↓
Logical (attributes, relationships, keys)
↓
Physical (tables, columns, indexes)
Normalization
Reduce duplication, improve integrity.
First Normal Form — 1NF
Values atomic. Bad: student_id | courses = SQL, Python, Java → Better: one row per course.
| student_id | course |
|---|---|
| 1 | SQL |
| 1 | Python |
Second Normal Form — 2NF
Be in 1NF + non-key attributes depend on entire primary key (no partial dependency, relevant for composite keys).
Third Normal Form — 3NF
Be in 2NF + no transitive dependency: employee_id → department_id → department_name (department_name belongs in department table).
OLTP vs OLAP
OLTP handles many small transactional writes, while OLAP handles large analytical reads and aggregations.
| OLTP | OLAP |
|---|---|
| Many small transactions, INSERT/UPDATE/DELETE, operational | Large analytical queries, aggregations, historical, read-heavy |
| Banking, Orders | Reporting, BI |
Fact & Dimension Tables
Fact: measurable events (sales: sales_id, customer_id, product_id, quantity, sales_amount). Dimension: descriptive (customer: customer_id, name, city, country).
Star Schema
A Star Schema places a central fact table surrounded by dimension tables, enabling simple and fast analytical queries.
│
Customer ── Sales ── Product
│
Geography
Central fact, dimensions around, simpler queries.
Snowflake Schema
A Snowflake Schema further normalizes dimensions into multiple related tables, reducing redundancy at the cost of more joins.
│
Product Category
│
Customer ── Sales ── Date
More normalized dimensions, more joins.
Star vs Snowflake
Compare Star and Snowflake schemas to understand the trade-offs between query simplicity, join count, and normalization.
| Star | Snowflake |
|---|---|
| Less normalized, simpler, fewer joins | More normalized, more tables, more joins |