DBMS Interview Questions

ACID properties ensure database transactions are processed reliably:

1. Atomicity: The transaction must succeed fully or fail completely ('all-or-nothing'). If one statement fails, the entire transaction is rolled back.
2. Consistency: Database state must remain valid according to all schema rules, constraints, triggers, and cascades before and after the transaction.
3. Isolation: Ensures concurrent execution of transactions leaves the database in the same state as if they were executed sequentially. Isolated levels prevent dirty reads, non-repeatable reads, and phantom reads.
4. Durability: Once a transaction commits, its modifications are permanently recorded on non-volatile media (disk) and will not be lost even in a system crash.

Normalization is the process of structuring a relational database to minimize redundancy and avoid insertion, update, and deletion anomalies.

• 1NF (First Normal Form): Attributes must contain only atomic (indivisible) values, and there must be no repeating groups or multi-valued columns.
• 2NF (Second Normal Form): Must be in 1NF, and all non-key attributes must be fully functionally dependent on the entire primary key (eliminating partial dependencies where an attribute depends on only a part of a composite key).
• 3NF (Third Normal Form): Must be in 2NF, and no non-prime attribute should be transitively dependent on the primary key (meaning a non-prime attribute cannot depend on another non-prime attribute).
• BCNF (Boyce-Codd Normal Form): A stronger version of 3NF. For every non-trivial functional dependency X -> Y, X must be a super key.

Indexing is a data structure technique to quickly locate data without scanning the entire table.

B-Tree Index (default in most RDBMS): - Stores data in a balanced tree structure with sorted keys. - Supports range queries (WHERE age > 25), ORDER BY, and pattern matching with prefix (LIKE 'abc%'). - Time complexity: O(log N) for search, insert, delete.

Hash Index: - Uses a hash function on the key to directly map to the data block. - Only supports equality lookups (WHERE id = 42). Does NOT support range queries. - Better for exact-match lookups (e.g., primary key lookups, unique column lookups). - Time complexity: O(1) average for equality lookups.

When to use which? - B-Tree: Range scans, sorting, composite index on multiple columns (leftmost prefix rule applies). - Hash Index: High-throughput key-value lookups (e.g., in-memory cache with adaptive hash indexes in InnoDB).

A transaction is a sequence of database operations treated as a single logical unit of work.

Concurrency Control problems: 1. Dirty Read: Reading uncommitted data from another transaction. 2. Non-Repeatable Read: Same query returns different results within the same transaction because another transaction modified the data. 3. Phantom Read: Same query returns different rows because another transaction inserted/deleted rows.

Isolation Levels (lowest to highest consistency): 1. Read Uncommitted: No isolation; dirty reads possible. 2. Read Committed: Only committed data is read (prevents dirty reads). Default in PostgreSQL, Oracle. 3. Repeatable Read: Same read always returns the same rows (prevents non-repeatable reads). Default in MySQL/InnoDB. 4. Serializable: Transactions execute as if they ran sequentially (prevents all anomalies including phantoms).

Mechanisms: - Pessimistic Locking: Locks rows/tables explicitly (e.g., SELECT ... FOR UPDATE). - Optimistic Locking: Version numbers or timestamps; check at commit time and roll back on conflict. - MVCC (Multi-Version Concurrency Control): Each transaction sees a snapshot of data as of a point in time; writers don't block readers.