PLEASE ANSWER A B AND C

$[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$ :

CREATE TABLE S $($

D INT NOT NULL PRIMARY KEY,

E INT NOT NULL,

INDEX $($E$)$ USING BTREE $)$;

CREATE TABLE T$($

F INT NOT NULL PRIMARY KEY,

G VARCHAR$(5)$

INDEX $($G$)$ UISING BTREE$)$;

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

SELECT R$.$A$,$ S$.$E$,$ T$.$G

FROM R$,$ S$,$ T

WHERE R$.$A $=$ S$.$D AND R$.$A $=$ R$.$F AND R$.$B $>0$ AND R$.$C $\text{'}2\text{'}$ AND S$.$E $!=10$ ANS T$.$G $=10$;