Choose indices for the following SELECT statement. Specify whether your choices are clustered or unclustered, hash index or B + tree.

```
SELECT C.CrsName, COUNT(*)
FROM Course C, Transcript T
WHERE T.CrsCode = C.CrsCode AND T.Semester = :sem
GROUP BY T.CrsCode, C.CrsName
HAVING COUNT(*) ? 100
```

Suppose the primary key of Transcript is declared to be (Semester, CrsCode, StudId) with a corresponding B+ tree. Then the DBMS can identify all rows of Transcript satisfying the condition on Semester. These rows will already be grouped on CrsCode and hence the index supports the GROUP BY clause of the query. Each group can be scanned to determine which satis?es the HAVING clause and then any index on the CrsCode attribute of Course can be used to locate the unique row of Course containing the CrsName attribute for the group. Note that since the search key of the index on Transcript contains both Semester and CrsCode, it supports an index-only search. Hence, the rows of Transcript do not have to be accessed and as a result the index does not have to be clustered.