$[40\%]$ Consider the following SQL that creates a table $R$ with $2$ columns and $2$ indexes $($a

primary key on A$,$ a secondary B$+$tree index on B$.)$

CREATE TABLE R$($

A INT NOT NULL PRIMARY KEY,

B INT NOT NULL,

C VARCHAR $(20),$

INDEX $($B$)$ USING BTREE,

INDEX $($C$)$ USING HASH $)$;

Assume the following storage parameters:

i$)$ There are $20,000$ records in $R$ stored using fixed$-$length record arrangement, and

sequential file organization w$.$r$.$t$.$ the column A$,$ there are no overflow blocks.

ii$)$ Records are stored on a disk with sector size of $512$ bytes and a block spans $2$ sectors$.$

Each block contains a header which occupies $30$ bytes.

iii$)$ The size of a CHAR is $1$ byte, an INT is $4$ bytes, and the size of a pointer is $4$ bytes.

a$.$ What is the size of $R$ in $MB$ excluding the indices?

b$.$ Please estimate the number of disk block accesses are required to answer the following SQL

with the use of the B$+$tree secondary index on B$.$ You can assume that the SQL returns $5$

matching records. Please explain your answer and list your assumptions.

SELECT $*$ FROM $R$ WHERE $B1$ AND $B10$;

c$.$ Suppose there are two other tables $S$ and $T$ :

Give an optimized relational algebra expression tree for the following query by considering

the use of indices, justify your answer with some explanations. You can assume that:

S has $1000$ records, average value in S$.$E is $500,$ with standard deviation of $100.$

T has $100,000$ records, average value in T$.$G is $1000,$ with standard deviation of $300.$