DEVPREP CODEX · #04

DBMS Interview Questions

171 curated questions graded from Foundations (Easy) to Practical Patterns (Medium) and Internals & Architecture (Hard).

TOTAL QUESTIONS

171

THEORY QUESTIONS

150

IMPLEMENTATION FOLIOS

FREE QUESTIONS

5 / Level

Easy Level·57 Questions Total

Foundations & Core Concepts

Open Full Easy List

Q1What is a Database Management System (DBMS) and what are its main advantages?

Definition: A software system designed to store, retrieve, manage, and run queries on databases. It acts as an interface between the database and end-users/applications.
Advantages:
- Data Redundancy Control: Minimizes duplicate data storage through normalization.
- Data Integrity: Enforces business constraints and rules (e.g., unique email IDs).
- Concurrent Access: Supports multiple users accessing the database simultaneously without conflicts.
- Security: Restricts unauthorized user access via robust permission models.
- Backup & Recovery: Provides mechanisms to recover data automatically after hardware or software failures.

Q2What are the main differences between a File System and a DBMS?

Data Redundancy: File systems contain highly redundant data across files, whereas DBMS controls redundancy through data modeling.
Accessing Data: File systems require complex custom code to query files. DBMS provides structured languages like SQL for declarative queries.
Concurrency: File systems lack proper concurrent access control, leading to race conditions. DBMS uses locking mechanisms to support high concurrency.
Transaction Support: File systems do not guarantee ACID compliance. DBMS provides complete transaction isolation and rollback guarantees.

Q3Explain the three-schema architecture of a DBMS.

Definition: A design pattern that separates the user applications from the physical database to achieve data independence.
Levels:
- External Level (View Schema): Describes the portion of the database that is visible to specific end-user groups.
- Conceptual Level (Logical Schema): Describes the structural layout of the entire database (entities, relationships, data types, constraints).
- Internal Level (Physical Schema): Describes how the database is physically stored on storage media (allocation, indexing structures, data compression).

Q4What is physical and logical data independence?

Physical Data Independence: The ability to modify the physical schema (e.g., changing storage hardware, shifting from B-tree to hashing indexes) without affecting the logical schema or user applications.
Logical Data Independence: The ability to modify the logical schema (e.g., adding a new attribute or changing relationships) without altering the external views or user applications.

Q5What are the primary responsibilities of a Database Administrator (DBA)?

Key Tasks:
- Database Design: Configuring the logical and physical layout.
- Performance Tuning: Monitoring and optimizing queries, indexing, and memory allocation.
- Security & Access Control: Provisioning user accounts and setting authorization privileges.
- Backup & Recovery: Planning and executing robust backup policies to prevent data loss.
- System Upgrades: Patching and upgrading DBMS software with zero downtime.

52 More Easy Questions Locked

Unlock the complete DBMS Easy question bank

Get instant access to all 57 questions, in-depth model answers, code sandboxes, and all 27+ technologies for a single one-time payment.

Unlock All — One-time payment · Lifetime access

Medium Level·57 Questions Total

Mechanisms & Practical Patterns

Open Full Medium List

Q1Explain the different levels of Isolation in transaction management and the anomalies they prevent.

Read Uncommitted: Lowest isolation. Allows dirty reads, non-repeatable reads, and phantom reads.
Read Committed: Prevents dirty reads (reading uncommitted data). Still allows non-repeatable and phantom reads.
Repeatable Read: Prevents dirty reads and non-repeatable reads (reading different values for the same row during a single transaction). Still allows phantom reads.
Serializable: Highest isolation. Prevents all anomalies including phantom reads (new rows appearing in subsequent reads) by locking ranges of rows or using snapshot isolation.

Q2What is the difference between Optimistic and Pessimistic concurrency control?

Pessimistic Concurrency Control:
- Assumes conflicts are highly likely.
- Locks resources immediately when accessed (e.g., acquiring X-locks during reads).
- Trade-off: High locking overhead, potential deadlocks, and reduced concurrency.
Optimistic Concurrency Control (OCC):
- Assumes conflicts are rare.
- No locks are acquired during transaction execution. Instead, the transaction records changes to private workspace.
- During the validation phase, the system checks if other transactions modified the same data. If yes, it aborts and rolls back.
- Trade-off: Excellent throughput for read-heavy loads, high abort rates under heavy write-write contention.

Q3Detail the Two-Phase Commit (2PC) protocol used in distributed databases.

Purpose: Enforces atomic transaction commits across multiple distributed database nodes.
Phase 1: Prepare Phase:
- The coordinator node sends a PREPARE message to all participant nodes.
- Participants execute the transaction locally up to the commit point, write to log files, and reply with VOTE_COMMIT or VOTE_ABORT.
Phase 2: Commit Phase:
- If all participants voted to commit, the coordinator broadcasts a GLOBAL_COMMIT message, and participants write changes permanently.
- If any participant voted to abort, the coordinator broadcasts GLOBAL_ABORT, forcing all participants to rollback.

