Learn�SQL�Normalization

Lesson 10 � Database Design

DATABASE KO
SAHI SE DESIGN KARO.

Ek bekaar design ki table mein duplicate data bhara hota hai, update karna mushkil hai, aur storage waste hoti hai. Normalization woh technique hai jo database ko clean, organized aur efficient banata hai � har data ek jagah, zero redundancy.

? 20 min✓ Intermediate✓ Prerequisite: JOINs

WHY: normalization kyun zaruri hai?

Socho ek orders table hai jismein har row mein customer ka naam, phone, address bhi hai. Agar customer apna address change kare toh✓ Har row mein address update karna padega � agar ek bhi row chhoot gayi, data inconsistent hai. Yahi problem hai bina normalization ke.

REDUNDANCY

Same data baar baar repeat hona. Jaise customer ka naam 100 orders mein 100 baar likha hai � storage waste, update errors ka risk.

UPDATE ANOMALY

Data change karte waqt kuch rows update ho jaati hain, kuch reh jaati hain. Inconsistency ka source.

NORMALIZATION

Data ko properly organize karna � har cheez ek jagah, relationships foreign keys se. Clean database, fast queries, zero waste.

Normal Forms ka overview

Normalization ke levels hain � 1NF, 2NF, 3NF. Har level ek specific problem solve karta hai. Real-world mein 3NF kaafi hota hai, 4NF aur 5NF rarely zaroori hote hain.

1NF � First Normal Form

Har cell mein ek single value ho. Repeating groups aur multi-value columns hatao. "Python,SQL,ML" ek cell mein nahi � teen alag rows mein.

2NF � Second Normal Form

Pehle 1NF complete karo, phir partial dependency hatao. Jo columns poori primary key pe depend nahi karte, unko alag table mein le jao.

3NF � Third Normal Form

Pehle 2NF complete karo, phir transitive dependency hatao. Agar column A ✓ B ✓ C hai, toh C ko seedha A se link karo, B ke through nahi.

HOW: 1NF � Atomic values

1NF ka rule hai: har cell mein sirf ek value ho. Koi "Python,SQL,ML" jaisa comma-separated data nahi. Yeh sabse basic aur zaruri level hai.

sql
-- BAD: Unnormalized (repeating groups)
CREATE TABLE orders_bad (
 id INT,
 customer_name VARCHAR(100),
 products TEXT, -- "Python,SQL,ML" BAD!
 prices TEXT -- "100,200,300" BAD!
);

-- GOOD: 1NF (atomic values)
CREATE TABLE orders_1nf (
 order_id INT,
 customer_name VARCHAR(100),
 product VARCHAR(100),
 price DECIMAL(10,2)
);

-- Data insert karo (1NF compliant)
INSERT INTO orders_1nf VALUES
(1, 'Aman', 'Python', 100),
(1, 'Aman', 'SQL', 200),
(1, 'Aman', 'ML', 300),
(2, 'Priya', 'Python', 100),
(2, 'Priya', 'Data Science', 250);
ℹ
Mental model: 1NF ka matlab hai � Excel mein koi cell mein comma-separated list mat rakho. Har value apni row mein ho. Jaise ek almirah mein sab kapde alag-alag hain, ek drawer mein 5 kapde nahi.

1NF ka test

HOW: 2NF � Partial dependency hatao

2NF ke liye pehle 1NF complete hona zaroori hai. Phir check karo: kya koi column sirf primary key ka ek part pe depend hai? Agar haan, toh use alag table mein le jao.

sql
-- BAD: 1NF hai, but partial dependency hai
CREATE TABLE order_items_bad (
 order_id INT,
 product_id INT,
 product_name VARCHAR(100), -- sirf product_id pe depend
 customer_name VARCHAR(100), -- sirf order_id pe depend
 quantity INT,
 PRIMARY KEY (order_id, product_id)
);

-- product_name sirf product_id se relate hota hai
-- customer_name sirf order_id se relate hota hai
-- Dono composite key ke sirf EK part pe depend karte hain = partial dependency

-- GOOD: 2NF (partial dependency hatao)
CREATE TABLE customers (
 id INT PRIMARY KEY,
 name VARCHAR(100)
);

CREATE TABLE products (
 id INT PRIMARY KEY,
 name VARCHAR(100),
 price DECIMAL(10,2)
);

CREATE TABLE order_items (
 order_id INT,
 customer_id INT,
 product_id INT,
 quantity INT,
 FOREIGN KEY (customer_id) REFERENCES customers(id),
 FOREIGN KEY (product_id) REFERENCES products(id)
);
ℹ
Key point: 2NF tab zaroori hota hai jab primary key composite ho (2 ya zyada columns). Agar primary key sirf ek column hai, toh 1NF se seedha 3NF ja sakte ho � 2NF automatically satisfy hota hai.

HOW: 3NF � Transitive dependency hatao

3NF ke liye pehle 2NF complete hona zaroori hai. Phir check karo: kya koi non-key column doosre non-key column pe depend hai? Agar A ✓ B ✓ C hai (transitive), toh use tod do.

sql
-- BAD: 2NF hai, but transitive dependency hai
CREATE TABLE orders_transitive (
 order_id INT PRIMARY KEY,
 customer_id INT,
 customer_name VARCHAR(100),
 customer_city VARCHAR(100),
 customer_state VARCHAR(100)
);

-- customer_name, customer_city, customer_state sab
-- customer_id pe depend karte hain (non-key ✓ non-key)
-- order_id ✓ customer_id ✓ customer_name (TRANSITIVE!)

-- GOOD: 3NF (transitive dependency hatao)
CREATE TABLE customers_3nf (
 id INT PRIMARY KEY,
 name VARCHAR(100),
 city VARCHAR(100),
 state VARCHAR(100)
);

CREATE TABLE orders_3nf (
 order_id INT PRIMARY KEY,
 customer_id INT,
 FOREIGN KEY (customer_id) REFERENCES customers_3nf(id)
);
ℹ
Real-world example: Agar ek students table mein course_id aur course_fee dono hain, aur course_fee sirf course_id pe depend karta hai � toh course_fee ko alag courses table mein daalo aur sirf course_id (foreign key) rakho.

Normalization ka summary

sql
-- Quick checklist:

1NF: Har cell mein ek value
 +- Comma-separated lists hatao
 +- Repeating groups hatao

2NF: 1NF + Partial dependency hatao
 +- Composite key ke partial dependencies ko alag table mein
 +- (Sirf ek-column primary key ho toh skip)

3NF: 2NF + Transitive dependency hatao
 +- Non-key ✓ Non-key dependency hatao
 +- Har non-key column seedha primary key pe depend kare

Try it: normalization identify karo

Neeche ka editor SQL jaisa hai. Normalized aur unnormalized dono tables ke examples dekho. Code modify karke dekho ki kaise design badalta hai.

SQL playgroundBefore/after normalization dekho
Run query dabayein
SQL queries browser mein run hoti hain

Quick check

Batao 1NF kya hai✓ Ek line mein batao.

Socho ki Excel mein ek cell mein comma-separated list rakh sakte ho ya nahi.

Common normalization mistakes

Normalization clear hai?

Ab Indexes par chalo � query performance kaise optimize karte hain seekho. Database design aur speed dono important hain.