4. Consider a Student table-with scheme Students (SID, sName)-containing Nstudents = 2,000 tuples that are sorted...

 

Transcribed Image Text:

4. Consider a "Student" table-with scheme Students (SID, sName)-containing Nstudents = 2,000 tuples that are sorted in ascending order of sID, and each page can hold 10 such Student tuples. Compute the I/O cost for evaluating the following SQL selection statement "SELECT * FROM Students WHERE SID=7774380". Note that the cost metric is the number of page I/Os. Please show your equation. a) Plan S1 is to perform a binary search to evaluate the SQL statement. What is the I/O cost for this plan? b) Assume that (i) we have an Option-1 B-tree index on sID and (ii) it costs 3 I/Os to traverse from the root to a leaf (i.e., actual student data records). Plan S2 is to use this index (i.e. index probe) to evaluate the SQL statement. What is the I/O cost for this plan? c) Assume that (i) we have an Option-2 B-tree index on sID and (ii) it costs 3 I/Os to traverse from the root to a leaf (i.e., data entries, which capture the record IDs of student data). Plan S3 is to use this index to evaluate the SQL statement. What is the I/O cost for this plan? 4. Consider a "Student" table-with scheme Students (SID, sName)-containing Nstudents = 2,000 tuples that are sorted in ascending order of sID, and each page can hold 10 such Student tuples. Compute the I/O cost for evaluating the following SQL selection statement "SELECT * FROM Students WHERE SID=7774380". Note that the cost metric is the number of page I/Os. Please show your equation. a) Plan S1 is to perform a binary search to evaluate the SQL statement. What is the I/O cost for this plan? b) Assume that (i) we have an Option-1 B-tree index on sID and (ii) it costs 3 I/Os to traverse from the root to a leaf (i.e., actual student data records). Plan S2 is to use this index (i.e. index probe) to evaluate the SQL statement. What is the I/O cost for this plan? c) Assume that (i) we have an Option-2 B-tree index on sID and (ii) it costs 3 I/Os to traverse from the root to a leaf (i.e., data entries, which capture the record IDs of student data). Plan S3 is to use this index to evaluate the SQL statement. What is the I/O cost for this plan?