Consider the relations:
Student (sid, sname, major, dob, addr, phone)
Class (cid, cname, croom, cdate, ctime)
Enrolled (sid, cid)
Faculty (fid, fname, deptid)
Draw and compare the cost of two Query Trees for the query:
Select Fname, cname from Class c, Faculty f where c.cname = "data structures" and f.deptid = 2.
Statistical data for the relations are as follows:
Student: Each tuple is 100 bytes long, 100 tuples per page, 1000 pages. Let's say there are 10000 students.
Class: Each tuple is 50 bytes long, 80 tuples per page, 500 pages. Let's say there are 100 classes.
No indexes are available for both relations.
Enrolled: Each tuple is 20 bytes long, 80 tuples per page, 500 pages. Let's say there are 9000 enrollments.
Faculty: Each tuple is 80 bytes long, 80 tuples per page, 500 pages. Let's say there are 500 faculties.
i) Draw the initial TWO query trees (5 marks).
ii) Compute the cost of TWO query trees (5 marks).
iii) Which is less expensive QUERY PLAN (2 marks).