title: DBMS: Relational Algebra Execution Plan Cost Calculation
body: <p>I have been trying the final days to come with a solution to the following question.</p>
  
  <p>Lets suppose that we have the following two tables.</p>
  
  <p><strong><em>Film(ID',Title,Country,Production_Date)</em></strong></p>
  
  <p><strong><em>Actor(ID',Name,Genre,Nationality)</em></strong></p>
  
  <p><strong><em>Cast(Actor_ID',Film_ID',Role)</em></strong></p>
  
  <p><em>Given information:</em></p>
  
  <p>Film holds N(film)=50.000 records, r(film)=40bytes, sequential organized, index on PK</p>
  
  <p>Actor holds N(actor)=200.000 records r(actor)=80bytes,heap organized, index on PK</p>
  
  <p>Cast holds N(cast)=100.000 records,r(cast)=25 bytes, heap organized, No INDEXES</p>
  
  <p>The execution tree and relation expression for an execution plan is in the following picture:</p>
  
  <p><a href="https://i.stack.imgur.com/xKaHP.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/xKaHP.png" alt="enter image description here"></a></p>
  
  <p>For the lower level join between cast &amp; film I'm calculating the followings:</p>
  
  <p>Block Nested Loop Join : Bcast x Bfilm</p>
  
  <p>Index Nested Loop Join : Bcast + Ncast x Cfilm</p>
  
  <p>I'm keeping the smallest value which is given with an INLJ.</p>
  
  <p><strong>Question:</strong></p>
  
  <p><strong>Now how can I calculate the size of the joined table and the new r which is the size of a record on the new joined table in order to proceed and calculate the upper level join between the already joined table with table actor after having calculated the cost B in blocks that join operation will take?</strong></p>