GATE Databases 2 — Questions and Answers
Question 1: Which normal form eliminates transitive dependencies in a relation?
- 1NF
- 2NF
- 3NF (Correct answer)
- BCNF
Correct answer: 3NF
3NF removes transitive dependencies where a non-key attribute depends on another non-key attribute.
Question 2: In SQL, which isolation level prevents dirty reads but allows non-repeatable reads?
- READ UNCOMMITTED
- READ COMMITTED (Correct answer)
- REPEATABLE READ
- SERIALIZABLE
Correct answer: READ COMMITTED
READ COMMITTED ensures a transaction only reads committed data, preventing dirty reads but not non-repeatable reads.
Question 3: What is the result of a natural join between R(A,B,C) and S(B,C,D)?
- A relation with attributes A,B,C,D joined on B and C (Correct answer)
- A relation with attributes A,B,C,B,C,D
- A Cartesian product of R and S
- A relation with only attributes A and D
Correct answer: A relation with attributes A,B,C,D joined on B and C
Natural join automatically joins on all common attributes (B and C) and produces a result with no duplicate columns.
Question 4: In the context of B+ trees, what is the minimum number of keys in a non-root node of order m?
- ⌈m/2⌉ - 1 (Correct answer)
- ⌊m/2⌋
- m - 1
- ⌈m/2⌉
Correct answer: ⌈m/2⌉ - 1
A non-root node in a B+ tree of order m must have at least ⌈m/2⌉ - 1 keys to maintain the fill-factor invariant.
Question 5: Which relational algebra operation is equivalent to the SQL HAVING clause?
- Selection (σ) applied before grouping
- Selection (σ) applied after aggregation (Correct answer)
- Projection (π) applied to groups
- Division (÷) of grouped tuples
Correct answer: Selection (σ) applied after aggregation
HAVING filters groups after aggregation, equivalent to applying selection on the result of a group-by aggregation in relational algebra.
Question 6: Which of the following is NOT a property of transactions guaranteed by ACID?
- Atomicity
- Consistency
- Isolation
- Distribution (Correct answer)
Correct answer: Distribution
ACID stands for Atomicity, Consistency, Isolation, and Durability — Distribution is not part of ACID.
Question 7: A relation R has 3 candidate keys. The minimum number of superkeys R must have is:
- 3
- 4
- 7
- At least 7 (Correct answer)
Correct answer: At least 7
Every superset of a candidate key is also a superkey, so with 3 candidate keys the count depends on attribute count but is at least 7 (3 keys + their unions).
GATE Databases 2
Which normal form eliminates transitive dependencies in a relation?