1. An automobile part trading company would like to store the following attribut

Question

1. 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.

Explanation / Answer

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

Calculate the record size R in bytes.

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

Calculate the blocking factor bfr and the number of file blocks b assuming an unspanned organization.

Blocking factor bfr = floor (B/R) =

floor(512/91) = 5.626 records per block

Number of blocks needed for file = ceiling(r/bfr) = ceiling(50000/5.626) = 8887

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

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

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

Number of first-level index entries r1= number of file blocks b = 8887 entries

Number of first-level index blocks b1= ceiling(r1/ bfri) = ceiling(8887/32)= 228 blocks

Number of second-level index entries r2= number of first-level blocks b1= 228 entries

Number of second-level index blocks b2= ceiling(r 2 /bfri) = ceiling(228/32)= 8 blocks

Number of third-level index entries r3= number of second-level index blocks b2= 8 entries

Number of third-level index blocks b3= ceiling(r3/bfri) = ceiling(8/32) = 1

Since the third level has only one block, it is the top index level.

Hence, the index has x = 3 levels.

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

Suppose the file is not ordered by the key field PartNo and we want to construct a secondary index on PartNo - Repeat the previous exercise for the secondary index and compare with the primary index.

Number of first-level index entries r1= number of file records r = 50000

Number of first-level index blocks b1= ceiling(r1/bfri) = ceiling(50000/32)= 1563 blocks

We can calculate the number of levels as follows:

Number of second-level index entries r2= number of first-level index blocks b1= 1563 entries

Number of second-level index blocks b2= ceiling(r2/bfri) = ceiling(1563/32)= 49 blocks

Number of third-level index entries r3= number of second-level index blocks b2= 49 entries

Number of third-level index blocks b3= ceiling(r3/bfri) = ceiling(49/32) = 2

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

The percentage of disk space saving achieved by implementing PartNo as primary
index from implementing it as secondary index= Total number of blocks for primary index/Total number of blocks for secondary index = (237/1614 )*100 = 14.68 % = ~ 15%

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.