Data Insights MySQL Masterclass — Part 7 (Final)

Database Design — ER Diagram, Normalization & Real-World Schema

MySQL Masterclass ka final aur sabse important part — Database Design. ER Diagrams se schema plan karo, Normalization se data redundancy hatao, aur ek complete real-world E-commerce database banao. Sab kuch step-by-step Data Insights par.

📑 Is Part 7 Mein Aap Kya Sikhenge:

• Topic 1: ER Diagram — Entities, Attributes & Relationships
• Topic 2: Normalization — Kya Hai & Kyun Zaroori Hai
• Topic 3: 1NF — First Normal Form
• Topic 4: 2NF — Second Normal Form
• Topic 5: 3NF — Third Normal Form
• Topic 6: BCNF — Boyce-Codd Normal Form
• Topic 7: Denormalization — Kab Torna Hai Rules
• Topic 8: Real-World Schema — Complete E-commerce Database

text

🔍 Definition: An Entity-Relationship (ER) Diagram is a visual blueprint of a database. It shows Entities (objects/tables), their Attributes (columns), and Relationships (how entities connect). ER diagrams are created BEFORE writing any SQL — they are the planning phase of database design.

🎯 Samjho Simple Bhasha Mein: ER Diagram ek architectural blueprint hai — jaise ghar banane se pehle architect drawing banata hai. Database banane se pehle ER diagram banate hain — kaunsi tables hongi, unmein kya columns honge, aur tables kaise ek dusre se connected honge. Yeh paper ya whiteboard par bana lo PEHLE, phir SQL likho.

💡 ER Diagram ke 3 Main Components:

1. Entity (Rectangle ▭): Real-world object jo table banega
Example: Student, Employee, Product, Order

2. Attribute (Ellipse ○): Entity ka property jo column banega
Example: Student ka naam, roll_number, email
Types: Simple, Composite, Multi-valued, Derived

3. Relationship (Diamond ◇): Entities ke beech connection
Example: Student "enrolls in" Course, Customer "places" Order

📊 Relationship Types — Cardinality:

Type	Notation	Example	Implementation
One-to-One (1:1)	1 — 1	Employee — Passport	Foreign key with UNIQUE
One-to-Many (1:N)	1 — ∞	Department — Employees	Foreign key in "many" table
Many-to-Many (M:N)	∞ — ∞	Students — Courses	Junction/Bridge table

📊 ER Diagram — E-commerce Example (Text Representation):

┌─────────────┐ places ┌─────────────┐
│ CUSTOMER │ ──────────────────────▶ │ ORDER │
│─────────────│ 1 Many │─────────────│
│ customer_id │ │ order_id │
│ name │ │ customer_id │
│ email │ │ order_date │
│ phone │ │ total_amt │
└─────────────┘ └──────┬──────┘
│ contains
│ (1 to Many)
▼
┌─────────────┐ belongs to ┌─────────────┐
│ PRODUCT │ ◀──────────────────── │ ORDER_ITEM │
│─────────────│ 1 Many │─────────────│
│ product_id │ │ order_id │
│ name │ │ product_id │
│ price │ │ quantity │
│ category_id │ │ unit_price │
└──────┬──────┘ └─────────────┘
│ belongs to
│ (Many to 1)
▼
┌─────────────┐
│ CATEGORY │
│─────────────│
│ category_id │
│ name │
│ description │
└─────────────┘

Relationships:
Customer (1) ──places──▶ (Many) Orders
Order (1) ──contains──▶ (Many) Order_Items
Product (1) ──in──▶ (Many) Order_Items
Category (1) ──has──▶ (Many) Products

💻 ER Diagram se SQL Schema:

-- ER Diagram ko SQL mein convert karna
-- Entity: CUSTOMER
CREATE TABLE customers (
customer_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
phone VARCHAR(15)
);

