Buffer management in DBMS Theory - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Buffer Replacement Policies

Which buffer replacement policy is best described as replacing the page that has not been used for the longest time?

A Least Recently Used (LRU)

B First-In-First-Out (FIFO)

C Most Recently Used (MRU)

D Clock Algorithm

💡 Hint

Think about which page has been idle the longest.

The Least Recently Used (LRU) policy replaces the page that has not been used for the longest time, assuming that pages used recently will likely be used again soon.

📋 Factual

intermediate

Buffer Pool Size Impact

What is the primary effect of increasing the size of the buffer pool in a database management system?

A Decreases the number of page faults

B Increases disk I/O operations

C Increases CPU usage significantly

D Decreases the size of the database

💡 Hint

Think about how more memory affects data access.

A larger buffer pool can hold more pages in memory, reducing the need to read from disk and thus decreasing page faults.

🔍 Analysis

advanced

Analyzing Buffer Pinning

In buffer management, what happens if a page is pinned in the buffer pool?

A The page is removed from the buffer pool

B The page is immediately written to disk

C The page cannot be replaced until it is unpinned

D The page is duplicated in the buffer pool

💡 Hint

Consider what pinning means for page replacement.

Pinning a page means it is in use and cannot be replaced until unpinned, ensuring data consistency during operations.

❓ Comparison

advanced

Comparing Dirty and Clean Pages

What is the key difference between a dirty page and a clean page in buffer management?

A Dirty pages are stored on disk; clean pages are only in memory

B Dirty pages are read-only; clean pages are writable

C Dirty pages are older than clean pages

D Dirty pages have been modified in memory but not written to disk; clean pages have not been modified

💡 Hint

Think about changes and disk synchronization.

Dirty pages contain changes not yet saved to disk, while clean pages match the disk content.

❓ Reasoning

expert

Effect of Buffer Management on Transaction Throughput

How does efficient buffer management improve transaction throughput in a database system?

A By increasing the number of transactions that require disk writes

B By reducing disk access latency, allowing faster data retrieval

C By forcing all transactions to wait for buffer availability

D By limiting the number of concurrent transactions

💡 Hint

Consider how memory caching affects speed.

Efficient buffer management keeps frequently accessed data in memory, reducing slow disk reads and improving transaction speed.

Practice

1. What is the main purpose of buffer management in a database system?

easy

A. To temporarily store data pages in memory for faster access

B. To permanently save data on disk

C. To encrypt data for security

D. To compress data to save space

Solution

1. Step 1: Understand buffer management role

   Buffer management temporarily holds data pages in memory to reduce slow disk access.

2. Step 2: Compare options

   Only To temporarily store data pages in memory for faster access describes temporary storage for faster access, others describe unrelated tasks.

3. Final Answer:

   To temporarily store data pages in memory for faster access -> Option A

4. Quick Check:

   Buffer management = temporary memory storage [OK]

Hint: Buffer means temporary memory storage for quick data access [OK]

Common Mistakes:

• Confusing buffer with permanent storage
• Thinking buffer encrypts data
• Assuming buffer compresses data

2. Which of the following is the correct operation to mark a page as in use in buffer management?

easy

A. unpin

B. replace

C. flush

D. pin

Solution

1. Step 1: Recall buffer operations

   Pin operation marks a page as in use so it is not replaced.

2. Step 2: Eliminate incorrect options

   Unpin releases the page, flush writes to disk, replace removes a page.

3. Final Answer:

   pin -> Option D

4. Quick Check:

   Pin = mark page in use [OK]

Hint: Pin means hold page in memory, unpin means release it [OK]

Common Mistakes:

• Confusing pin with unpin
• Thinking flush marks page in use
• Mixing replace with pin

3. Consider a buffer pool with 3 frames and pages requested in order: 2, 3, 2, 1, 5. Using the Least Recently Used (LRU) policy, which page will be replaced when page 5 is requested?

medium

A. Page 3

B. Page 1

C. Page 2

D. Page 5

Solution

1. Step 1: Track pages in buffer with LRU

   Initially empty: request 2 (load), 3 (load), 2 (already in buffer), 1 (load, buffer full now with 2,3,1).

2. Step 2: Identify least recently used page before requesting 5

   Pages in buffer: 2 (used recently), 3 (used before 1), 1 (most recent). LRU is page 3.

3. Step 3: Update usage after last request

   After the sequence 2,3,2,1, the usage order from most recent to least recent is 1,2,3. When page 5 is requested, the least recently used page is page 3, so page 3 should be replaced.

4. Final Answer:

   Page 3 -> Option A

5. Quick Check:

   LRU replaces least recently used page 3 [OK]

Hint: LRU removes the page not used for longest time [OK]

Common Mistakes:

• Replacing the most recently used page
• Confusing page numbers order
• Forgetting page 2 was used twice

4. A buffer manager uses the following code snippet to unpin a page:

if (page.pin_count > 0) {
  page.pin_count = page.pin_count - 1;
}

What is the likely error in this code?

medium

A. It increments pin_count instead of decrementing

B. It does not check if pin_count is already zero before decrementing

C. It should set pin_count to zero directly

D. It does not flush the page to disk

Solution

1. Step 1: Analyze the unpin logic

   The code decrements pin_count only if greater than zero, which is correct to avoid negative counts.

2. Step 2: Identify missing check

   However, if pin_count is zero, unpin should not be called or should raise error; code silently ignores this, which can hide bugs.

3. Final Answer:

   It does not check if pin_count is already zero before decrementing -> Option B

4. Quick Check:

   Unpin must avoid negative pin_count [OK]

Hint: Unpin must never reduce pin_count below zero [OK]

Common Mistakes:

• Ignoring pin_count zero condition
• Confusing increment and decrement
• Assuming flush is part of unpin

5. You have a buffer pool of size 2 and pages requested in this order: 4, 7, 4, 8, 7. Using the Clock replacement policy, which page will be replaced when page 7 is requested the second time?

hard

A. Page 8

B. Page 7

C. No page is replaced

D. Page 4

Solution

1. Step 1: Understand Clock policy basics

   Clock uses a circular pointer and reference bits to decide which page to replace.

2. Step 2: Track pages and reference bits

   Request 4 (load, ref=1), 7 (load, ref=1), 4 (ref bit set again), 8 (replace page with ref=0, but both 4 and 7 have ref=1, so pointer clears ref bits first, then replaces). When 7 is requested again, it is already in buffer with ref=1, so no replacement.

3. Final Answer:

   No page is replaced -> Option C

4. Quick Check:

   Clock keeps page if ref bit is set [OK]

Hint: Clock skips pages with reference bit set before replacing [OK]

Common Mistakes:

• Replacing a page that still has reference bit set
• Assuming immediate replacement on new request
• Confusing Clock with LRU policy