Question

Database System Question

An automobile part trading company would like to store the following attributes for each
part in a database table:

PartNo: 10 bytes
Name: 30 bytes
UintMeasure: 5 bytes
UnitCost: 4 bytes
UnitPrice: 4 byes
QtyOnHand: 4 bytes
QtyOnOrder: 4bytes
PreferredSupplier: 30 bytes

Consider a disk with block size of 512 bytes and block pointer of 6 bytes long. Each record has a unique value of PartNo.
There are altogether 50,000 parts in the company.

Compute the following:

i) The number of file blocks required for storing the part records.

ii) The percentage of disk space saving achieved by implementing PartNo as primary index from implementing it as secondary index.

iii) The percentage of improvement on number of block accesses required for searching a product record based on PartNo’s value achieved by implementing PartNo as primary index from implementing it as secondary index.

Answer 1

Answer:

1)

Number of file blocks required for storing the part records.

Record length R = (10 + 30 + 5 + 4 + 4 + 4 + 4 + 30)

= 91bytes

Here number of file blocks b assuming an unspanned organization.

Blocking factor bfr = floor(512/91) = 5.626 records/block

No. of blocks required for one file =ceiling(50000/5.626) = 8887

2)

Disk space saving achieved by implementing PartNo as primary

index from implementing it as secondary index.

Index record size Ri = (10 + 6) = 16bytes

Index blocking factor bfri=floor(512/16) = 32

No. of 1st level index entries r1= No of file blocks b = 8887entries

No. of 1stlevel index blocks b1 = ceiling(8887/32) = 228blocks

No. of 2nd level index entries r2= No of 1st level blocks b1= 228entries

No. of 2nd level index blocks b2= ceiling(228/32)= 8blocks

No. of 3rd level index entries r3= No. of 2nd level index blocks b2= 8entries

No. of 3rd level index blocks b3 = ceiling(8/32) = 1

Here 3rd level is the top index level. Because it has only one block

Hence, the index has x =3levels.

Total number of blocks for the index bi= 228 + 8 + 1= 237blocks

If file is not ordered by the PartNo and we construct a secondary index on

PartNo - We will Repeat the previous exercise for the secondary index and we will compare with the primary index.

No. of 1st level index entries r1= No. of file records r = 50000

No. of 1st level index blocks b1 = ceiling(50000/32)= 1563blocks

To calculate the number of levels:

No. of 2nd level index entries r2= No. of first-level index blocks b1= 1563 entries

No. of 2nd level index blocks b2 = ceiling(1563/32)= 49blocks

No. of 3rd level index entries r3= No. of 2nd level index blocks b2= 49 entries

No. of 3rd level index blocks b3 = ceiling(49/32) = 2

Total number of blocks for the index bi = 1563 + 49 + 2 = 1614 blocks

The % of disk space saving achieved by implementing PartNo as primary

index from implementing it as secondary index = (237/1614 )*100 = 14.68 %