Visit Patient 180,000 Answer the following questions about performing a natural join between the Patient and Visit table

[answerhappygod]

Visit Patient 180,000 Answer the following questions aboutperforming a natural join between the Patient and Visit tables. Theonly attribute the two tables have in common is msp, which is theprimary key of Patient and a foreign key in Visit. Relevantinformation is shown to the right. [15] T(R) B(R) V(R, msp)1,800,000 180,000 18,000 180,000 180,000 a) How many records willthe joined relation contain? [1] b) Approximately how many recordsof the joined relation fit on a single block? [1] c) What is themain memory requirement (in frames) to perform the join in twopasses using the sort-join algorithm? Briefly explain yourcalculation. [2] d) If Patient was already sorted on msp would youranswer to part (c) change? Explain why or why not? [2] e) What isthe main memory requirement (in frames) to perform the join in twopasses using the hash-join algorithm? Briefly explain yourcalculation. [2] f) Assume that there are approximately 1,200frames available for performing the join; what is the cost ofperforming the join using the block nested loop join algorithm?Briefly explain your calculation. [3] g) Assume that there is asecondary extensible hash index on msp in Patient. If the directorypage of the index is retained in main memory, what is the cost ofperforming the join using the index nested loop join algorithm?Briefly explain your calculation. [3] h) Assume that there is aprimary B+ tree index of height 3 on msp in Visit. If the root nodeof the index is retained in main memory, what is the cost ofperforming the join using the index nested loop join algorithm?Briefly explain your calculation. [3] i) Assume that there are justover 4,000 frames available for performing the join; what is thecost of performing the join using the hybrid hash join algorithmwhere one partition of the outer relation is to be retained in mainmemory? Briefly explain your calculation. [3]