View the step-by-step solution to:

Consider the following relations. Employee (eid, ename, salary, age): 10,000 tuples Work_On (eid, pid): 200,000 tuples Project (pid, pname, location,...

Consider the following relations.


Employee (eid, ename, salary, age): 10,000 tuples

Work_On (eid, pid): 200,000 tuples

Project (pid, pname, location, mgr_id): 1,000 tuples 


We assume that the size of each column is 50 bytes. The size of disk block is 4KB.

 Each tuple in Employee matches with 20 tuples in Work_On on average. 

 Each tuple in Project matches with 200 tuples in Work_On on average.

 The column Employee.eid has the secondary Index with index level = 2

 The column Work_On.eid has the secondary index with index level = 4.

 The column Work_On.pid has the secondary index with index level = 4.

 The column Project.pid has the secondary index with index level = 1. 


Assume that all the operators are pipelined.

Given the Relational Algebra (Employee ⋈ Work_On) ⋈ Project, compute I/O costs for the following 

join methods.


1) (2 points) Block nested loop join (52 buffer pages) 

2) (2 points) Index based nested loop join

3) (2 points) Index based nested loop join (with the assumption that the Work_On table is clustered 

based on eid)

Given the Relational Algebra (Project ⋈ Work_On) ⋈ Employee, compute I/O costs for the following 

join methods.


4) (2 points) Block nested loop join (52 buffer pages) 

5) (2 points) Index based nested loop join (with the assumption that the Work_On table is clustered

based on pid) 



5. (6 points) Consider the following schedules 

S1: R1(A), R2(A), R3(B), W1(A), R2(C), R2(B), W2(B), W1(C)

S2: R1(A), R2(A), W1(B), W2(B), R1(B), R2(B), W2(C), W1(D) 

 Draw the precedence graph for the schedule. Is the schedule conflict-serializable?

Recently Asked Questions

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

-

Educational Resources
  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question