Databases for the GATE Exam

Common failure modes

These are the patterns that cause most candidates to lose marks on this topic. Recognising them in advance is half the work.

• !Confusing natural join (merges on common attribute name) with equi-join (explicit equality condition)
• !Normalisation errors: misidentifying partial dependencies for 2NF or transitive dependencies for 3NF
• !Forgetting that BCNF requires every determinant to be a superkey, not just a candidate key
• !Misapplying conflict serialisability test — forgetting to check for cycles in the precedence graph
• !SQL NULL logic errors: NULL compared with any value (including NULL) yields UNKNOWN, not TRUE or FALSE

Study tips

• 1Drill relational algebra: σ (selection), π (projection), ⋈ (natural join), ÷ (division). GATE gives relational algebra expressions and asks for the result — don't confuse the operators.
• 2Practice finding all functional dependencies' closures for a given relation and identifying all candidate keys.
• 3For normalisation, always verify: 1NF (atomic values), 2NF (no partial dependencies), 3NF (no transitive dependencies), BCNF (every determinant is a superkey).
• 4Memorise the ACID properties and their implementation: Atomicity (undo log), Consistency (constraints), Isolation (locking/MVCC), Durability (redo log).
• 5Practice SQL: GROUP BY + HAVING, nested subqueries with EXISTS/IN, and outer joins are the most frequently tested SQL constructs.

Sample GATE Databases questions

These sample items mirror the format and difficulty of real GATE questions. Practice with thousands more on the free Koydo question bank.

1. 1

   Relation R(A, B, C) with FDs: A→B, B→C. The highest normal form R is in is:

   • A1NF
   • B2NFCorrect
   • C3NF
   • DBCNF
   Why this answer?

   (GATE CS style) The candidate key is A (since A→B and B→C give A→ABC). C is transitively dependent on A via B (A→B→C). This violates 3NF. R is in 2NF (no partial dependencies since key is single-attribute) but not 3NF.

2. 2

   In SQL, the result of: SELECT * FROM T WHERE col = NULL is:

   • AAll rows where col is NULL
   • BNo rows (empty result)Correct
   • CAn error
   • DAll rows in T
   Why this answer?

   (GATE CS style) In SQL, any comparison with NULL evaluates to UNKNOWN, not TRUE. The WHERE clause retains only rows where the condition is TRUE. Therefore no rows satisfy col = NULL. The correct syntax is col IS NULL.

3. 3

   A schedule is conflict-serializable if and only if its precedence (serialization) graph is:

   • AA tree
   • BAcyclic (has no cycles)Correct
   • CA complete graph
   • DBipartite
   Why this answer?

   (GATE CS style) A schedule is conflict-serializable iff its precedence graph (directed graph with an edge from Tᵢ to Tⱼ for each conflicting operation pair) contains no directed cycle. A topological sort of the acyclic graph gives the equivalent serial order.