GATE Databases 3 — Questions and Answers
Question 1: In a hashed file organization, if the bucket size is B and there are N records, the expected number of disk I/Os for a search with no overflow is:
- O(N)
- O(log N)
- O(1) (Correct answer)
- O(N/B)
Correct answer: O(1)
With a good hash function and no overflow, hashing directly computes the bucket address in O(1) disk I/Os.
Question 2: Which of the following SQL constraints ensures that a column value in one table exists in another table?
- PRIMARY KEY
- UNIQUE
- FOREIGN KEY (Correct answer)
- CHECK
Correct answer: FOREIGN KEY
A FOREIGN KEY constraint enforces referential integrity by requiring the value to exist as a primary key in the referenced table.
Question 3: The two-phase locking (2PL) protocol guarantees:
- Freedom from deadlock
- Serializability of transactions (Correct answer)
- High throughput under contention
- No cascading rollbacks
Correct answer: Serializability of transactions
2PL guarantees conflict-serializability by requiring all lock acquisitions to precede any lock release.
Question 4: Which functional dependency set is equivalent to {A→BC, B→C, AB→C}?
- {A→B, B→C}
- {A→BC, B→C} (Correct answer)
- {AB→C, B→C}
- {A→C, B→C}
Correct answer: {A→BC, B→C}
AB→C is redundant since A→BC already implies A→C, so the minimal equivalent set is {A→BC, B→C}.
Question 5: What does the term 'phantom read' refer to in transaction isolation?
- Reading a value that was never committed
- Reading the same row twice and getting different values
- A query returning rows that were inserted by another transaction mid-execution (Correct answer)
- A read that returns no rows due to a concurrent delete
Correct answer: A query returning rows that were inserted by another transaction mid-execution
A phantom read occurs when a transaction re-executes a range query and finds new rows inserted by another committed transaction.
Question 6: In the E-R model, a 'weak entity set' is one that:
- Has no attributes of its own
- Cannot exist without a related entity in another set (Correct answer)
- Has only partial keys
- Cannot participate in more than one relationship
Correct answer: Cannot exist without a related entity in another set
A weak entity set lacks sufficient attributes to form a primary key and depends on a strong entity set for its existence.
Question 7: Which SQL command is used to remove all rows from a table without logging individual row deletions?
- DELETE FROM table
- DROP TABLE table
- TRUNCATE TABLE table (Correct answer)
- REMOVE FROM table
Correct answer: TRUNCATE TABLE table
TRUNCATE removes all rows using minimal logging (deallocating data pages) and is faster than DELETE for bulk removal.
GATE Databases 3
In a hashed file organization, if the bucket size is B and there are N records, the expected number of disk I/Os for a search with no overflow is: