All queries in this question are based on the sample schema shown below. Assume that the tables have many more rows than are shown there.

There are six tables describing employees, departments, buildings, department$($s$)$ in which an employee works $($and a percentage of the time for each$),$ department managers $($possibly more than one per department$),$ and building$($s$)$ in which an employee works $($an employee may have more than one office$).$ The primary key of each table is the attribute or attributes in capital letters. The database in which these tables are stored supports hash indexes, B$+$ tree indexes, and ISAM indexes. Multi$-$attribute indexes are defined in the order that matches the column order. The primary key index for a table is a clustered hash index on the primary key attribute$($s$).$ EMP, BUILDING AND DEPT all have unclustered B$+$ tree indexes on their name attributes.

$2\mathrm{.}4$ What value will the following SQL query produce if run against our sample data?

SELECT COUNT$($DISTINCT salary$)$

FROM EMP

WHERE salary $<mtext\; id="EditorElement\_985984"></mtext><mn\; id="EditorElement\_985985">9</mn><mn\; id="EditorElement\_985986">5</mn><mn\; id="EditorElement\_985987">0</mn><mn\; id="EditorElement\_985988">0</mn><mn\; id="EditorElement\_985989">0</mn>$ AND

$($start$\_$date $>\text{'}3/1/93\text{'}$ OR start$\_$date ;

$2\mathrm{.}5$ Consider running the following query against the sample data. What will John$$s rank field value be$?$

SELECT DENSE$\_$RANK$()$ OVER $($ORDER BY salary ASC$)$ AS rank, $*$

FROM EMP

WHERE start$\_$date IS NOT NULL

ORDER BY rank;

$2\mathrm{.}6$ Consider running the following query against the sample data. How many rows will the query produce that have $$Field$\text{'}$ in one or more of their columns?

SELECT division, name, sum$($budget$)$

FROM DEPT D

GROUP BY ROLLUP$($division$,$name$)$

ORDER BY division, name;

$2\mathrm{.}7$ Now consider that this company creates a snapshot of the EMP table for every decade called EMP followed by the decade name $($e$.$g$.$ EMP$2010,$ EMP$2020,$ etc.$)$ to maintain an historical record of its employees that can be used for reporting and OLAP queries. Once the tables are successfully created, they are never updated again. Almost of the queries used for reporting will involved various range predicates on salary. These new tables need not use the same indexing strategy as the EMP table itself. What would be the most efficient choice for indexing the salary column on the decade tables $($e$.$g$.,$ EMP$2010,$ EMP$2020,$ etc.$)?$

Choice $1$ of $5$:Unclustered B$+$ Tree index on salary

Choice $2$ of $5$:Clustered Hash index on salary

Choice $3$ of $5$:Clustered ISAM index on salary

Choice $4$ of $5$:The decade tables should have exactly the same indexes as the EMP table.

Choice $5$ of $5$:No indexes will be needed for the decade tables.EID values range from $1$ to $100,000$

BUILDING $-2,000$ tuples, $1$pages

BID values range from $1$ to $500$

DEPT $-1,000$ tuples, $5$ pages

DID values range from $1$ to $2000$

IN$\_$DEPT $-110,000$ tuples, $550$ pages

IN$\_$BUILDING $-110\mathrm{.}000$ tuples, $550$ pages

MANAGES$\_$DEPT $-800$ tuples, $4$ pages