Consider two relations called Invoice and Customers. Imagine that the Customers relation has 1,000 pages and...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Consider two relations called Invoice and Customers. Imagine that the Customers relation has 1,000 pages and the Invoice relation has 5,000 pages. Consider the following SQL statement: SELECT * FROM Customers INNER JOIN Invoice ON Customers. Cust_ID = Invoice.Cust_ID; We wish to evaluate an equijoin between Customers and Invoice, with an equality condition Customers. Cust_ID = Invoice.Cust_ID. There are 502 buffer pages available in memory for this operation. Both relations are stored as (unsorted) heap files. Neither relation has any indexes built on it. Consider the alternative join strategies described below and calculate the cost of each alternative. Evaluate the algorithms using the number of disk I/O's (i.e. pages) as the cost. For each strategy, provide the formulae you use to calculate your cost estimates. a) Page-oriented nested loops join. Consider Customers as the outer relation. (1 mark) b) Block-oriented nested loops join. Consider Customers as the outer relation. (1 mark) c) Sort-Merge join. Assume that Sort-Merge Join can be done in 2 passes. (1 mark) d) Hash Join (1 mark) e) What would be the lowest possible cost to perform this query, assuming that no indexes are built on any of the two relations, and assuming that sufficient buffer space is available? What would be the minimum buffer size required to achieve this cost? Explain briefly. (1 mark) Consider two relations called Invoice and Customers. Imagine that the Customers relation has 1,000 pages and the Invoice relation has 5,000 pages. Consider the following SQL statement: SELECT * FROM Customers INNER JOIN Invoice ON Customers. Cust_ID = Invoice.Cust_ID; We wish to evaluate an equijoin between Customers and Invoice, with an equality condition Customers. Cust_ID = Invoice.Cust_ID. There are 502 buffer pages available in memory for this operation. Both relations are stored as (unsorted) heap files. Neither relation has any indexes built on it. Consider the alternative join strategies described below and calculate the cost of each alternative. Evaluate the algorithms using the number of disk I/O's (i.e. pages) as the cost. For each strategy, provide the formulae you use to calculate your cost estimates. a) Page-oriented nested loops join. Consider Customers as the outer relation. (1 mark) b) Block-oriented nested loops join. Consider Customers as the outer relation. (1 mark) c) Sort-Merge join. Assume that Sort-Merge Join can be done in 2 passes. (1 mark) d) Hash Join (1 mark) e) What would be the lowest possible cost to perform this query, assuming that no indexes are built on any of the two relations, and assuming that sufficient buffer space is available? What would be the minimum buffer size required to achieve this cost? Explain briefly. (1 mark)
Expert Answer:
Related Book For
Database management systems
ISBN: 978-0072465631
3rd edition
Authors: Raghu Ramakrishan, Johannes Gehrke, Scott Selikoff
Posted Date:
Students also viewed these programming questions
-
"internet radios" for streaming audio, and personal video recorders and players. Describe design and evaluation processes that could be used by a start-up company to improve the usability of such...
-
Let A, B be sets. Define: (a) the Cartesian product (A B) (b) the set of relations R between A and B (c) the identity relation A on the set A [3 marks] Suppose S, T are relations between A and B, and...
-
W3 = 8 Oa. 0.707-j0.707 O b. 0.707+j0.707 O c. -0.707-j0.707 O d. -0.707+j0.707
-
Inspect the earing shown in Fig. 7.57 and esti-1 mate the direction in which the blank was cut.
-
A flow of R-410a at 2000 kPa, 40C in an isothermal expander is brought to a state of 1000 kPa in a reversible process. Find the specific heat transfer and work.
-
What performance measure would you consider most important for McDonald's? For Chevrolet?
-
Mary Pierce is the controller of Arnold Corporation and is responsible for the preparation of the year-end financial statements. The following transactions occurred during the year. (a) On December...
-
1. a) Suppose you deposit $2500 each year for 20 years in a savings account paying 5% compounded annually. How much would the account contain after 20 years? How much of the FV did you actually...
-
1- Write your own scripts to perform the following tasks: a) A for loop that multiplies all even numbers from 2 to 10. b) A for loop that assigns the values 10, 20, 30, 40, and 50 to a vector. 2-...
-
The thermal conductivity of a certain solid material is k = 25 W/m K. At a certain point and time in a block of this material sketched in Fig. P.1.1, the temperature is 65C, the temperature gradient...
-
One of the main sources of water pollution is agricultural runoff. Consider three options to control this problem: (1) a CAC requirement prohibiting irrigation ditches from draining directly into...
-
Suppose that you are in charge of the demand-side management program at Megabucks Power, and your state public utility commission has just decided that you can now count energy-efficiency measures as...
-
Governor Blabla has decided that, rather than build a new nuclear power plant to service power needs, the state should save an equivalent amount of energy. As one component of an efficiency plan, he...
-
Reducing the rate of infant and childhood mortality in poor countries clearly leads to a short-run increase in population growth. Use the economic model of family size developed in this chapter to...
-
Write a paper on Pathogenic Microorganism in which you need to address the following: Morphological Characteristics Type of Metabolism Environmental Conditions Required for Growth Description of the...
-
A company produces earbuds. The revenue from the sale of x units of these earbuds is R = 8x. The cost to produce x units of earbuds is C = 3x + 1500. In what interval will the company at least break...
-
Consider the join of R and S described in Exercise 14.1. 1. With 52 buffer pages, if unclustered B+ indexes existed on R.a and S.b, would either provide a cheaper alternative for performing the join...
-
Consider the following relations: Emp(eid: integer, ename: varchar, sal: integer, age: integer, did: integer) Dept(did: integer, budget: integer, floor: integer, mgr eid: integer) Salaries range from...
-
Consider the ER diagram that you designed for the Prescriptions-R-X chain of pharmacies in Exercise 2.7. Define relations corresponding to the entity sets and relationship sets in your design using...
-
Outline the main factors that might influence the size of the profit mark-up set by a business.
-
When are increased profits in a managers personal interest?
-
A frequent complaint of junior and some senior managers is that they are frequently faced with new targets from above, and that this makes their life difficult. If their complaint is true, does this...
Study smarter with the SolutionInn App