Databases Flashcards

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:

Answer: O(1)

With a good hash function and no overflow, hashing directly computes the bucket address in O(1) disk I/Os.

Which of the following SQL constraints ensures that a column value in one table exists in another table?

Answer: FOREIGN KEY

A FOREIGN KEY constraint enforces referential integrity by requiring the value to exist as a primary key in the referenced table.

The two-phase locking (2PL) protocol guarantees:

Answer: Serializability of transactions

2PL guarantees conflict-serializability by requiring all lock acquisitions to precede any lock release.

Which functional dependency set is equivalent to {A→BC, B→C, AB→C}?

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}.

What does the term 'phantom read' refer to in transaction isolation?

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.

In the E-R model, a 'weak entity set' is one that:

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.

Which SQL command is used to remove all rows from a table without logging individual row deletions?

Answer: TRUNCATE TABLE table

TRUNCATE removes all rows using minimal logging (deallocating data pages) and is faster than DELETE for bulk removal.