Quiz · Hard
Isolation anomalies, ACID, B-tree and index behaviour, normal forms and distributed trade-offs.
Which ACID property guarantees that a committed transaction survives a power failure?
Atomicity
Consistency
Isolation
Durability
Durability is usually implemented with a write-ahead log flushed to stable storage before commit is acknowledged.
The diagram shows the four ACID properties. A transfer of 100 from account A to B leaves total money unchanged after commit. Which property is this primarily about?

Atomicity
Consistency
Isolation
Durability
Consistency means the transaction moves the database from one valid state (total balance preserved) to another.
Transaction T1 reads a row. T2 updates it and commits. T1 reads it again and sees a new value. What is this anomaly?
Dirty read
Non-repeatable read
Phantom read
Lost update
The same row returned different committed values within one transaction: a non-repeatable read.
T1 runs SELECT COUNT(*) WHERE dept = 5 twice. Between the runs T2 inserts a matching row and commits. T1's second count is higher. What is this?
Dirty read
Non-repeatable read
Phantom read
Deadlock
A new row appearing in a range query result is a phantom read.
Which isolation level allows dirty reads?
READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE
READ UNCOMMITTED is the only standard level that permits reading uncommitted changes.
What is a deadlock?
A transaction that runs too long
Two or more transactions each waiting for locks the others hold
A disk failure during commit
A query without an index
Neither can proceed, so the DBMS picks a victim and rolls it back.
What is the main advantage of MVCC?
It removes the need for indexes
Readers do not block writers and writers do not block readers
It guarantees serializability without cost
It stores only one version of each row
Readers use a snapshot of older row versions, so reads and writes rarely block each other.
Why do relational databases usually use B+ trees for indexes?
They need no disk access
They are balanced, shallow with high fanout and keep linked leaves for range scans
They only support equality
They store rows unsorted
A fanout of hundreds keeps trees only 3 to 4 levels deep, and the linked leaves make range queries efficient.
In the B-tree shown, which child is searched for the key 18?

Left child
Middle child
Right child
The root only
18 is greater than 16, the root's largest key, so the search follows the right pointer (keys 18 and 21).
Given an index on (last_name, first_name), which query can use it most effectively?
WHERE first_name = 'Ann'
WHERE last_name = 'Lee' AND first_name = 'Ann'
WHERE first_name LIKE '%n'
WHERE LOWER(first_name) = 'ann'
The leftmost prefix rule: the index is ordered by last_name first, so filters must start with that column.
A query needs only columns that are all stored in one index. What is that index called relative to the query?
Clustered
Covering
Hash
Sparse
A covering index lets the database answer from the index alone with no table lookups.
Which index type cannot support range queries?
B+ tree
Hash index
Composite B-tree
Clustered index
Hashing destroys ordering, so only equality lookups work.
A table is in 2NF but the attribute dept_name depends on dept_id, which depends on emp_id (the key). Which normal form is violated?
1NF
2NF
3NF
None
A non-key attribute depending on another non-key attribute is a transitive dependency, which 3NF forbids.
Which table is in violation of 1NF?
A table where phone_numbers holds the text '555-1,555-2'
A table with a composite primary key
A table with a foreign key
A table with an index
A cell must hold a single atomic value; a list in one column breaks 1NF.
The diagram shows an Orders table split from Customers. What is the main benefit?
Fewer tables
Customer data is stored once, removing update anomalies
Faster inserts of orders without joins
The foreign key becomes unnecessary
With names and cities in Customers only, a change is made in one row instead of many orders.
Which situation is the best reason to denormalize?
Saving a few kilobytes
Read-heavy reporting where joins are the bottleneck
Frequent updates to the duplicated column
Teaching a database course
Denormalization trades redundancy for read speed. It hurts when duplicated data changes often.
What does the CAP theorem say a distributed system must give up during a network partition?
Either consistency or availability
Partition tolerance
Durability
Both consistency and availability always
When a partition occurs you must choose between returning possibly stale data (availability) and refusing requests (consistency).
Which NoSQL family is Redis most commonly classified under?
Document
Key-value
Graph
Wide-column
Redis stores values (strings, hashes, lists, sets) under keys.
Which database type is best suited for traversing relationships such as friends-of-friends?
Key-value store
Graph database
Time-series database
Plain CSV files
Graph databases (for example Neo4j) store relationships natively and make multi-hop traversals efficient.
Which is a typical sign of the N+1 query problem?
One slow query with a missing index
One query for a list, then one extra query per item
A deadlock
A full table backup
Fix it by joining or batching the child lookups.