Question
For this question, there exists 4 relations: R(A,B,C), S(C,D), T(D,E,F), and U(B,G). The size of attributes A to D are 10 bytes each, attributes E
For this question, there exists 4 relations: R(A,B,C), S(C,D), T(D,E,F), and U(B,G). The size of attributes A to D are 10 bytes each, attributes E and F have size 100 bytes, and attribute G has size 1000 bytes. The blocks size is 16,384 bytes with no spanning. Relation R has 100,000 tuples, relation S has 20,000 typles, T has 5,000 tuples, and relation U has 150,000 tuples. Other statistics: V(R,C)=10,000, V(S,D)=500, V(R,B)=1000. Assume that attributes with the same name can be joined across relations (and duplicates are eliminated during a join), and the first attribute is the primary key of the relation. Answer the following questions about calculating intermediate result sizes. Answer the question in terms of the # of tuples and the # of blocks.
a) (1 mark) What is the size of the intermediate result for projecting (keeping) only attribute B from U?
b) (1 mark) What is the size of the intermediate result for projecting (keeping) attributes B,C from R?
c) (1 mark) What is the size of the intermediate result for a selection D=10 on S?
d) (1 mark) What is the size of the intermediate result for a selection C>10 on S?
e) (1 mark) What is the size of the intermediate result for a selection B=5 on R?
f) (1 mark) What is the size of the intermediate result of operations c and d together?
g) (1 mark) What is the size of the intermediate result for joining R and S together?
h) (3 marks) Find the best join ordering for all 4 relations using the minimum intermediate result sizes IN BLOCKS as the determining factor.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started