Physical Database Systems: Storage Media, Blocks, and Buffers

What are Physical Database Systems? (In Simple Words)

Up to this point in our database studies, we have viewed data logically as tables, rows, columns, and mathematical sets.

In Physical Database Systems, we look under the hood of the computer hardware to understand how data is actually stored as magnetic charges or flash memory bits on physical storage disks (HDDs, NVMe SSDs), and how the DBMS moves data between slow disks and fast CPU RAM.

1. Physical Access of the Database & Buffer Management

The CPU can only execute instructions and read data that resides inside Main Memory (RAM). It cannot directly manipulate records on a hard drive!

Therefore, every database read and write requires the Buffer Manager:

1. The Buffer Pool is a large designated region of RAM reserved by the DBMS.
2. The buffer pool is partitioned into equal-sized slots called Frame Buffers.
3. When a SQL query asks for a record, the DBMS checks if the disk block containing that record is already in the buffer pool (a Buffer Hit).
4. If not found (a Buffer Miss), the DBMS issues an operating system I/O request to load the entire physical disk block from disk into an available RAM buffer frame.
5. If the RAM buffer pool is completely full, a page must be evicted using a Buffer Replacement Policy (most commonly LRU - Least Recently Used). If the evicted block was modified, its Dirty Bit is set and it must be written back to disk before being cleared!

2. The Physical Storage Hierarchy

Computer memory is organized in a hierarchical pyramid balancing speed, capacity, and cost:

3. Physical Storage Blocks (Pages) and Sectors

A disk does not read or write one byte or one record at a time. All disk I/O operations are performed in units of fixed-size Physical Storage Blocks (often called Pages):

• Sector: The smallest physical unit formatted on a magnetic platter (typically 512 bytes or 4,096 bytes).
• Block (Page): A contiguous cluster of sectors treated as the atomic unit of data transfer between disk and RAM. Typical database page sizes are 4 KB, 8 KB, or 16 KB (e.g., MySQL InnoDB uses 16 KB pages by default).

4. Mathematical Concept: Blocking Factor (bfr)

The Blocking Factor (bfr) is the maximum number of records that can fit into a single physical disk block.

The Formula:

Blocking Factor (bfr) = Floor( Block_Size / Record_Size )

Practical Numerical Example for DAVV Exams:

Problem:

• A database table has 10,000 employee records.
• Each employee record has a fixed size of R = 200 bytes.
• The physical disk block size is B = 4,096 bytes (4 KB).

Calculate:

1. The Blocking Factor (bfr).
2. The total number of disk blocks needed to store the entire table.

Solution:

1. bfr = Floor( 4096 / 200 ) = Floor( 20.48 ) = 20 records per block.
   (The remaining 96 bytes per block is unused space/padding).
2. Total Blocks (b) = Ceiling( Total_Records / bfr )
Total Blocks = Ceiling( 10000 / 20 ) = 500 blocks.

To read the entire employee table into RAM, the database engine must execute 500 physical block I/O operations!

💡 DAVV Exam Preparation Tips

5-Mark Question: “What is Buffer Management in DBMS? What is a dirty bit and why is it used?”

• Explain the Buffer Pool in RAM and frame buffers.
• Define Buffer Hit and Buffer Miss.
• Explain that a Dirty Bit indicates a block was modified in RAM and must be written back to disk before eviction.

10-Mark Question: “Explain the Physical Storage Hierarchy in database systems. What are physical storage blocks? Define Blocking Factor (bfr) and calculate the number of blocks needed to store 25,000 records of size 150 bytes on a 4KB block disk.”

• Diagram the 3 storage tiers (Primary, Secondary, Tertiary).
• Define blocks, sectors, and why I/O transfers are block-based.
• State the formula bfr = Floor(B / R).
• Solve: bfr = Floor(4096 / 150) = 27 records/block.
• Total blocks = Ceiling(25000 / 27) = 926 blocks.