ACID properties in DBMS Theory - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Atomicity in Transactions

Which statement best describes the Atomicity property in database transactions?

A Transactions are isolated from each other to prevent interference.

B Data remains consistent before and after a transaction.

C All parts of a transaction must complete successfully or none at all.

D Once a transaction is committed, changes are permanent even after a crash.

💡 Hint

Think about whether a transaction can be partially done or not.

Atomicity means a transaction is all or nothing. If any part fails, the whole transaction is undone.

📋 Factual

intermediate

Identifying Durability in ACID

What does the Durability property guarantee in a database system?

A Transactions do not affect each other’s data during execution.

B Changes made by a committed transaction are saved permanently, even if the system crashes.

C Data is always valid before and after a transaction.

D A transaction can be partially completed if needed.

💡 Hint

Consider what happens to data after a power failure.

Durability means once a transaction commits, its changes survive system failures.

🔍 Analysis

advanced

Analyzing Isolation Levels

Which scenario best illustrates a violation of the Isolation property in transactions?

A Two transactions read and write the same data simultaneously, causing inconsistent results.

B A transaction fails halfway and rolls back all changes.

C Data remains consistent before and after a transaction commits.

D Once a transaction commits, its changes are permanent.

💡 Hint

Think about what happens when transactions interfere with each other.

Isolation prevents transactions from seeing intermediate states of others. Simultaneous conflicting access breaks this.

❓ Comparison

advanced

Comparing Consistency and Atomicity

How does the Consistency property differ from Atomicity in ACID?

A Both mean the same and can be used interchangeably.

B Atomicity guarantees transaction completion; Consistency guarantees data rules.

C Consistency means transactions are isolated; Atomicity means changes are permanent.

D Consistency ensures data rules are followed after a transaction; Atomicity ensures the transaction is all or nothing.

💡 Hint

Think about data correctness versus transaction completeness.

Consistency means data stays valid according to rules after a transaction; Atomicity means the transaction fully happens or not at all.

❓ Reasoning

expert

Determining the Outcome of a Transaction Sequence

Consider two transactions running concurrently on a bank database:

Transaction 1: Withdraw $100 from Account A
Transaction 2: Deposit $100 to Account A

If the system does not enforce Isolation, what problem might occur?

A The account balance might show an incorrect value due to overlapping operations.

B The withdrawal will always fail due to locking.

C The deposit will be lost permanently.

D The system will automatically rollback both transactions.

💡 Hint

Think about what happens if transactions see each other's partial changes.

Without isolation, transactions can interfere, causing incorrect balances due to reading uncommitted data.