Choose an index for each of the following SELECT statements. Specify whether you
ID: 3623037 • Letter: C
Question
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.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’
Explanation / Answer
1. Non-clustered indexes are more applicable when we want to return a single row from a table. 2. Clustered index is more useful when we have to retrieve many rows of data, ranges of data, and when BETWEEN is used in the WHERE clause. 3. There can be only one clustered index for a database table and there can be any number of non-clustered indexes. 4. The clustered index is implemented as a B-tree index structure that supports fast retrieval of the rows and heaps are tables that have no clustered index By considering all these constraints we choose the indexes for the following select statements. a) non-cluster index (Id) Heap is used b) non-clustered (Status) Heap is used c) clustered index (Grade, CrcCode) B+ tree is used d) clustered index (Salary) B+ tree e) clustered index (CrcCode, Semester) B+ tree