DBMS · MEDIUM CODEX

DBMS Interview Questions — Medium

Q: Explain 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 phanto

Q: What 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 dead

Q: Detail 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 exe

Q: What 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.

Q: Explain 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

Real-world mechanisms, state management, edge cases, performance trade-offs, and practical coding.

50 Theory Questions7 Implementation Folios5 Free Model Answers

THEORY QUESTIONS & SOLUTIONSShowing 5 of 50 questions

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.

Unlock the remaining 52 DBMS (Medium) questions

You've completed the 5 free sample questions. Get unrestricted lifetime access to every question, model answer, implementation challenge, and all 27+ technologies for a single payment.

Unlock Everything — Already Enrolled? Sign In

One-time settlement () · Lifetime access · Zero subscription

CROSS-DIFFICULTY NAVIGATION

Continue Preparing DBMS

EASY LEVEL

Foundations & Core Concepts

HARD LEVEL

Internals & System Architecture