Question

Page size is 1K bytes, and the avg record size is 100 bytes. Given 1 million records in a table, answering the following questions. 1) Suppose the table is stored in a heapfile, how many pages are in this file? 2) We will build an unclustered index B+ tree on this table over attribute A, how many data entries are contained in the leaf level? 3) Suppose A is integer type, what’s the size of a data entry? 4) Suppose each leaf level page is 70% filled, how many leaf level pages are needed? 5) What’s the size of one index entry? 6) What is the height of the B+ tree (suppose each index level page is also 70% filled)? 7) Given a query: find all records with 100

Answer 1

Solution

1)

Incase if it is 100 percent utilisation

Pages in the file will be

=10^6/(1000/100)

=10⁶/10

=10⁵

So the answer is

10⁵ pages

--

2)

1 data entry per record

Please note that by default we use alternative two for a data entry

10⁶

3)

8 bytes

Because search key value and pid

4)

Each leaf level page is 70% filled

So we need to assume 70% utilisation for the page

---

5)

Index entry is the search key value and pid

=search key value+pid

=8 bytes

---

6)

The index entry is the same size as the data entry

Therefore

The fanout of an index page is

[(1000/8)*0.7]+1

=88

“+1” is the left most pointer in an index page

The index page is 11429 pages in the leaf level

Therefore

The height of the tree

[log88 11429]

=3(without counting the root level or leaf level)

With the root level or leaf level

The height of the tree is

=4

---

all the best