Task $3$: Storage and queries with a clustered B$+$ tree

Assume that we have a database storing information about persons:

Person$($PersonID$,$ FirstName, LastName, Birthyear, DepartmentNo$)$

We have information about $10000$ persons, where each person will be stored in a $250$ byte

record. The records are stored in blocks of size $2$ KB $(2048$ byte$),$ assume therefore that

each block can contain $8$ records. The table is stored in a clustered B$+$ tree with PersonID as

the search key.

a$)$ Show that the tree has $1875$ blocks at the leaf level $($level $0)$ if we assume a fill

degree of $\frac{2}{3}.$

b$)$ Assume that PersonID is stored with $4$ byte and that a block identifier is stored with $4$

byte. Show that the tree has $12$ blocks at level $1$ and only a single block $($root$)$ at level

$2$ if we assume a fill degree of $\frac{2}{3}.$

c$)$ Give an estimate for how many blocks are accessed by the following SQL queries.

Write a short explanation for each answer, and remember to write down any

assumptions that you find necessary.

SELECT $*$ FROM Person where PersonID $=195454$;

SELECT $*$ FROM Person;

SELECT $*$ FROM Person ORDER BY PersonID ASC;

SELECT FirstName, LastName FROM Person WHERE PersonID $<mn>1</mn><mn>0</mn><mn>0</mn><mn>0</mn><mn>0</mn><mn>0</mn>$;

Assume that $5\%$ of the persons satisfy the requirement.