Choose an index for each of the following SELECT statements. Specify whether your choice is clustered or unclustered and whether it is a hash index or a B tree. +

Question

a.```
SELECT S.Name
FROM Student S
WHERE S.Id = ’111111111’
```

b.```
SELECT S.Name
FROM Student S
WHERE S.Status = ’Freshman’
```

c.```
SELECT T.StudId
FROM Transcript T
WHERE T.Grade = ’B’ AND T.CrsCode = ’CS305’
```

d. ```
SELECT P.Name
FROM Professor P
WHERE P.Salary BETWEEN 20000 AND 150000
```

e. ```
SELECT T.ProfId
FROM Teaching T
WHERE T.CrsCode LIKE ’CS%’ AND T.Semester = ’F2000’
```

f. ```
SELECT C.CrsName
FROM Course C, Teaching T
WHERE C.CrsCode = T.CrsCode AND T.Semester = ’F2002’
```

Answer 1

a. Since Id is a primary key, an index on Id will automatically be created by the DBMS. Any index, clustered or unclustered, hash or B+ tree, will support this query. Hence, no additional index is necessary.
b. Roughly 25% of the rows in the Student table satisfy the condition and the DBMS will not automatically create an index on Status since it is not unique. A clustered index is needed in this case because of the number of rows that have to be retrieved. The index can be a hash or B+ tree since all that is required is a grouping of all rows with a particular value of Status. The main index (on Id) will be unclustered in this case.
c. An index on CrsCode (rather than Grade) is needed since CrsCode is more selective. All rows satisfying the condition on CrsCode can be retrieved through the index and scanned, and only those satisfying the condition on Grade will be returned. The index should be clustered because the number of rows that will be selected is expected to be high. If the primary key (which involves the attributes StudId, Semester and CrsCode) is speci?ed to be a clustered B+ tree with CrsCode as the ?rst search key attribute then it can support the query (note that a clustered hash index could not be used in this way).
d. The DBMS will not automatically create an index on Salary since it is not unique. A clustered index is needed in this case because of the number of rows that have to be retrieved. The index must be a B+ tree since since a range of salaries is requested. Hence the primary index (on Id) will be unclustered.
e. An index on CrsCode (rather than Semester) is needed since CrsCode is more selective. All rows satisfying the condition on CrsCode can be retrieved through the index and scanned, and only those satisfying the condition on Semester will be returned. The index should Be clustered B+ tree because the number of rows that will be selected is expected to be high and due to the fact that a range of search key values has e?ectively been speci?ed (note: CS is a pre?x of the required course codes, so the range is CS000 through CS999). If the primary key (which involves the attributes Semester and CrsCode) is speci?ed to be a clustered B+ tree and CrsCode is the ?rst search key attribute in that tree then this index can support the query.
f. The key of Course is CrsCode and there will be an index on this. This is su?cient to support an index nested loop join with Teaching in the outer loop. Each row of Teaching can be scanned (no index on Teaching used) and if it satis?es the condition on Semester, the unique row of Course that it joins with can be found using this index. Any type of index will work (clustered, unclustered, B+ tree, hash). An improvement on this is to have a clustered index (B+ tree or hash) on Semester of Teaching since then a complete scan of Teaching can be avoided. By declaring the key of Teaching to be (Semester, CrsCode) the main index can be used for this purpose.