Consider the natural join of the relation R(A,B) and S(A,C) on attribute A. Neither relations have any indexes built on them. Assume that R and S have 80,000 and 20,000 blocks, respectively. The cost of a join is the number of its block I/Os accesses. If the algorithms need to sort the relations, they must use two-pass multi-way merge sort.
Assume that there are 110,000 blocks available in the main memory. We like to have the output sorted based on the join attribute. What is the fastest join algorithm to compute the join of R and S? What is the cost of this algorithm?
Hi,
Answer:
Given: R(A, B) AND R(A, C) relations are natural joins.
sort-merge:
This join algorithm is used for implementing the relational
database and for every distinct value of the attribute and the set
of the tuples through which every relation to be displayed.
Hash-join:
Hash join algorithm is used for implementing the nested loops joins
except the probe side of the joins which can be very small.
Solution-:
Now if there are 110,000 blocks available in the main
memory.
Lets consider M = 110,000.
As we should know that the Hash-join and the optimized sort-merge
join are the fastest algorithm in the given setting and have equal
costs.
The Hash-join should be required that B(R) + B(S) <=
M^2.
But B(R) + B(S) is not smaller than M^2.So, we cannot optimize the
hash-join algorithm
Now apply the sort-merge join :
B(R) + B(S) >= M^2
As we know that B(R) + B(S) is greater than the M^2. So, we can use
an optimized sort-merge algorithm.
Hence, the sort-merge join is the fastest algorithm for computing the join of R and S.
And, the cost this sort-merge join will be:
5(R blocks + S blocks)
5 * (80,000 + 20,000)
5 * 10,000
50,000
Hence the cost of the algorithm = 50,000
Consider the natural join of the relation R(A,B) and S(A,C) on attribute A. Neither relations have...
Consider the natural join of the relation R(A,B) and S(A,C) on attribute A. Neither relations have any indexes built on them. Assume that R and S have 80,000 and 20,000 blocks, respectively. The cost of a join is the number of its block I/Os accesses. If the algorithms need to sort the relations, they must use two-pass multi-way merge sort. QUESTION: Assume that there are 10 blocks available in the main memory. What is the fastest join algorithm for computing...
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...
! Exercise 15.4.4: In Example 15.6 we discussed the join of two relations R and S, with 1000 and 500 blocks, respectively, and M = 101. However, we need additional additional disk I/O's if there are so many tuples with a given value that neither relation's tuples could fit in main memory. Calculate the total number of disk I/O's needed if: a) There are only two Y-values, each appearing in half the tuples of R and half the tuples of...
Consider a relation schema R with attributes ABCDEFGH with functional dependencies S: S={B→CD, BF→H, C→AG, CEH→F, CH→B} Employ the BCNF decomposition algorithm to obtain a lossless decomposition of R into a collection of relations that are in BCNF. Make sure it is clear which relations are in the final decomposition and project the dependencies onto each relation in that final decomposition.
Language: SQL - Normalization and Functional Dependencies Part 4 Normalization and Functional Dependencies Consider the following relation R(A, B, C, D)and functional dependencies F that hold over this relation. F=D → C, A B,A-C Question 4.1 (3 Points) Determine all candidate keys of R Question 4.2 (4 Points) Compute the attribute cover of X-(C, B) according to F Question 43 (5 Points) Compute the canonical cover of F.Show each step of the generation according to the algorithm shown in class....