Answers week 10

COSC 344
Tutorial for Week 10
1. A file has r=20,000 STUDENT records of fixed length. Each record has the following fields:
NAME (30 bytes)
IRD (9 bytes)
ADDRESS (40 bytes)
PHONE (9 bytes)
BIRTHDATE (8 bytes)
SEX (1 byte)
MAJORDEPTCODE (4 bytes)
MINORDEPTCODE (4 bytes)
CLASSCODE (4 bytes, integer)
DEGREEPROGRAM (3 bytes)
An additional byte is used as a deletion marker.
The file is stored on the disk whose parameters are:
disk block size = 512 bytes
a. Calculate the record size R in bytes
30+9+40+9+8+1+4+4+4+3=113bytes
b. Calculate the blocking factor bfr and the number of file blocks b, assuming an unspanned
organisation.
bfr =512 bytes/113bytes=4
number of file blocks=20,000/4=5,000
2. Assume the following:
Disk block size = 1024 bytes
Record size = 320 bytes
Number of records = 600,000
Use unspaned records
•
Assuming the file data is ordered on the primary key, how many disk accesses are required
to find a record based on the primary key?
Blocking factor=1024/320=3
Index entries (rows)=600,000/3=200,000
Accesses =Log_2^200,000 is about 18
COSC 344
Lectures 17, 18
Week 10
•
Construct a primary index on the database. Assume the primary key requires 12 bytes and
pointers are 8 bytes. How many disk accesses are required to find a record using the
primary index?
Note: number of rows of the primary index is equal to the number of blocks of the data file
Index record size=12+8=20
Index blocking factor=1024/20=51
Index blocks=200,000/51 is about 4000
Accesses on the index is log_2^4000 is about 12
Plus one access of data clock
13 blocks in total
•
How many disk accesses are required to find a record based on a secondary key?
Linear search
200,000/2=100,000
•
Construct a secondary index on this key. Assume the secondary key requires 22 bytes and
pointers are 8 bytes. Now how many disk accesses are required to find a record based on a
secondary key?
Note: in this example, we assume the number of rows of the secondary index is equal to the
number of record in the data file (secondary index is based on the non-ordering field)
Index record size=22+8=30
Index blocking factor=1024/30=34
Index storage blocks=600,000/34=17647 blocks
Binary search log_2^17647 is about 15
Plus one access of data clock
16 blocks in total
COSC 344
Lectures 17, 18
Week 10

Download Report