Question
Consider 4 relations R(A,Z);S(B,A);T(C,B) AND U(D,C).Assume that A in S is a foreign key to R.B in T is a foreign key to S and
Consider 4 relations R(A,Z);S(B,A);T(C,B) AND U(D,C).Assume that A in S is a foreign key to R.B in T is a foreign key to S and C in U is a foreign key in T.
These relations do not have any null value.and the size(i.e the number of rows in each relation ) is given by:
rR=4000,rs=3000,rt=2000 and ru=1000.
Also assume that the attributes of the relation are of the same length and we use hash join.So the cost of joining X and Y can be defined as
k(rX cX + rY cY )
where k is a constant, rX and cX denote the number of rows and the number of columns of X, respectively, and rY and cY denote the number of rows and the number of columns of Y (we ignore the cost of producing the output relation).
Under the above assumptions, find the lowest cost plan for computing R S T U using dynamic programming and left-deep join trees. You need to complete the following table while finding the best plans (e.g., in the form of (( ) ) in the last line) and associated costs.
Subquery | Size | Cost | BestPlan |
R Join S | 3000 | 14000k | R join S |
R Join T |
|
|
|
R Join U |
|
|
|
S Join T |
|
|
|
S Join U |
|
|
|
T Join U |
|
|
|
R Join S Join T |
|
|
|
R Join S Join U |
|
|
|
R Join T Join U |
|
|
|
S Join T Join U |
|
|
|
R Join S Join T Join U |
|
|
|
Can anyone help me .PLease also tell me how did they compute the size of R Join S as 3000.I think that the join selectivity ration in this case is 0.25*10^-3,but my professor says that my reasoning is incorrect.
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