consider the database with around $10000$ records:

CREATE TABLE employees $($

emp$\_$no INT NOT NULL,

birth$\_$date DATE NOT NULL,

first$\_$name VARCHAR$(14)$ NOT NULL,

last$\_$name VARCHAR$(16)$ NOT NULL,

gender ENUM $(\text{'}$M$\text{'},\text{'}$F$\text{'})$ NOT NULL,

hire$\_$date DATE NOT NULL,

PRIMARY KEY $($emp$\_$no$)$

$)$;

Answer those questions:

$1)$ The company has frequent look$-$ups of its employees by ID$,$ i$.$e$.$ whenever a person's job or salary is reviewed on a screen. The query is:

SELECT $*$ FROM employees WHERE emp$\_$no $=212900$;

Would you consider adding an index to the employees table to make this query faster? Discuss and give reasons.

$2)$ To determine the eligibility of employees for certain bonuses, the employees often have to be listed by age $($or rather, birth year$).$

SELECT emp$\_$no FROM employees WHERE YEAR$($birth$\_$date$)<=1960$;

What could potentially be indexed in this case? Add the index and investigate whether it is used when you run this query. What do you conclude?

$3)$ Sometimes the company has to calculate leave entitlements, and for this it is helpful to list employees by hire date:

SELECT emp$\_$no$,$ first$\_$name, last$\_$name FROM employees ORDER BY hire$\_$date;

Add an index where you think it would best help speed up this query and see if it is used.

$4)$ Sometimes the company wants to determine if there is gender bias in appointments to higher positions. For this purpose, male and female employees have to be fetched separately:

SELECT emp$\_$no$,$ first$\_$name, last$\_$name FROM employees WHERE gender $=\text{'}$F$\text{'}$;

Add an index where you think it would best help speed up this query and see if it is used.