An automobile part trading company would like to store the following attributes

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.

Explanation / Answer

Answer for (i):.

As per the given data we can calculated as

Here calculates as no. of blocks needed for file as  

= ceiling(r/bfr)----------------------------- (1)

Where

Record length:

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

= 91 bytes

                Blocking factor

bfr = floor (B/R)

=floor(512/91)

= 5.626 records per block

Then we can substrate as the equation(1)

= ceiling(50000/5.626)

= 8887

Answer for ii):

As per the given data

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

Number of first-level index entries

Here no. of first-level index blocks b1

= ceiling(r1/ bfri)

Where

r1= number of file blocks b

= 8887 entries

bfr= ifo = floor(B/Ri)

= floor(512/16)

= 32

then

= ceiling(8887/32)= 228 blocks

Here number of second-level index blocks b2

= ceiling(r 2 /bfri)

Where

r2= number of first-level blocks b1

= 228 entries

Then

= ceiling(228/32)

= 8 blocks

Here no.of third-level index blocks b3

= ceiling(r3/bfri)

Where

                r3= number of second-level index blocks b2

= 8 entries

bfri= fo = floor(B/Ri)

= floor(512/16)

= 32

then

= ceiling(8/32)

= 1

Here, the index has x = 3 levels.

Overall total no. of blocks for the index bi=

b1+ b2+ b3= 228 + 8 + 1

= 237 blocks

Here number of first-level index blocks b1=

ceiling(r1/bfri)

where

r1= number of file records r

= 50000

then

= ceiling(50000/32)

= 1563 blocks

calculate the number of levels

Number of second-level index entries r2

= number of first-level index blocks b1

= 1563 entries

Here number of second-level index blocks b2

= ceiling(r2/bfri)

= ceiling(1563/32)

= 49 blocks

Here number of third-level index entries r3

= number of second-level index blocks b2

= 49 entries

Here number of third-level index blocks b3

= ceiling(r3/bfri)

= ceiling(49/32)

= 2

Over all Total number of blocks for the index bi

= b1+ b2+ b3

= 1563 + 49 + 2

= 1614 blocks

We can doThe percentage of disk space saving achieved by implementing PartNo as primaryindex 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 A
• Subjects

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