DBMS can be a reliable scoring subject in GATE CS, but only if you practise applying its rules. Reading theory once will not prepare you to decompose a relation, test serializability or calculate B+ tree order. Organize your preparation around the official syllabus, a qualitative PYQ priority map and a prerequisite-aware study order. The priority labels are not an official mark split.
DBMS for GATE CS: what the official syllabus covers
The official GATE 2027 test-papers and syllabus page links the CS syllabus. Its DBMS scope can be grouped into six areas:
ER model.
Relational model: relational algebra, tuple calculus and SQL.
Integrity constraints.
Normal forms; functional dependencies are the tool used to reason about keys and normalization.
File organization and indexing, including B-trees and B+ trees.
Transactions and concurrency control.
For learning, fold integrity constraints into relational model and SQL, then study FDs before normal forms. Hashing belongs elsewhere in the current syllabus, while query-processing cost, distributed databases and detailed recovery logging are outside its DBMS wording. Treat them as optional enrichment after the listed areas.
DBMS question weightage: a qualitative PYQ priority map
GATE CS does not publish a topic-wise DBMS mark split. The table is a qualitative study-priority map based on recurring question forms in PYQs, not a forecast or a marks average. 'First priority' means a topic supports several repeatable problem types; 'Foundation' means it enables later topics even when it is not the final question.
Area | Qualitative weightage | How it is tested |
|---|---|---|
Normalization + FDs | First priority | Find candidate keys, highest normal form and lossless decomposition |
Transactions + concurrency | First priority | Conflict serializability, precedence graphs, 2PL and deadlock |
SQL + relational algebra | First priority | Find query output or cardinality, or an equivalent algebra expression |
Indexing (B/B+ trees) | First priority | Calculate order, fan-out, levels or block accesses |
ER + integrity constraints | Foundation | Map ER designs to relations and apply key, domain and referential constraints |
Tuple calculus | Second pass | Translate a quantified query between tuple-calculus notation and its meaning |
These labels set study order, not expected marks. Confirm the applicable syllabus, paper pattern and marking rules on the official GATE site linked above.
Normalization and functional dependencies: a first-priority drill
Use one drill: compute closures, find candidate keys, mark prime attributes, identify the highest normal form, then test decomposition properties.
Take R(StudentID, Course, Instructor, InstructorPhone) with these FDs:
{StudentID, Course} -> InstructorInstructor -> InstructorPhone
The closure {StudentID, Course}+ = {StudentID, Course, Instructor, InstructorPhone} reaches every attribute. Neither StudentID nor Course can be removed, and neither appears on an FD's right side, so both are required. Thus {StudentID, Course} is the only candidate key. Its attributes are prime; the other two are non-prime.
The relation is in 1NF and 2NF because no non-prime attribute depends on part of the key. It fails 3NF and BCNF at Instructor -> InstructorPhone: the determinant is not a superkey and the dependent attribute is non-prime.
Decompose into R1(StudentID, Course, Instructor) and R2(Instructor, InstructorPhone). Their common attribute, Instructor, is R2's key, which proves a lossless join. Each original FD remains inside one relation, so the decomposition also preserves dependencies.
Never stop at “it looks normalized.” Check transitive dependencies and prove losslessness. Then practise the drill in DBMS Normalization MCQs: 12 solved questions from 1NF to BCNF.
Transactions and concurrency: another first-priority drill
Create one graph node per transaction. Add a directed edge for conflicting operations on the same item when at least one is a write. A cycle means the schedule is not conflict serializable.
For S: R1(A), W1(A), R2(A), W2(A), every conflict points from T1 to T2: W1-R2, W1-W2 and R1-W2. The graph has one effective edge, T1 -> T2, and no cycle. S is conflict serializable, equivalent to T1 then T2.
For R1(A), R2(A), W1(A), W2(A), R2-W1 gives T2 -> T1, while R1-W2 and W1-W2 give T1 -> T2. This cycle makes the schedule not conflict serializable. The operations are unchanged; only their interleaving changes the result.
Know ACID, basic versus strict two-phase locking, and recoverable versus cascadeless schedules. 2PL guarantees conflict serializability, not deadlock freedom, and a serializable schedule need not be serial.
Indexing and B+ trees: where the numericals live
The standard numerical asks for internal-node order. Let block size B = 4096 bytes, search key K = 12 bytes and pointer P = 8 bytes. An internal B+ tree node of order n stores n pointers and n - 1 keys:
nP + (n - 1)K <= B
Substitute the values:
8n + 12(n - 1) <= 4096
8n + 12n - 12 <= 4096
20n <= 4108
n <= 205.4
Therefore the maximum integer order is n = 205, giving 205 pointers and 204 keys. Check the boundary: order 205 uses 205 x 8 + 204 x 12 = 4088 bytes and fits; order 206 uses 206 x 8 + 205 x 12 = 4108 bytes and fails.
High fan-out keeps even large indexes shallow. Do not use leaf capacity when asked for internal order. In a B+ tree, all data entries are in linked leaves, unlike a B-tree. For dense and sparse indexes, fan-out, height and insertion, continue with B+ Trees and Database Indexing: Worked Insert Example.
SQL and relational algebra: a fast query-output drill
GATE often asks what a query returns. Suppose Employee(eid, dept) has 5 rows and Department(dept, mgr) has 3. Department.dept is unique, and every employee has a matching department. Their natural join therefore returns 5 rows, one per employee, not the cross product's 15.
In relational algebra, select matching department values from Employee x Department, then project the required columns. The product creates 5 x 3 = 15 pairs; the equality condition keeps one per employee, leaving 5.
Also revise GROUP BY with HAVING, COUNT versus COUNT(DISTINCT), and correlated subqueries. Remember that joins do not always multiply rows, NULL does not match through =, and COUNT(column) ignores it. Practise these traps in DBMS SQL Query MCQs: 12 solved questions on SELECT, joins and subqueries.
The prep order and the short version
Learn ER modelling, keys, the relational model and algebra first. Follow with SQL, FDs and normalization, transactions and concurrency, indexing and B+ trees, then use query processing only as optional enrichment. Solve topic-wise PYQs after each official area. This is a dependency order, not an eight-week timetable; scale each stage to the time you actually have.
If time is short, protect normalization, transactions, indexing and SQL. Their priority comes from PYQ patterns, not a guaranteed split.
DBMS rewards rule-based practice, not passive reading. Use GATE Guidance by Sanchit Sir for a guided syllabus sequence. To work topic by topic, open the DBMS learning module and follow the same dependency order.
