GATE CSE MCQs - DBMS: Keys, Normalization, Transactions and Indexing

Q1. R(A, B, C, D, E) has functional dependencies A → B, B → C and CD → E. Which is a candidate key?

a) CD

b) AD

c) A

d) AB

Q2. A relation R(A, B, C, D) has exactly one candidate key, A. How many superkeys does it have?

a) 4

b) 16

c) 7

d) 8

Q3. A relation is in 3NF but not in BCNF. Which situation causes this?

a) A dependency X → A where X is not a superkey but A is a prime attribute

b) A transitive dependency on a non-prime attribute

c) A partial dependency on part of a key

d) A multivalued attribute

Q4. R is decomposed into R1 and R2. The decomposition is lossless if:

a) Every FD is preserved

b) R1 and R2 have the same number of attributes

c) R1 ∩ R2 → R1 or R1 ∩ R2 → R2 holds in the closure of F

d) R1 ∩ R2 is empty

Q5. Which statement about BCNF decomposition is true?

a) It can always be made lossless, but may not preserve all dependencies

b) It is never lossless

c) It always preserves dependencies but may be lossy

d) It is always lossless and dependency preserving

Q6. Relation R has 10 tuples and S has 20 tuples. How many tuples does R × S (Cartesian product) have?

a) 20

b) 200

c) 30

d) 10

Q7. Which relational algebra operator is the natural fit for queries like "students who have taken all courses"?

a) Division

b) Selection

c) Union

d) Projection

Q8. What does this query return?

SELECT dept, COUNT(*)
FROM emp
GROUP BY dept
HAVING COUNT(*) > 5;

a) All employees in departments with more than 5 people

b) Each department with more than 5 employees, with its employee count

c) The total number of employees

d) Departments with exactly 5 employees

Q9. A schedule is conflict serializable if and only if:

a) It is recoverable

b) Its precedence (conflict) graph has no cycle

c) All transactions commit

d) It has no write operations

Q10. Which property does basic two-phase locking (2PL) guarantee?

a) Conflict serializability

b) Freedom from deadlock

c) No waiting

d) Freedom from cascading rollbacks

Q11. A schedule is recoverable if:

a) All reads happen before writes

b) Whenever Tj reads data written by Ti, Ti commits before Tj commits

c) It is serial

d) No transaction ever aborts

Q12. A disk block is 1024 bytes, a search key is 10 bytes and a block pointer is 6 bytes. What is the maximum order p (pointers per node) of a B+ tree internal node?

a) 65

b) 63

c) 64

d) 62

Q13. Which index has an entry for every search-key value in the data file?

a) Dense index

b) Clustering index only

c) Hash index only

d) Sparse index

Q14. Under the Thomas write rule in timestamp ordering, what happens to an obsolete write (a write by an older transaction to an item already written by a younger one)?

a) The older transaction is aborted

b) The younger transaction is aborted

c) The system deadlocks

d) The write is ignored and the transaction continues

Q15. In ACID, which property is mainly ensured by the recovery manager using logs?

a) None of them

b) Atomicity and durability

c) Consistency only

d) Isolation only