Suppose that the current instances of relations R(A, B) and S(A, C) have 100 tuples and 200 tuples, respectively.
(a) What are the minimum and maximum possible number of tuples in the resulting relation instance of R S.
(b) What is your answer to the above question if it is given that attribute A is a key for R.
(c) When attribute A is a key for R and A is a foreign key for S referencing R.
Solution:
(a): As no functional dependency is given for R and S in the first case, we can assume things as per our need.
Let us assume R has 100 different entries for A(1 to 100) and S has 200 values for A(1 to 200), all different values. So the maximum possible tuples, in this case, will be 100(1 to 100) after using the natural join.
For minimum let us assume all the values for A in R are 0 to 100 and in S are 101 to 300. So, in this case, none of the value of A will match and it will return zero tuples after joining using natural join.
(b): If A is given as the key attribute of R, that means R will surely have 100 distinct values of A. But S can still have any set of values for A. So for maximum again we can get 100 tuples if any of the 100 values of S.A matches with R.A.
And for the minimum again it will be zero as S.A can have all the 200 values which will be different from R.A.
(c): For this condition where S.A is a foreign key referencing R.A, the maximum will the same as 100, because all the 100 values of R.A will be there S.A.
But for minimum, it will not be zero this time. As all the values of R.A will definitely be there in S.A. So the minimum possible tuples will be 100 in this case.
** R.A means A of R
** S.A means A of S
I hope the solution helps.
Thanks!
Suppose that the current instances of relations R(A, B) and S(A, C) have 100 tuples and...
Suppose relation R(A, B, C) has the following tuples: and relation S(A, B, C) has the following tuples: Compute the intersection of the relations R and S. which of the following tuples is in the result? (2, 5, 4) (1, 2, 6) (2, 2, 6) (2, 5, 3)
! 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 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...
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...
Database Structures Question (50 points) . (10 points) Consider the join RDRa-Sb S, given the following information about the relations to be joined. Compute O costs for the following join operations. Relation R contains 4,000 tuples with blocking factor 40 .Relation S contains 2,000 tuples with blocking factor 40 Attribute a of relation R refers to attribute b of relation S Attribute b of relation S is the primary key for S Both relations are stored as simple heap files...
1) Consider a relation R(A,B) with r tuples, all unique within R, and a relation S(B,C) with s tuples, all unique within S. Let t represent the number of tuples in R natural-join S. What is the value range of t? What is the value of t for R natural-join R (assuming no null values in R)? Explain your answer.
Suppose: Relation S(B,C,D) has the following tuples: 68 79 Which of the following tuples is NOT in the result of the following query: SELECT Sum( BC) ASSUM, Max(D) AS MAX FROM S GROUP BY B; O (2,8,6) O (4,52,9) O (4,24,8) None of the above
Suppose that we pick the following four tuples from a legal relation S (S has 100 tuples in total). S has the following schema: (A: integer, B: integer, C: integer) The four tuples are: (1,2,3), (4,2,3), (5,3,3) and (5,3,4). Which of the following functional (→)can you infer does not hold over relation S? a. A → B b. BC → A c. A → C d. AC → B
B. Consider the following two relations: Relation r Relations R Р 10 В a b 5 8. B b c A 10 25 10 C 6 3 5 15 25 a 6 show the results of the following relational algebraic operations. Give 0 as your answer if you think the operation makes no sense. However, if you believe an operation makes sense but no tuples should be returned, you should still draw the schema of the resulting table. n. r^r.p=s.A...