DBT: Storage, Layouts, Caching

Choose one answer.

Mark each statement as True or False.

Choose one answer.

Choose one answer.

Choose one answer.

Consider the following relation R:

Assume:

• Each field in a record consumes 4 bytes (integers as well as the date).
• Block size as well as page size is 32 bytes.
• No extra storage overhead is required for any storage model (i.e., page and block are same sized and contain purely data).
• For each query in the question below, the disk head is above the first block to be read.
• No further information about the schema exists (especially no info on indices and constraints).

How many blocks are retrieved from disk for the following query?

SELECT AVG(amount)
FROM R;

Consider the following relation R:

Assume:

• Each field in a record consumes 4 bytes (integers as well as the date).
• Block size as well as page size is 32 bytes.
• No extra storage overhead is required for any storage model (i.e., page and block are same sized and contain purely data).
• For each query in the question below, the disk head is above the first block to be read.
• No further information about the schema exists (especially no info on indices and constraints).

How many blocks are retrieved from disk for the following query?

SELECT *
FROM R
WHERE employee_id = 1;

Consider the record structure with header fields pointer2schema, length, timestamp and data fields name, age, state.

Note: all fields are aligned separately.

Assume a variable-length record with header components A, B, C, D and data fields Name, Address.

• A = 8 bytes
• B = 4 bytes
• C = 2 bytes (pointer to Name)
• D = 2 bytes (pointer to Address)

Data table (numbers in parentheses are bytes for value storage):

Furthermore, assume:

• Each variable-sized field needs 2 additional bytes besides raw storage.
• Each page has a fixed 12-byte page header.
• Records are stored as row store (NSM).
• Records can span multiple blocks; no extra overhead besides record storage.
• Records can be placed one after another with no additional overhead.

How many pages are required if the page size is 256 bytes?

Consider the CLOCK (a.k.a. Second Chance) algorithm. The system cache has space for 8 pages, and the current content of the cache is shown below.

When a page is accessed and already in cache, its reference bit is set to 1 (or remains 1). The clock hand does not move in that case.

The clock hand only moves when the accessed page is not in cache and the algorithm searches clockwise for a victim to evict. A newly inserted page gets reference bit 1.

Red boxes indicate reference bit = 1, orange boxes indicate reference bit = 0.

Request sequence:

81, 15, 36, 6, 42, 3, 69, 55

Fill the pages in the clock after the last request is completed (all pages in the sequence have been accessed).

Consider the following queries A1 and A2:

A1: SELECT name FROM R WHERE age > 25
A2: SELECT * FROM R WHERE age > 40

The cache can hold 3 pages and is initially empty. Consider the following page request sequence:

15, 4, 6, 15, 10, 11, 6, 9, 12, 1

How many cache hits occur if the replacement policy is FIFO?

Cache Hit: page is found in cache. Cache Miss: page is not found; writes to empty cache are misses.

The cache can hold 3 pages and is initially empty. Consider the following page request sequence:

15, 4, 6, 15, 9, 11, 6, 12, 15, 9

How many cache misses occur if the replacement policy is FIFO?

Cache Hit: page is found in cache. Cache Miss: page is not found; writes to empty cache are misses.

The cache can hold 3 pages and is initially empty. Consider the following page request sequence:

15, 4, 6, 15, 9, 11, 6, 9, 12, 1

How many cache hits occur if the replacement policy is LRU?

Cache Hit: page is found in cache. Cache Miss: page is not found; writes to empty cache are misses.

A query plan has a burst access pattern: it accesses a particular section of data at a time and then never accesses that section again.

Choose one answer.

Use the blank disk summary figure and assign the correct label to A, B, C, and D.

Select the correct term for each marker: