Consider the following query:

```
SELECT T.CrsCode, T.Grade
FROM Transcript T, Student S
WHERE T.StudId = S.Id AND S.Name = ’Joe’
```

Assume that Id is the primary key of Student,(CrsCode, Semester, StudId) is the primary key of Transcript,andthatName is not unique. Set up a database containing these two tables on the DBMS available to you. Initialize the tables with a large number of rows. Write a program that measures the query execution time by reading the clock before and after submitting the query. Be sure to ?ush the cache between successive measurements (perhaps by executing a query that randomly reads a su?cient number of rows of a large dummy table).

a. Test your understanding by making an educated guess of what query plan will be chosen by the DBMS assuming that there are no indices other than those for the primary keys. Run the query, output the query plan, and check your guess. Measure the response time.
b. Now assume that an unclustered index on StudId on Transcript is added. What query plan would you expect? Run the query, check your answer, and measure the response time. Try the query under two conditions: Joe has taken very few courses; Joe has taken many courses.
c. In addition to the index added in (b), assume that an unclustered index on Student on Name has been added and repeat the experiment.

a. A reasonable plan is one in which a selection is done on Student using the condition S.Name = :name and then the Ids obtained from the resulting rows are used in a scan of Transcript.
b. A reasonable plan is one in which a selection is done on Student using the condition S.Name = :name and then the index is used on Transcript to locate the matching rows. If Joe has taken only a few courses the response time should be much smaller than that in (a). If Joe has taken many courses the response time will be worse. Unless your DBMS keeps statistics on the number of courses per student, it probably won’t be smart enough to know that a table scan is better in this case.
c. Now the selection on Student is replaced by an index search using the new index, causing a further reduction in the response time.