Back to Study Material GATE Exam

DBMS Previous Year Questions

SQL and relational algebra are the most frequently examined DBMS topics in GATE CS, appearing in virtually every paper from 2010 to the present. The questi...

DBMS Previous Year Questions

PYQ Patterns in SQL and Relational Algebra

SQL and relational algebra are the most frequently examined DBMS topics in GATE CS, appearing in virtually every paper from 2010 to the present. The question types are diverse but fall into a recognizable set of patterns that you can systematically prepare for.

The most common SQL question type gives you a schema with two or three tables and asks what a specific SQL query returns, or asks you to write a query that returns a specific result. GATE 2016 gave a student-course schema and asked what the result of a query with a LEFT JOIN and a WHERE clause on a column that could be NULL would be. The key was recognizing that filtering on a column from the right side of a LEFT JOIN in the WHERE clause effectively converts it to an INNER JOIN, because NULLs fail the WHERE condition.

Aggregate and GROUP BY questions test your understanding of evaluation order. GATE 2018 asked about a query with GROUP BY and HAVING with an aggregate in the HAVING clause. The trick was that HAVING operates on groups after aggregation, so the aggregate in HAVING is computed per group, not globally.

Relational algebra questions ask you to convert an English description or SQL query into a relational algebra expression, or to evaluate a relational algebra expression on a small sample relation. GATE 2014 gave a relational algebra expression with natural joins and projections and asked how many tuples the result contained, given the sizes and schemas of the input relations. The key skill is tracking which attributes remain after each projection and how tuples combine during joins.

Division operator questions are a perennial trap. The relational algebra division operator R divided by S returns the set of tuples from R that are related to every tuple in S. GATE 2013 asked about a scenario that required the division operator semantics, phrased as finding employees who have worked in every department. Recognizing when a query requires division is the challenge; the execution is straightforward once you have recognized it.

Null handling questions require knowledge of three-valued logic. GATE 2019 asked about a query using IS NOT NULL versus checking for a specific value. Understanding that NULLs behave differently in equality comparisons (NULL equals NULL is not true, it is unknown) is essential. Use IS NULL and IS NOT NULL for null testing, never equality operators.

For PYQ practice, solve the SQL and relational algebra questions from GATE 2014 to 2024 in order by topic rather than by year. After solving each, look up the official answer and understand any discrepancy before moving on. SQL questions reward careful logical reasoning more than speed.

PYQ Patterns in Keys, Normalization, and Transactions

Normalization and transaction management questions form the second major cluster of DBMS previous year questions in GATE. These questions require applying well-defined algorithms to given inputs, making them highly predictable in structure even when the specific details vary.

Candidate key questions are among the most frequently tested in GATE. A typical format gives you a relation with several attributes and a set of functional dependencies and asks how many candidate keys the relation has. GATE 2015 gave a relation with five attributes and four functional dependencies and asked for all candidate keys. The approach is to compute closures systematically: identify which attributes are always in candidate keys (those appearing only on left sides of all FDs), which are never in candidate keys (those appearing only on right sides), and which might or might not appear. Then enumerate the possibilities and test closures.

Normal form questions ask you to identify the highest normal form a given relation satisfies, or to perform a decomposition into a target normal form. GATE 2017 gave a relation in a specific normal form and asked which normalization step would eliminate a specific anomaly. Understanding which anomaly each normal form eliminates is key: 2NF eliminates partial dependencies (redundancy due to composite key subsets), 3NF eliminates transitive dependencies, and BCNF eliminates all non-superkey determinants.

Lossless decomposition questions present a decomposition and ask whether it is lossless. GATE 2019 gave a relation and two decompositions and asked which one was lossless. Applying the intersection test to each pair of relations in each decomposition determines the answer. A decomposition into more than two relations requires the Chase algorithm for a complete test, but GATE typically restricts these questions to binary decompositions where the two-condition test suffices.

Transaction and schedule questions ask you to determine whether a given schedule is conflict serializable by constructing the precedence graph. GATE 2016 gave a schedule of four transactions and asked whether it was serializable and, if so, what the equivalent serial order was. Drawing the graph, checking for cycles, and topologically sorting to find the serial order is the complete procedure.

For all these question types, speed matters. Practice candidate key finding, normal form identification, and precedence graph construction until you can complete each exercise in under five minutes. GATE provides two marks for these questions, and losing them to running out of time is avoidable with sufficient practice.

PYQ Patterns in Indexing and Query Processing

Indexing and query processing questions appear less frequently than SQL or normalization questions in GATE but are worth preparing because they tend to be high-value and straightforward once you understand B-plus tree mechanics.

B-plus tree order questions are the most common indexing question type. GATE defines the order of a B-plus tree as the minimum number of keys in a non-root node, or as the maximum number of children per internal node, depending on the textbook being referenced. GATE questions typically specify the page size and key size and ask how many keys fit in a node, or how many levels the tree has for a given number of records. For example, if a B-plus tree of order 4 has a maximum of 3 keys per internal node and 3 data entries per leaf node, then for 1000 records you need at least 334 leaves (rounding up from 1000 divided by 3), and the internal level needs at most 112 nodes to index those leaves, and the root can index all internal nodes if there are at most 3 of them. Tracing through such calculations accurately is the skill being tested.

Index type selection questions ask which index type is more efficient for a given query. Equality queries benefit from both hash indexes and B-plus tree indexes. Range queries require a B-plus tree or another ordered index because a hash index cannot efficiently retrieve all values in a range. Clustered indexes are faster for range scans because the data pages are already ordered, avoiding random disk accesses.

Query optimization questions test your understanding of join algorithms. Nested loop join, block nested loop join, merge join, and hash join are the four standard algorithms. GATE has asked about the I/O cost of each algorithm given buffer sizes and relation sizes. Merge join requires sorted inputs (or adds a sorting cost) but is efficient when both relations are sorted on the join attribute. Hash join partitions both relations into buckets and then joins matching buckets.

Query execution plan questions give you a relational algebra expression and ask about an efficient execution order. Applying selections early before joins reduces the size of intermediate results. Pushing selections down through joins is the primary heuristic used by query optimizers. GATE has asked students to identify which of several execution plans for the same query is most efficient given information about relation sizes.

For PYQ preparation in this area, focus on B-plus tree height and capacity calculations, index type selection for specific query patterns, and basic cost formulas for join algorithms. These three areas cover the majority of indexing and query processing questions that GATE has set.