DBMS Interview Questions and Answers

A clustered index defines the physical order of rows in the table. Because the data is stored in that order, a table can have only one clustered index, usually the primary key when the engine supports clustered storage, as SQL Server does. Range scans and ordered retrieval along the key are fast because rows are adjacent.

A non-clustered index is a separate structure that stores the indexed key plus a pointer back to the row. A table can have many. A lookup may require an extra hop, called a key lookup, to fetch remaining columns. If the index includes all queried columns, it covers the query and avoids that hop.

CREATE CLUSTERED INDEX ix_orders_date ON orders(order_date);
CREATE INDEX ix_orders_customer ON orders(customer_id) INCLUDE (total);

Choose a clustered key that is narrow, unique, stable and ever-increasing, like an identity, to avoid page splits. Wide or random clustered keys such as UUIDs can cause fragmentation and poor insert performance.

Normalisation organises columns into tables to reduce redundancy and update anomalies. The common normal forms:

• 1NF: atomic values, no repeating groups, each row unique.
• 2NF: 1NF plus no partial dependency on part of a composite key.
• 3NF: 2NF plus no transitive dependency, so non-key attributes depend only on the key.
• BCNF: every determinant is a candidate key, a stricter version of 3NF.

Unnormalised: order(id, customer_name, customer_city, product, qty)
3NF: customer(id, name, city)
     product(id, name, price)
     order(id, customer_id)
     order_item(order_id, product_id, qty)

Each fact is stored once, so updating a customer's city touches one row. Normalisation improves integrity and simplifies writes, and it can cost joins on read. Denormalisation deliberately reintroduces redundancy, for example storing a total or a copied name, to speed reads, and must be maintained carefully with triggers or application logic.

ACID describes the guarantees of a transaction, a unit of work that either completes fully or not at all.

• Atomicity: all statements commit or all roll back. Implemented with logging and undo.
• Consistency: the database moves from one valid state to another, respecting constraints, keys and invariants.
• Isolation: concurrent transactions do not observe each other's uncommitted intermediate states; the degree is set by the isolation level.
• Durability: once committed, changes survive a crash, usually by writing to a write-ahead log and fsyncing before acknowledging.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- or ROLLBACK

The transfer is atomic and consistent, and isolation prevents another transaction from seeing only the debit. Durability is often relaxed slightly for performance, and distributed systems extend the idea with two-phase commit or consensus protocols.

SQL defines four isolation levels that trade consistency against concurrency. They are usually described by which anomalies they permit.

• Read uncommitted: dirty reads allowed, sees uncommitted changes.
• Read committed: no dirty reads, but non-repeatable reads and phantoms can occur. The common default in PostgreSQL and Oracle.
• Repeatable read: rows read stay stable within the transaction, but phantoms may appear. MySQL InnoDB's default, implemented with MVCC.
• Serializable: transactions behave as if run one at a time, preventing all three anomalies, at the cost of blocking or aborts.

level              dirty  non-repeatable  phantom
read uncommitted    yes        yes           yes
read committed      no         yes           yes
repeatable read     no         no            yes
serializable        no         no            no

Implementations vary: MVCC snapshots give readers a consistent view without blocking writers, while lock-based systems block. Higher isolation reduces anomalies but increases contention, deadlocks and retries, so choose per transaction rather than globally.

A database deadlock is a cycle where two or more transactions each hold a lock the other needs, so neither can proceed.

T1: UPDATE a; UPDATE b;
T2: UPDATE b; UPDATE a;  -- cycle

Most engines detect deadlocks by maintaining a wait-for graph and aborting a victim, which then rolls back and surfaces an error that the application should retry.

Prevention and mitigation:

• Access tables and rows in a consistent order across the application.
• Keep transactions short and avoid user interaction inside them.
• Touch rows in a deterministic order and use appropriate indexes so locks target fewer rows.
• Use lower isolation where acceptable, or optimistic concurrency with version columns.
• Retry on deadlock with backoff, since avoiding all cycles in a busy system is unrealistic.

Deadlocks differ from lock waits: a long wait is usually contention, while a deadlock is a genuine cycle. Monitor deadlock graphs and index scans to find hot spots.

Query optimisation starts by measuring. EXPLAIN shows the plan; EXPLAIN ANALYZE runs it and reports actual rows and time.

Things to look for: sequential scans on large tables, nested loops over big inputs, misestimated row counts, sorts spilling to disk and repeated subplans.

Common improvements:

• Add the right index for filtering and join columns, and consider covering indexes with included columns.
• Keep statistics fresh so the planner estimates well, and avoid functions on indexed columns in predicates.
• Select only needed columns, and avoid SELECT *.
• Rewrite correlated subqueries as joins where appropriate, and reduce the number of joins per query.
• Verify the join order and join types suit the data distribution.

EXPLAIN ANALYZE
SELECT id FROM orders WHERE customer_id = 42 ORDER BY created_at DESC;

Indexes speed reads but slow writes and consume space, so balance them. Always verify with real data volumes, because plans on tiny test tables are misleading.

Both approaches handle concurrent updates to the same data.

Pessimistic locking assumes conflicts are likely, so it locks a row when read: SELECT ... FOR UPDATE or a shared lock. Other transactions block until the lock is released at commit or rollback. This prevents lost updates directly but reduces concurrency and risks deadlocks and long waits. It suits high-contention, short transactions.

Optimistic locking assumes conflicts are rare. You read a row with its version or timestamp, do your work, then update with a condition that the version is unchanged.

UPDATE items SET qty = 5, version = version + 1
WHERE id = 7 AND version = 3;
-- 0 rows affected means someone else changed it

If zero rows are affected, you detect the conflict and retry or report it. This avoids holding locks during user think time and scales well, but every write must check the result and handle retries. Many ORMs expose both, and hybrid strategies are common.