CS 377 - Database Systems

Hw6: Disk Storage and Indexing

Out: Thursday, October 27

Due: Thursday, November 3, 11:59pm

1)    Consider the Cheetah NS.2 disk from Seagate. The average seek time is 4 msec.  The disk drive rotates the disk pack at a speed of 10K rpm (revolutions per minute).  The transfer rate is 1840 Mb/s. Suppose the block size B = 512 bytes. Calculate:

a)    the block transfer time btt in msec

b)    the average rotational delay rd in msec

c)    the time it takes (on average) in msec to locate and transfer a single block given its block address

d)    the average time it takes to transfer 20 random blocks and compare it with the time it takes to transfer 20 consecutive blocks

2)    Consider the above disk with block size B=512 bytes. Suppose a block pointer is P=6 bytes long, and a record pointer is PR = 7 bytes long.  A file has r=30,000 EMPLOYEE records of fixed-length. Each record has the following fields: NAME (30 bytes), SSN (9 bytes), DEPARTMENTCODE (9 bytes), ADDRESS (40 bytes), PHONE (9 bytes), BIRTHDATE (8 bytes), SEX (1 byte), JOBCODE (4 bytes), SALARY (4 bytes, real number). An additional byte is used as a deletion marker.

a)    Calculate:

i)      the record size R in bytes

ii)     the blocking factor bfr

iii)    the number of file blocks b assuming an unspanned organization.

b)    Suppose the file is ordered by the key field SSN and we want to construct a single-level primary index on SSN. Calculate:

i)      the index blocking factor bfri

ii)     the number of index entries

iii)    the number of blocks required to store the index

iv)   the number of block accesses needed to search for and retrieve a record from the file given its SSN value using the primary index, and compare it with the number of block accesses if there is no index

c)    Suppose the file is not ordered by the key field SSN and we want to construct a secondary index on SSN. Repeat exercise b).

d)    Suppose the file is not ordered by the non-key field DEPARTMENTCODE and we want to construct a secondary index on DEPARTMENTCODE using an extra level of indirection that stores record pointers (Option 3 of Section 18.1.3). Assume there are 1000 distinct values of DEPARTMENTCODE, and that the EMPLOYEE records are evenly distributed among these values. Calculate:

i)      the index blocking factor bfri

ii)     the number of index entries

iii)    the number of blocks required by the index entries

iv)   the number of blocks required by the extra level of indirection that stores record pointers

v)    the approximate number of block accesses needed to search for and retrieve all records in the file having a specific DEPARTMENTCODE value using the index, and compare it with the number of block accesses if there is no index

## Submission

·           Please place your solution in your cs377 directory ~/cs377/hw6/hw6.pdf