Q4What is the difference between 3NF and BCNF? Explain with a scenario.

Definition: Boyce-Codd Normal Form (BCNF) is a stricter version of 3NF.
Constraint: In 3NF, for any dependency X -> Y, either X is a super key or Y is a prime attribute. In BCNF, X must be a super key.
Scenario: Consider a table Teaches with fields [Student, Subject, Teacher]. Assume:
- A teacher teaches only one subject (Teacher -> Subject).
- For a given subject, a student has only one teacher ({Student, Subject} -> Teacher).
- Here, Candidate Keys are {Student, Subject} and {Student, Teacher}.
- The dependency Teacher -> Subject violates BCNF because Teacher is not a super key, even though it satisfies 3NF because Subject is a prime attribute.

Q5Explain 4th Normal Form (4NF) and Multi-valued Dependency.

Multi-valued Dependency (MVD): Occurs when the presence of one or more attributes in a table uniquely determines the presence of another set of independent attributes (X ->> Y).
4NF Rule: A table is in 4NF if:
- It is already in BCNF.
- It contains no multi-valued dependencies (every MVD X ->> Y must have X as a super key).
Impact: Prevents storing independent 1-to-many relationships in a single table, which leads to massive row duplication.

52 More Medium Questions Locked

Unlock the complete DBMS Medium question bank

Get instant access to all 57 questions, in-depth model answers, code sandboxes, and all 27+ technologies for a single one-time payment.

Unlock All — One-time payment · Lifetime access

Hard Level·57 Questions Total

Internals & System Architecture

Open Full Hard List

Q1Explain the ARIES recovery algorithm in detail.

ARIES (Algorithms for Recovery and Isolation Exploiting Semantics): A state-of-the-art database recovery framework.
Phase 1: Analysis Phase:
- Scans the transaction log forward from the last checkpoint.
- Identifies all active transactions (dirty transactions) and dirty pages in memory at the time of the crash.
Phase 2: Redo Phase:
- Replays all logged operations forward from the oldest unwritten page (using RecLSN in the Dirty Page Table).
- Restores the database state to the exact moment of the crash (repeating history, including aborted transactions).
Phase 3: Undo Phase:
- Scans the log backward, reversing the modifications of all transactions that were active or aborted but never committed prior to the crash. Writes Compensation Log Records (CLRs) for each reversed write to prevent duplicate undos during subsequent crashes.

Q2Detail the Transaction Table, Dirty Page Table, and the role of LSNs in ARIES.

Log Sequence Number (LSN): A unique, monotonically increasing integer assigned to every log record.
Transaction Table: Maps active transactions to their LastLSN (the LSN of the most recent log record written by that transaction).
Dirty Page Table (DPT): Maps dirty in-memory pages to their RecLSN (the oldest LSN that made the page dirty since it was last flushed to disk).
PageLSN: A metadata header field on every physical page on disk and in memory indicating the LSN of the most recent write applied to that page. During recovery, if PageLSN >= LogRecordLSN, the database engine skips the write (already applied).

Q3Explain Write-Ahead Logging (WAL) rules mathematically.

WAL Theorem: A page modification cannot be written to disk before the log record describing the modification is flushed to non-volatile disk storage.
Mathematical rule: Let $PageLSN$ be the LSN of the most recent modification of page $P$, and let $FlushedLSN$ be the highest LSN flushed to disk. The storage engine must guarantee: $$\text{PageLSN} \le \text{FlushedLSN}$$ before writing page $P$ to disk.

Q4Detail the differences between strict 2PL, rigorous 2PL, and conservative 2PL.

Strict 2PL: Follows 2PL, but forces transactions to hold all Exclusive (write) locks until commit/abort. Prevents cascading rollbacks.
Rigorous 2PL: Forces transactions to hold all locks (both Shared and Exclusive) until commit/abort. Guarantees that the serialization order matches the commit order.
Conservative 2PL: Forces the transaction to acquire all required locks up front before it begins execution. If any lock is unavailable, the transaction waits without holding any locks. Completely eliminates deadlocks, but drastically restricts concurrent throughput.

Q5Explain Multi-Version Two-Phase Locking (MV2PL) and how it achieves lock-free reads.

Mechanism: Combines MVCC with 2PL.
Behavior:
- Writes create a new version of the document/row, acquiring exclusive locks only on the write version.
- Readers access a stable, committed snapshot version corresponding to their transaction start timestamp.
Impact: Readers never block writers, and writers never block readers, yielding massive concurrency gains in high-throughput transactional databases.