-- Entity: ORDER (1:N with Customer)
CREATE TABLE orders (
order_id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT NOT NULL,
order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
total_amount DECIMAL(10,2),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Junction Table: ORDER_ITEMS (M:N between Order and Product)
CREATE TABLE order_items (
order_id INT,
product_id INT,
quantity INT NOT NULL,
unit_price DECIMAL(10,2) NOT NULL,
PRIMARY KEY (order_id, product_id),
FOREIGN KEY (order_id) REFERENCES orders(order_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);

⚠️ Common Mistakes:

• Mistake: ER Diagram banaye bina seedha SQL likhna → Later schema changes bahut painful hoti hain.
  Fix: Hamesha pehle ER diagram banao — paper par bhi chalega.
• Mistake: Many-to-Many relationship ko directly implement karna → 2 tables ke beech direct M:N possible nahi — junction table chahiye.
  Fix: M:N ke liye hamesha ek junction/bridge table banao.
• Mistake: Relationship aur Attribute confuse karna → Relationship do entities ke beech hota hai, attribute ek entity ka property hai.

💬 Interview Questions:

text

🔍 Definition: Normalization is the process of organizing a relational database to reduce data redundancy and improve data integrity. It involves decomposing tables into smaller, well-structured tables and defining relationships between them. Each Normal Form (NF) has specific rules that must be satisfied.

🎯 Samjho Simple Bhasha Mein: Normalization matlab "data ko sahi jagah rakhna." Socho ek table mein student ka naam, college ka naam, aur college ka address — agar student transfer ho toh 100 jagah college address update karna padega. Normalization se college ki info alag table mein jaati hai — ek jagah update karo, sab jagah reflect hota hai. Yeh data duplication hatane ka systematic process hai.

💡 Problems Without Normalization:

Insert Anomaly: Naya record add nahi ho sakta bina unnecessary data ke
Update Anomaly: Ek data ko multiple rows mein update karna padta hai
Delete Anomaly: Ek record delete karne se important data bhi delete ho jaata hai
Data Redundancy: Same data multiple jagah store — storage waste + inconsistency

📊 Un-Normalized Table — Problems Dekho:

-- PROBLEM TABLE: student_courses (Un-normalized)

student_id	student_name	course_id	course_name	teacher_name	grade
101	Rahul	C01	MySQL	Mr. Sharma	A
101	Rahul	C02	Python	Mrs. Gupta	B
102	Priya	C01	MySQL	Mr. Sharma	A+
102	Priya	C03	Data Science	Mr. Sharma	B+
103	Amit	C02	Python	Mrs. Gupta	C

PROBLEMS: ❌ Update Anomaly: Mr. Sharma ka naam change karo → 3 rows update! ❌ Insert Anomaly: Naya course add karo bina student ke → NULL values! ❌ Delete Anomaly: Amit delete karo → Python course ka record lost! ❌ Redundancy: 'MySQL' aur 'Mr. Sharma' multiple baar store

text

💬 Interview Questions:

text

🔍 Definition: First Normal Form (1NF) requires that: (1) Each column contains only atomic (indivisible) values — no lists or sets. (2) Each column contains values of the same data type. (3) Each row is uniquely identifiable (has a primary key). (4) No repeating groups of columns.

🎯 Samjho Simple Bhasha Mein: 1NF ka matlab hai "har cell mein sirf ek value." Socho ek cell mein "MySQL, Python, Java" likh diya — yeh 1NF violate karta hai. Har course ko alag row mein hona chahiye. Also, columns repeat nahi hone chahiye jaise course1, course2, course3 — yeh bhi violation hai. Simple rule: har cell = ek value, table mein primary key hona chahiye.

💡 1NF Rules — Checklist:
✅ Har column mein atomic (single) value
✅ Saare values ek column mein same data type ke
✅ Primary Key exist karta hai
✅ Koi repeating column groups nahi (course1, course2, course3)
✅ Row order irrelevant (data order pe depend nahi)

📊 1NF Violation — Before & After:

-- ❌ VIOLATES 1NF (multi-value column + repeating groups)

student_id	student_name	courses	course1	course2
101	Rahul	MySQL, Python, Java	MySQL	Python
102	Priya	Data Science, MySQL	DataSci	MySQL
student_id	student_name	course	101	Rahul
MySQL	101	Rahul	Python	101
Rahul	Java	102	Priya	DataSci

Problems: 'courses' column multi-value, repeating course1/course2 columns -- ✅ AFTER 1NF (atomic values, no repeating groups) Primary Key: (student_id, course) — composite key Each cell has ONE value ✅ 102 Priya MySQL

text

💻 1NF Compliant Table Creation:

-- 1NF: Separate rows for each course enrollment
CREATE TABLE student_courses_1nf (
student_id INT,
student_name VARCHAR(50),
course VARCHAR(50),
grade CHAR(2),
PRIMARY KEY (student_id, course) -- Composite PK for uniqueness
);

INSERT INTO student_courses_1nf
VALUES
(101, 'Rahul', 'MySQL', 'A'),
(101, 'Rahul', 'Python', 'B'),
(101, 'Rahul', 'Java', 'A+'),
(102, 'Priya', 'Data Science','B+'),
(102, 'Priya', 'MySQL', 'A+');

⚠️ Common Mistakes:

• Mistake: JSON ya comma-separated values ek column mein store karna → MySQL JSON type allow karta hai but 1NF violate hoti hai.
  Fix: Har value ki alag row banao — query karna bhi aasaan hoga.
• Mistake: Repeating column groups banana → phone1, phone2, phone3 — 4th phone add karna ho toh schema change karna padega.
  Fix: Separate phone_numbers table banao with foreign key.

💬 Interview Questions:

text

🔍 Definition: Second Normal Form (2NF) requires the table to be in 1NF AND every non-key attribute must be fully functionally dependent on the ENTIRE primary key — not just part of it. Partial dependency (where a non-key column depends on only part of a composite primary key) must be eliminated.

🎯 Samjho Simple Bhasha Mein: 2NF sirf tab apply hota hai jab composite primary key ho. Rule hai: "har non-key column ko POORI primary key pe depend karna chahiye — sirf ek part pe nahi." Socho student_courses table mein primary key hai (student_id, course_id). student_name sirf student_id pe depend karta hai — course_id se koi lena dena nahi. Yeh partial dependency hai — 2NF violate karta hai.

💡 2NF ka Rule:
Table 1NF mein ho + Koi Partial Dependency na ho

Partial Dependency: Non-key column depends on PART of composite PK
Full Dependency: Non-key column depends on ENTIRE composite PK

Note: Agar single column primary key hai toh 2NF automatically satisfied hai (partial dependency possible hi nahi)

📊 2NF Violation — Before & After:

-- ❌ VIOLATES 2NF (Partial Dependencies!)
-- Primary Key: (student_id, course_id)

student_id	course_id	student_name	course_name	grade
101	C01	Rahul	MySQL	A
101	C02	Rahul	Python	B
102	C01	Priya	MySQL	A+

Partial Dependencies Found:
❌ student_name depends only on student_id (not full PK)
❌ course_name depends only on course_id (not full PK)
✅ grade depends on BOTH student_id AND course_id (full dependency)

-- ✅ AFTER 2NF (Split into 3 tables)
-- Table 1: students (student_id → student_name)

student_id	student_name
101	Rahul
102	Priya

-- Table 2: courses (course_id → course_name)

course_id	course_name
C01	MySQL
C02	Python

-- Table 3: enrollments (student_id, course_id → grade)

student_id	course_id	grade
101	C01	A
101	C02	B
102	C01	A+

text

💻 2NF Compliant Tables:

-- 2NF: Separate tables for each entity
CREATE TABLE students (
student_id INT PRIMARY KEY,
student_name VARCHAR(50) NOT NULL
);

CREATE TABLE courses (
course_id VARCHAR(5) PRIMARY KEY,
course_name VARCHAR(50) NOT NULL
);

-- Junction table: Only fully-dependent column (grade) stays here
CREATE TABLE enrollments (
student_id INT,
course_id VARCHAR(5),
grade CHAR(2),
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES students(student_id),
FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

⚠️ Common Mistakes:

• Mistake: Single column PK wale table mein 2NF violation dhundhna → Single column PK se partial dependency possible nahi — 2NF auto-satisfied.
  Fix: 2NF sirf composite primary key wale tables mein check karo.
• Mistake: Har dependency ko partial samajhna → grade DONO columns pe depend karta hai — yeh partial nahi, fully dependent hai.
  Fix: Check karo: kya column sirf ek part se determine ho sakta hai? Haan → Partial. Nahi → Full.

💬 Interview Questions:

text

🔍 Definition: Third Normal Form (3NF) requires the table to be in 2NF AND there must be no Transitive Dependencies — meaning no non-key attribute should depend on another non-key attribute. Every non-key attribute should depend ONLY on the primary key, nothing else.

🎯 Samjho Simple Bhasha Mein: 3NF ka rule: "non-key column kisi dusre non-key column se determine nahi hona chahiye." Example: Employee table mein emp_id → dept_id → dept_name. dept_name, emp_id se directly depend nahi karta — pehle dept_id pe aata hai, phir dept_name. Yeh transitive dependency hai. Fix: dept_name ko alag departments table mein le jao.

💡 Transitive Dependency:
A → B → C (C transitively depends on A via B)

Example: emp_id → dept_id → dept_name
emp_id determines dept_id (direct)
dept_id determines dept_name (direct)
Therefore: emp_id → dept_name (TRANSITIVE — violation!)

Fix: dept_name ko employees table se hataao, departments table mein rakho

📊 3NF Violation — Before & After:

-- ❌ VIOLATES 3NF (Transitive Dependencies!) -- Primary Key: emp_id

emp_id	emp_name	dept_id	dept_name	dept_city
101	Rahul	D1	IT	Bangalore
102	Priya	D2	HR	Mumbai
103	Amit	D1	IT	Bangalore
emp_id	emp_name	dept_id	101	Rahul
D1	102	Priya	D2	103
Amit	D1	dept_id	dept_name	dept_city
D1	IT	Bangalore	D2	HR

Transitive Dependencies: emp_id → dept_id ✅ (direct) dept_id → dept_name ❌ (transitive via dept_id) dept_id → dept_city ❌ (transitive via dept_id) Problems: ❌ IT ka dept_city change karo → 2 rows update! ❌ Amit delete karo → D1 dept info lost! -- ✅ AFTER 3NF (Remove transitive dependencies) -- Table 1: employees -- Table 2: departments Mumbai

text

💻 3NF Compliant Tables:

-- 3NF: Transitive dependency removed
-- departments table (dept_name and dept_city stay here)
CREATE TABLE departments_3nf (
dept_id VARCHAR(5) PRIMARY KEY,
dept_name VARCHAR(50) NOT NULL,
dept_city VARCHAR(50)
);

-- employees table (only FK to dept, not dept details)
CREATE TABLE employees_3nf (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(50) NOT NULL,
dept_id VARCHAR(5),
FOREIGN KEY (dept_id) REFERENCES departments_3nf(dept_id)
);

-- Now dept_city update = 1 row only!

UPDATE departments_3nf

SET dept_city = 'Hyderabad'

WHERE dept_id = 'D1';

-- Affects both Rahul and Amit automatically via FK!

⚠️ Common Mistakes:

• Mistake: 2NF aur 3NF ka fark bhool jaana → 2NF = partial dependency (composite PK). 3NF = transitive dependency (non-key to non-key).
  Fix: Yaad rakho: 2NF partial, 3NF transitive.
• Mistake: Har dependency ko transitive samajhna → emp_id → emp_name direct dependency hai — not transitive.
  Fix: Sirf non-key se non-key dependency transitive hai.

💬 Interview Questions:

text

🔍 Definition: Boyce-Codd Normal Form (BCNF) is a stronger version of 3NF. A table is in BCNF if for every functional dependency X → Y, X must be a super key (a key that uniquely identifies all rows). Even candidate keys cannot determine non-key attributes if they are not the chosen primary key.

🎯 Samjho Simple Bhasha Mein: BCNF 3NF se thoda strict hai. 3NF mein ek loophole tha — agar ek candidate key doosre candidate key ka column determine kare toh 3NF mein allowed tha. BCNF yeh bhi band karta hai. Real projects mein BCNF rarely needed hoti hai — 3NF mostly sufficient hai. But interview mein zaroor poochha jaata hai!

💡 BCNF Rule:
For every functional dependency X → Y in the table:
X must be a SUPERKEY (must uniquely identify every row)

3NF vs BCNF Difference:
3NF allows: Non-key → Prime attribute (part of candidate key)
BCNF disallows: Even this — every determinant must be superkey

Most 3NF tables are also in BCNF. BCNF violation is rare.

📊 BCNF Violation Example:

-- Scenario: Student, Subject, Teacher assignment -- Rules: Each subject has multiple teachers -- Each teacher teaches only one subject -- Each student can study each subject with only one teacher -- Candidate Keys: (student, subject) OR (student, teacher) -- Chosen PK: (student, subject) -- ❌ VIOLATES BCNF

student	subject	teacher
Rahul	MySQL	Mr. Sharma
Priya	MySQL	Mr. Sharma
Amit	Python	Mrs. Gupta
Rahul	Python	Mrs. Gupta
teacher	subject	Mr. Sharma
MySQL	Mrs. Gupta	Python
student	teacher	Rahul
Mr. Sharma	Priya	Mr. Sharma
Amit	Mrs. Gupta	Rahul

Dependency: teacher → subject But 'teacher' is NOT a superkey (it's part of candidate key, not full superkey) This violates BCNF! -- ✅ AFTER BCNF Fix (Decompose into 2 tables) -- Table 1: teacher_subjects -- Table 2: student_teachers Mrs. Gupta

text

💬 Interview Questions:

text

🔍 Definition: Denormalization is the intentional process of adding redundancy back into a normalized database to improve read performance. It trades storage space and write complexity for faster query execution. Used in data warehouses, reporting systems, and high-traffic read-heavy applications.

🎯 Samjho Simple Bhasha Mein: Normalization mein hum data ko tod ke alag tables mein daala. Denormalization mein hum kuch data wapas saath rakhte hain — performance ke liye. Socho ek e-commerce site par har product query mein category table ka JOIN lagana padta hai. Agar traffic 1 lakh users per second hai toh yeh JOIN slow padega. Solution: category_name ko products table mein hi rakh do — ek JOIN bachega, query fast hogi. Yeh denormalization hai.

💡 Denormalization — Kab Use Karein:
✅ Read-heavy applications (100x reads vs writes)
✅ Reporting aur Analytics dashboards
✅ Data Warehouses (OLAP systems)
✅ Frequently joined tables jo slow hain
✅ Aggregate values jo baar baar calculate hoti hain

❌ Write-heavy transactional systems (banking, inventory)
❌ Jab data consistency critical ho
❌ Small datasets (JOIN overhead negligible)

📊 Normalization vs Denormalization:

Feature	Normalization	Denormalization
Data Redundancy	❌ Minimal	✅ Intentional
Read Performance	🐢 Slower (JOINs needed)	⚡ Faster (no JOINs)
Write Performance	⚡ Faster (one place)	🐢 Slower (multiple updates)
Storage Space	Less	More
Data Consistency	✅ High	⚠️ Risk of inconsistency
Use Case	OLTP (banking, orders)	OLAP (reports, analytics)

💻 Denormalization Example:

-- Normalized (3NF): products + categories JOIN needed
SELECT p.name, p.price, c.category_name

FROM products p

JOIN categories c
ON p.category_id = c.category_id;

-- JOIN slow hai on high traffic

-- Denormalized: category_name directly in products
CREATE TABLE products_denorm (
product_id INT PRIMARY KEY,
name VARCHAR(100),
price DECIMAL(10,2),
category_id INT,
category_name VARCHAR(50) -- Redundant but fast!
);

-- No JOIN needed now!
SELECT name, price, category_name

FROM products_denorm;

-- Much faster on millions of rows!

-- But update problem: category name change = update all products!

UPDATE products_denorm

SET category_name = 'Electronics & Gadgets'

WHERE category_id = 5;

-- Might update thousands of rows!

💬 Interview Questions:

text

🔍 Definition: A real-world E-commerce database schema that applies all the concepts learned — ER modeling, proper normalization (3NF), relationships, constraints, and best practices. This is the type of schema asked in system design interviews and used in actual production applications.

🎯 Samjho Simple Bhasha Mein: Ab sab kuch ek saath use karenge. Amazon ya Flipkart jaisi e-commerce site ka database kaise design hoga? Customers, Products, Orders, Categories, Addresses, Payments — sab kuch proper normalized schema mein. Yeh poori masterclass ka practical culmination hai!

📊 Complete E-commerce Schema — ER Overview:

▼

▼ ▼
PAYMENTS CATEGORIES

💻 Complete E-commerce SQL Schema:

Step 1: Database aur Categories table banao.

-- Complete E-commerce Database
CREATE DATABASE ecommerce_db;

USE ecommerce_db;

-- 1. CATEGORIES (parent table — no foreign keys)
CREATE TABLE categories (
category_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL UNIQUE,
description TEXT,
parent_id INT DEFAULT NULL, -- Self-referencing for sub-categories
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (parent_id) REFERENCES categories(category_id)
);

INSERT INTO categories (name, description, parent_id)
VALUES
('Electronics', 'Electronic devices', NULL),
('Mobiles', 'Mobile phones', 1),
('Laptops', 'Laptop computers', 1),
('Clothing', 'Apparel and fashion', NULL),
('Books', 'Books and literature', NULL);

Step 2: Customers aur Addresses tables.

-- 2. CUSTOMERS
CREATE TABLE customers (
customer_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
phone VARCHAR(15),
password_hash VARCHAR(255) NOT NULL,
is_active TINYINT(1) DEFAULT 1,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 3. ADDRESSES (1:N with customers — one customer, many addresses)
CREATE TABLE addresses (
address_id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT NOT NULL,
address_type ENUM('Home','Work','Other') DEFAULT 'Home',
address_line1 VARCHAR(200) NOT NULL,
address_line2 VARCHAR(200),
city VARCHAR(50) NOT NULL,
state VARCHAR(50) NOT NULL,
pincode VARCHAR(10) NOT NULL,
is_default TINYINT(1) DEFAULT 0,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE
);

INSERT INTO customers (first_name, last_name, email, phone, password_hash)
VALUES
('Rahul', 'Sharma', 'rahul@email.com', '9876543210', 'hashed_pw_1'),
('Priya', 'Singh', 'priya@email.com', '9876543211', 'hashed_pw_2'),
('Amit', 'Kumar', 'amit@email.com', '9876543212', 'hashed_pw_3');

Step 3: Products table.

-- 4. PRODUCTS
CREATE TABLE products (
product_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(200) NOT NULL,
description TEXT,
price DECIMAL(10,2) NOT NULL,
sale_price DECIMAL(10,2),
stock_qty INT DEFAULT 0,
category_id INT,
brand VARCHAR(100),
sku VARCHAR(50) UNIQUE,
is_active TINYINT(1) DEFAULT 1,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (category_id) REFERENCES categories(category_id),
CHECK (price > 0),
CHECK (stock_qty >= 0)
);

INSERT INTO products (name, price, sale_price, stock_qty, category_id, brand, sku)
VALUES
('Samsung Galaxy S24', 79999, 74999, 50, 2, 'Samsung','SKU-MOB-001'),
('iPhone 15 Pro', 134900,NULL, 30, 2, 'Apple', 'SKU-MOB-002'),
('Dell XPS 15 Laptop', 124999,119999,20, 3, 'Dell', 'SKU-LAP-001'),
('Clean Code Book', 599, 499, 100, 5, 'Pearson','SKU-BOK-001');

Step 4: Orders, Order Items aur Payments.

-- 5. ORDERS
CREATE TABLE orders (
order_id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT NOT NULL,
address_id INT,
status ENUM('Pending','Confirmed','Shipped','Delivered','Cancelled')
DEFAULT 'Pending',
total_amount DECIMAL(12,2) NOT NULL,
discount_amount DECIMAL(10,2) DEFAULT 0,
final_amount DECIMAL(12,2) NOT NULL,
notes TEXT,
ordered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
FOREIGN KEY (address_id) REFERENCES addresses(address_id)
);

-- 6. ORDER_ITEMS (Junction table — M:N between orders and products)
CREATE TABLE order_items (
order_item_id INT PRIMARY KEY AUTO_INCREMENT,
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
unit_price DECIMAL(10,2) NOT NULL, -- Price at time of order (not current price)
total_price DECIMAL(12,2) NOT NULL,
FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
FOREIGN KEY (product_id) REFERENCES products(product_id),
CHECK (quantity > 0)
);

-- 7. PAYMENTS
CREATE TABLE payments (
payment_id INT PRIMARY KEY AUTO_INCREMENT,
order_id INT UNIQUE NOT NULL, -- 1:1 with order
payment_method ENUM('UPI','Card','NetBanking','COD','Wallet'),
amount DECIMAL(12,2) NOT NULL,
status ENUM('Pending','Success','Failed','Refunded') DEFAULT 'Pending',
transaction_id VARCHAR(100) UNIQUE,
paid_at TIMESTAMP,
FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

Step 5: Sample data insert aur analytics queries.

-- Sample orders

INSERT INTO orders (customer_id, status, total_amount, final_amount)
VALUES
(1, 'Delivered', 79999, 74999),
(2, 'Shipped', 134900,134900),
(1, 'Pending', 599, 499);

INSERT INTO order_items (order_id, product_id, quantity, unit_price, total_price)
VALUES
(1, 1, 1, 74999, 74999),
(2, 2, 1, 134900, 134900),
(3, 4, 1, 499, 499);

-- ===== ANALYTICS QUERIES ON E-COMMERCE SCHEMA =====

-- 1. Customer order history with totals
SELECT
CONCAT(c.first_name, ' ', c.last_name) AS customer,
COUNT(o.order_id) AS total_orders,
SUM(o.final_amount) AS lifetime_value,
MAX(o.ordered_at) AS last_order

FROM customers c
LEFT
JOIN orders o
ON c.customer_id = o.customer_id

GROUP BY c.customer_id

ORDER BY lifetime_value DESC;

-- 2. Top selling products
SELECT
p.name,
SUM(oi.quantity) AS units_sold,
SUM(oi.total_price) AS revenue

FROM products p
INNER
JOIN order_items oi
ON p.product_id = oi.product_id

GROUP BY p.product_id

ORDER BY revenue DESC;

-- 3. Category wise revenue
SELECT
cat.name AS category,
COUNT(DISTINCT p.product_id) AS products,
COALESCE(SUM(oi.total_price), 0) AS total_revenue

FROM categories cat
LEFT
JOIN products p
ON cat.category_id = p.category_id
LEFT
JOIN order_items oi
ON p.product_id = oi.product_id

GROUP BY cat.category_id

ORDER BY total_revenue DESC;

-- 4. Low stock alert
SELECT
name,
stock_qty,
brand,
CASE

WHEN stock_qty = 0
THEN 'OUT OF STOCK'

WHEN stock_qty 10
THEN 'LOW STOCK'

ELSE 'IN STOCK'

END AS stock_status

FROM products

WHERE is_active = 1

ORDER BY stock_qty ASC;

📊 Expected Output (Query 1 — Customer LTV):

customer	total_orders	lifetime_value	last_order
Rahul Sharma	2	75498.00	2024-01-15 10:00:00
Priya Singh	1	134900.00	2024-01-14 15:30:00
Amit Kumar	0	NULL	NULL

text

📊 Schema Design Best Practices Used:

Practice	Where Used	Why
AUTO_INCREMENT PK	All tables	Unique identifier, no manual management
ON DELETE CASCADE	addresses, order_items	Customer delete → addresses auto-delete
ENUM for status	orders, payments	Constrained values, storage efficient
unit_price in order_items	order_items	Price at order time — product price may change!
Self-referencing FK	categories.parent_id	Sub-categories without extra table
UNIQUE on payment order_id	payments	Enforces 1:1 between order and payment
CHECK constraints	products, order_items	Business rule enforcement at DB level
updated_at TIMESTAMP	customers	Auto-track last modification time

💬 Interview Questions:

Normal Form	Rule	Eliminates	Requires
1NF	Atomic values, PK exists	Multi-value columns, repeating groups	Nothing before it
2NF	Full functional dependency on PK	Partial dependencies	Must be in 1NF
3NF	No non-key → non-key dependencies	Transitive dependencies	Must be in 2NF
BCNF	Every determinant is superkey	Remaining anomalies from 3NF	Must be in 3NF

🎯 Top Database Design Interview Questions — Rapid Fire

Q1: 1NF kya violate karta hai? → Multi-value cells ya repeating column groups

Q2: 2NF kab apply hota hai? → Sirf composite primary key hone par — partial dependency eliminate karo

Q3: 3NF mein kya hatate hain? → Transitive dependency — non-key se non-key dependency

Q4: M:N relationship implement kaise karein? → Junction/Bridge table with both FKs as composite PK

Q5: Denormalization kab use karein? → Read-heavy OLAP systems, reporting dashboards, high-traffic applications

Q6: E-commerce mein unit_price order_items mein kyun? → Product price change ho sakti hai — historical accuracy maintain karne ke liye

🎉 MySQL Masterclass — Complete!

Tumne poori MySQL Masterclass complete kar li! Yeh sab cover hua:

Happy Coding & Keep Building! 🚀