DBMS Interview Questions with Answers

Atomicity: transaction is all-or-nothing. Consistency: database moves from one valid state to another. Isolation: concurrent transactions don't interfere. Durability: committed data survives system failure. Example: bank transfer — debit and credit must both succeed or both fail (Atomicity).

SQL (MySQL, PostgreSQL): structured schema, tables, ACID, joins, vertical scaling. Best for: complex queries, transactions. NoSQL (MongoDB, Redis, Cassandra): flexible schema, documents/key-value/graph, eventual consistency, horizontal scaling. Best for: high throughput, flexible data, massive scale.

Isolation levels (weakest to strongest): READ UNCOMMITTED (dirty reads possible), READ COMMITTED (default in most DBs), REPEATABLE READ (prevents non-repeatable reads), SERIALIZABLE (full isolation, slowest). Higher isolation = fewer anomalies but lower concurrency.

Stored procedure: precompiled SQL, can modify data, return multiple values, use transactions. Called with CALL/EXEC. Function: returns a single value, can be used in SELECT statements, typically no side effects. Functions are used in queries, procedures for business logic.

Denormalization intentionally adds redundancy to reduce joins and improve read performance. Use when: (1) Read-heavy workloads, (2) Complex joins causing slow queries, (3) Reporting/analytics tables. Trade-off: faster reads but slower writes, data inconsistency risk. Common in data warehouses.