Computer Science DBMS

Consider the relation below: Student (sid, sname, major, email)

All attributes are string fields of length 32bytes.

The sid is a key (i.e., sid values are unique).

Assume sid values are uniformly distributed between 000000100 and 000204900

The relation contains 102,400 records (assume fixed length records) Block size is 16K

Assume the time to read/write to/from a page is D; assume the records are compacted and there is no gap between records.

Assume each record pointer (RID) size is 8 bytes.

(a) Assume relation Student is stored in a heap file. What is the cost of (i) file scan, (ii) equality search, (iii) range search (sid<=000025700) on Student?

(b) Assume there is an unclustered sequential index on sid using alternative-3 for relation Student. What is the cost of (i) file scan, (ii) equality search, (iii) range search (sid<=000025700) on Student?

(c) Assume there is a clustered B+ tree index on sid using alternative-1 for relation Student. What is the cost of (i) file scan, (ii) equality search, (iii) range search (sid<=000025700) on Student? (assume the fanout value n = 400; assume the B+tree has 67% occupancy, i.e., the physical data pages are 1.5 times more than original data file.)

(d) Assume there is an unclustered B+ tree index on sid using alternative-3 for relation Student. What is the cost of (i) file scan , (ii) equality search, (iii) range search (sid<=000025700) on Student? (assume the fanout value n = 400; assume the B+tree has 67% occupancy, i.e., the index pages are 1.5 times more than sequential index. )