Question

Question 1 (5 marks) Consider two relations called Item and Orderltem. Imagine that relation Item has 160,000 tuples and Orderltem has 200,000 tuples. Both relations store 100 tuples per a page. Consider the following SQL statement SELECT * FROM Item INNER JOIN OrderItem ON Item.ItemID-OrderItem. ItemID; We wish to evaluate an equijoin between Orderltem and Item, with an equality condition Item.ltemID Orderltem.ItemID. There are 802 buffer pages available in memory for this operation. Both relations are stored as (unsorted) heap files. Neither relation has any indexes built on it. Consider the alternaive join strategies described below and calculate the cost of each alternative. Evaluate the algorithms using the number of disk I/O's (i.e. pages) as the cost. For each strategy, provide the formulae you use to calculate your cost estimates. (1 mark) a) Page-oriented Nested Loops Join. Consider Item as the outer relation b) Block-oriented Nested Loops Join. Consider Item as the outer relation1 mark) c) Sort-Merge Join. Assume that Sort-Merge Join can be done in 2 passes. mark) (1 mark) d) Hash Join e) What would be the lowest possible cost to perform this query, assuming that no indexes are built on any of the two relations, and assuming that sufficient buffer space is available? What would be the minimum buffer size required to achieve this cost? Explain briefly (1 mark)

Answer 1

Open fst ob,et fie while Cmohe object files) while (mahe machoe Code) of cade heam fyom object file memohy Pointe lob o exten-wy.te (oryole A 26 Section- hardle d b o Selection. teut Pysh duwond -) Call -wn、te Gngole A@ZO