4.4.1
Intuition Ek sentence mein pictureEk relational database facts ko a set of tables ki tarah store karta hai, jahan har table ek grid hoti hai: columns fact ke type ka naam batate hain, rows individual records hoti hain, aur ek special marker NULL ka matlab hai "value missing / yahan unknown hai." SQL mein baaki sab kuch is grid-of-facts idea ke upar hi bana hai.
Intuition Yeh jo problem solve karta haiRelations se pehle (Codd, 1970), data hand-linked pointers ki tarah store hota tha (hierarchical/network models). Agar aap storage reorganise karte, aapke programs toot jaate. Codd ki insight thi: data ke logical structure ko uski physical storage se alag karo. Data ko plain mathematical relations ki tarah describe karo aur system ko access figure out karne do.
WHY yeh matter karta hai: aap data ko kya chahiye ke basis pe query karte ho, kaise fetch karna hai ke basis pe nahi. Yahi SQL ke declarative hone ki jad hai.
Math mein ek relation kuch domains se liye gaye tuples ka ek set hai. "Table" ek relation ka roz-marra naam hai.
Definition Core vocabulary
Table (relation): rows ka ek named set jo same columns share karte hain.Column (attribute): ek named slot jiska ek domain hota hai (uska allowed data type, e.g. INTEGER, TEXT).Row (tuple / record): ek fact — har column ke liye ek value.Schema: table ka naam + uske columns + unke domains. Shape (dhanche) ki tarah.Degree: columns ki sankhya. Cardinality: rows ki sankhya.NULL: ek marker jiska matlab value absent / unknown hai. Nahi zero, nahi empty string.
Ek pure relation rows ka set hota hai — order matter nahi karta aur koi duplicate rows nahi hoti. WHY: yeh ek mathematical set hai. (Real SQL tables yeh relax karti hain: technically woh bags/multisets hoti hain aur duplicates allow karti hain jab tak aap ek key nahi add karte — neeche ek steel-mannable subtlety hai.)
Table Student:
id
name
age
major
1
Asha
20
CS
2
Ravi
NULL
Physics
3
Mira
22
NULL
Worked example Grid padhna
Degree = 4 (columns: id, name, age, major). Why? columns gino.Cardinality = 3 (teen rows). Why? records gino.Row 2 kehti hai "Ravi exist karta hai, major Physics hai, lekin age unknown hai ." Why NULL nahi 0? Kyunki hum yeh claim nahi karte ki Ravi 0 saal ka hai — hum claim karte hain ki hum nahi jaante .
Row 3: Mira ka major NULL hai — undeclared hai, "empty major" nahi.
Intuition WHY NULL normal true/false ko tod deta haiOrdinary logic two-valued hoti hai: ek statement TRUE ya FALSE hoti hai. Lekin "Kya Ravi ki age > 21 hai?" jab age unknown ho, yeh honestly TRUE ya FALSE nahi ho sakta. Isliye SQL ek teesri value introduce karta hai: UNKNOWN . Yahi three-valued logic (3VL) hai.
Worked example Classic NULL gotchaSELECT * FROM Student WHERE age = 20 OR age <> 20;
Aap sochte ho yeh sab ko return karega. Lekin Ravi ke liye, age NULL hai:
NULL = 20 → UNKNOWNNULL <> 20 → UNKNOWNUNKNOWN OR UNKNOWN → UNKNOWN → row excluded
Yeh step kyun? WHERE non-TRUE rows drop kar deta hai. Ravi gayab ho jaata hai. Fix: NULLs ko IS NULL / IS NOT NULL se test karo, jo real TRUE/FALSE return karte hain:
WHERE age = 20 OR age <> 20 OR age IS NULL;
Common mistake "NULL equals NULL"Why yeh sahi lagta hai: everyday code mein, x == x hamesha true hota hai. Why yeh galat hai: NULL ka matlab unknown hai; do unknown values ko equal prove nahi kiya ja sakta. NULL = NULL → UNKNOWN, TRUE nahi.
Fix: IS NULL use karo. Joins/grouping mein NULLs match karne ke liye, kuch systems IS NOT DISTINCT FROM use karte hain.
Common mistake "NULL sirf 0 ya empty string hai"Why yeh sahi lagta hai: screen pe woh sab empty dikhte hain. Why yeh galat hai: 0 ek jaana-pehchana number hai, '' ek jaani-pehchani empty text hai; NULL kisi bhi value ki absence hai. COUNT(col) NULLs skip karta hai lekin 0 aur '' count karta hai.
Fix: inhe mentally alag rakho; substitute karne ke liye COALESCE(col, default) use karo.
Common mistake "Tables mein row order hota hai"Why yeh sahi lagta hai: results aksar insertion order mein wapas aate hain. Why yeh galat hai: ek relation ek set hai; order data ka hissa nahi hai. ORDER BY ke bina, order guaranteed nahi hai .
Fix: jab order matter kare hamesha ORDER BY add karo.
Common mistake "Rows hamesha unique hoti hain"Why yeh sahi lagta hai: mathematical relation duplicates forbid karta hai. Why yeh galat hai: real SQL tables multisets hain; duplicates allowed hain jab tak koi PRIMARY KEY/UNIQUE constraint forbid na kare.
Fix: uniqueness enforce karne ke liye ek key define karo.
Recall Quick self-test (answers chhupao, pehle recall karo)
Degree vs cardinality? → degree = #columns, cardinality = #rows.
NULL ka matlab kya hai? → value missing/unknown, 0/'' nahi.
NULL = NULL kya return karta hai? → UNKNOWN.NULL ke liye test kaise karte hain? → IS NULL / IS NOT NULL.
FALSE AND NULL? → FALSE. TRUE OR NULL? → TRUE.Kya ek row WHERE se guzarti hai agar condition UNKNOWN ho? → Nahi, sirf TRUE survive karta hai.
Recall Feynman: ek 12-saal ke bacche ko samjhaoEk class attendance grid imagine karo. Har column ek sawal hai jo hum ek bachche ke baare mein poochte hain (naam, umar, favourite sport). Har row ek bachche ke jawab hain. Poori grid table hai. Kabhi kabhi ek bachcha apni umar likhna bhool jaata hai — woh khaali box "zero saal ka" nahi hai, iska matlab hai hum abhi nahi jaante . Woh blank-meaning-unknown NULL kehlata hai. Aur yahan funny rule hai: agar aap poochho "kya yeh unknown umar 10 se badi hai?" toh honest jawab hai "main bata nahi sakta" — isliye computer unknown kehta hai aur us bachche ko answer list se skip kar deta hai.
Mnemonic NULL ki nature yaad rakho"NULL = no clue." No clue zero nahi hai, blank text nahi hai — yeh no clue hai. Aur no clue ≠ no clue (UNKNOWN), kyunki aap do cheezein compare nahi kar sakte jo aap jaante hi nahi.
Primary Keys and Uniqueness — duplicate rows kaise forbid kareinForeign Keys and Referential Integrity — tables ko link karnaSQL SELECT and WHERE — three-valued logic in actionNormalization — anomalies avoid karne ke liye columns organize karnaData Types and Domains — ek column kya hold kar sakta haiCOALESCE and NULL handling functions
Relational model mein ek relation/table kya hota hai? Rows (tuples) ka ek named set jo sab same columns (attributes) share karte hain.
Table ka degree kya hota hai? Columns ki sankhya.
Table ki cardinality kya hoti hai? Rows ki sankhya.
NULL kya represent karta hai? Ek missing/unknown value — nahi zero aur nahi empty string.
NULL = NULL ka result?UNKNOWN (TRUE nahi).
NULL ki wajah se SQL kaun si logic use karta hai? Three-valued logic (TRUE, FALSE, UNKNOWN).
FALSE AND NULL evaluate hota hai?FALSE (unknown ki wajah se already false, chahe kuch bhi ho).
TRUE OR NULL evaluate hota hai?TRUE (unknown ki wajah se already true, chahe kuch bhi ho).
Kya ek row WHERE clause pass karti hai jab uski condition UNKNOWN ho? Nahi — sirf woh rows rakhi jaati hain jahan condition TRUE ho.
Missing value test karne ka sahi tarika? IS NULL / IS NOT NULL use karo.
Bina ORDER BY ke row order guaranteed kyun nahi hai? Ek relation mathematically ek set hoti hai, isliye order data ka hissa nahi hai.
COALESCE(x, d) kya karta hai? x return karta hai agar woh NULL nahi hai, warna default d return karta hai.
Logical vs physical structure
