Complete DBMS Notes for GATE CSE, UGC NET, PSU exams. Here you will get ER Model, Normalization, SQL, Transactions, PYQs and PDF download.

DBMS Syllabus for GATE CSE

ER Model
Relational Model
Normalization
SQL
Transactions

DBMS Complete Super Notes for GATE 2026 – 1800 Words

DBMS se GATE me har saal 6-8 marks pakka aata hai. Ye notes PYQ analysis se bane hain. Isko yaad kar liya to 80% questions direct ho jayenge.

1. ER Model & Relational Model [Most Important]

Entity: Real world object. Relationship: Association between entities.

Cardinality: 1:1, 1:N, M:N. GATE Trick: M:N relationship ko hamesha alag table banta hai.
Weak Entity: Jiska apna primary key na ho. Uska key = Partial Key + Owner Entity ka Primary Key.
Relational Model: Table = Relation. Row = Tuple. Column = Attribute.
Keys: Super Key > Candidate Key > Primary Key. Foreign Key dusre table ko refer karta hai.

Keys:
Super Key: Jo uniquely identify kare, extra attribute bhi ho sakta hai.
Candidate Key: Minimal Super Key. Isme se koi attribute hatao to unique nahi rahega.
Primary Key: Candidate Key me se ek jo hum select kar lete hain.
Alternate Key: Jo Candidate Key Primary Key nahi bana.
Foreign Key: Dusre table ke Primary Key ko apne table me refer karna.

2. Functional Dependency & Normalization [GATE ka Baap]

Ye topic se har saal 2 marks ka question aata hi hai.

Functional Dependency (FD): X -> Y ka matlab X se Y uniquely decide hoga. Example: RollNo -> Name.

Armstrong’s Axioms: Reflexive, Augmentation, Transitivity. Inse naye FD nikalte hain.

Closure (X+): X se kya kya derive ho sakta hai. Candidate Key nikalne ke liye X+ = All Attributes hona chahiye.

Normalization – Full Trick

Normal Form	Rule	GATE Trick
1NF	Har cell me atomic value	Multi-valued attribute hatao
2NF	1NF + No Partial Dependency (Non-prime -> Part of Candidate Key)	Composite key wale table me dekho
3NF	2NF + No Transitive Dependency (Non-prime -> Non-prime)	A->B, B->C hai to 3NF fail
BCNF	Har FD me Left side Super Key hona chahiye	3NF se bhi strict hai. Agar 3NF fail to BCNF bhi fail.

GATE Super Trick: Question me R(A,B,C) aur FD A->B, B->C diya hai. To A Candidate Key hai. A->B (B non-prime but A is Super Key so 3NF OK), B->C (B Super Key nahi hai, C non-prime) to 3NF Fail, BCNF Fail. Ye direct PYQ hai.

3. SQL & Relational Algebra

Relational Algebra: σ (Selection – Row filter), π (Projection – Column filter), ⨝ (Natural Join), × (Cross Product)
SQL: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY
Important: WHERE row pe filter karta hai, HAVING Group pe. GATE me isse confusion dete hain.
Aggregate: COUNT, SUM, AVG, MAX, MIN. NULL ke saath COUNT(*) aur COUNT(column) me farak hai.
JOIN: Inner Join = Common wale, Left Join = Left ke saare + common, Right Join = Right ke saare.

SQL – Important for GATE:
Difference between WHERE and HAVING: WHERE row par filter lagata hai, GROUP BY se pehle chalta hai. HAVING group par filter lagata hai, GROUP BY ke baad chalta hai.
NULL: NULL ka koi value se comparison Unknown deta hai. COUNT(*) NULL ko count karta hai, COUNT(column) NULL ko count nahi karta.
ORDER BY, GROUP BY: GROUP BY me jo attribute hai wahi SELECT me aana chahiye ya Aggregate function me.
Nested Query: IN, EXISTS, ANY, ALL. EXISTS correlated query me fast hota hai.

4. Transactions, Concurrency & Recovery [4 Marks Pakka]

ACID Property: Atomicity, Consistency, Isolation, Durability.

Schedule & Serializability

Conflict Serializable: Precedence Graph banao. Agar Cycle hai to NOT Conflict Serializable. No Cycle = Serializable. Ye GATE me 2 marks ka fix hai.
View Serializable: Conflict se bhi bada concept hai. Agar Conflict Serializable hai to View bhi hai, par ulta jaruri nahi.

Concurrency Control Protocols:

2-Phase Locking (2PL): Growing Phase (Lock lo) + Shrinking Phase (Lock chhodo). Guarantees Conflict Serializability.
Strict 2PL: Exclusive Lock ko Commit tak hold karo. No Cascading Abort.
TimeStamp Protocol: Har transaction ko timestamp. W-Timestamp, R-Timestamp se check.
Thomas Write Rule: Obsolete Write ko ignore kar deta hai, concurrency badhata hai.

Recovery – Log Based

WAL Protocol: Write-Ahead Logging. Data update se pehle Log likhna padega.
UNDO/REDO: Commit se pehle fail = UNDO, Commit ke baad fail = REDO.

5. Indexing B & B+ Tree [Numerical Wala]

B Tree vs B+ Tree: GATE sirf B+ Tree poochta hai. B Tree me har node me data hota hai. B+ Tree me sirf Leaf node me data hota hai, Internal node sirf Index ke liye. Leaf nodes aapas me Linked List se jude hote hain, isliye Range Query B+ Tree me bahut fast hoti hai.

Primary Index: Data sorted hai, index sorted hai. Dense ya Sparse ho sakta hai.
B Tree: Har node me data bhi hota hai.
B+ Tree (GATE Favorite): Sirf Leaf node me data, internal node sirf routing. Leaf nodes Linked List se jude hote hain. Range Query fast.

Formula: B+ tree ka order ‘p’ hai to max children = p, max keys = p-1, min children = ceil(p/2)

B+ Tree Formula – Numerical Pakka:
Order = p
Max Children = p
Max Keys = p-1
Min Children = ceil(p/2)
Min Keys = ceil(p/2) -1Numerical Trick: Block size 4096 byte, Key size 20 byte, Pointer 6 byte. Record 100000. B+ Tree ki height kitni?
Entry size = Key + Pointer = 26. Blocking Factor = 4096 / 26 = 157. Height = log_157 (100000) = 2 ya 3. Aise question GATE 2021 me aaya tha.

GATE Numerical Trick: 1 lakh records, block size 4096, key size 32. Kitne level ka B+ tree lagega? Formula: Blocking Factor = Block Size / (Key + Pointer). Level = log blocking_factor (total records).

6. Last Minute Formula Sheet for Revision

Candidate Key: X+ = R (all attributes)
Lossless Join: R1 ∩ R2 -> R1 ya R2 (Common attribute Super Key hona chahiye)
BCNF Condition: For every X->Y, X is Super Key
Conflict Serializable: Precedence Graph me No Cycle
B+ Tree Height: h = log_p (n)

Final Tip: DBMS me theory se zyada numerical aur concept pe dhyan do. Normalization aur Serializability ke 4 marks pakka samjho.

Next: Operating System Super Notes + PYQ Trick

Complete DBMS Notes for GATE CSE 2026-PDF DOWNLOAD