Databases Flashcards

Given relation R(A,B,C,D) with FDs {A→B, B→C, C→D}, which is the highest normal form R is in if A is the only key?

Answer: 2NF

B→C and C→D are transitive dependencies on the key A, so R violates 3NF and is only in 2NF (no partial key dependencies exist since the key is single-attribute).

Which of the following is the correct order of SQL query logical execution?

Answer: FROM → WHERE → GROUP BY → HAVING → SELECT

SQL logical execution order is FROM, WHERE, GROUP BY, HAVING, SELECT (then ORDER BY), regardless of written order.

In DBMS crash recovery, the UNDO phase of ARIES protocol:

Answer: Undoes all loser transactions in reverse log order

ARIES UNDO phase rolls back all loser (incomplete) transactions in reverse chronological order using compensation log records.

The time complexity of finding a record using a dense primary index with binary search over n index blocks is:

Answer: O(log n)

Binary search on a sorted dense index with n blocks requires O(log n) comparisons, each costing one disk I/O.

Which join algorithm is most efficient when one relation fits entirely in memory?

Answer: Hash join

Hash join builds an in-memory hash table on the smaller relation and probes it with the larger, achieving O(B(R)+B(S)) I/Os when the smaller relation fits in memory.

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

Answer: Acyclic

A schedule is conflict-serializable if and only if the precedence graph constructed from conflicting operations contains no cycles.

In SQL, what does the COALESCE(a, b, c) function return?

Answer: The first non-NULL value among a, b, c

COALESCE returns the first non-NULL argument from its list, or NULL if all arguments are NULL.