DBMS practice questions with answers

Practise DBMS questions at your own pace: ACID, normalisation, keys, indexes, transactions, isolation and the CAP theorem. Pick an answer to see whether you are right and why. Nothing is timed or ranked here; for that, play the Daily Quiz at 4 PM IST.

1. What does ACID stand for in databases?

   easy

   1. Atomicity, Consistency, Isolation, Durability
   2. Accuracy, Concurrency, Integrity, Durability
   3. Atomicity, Concurrency, Indexing, Distribution
   4. Availability, Consistency, Isolation, Durability

   Answer: A. Atomicity, Consistency, Isolation, Durability

   Explanation: Transactions are all-or-nothing, leave the data valid, do not interfere with each other and survive crashes once committed.

2. A foreign key is used to:

   easy

   1. Speed up every query
   2. Link a row to a row in another table and keep that reference valid
   3. Encrypt a column
   4. Make a column unique

   Answer: B. Link a row to a row in another table and keep that reference valid

   Explanation: Referential integrity: you cannot reference a parent row that does not exist, and deleting a referenced row is blocked or cascaded.

3. Which normal form requires every column to hold a single, atomic value?

   easy

   1. 1NF
   2. 2NF
   3. 3NF
   4. BCNF

   Answer: A. 1NF

   Explanation: First normal form: no repeating groups or lists inside a cell.

4. What is an index mainly used for?

   easy

   1. Saving disk space
   2. Speeding up lookups and sorting on its columns
   3. Backing up data
   4. Enforcing data types

   Answer: B. Speeding up lookups and sorting on its columns

   Explanation: Indexes (usually B+ trees) make reads faster at the cost of extra storage and slower writes.

5. A table is in 2NF but has a column that depends on a non-key column. Which normal form does it violate?

   medium

   1. 1NF
   2. 2NF
   3. 3NF
   4. None

   Answer: C. 3NF

   Explanation: 3NF forbids transitive dependencies (key → A → B). Move B into its own table keyed by A.

6. What is a dirty read?

   medium

   1. Reading a corrupted page from disk
   2. Reading data written by another transaction that has not committed yet
   3. Reading the same row twice and getting different values
   4. Reading deleted rows

   Answer: B. Reading data written by another transaction that has not committed yet

   Explanation: Only READ UNCOMMITTED allows dirty reads. Getting different values on a second read is a non-repeatable read.

7. Why do most database indexes use B+ trees rather than binary search trees?

   medium

   1. They are easier to code
   2. Each node holds many keys, so the tree is shallow and needs few disk reads; leaves are linked for range scans
   3. They use no memory
   4. They never need rebalancing

   Answer: B. Each node holds many keys, so the tree is shallow and needs few disk reads; leaves are linked for range scans

   Explanation: A high branching factor means even millions of rows sit within 3–4 levels, matching how disks read whole pages.

8. Which type of relationship needs a junction (bridge) table?

   medium

   1. One-to-one
   2. One-to-many
   3. Many-to-many
   4. None

   Answer: C. Many-to-many

   Explanation: Students and courses: a student_courses table holds pairs of foreign keys, one to each side.

9. What does the CAP theorem say about a distributed database during a network partition?

   hard

   1. It can be consistent, available and partition-tolerant at once
   2. It must choose between consistency and availability
   3. It must stop accepting writes
   4. Partitions never happen

   Answer: B. It must choose between consistency and availability

   Explanation: When nodes cannot talk, either some requests are refused (consistency) or nodes answer with possibly stale data (availability).

10. What is a deadlock in a database, and how do databases usually resolve it?

    hard

    1. A full disk; it is resolved by deleting logs
    2. Two transactions each wait for a lock the other holds; the database detects the cycle and rolls one back
    3. A slow query; it is resolved by adding an index
    4. A failed backup

    Answer: B. Two transactions each wait for a lock the other holds; the database detects the cycle and rolls one back

    Explanation: Applications should be ready to retry a rolled-back transaction, and lock rows in a consistent order to make deadlocks rarer.

11. What is write-ahead logging (WAL) for?

    hard

    1. Compressing data
    2. Durability and crash recovery: changes are logged before data pages are written, so committed work can be replayed
    3. Speeding up SELECTs
    4. Replicating schemas only

    Answer: B. Durability and crash recovery: changes are logged before data pages are written, so committed work can be replayed

    Explanation: After a crash the database replays the log for committed transactions and undoes uncommitted ones.

12. Which statements about denormalisation are true? (Select all that apply)

    hard

    1. It adds redundant data to speed up reads
    2. It makes writes more complex because copies must stay in sync
    3. It is common in analytics and NoSQL designs
    4. It always improves performance

    Answer: A. It adds redundant data to speed up reads; B. It makes writes more complex because copies must stay in sync; C. It is common in analytics and NoSQL designs

    Explanation: It is a trade-off: fewer joins at read time against more work and more risk of inconsistency at write time.

Play the Daily Quiz at 4 PM IST