Query Optimization

Consider the following SQL query that finds all applicants who want to major in CSE, live in Seattle, and go to a school ranked better than $10($i$.$e$.,$ rank

$\backslash$table$[[$Relation$,$Cardinality,Number of pages,Primary key$],[$Applicants $($id$,$ name, city, sid$),2,000,100,$id$],[$Schools $($sid$,$ sname, srank$),100,10,$sid$],[$Major $($id$,$ major$),3,000,200,($id$,$major$)]]$

SELECT

A$.$name

FROM Applicants A$,$ Schools S$,$ Major M

WHERE A$.$sid $=$ S$.$sid AND

A$.$id $=$

M$.$id

AND A$.$city $=$ 'Seattle' AND S$.$rank $<mtext\; id="EditorElement\_72094"></mtext><mn\; id="EditorElement\_72095">1</mn><mn\; id="EditorElement\_72096">0</mn>$ AND M$.$major $=$ 'CSE' And assuming:

Each school has a unique rank number $($srank value$)$ between $1$ and $100.$

There are $20$ different cities.

Applicants.sid is a foreign key that references Schools.sid.

Major.id is a foreign key that references

Applicants.id$.$

There is an unclustered, secondary B$+$ tree index on

Major.id and all index pages are in memory.