Part $2$ Database Performance Tuning and Query Optimisation $(40$ points$)$

The following relational database schema is used to keep track of customer orders for roses at

Lincoln Rose Farm:

Customer $($cust$\_$num, cust$\_$name, cust$\_$city, cust$\_$balance$)$

Orders $($order$\_$num, cust$\_$num, order$\_$date, order$\_$total$)$

Orderline $($line$\_$num, order$\_$num, prod$\_$num, line$\_$qty$,$ line$\_$total$)$

Product $($prod$\_$num, prod$\_$desc, prod$\_$price, prod$\_$qoh$)$

The following SQL query is used to select all customers from Christchurch with a balance greater

than $0$ who have ordered $$Ausblush$$ rose.

SELECT cust$\_$name

FROM $((($customer

INNER JOIN orders on customer.cust$\_$num $=$ orders.cust$\_$num$)$

inner join orderline on orders.order$\_$num $=$ orderline.order$\_$num$)$

inner join product on orderline.prod$\_$num $=$ product.prod$\_$num$)$

where product.prod$\_$desc $=$ "Ausblush"

and customer.cust$\_$city $=$ "Christchurch"

and customer.cust$\_$balance $>0\mathrm{.}00$;

Assume:

$$ There are $10$ customers, $10$ products, $10$ orders, and $22$ order lines.

$$Ausblush$$ has been ordered $7$ times.

$$ There are $6$ customers who are based in $$Christchurch$$ who has ordered $$Ausblush$.$

$$ There are $5$ customers who are based in $$Christchurch$$ and who has a balance greater than

$0\mathrm{.}00$ who has ordered $$Ausblush$.$

$$ There is only one product with prod$\_$desc equals to $$Ausblush$.$

Q$1.$ Create two alternative access plans and show their respective I$/$O costs using the example table

shown in Week $10$ Lecture. $(20$ points$)$

Q$2.$ You have been asked to optimise the performance of the query.

a$)$ Identify and describe what indexes should be created and why? $(10$ points$)$

b$)$ Revise your access plan and the I$/$O cost for the selected plan from Q$1$ assuming the

indexes you have identified are applied. $(10$ points$)$