Consider the following SOL query: SELECT FROM employee, department WHERE dno = dnumber Assume Employee table...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Consider the following SOL query: SELECT FROM employee, department WHERE dno = dnumber Assume Employee table has 10,000 records. The number of Employee records that can fit into one disk block is 5. So b. = 2,000. There is a secondary index on SSN with zean = 4,5=1 There is a secondary index on DNO with Zows=2 and b₁,DNO = 4 There are 125 distinct values for DMO so dow=125 H Selection cardinality of DNO: SONG=re/dono=80 Department table has 125 records. b = 13 There is a single level primary index on DNUMBER. So XONUMEER = 1 There is a secondary index on MGRSSN with Zucas 2 and Sucussy=1 Thus we are assuming an employee can manage at most one department Cost = Join Selectivity is is 1/125 Blocking factor for the resulting joined table is: bfren = 4 1. Use nested loop with EMPLOYEE on the outside: Cost = b + (be bo) + [(js * re *ro)/bfree] = 2000 + (2000 - 13) + [(1/125 * 10000* 125)/4] = 30,500 2. Use nested loop with DEPARTMENT on the outside: Which is faster? 3. Use index structure on DNUMBER with EMPLOYEE on the outside: Cost = b + (r. ^ (Xonumber + 1)) + [ijs * r * r₂)/bfren] = 2000 + (10000 * 2) + [(1/125 * 10000*125)/4] = 24.500 4. Use index structure on DNUMBER with DEPARTMENT on the outside: Cost = Which is faster? Consider the following SOL query: SELECT FROM employee, department WHERE dno = dnumber Assume Employee table has 10,000 records. The number of Employee records that can fit into one disk block is 5. So b. = 2,000. There is a secondary index on SSN with zean = 4,5=1 There is a secondary index on DNO with Zows=2 and b₁,DNO = 4 There are 125 distinct values for DMO so dow=125 H Selection cardinality of DNO: SONG=re/dono=80 Department table has 125 records. b = 13 There is a single level primary index on DNUMBER. So XONUMEER = 1 There is a secondary index on MGRSSN with Zucas 2 and Sucussy=1 Thus we are assuming an employee can manage at most one department Cost = Join Selectivity is is 1/125 Blocking factor for the resulting joined table is: bfren = 4 1. Use nested loop with EMPLOYEE on the outside: Cost = b + (be bo) + [(js * re *ro)/bfree] = 2000 + (2000 - 13) + [(1/125 * 10000* 125)/4] = 30,500 2. Use nested loop with DEPARTMENT on the outside: Which is faster? 3. Use index structure on DNUMBER with EMPLOYEE on the outside: Cost = b + (r. ^ (Xonumber + 1)) + [ijs * r * r₂)/bfren] = 2000 + (10000 * 2) + [(1/125 * 10000*125)/4] = 24.500 4. Use index structure on DNUMBER with DEPARTMENT on the outside: Cost = Which is faster?
Expert Answer:
Answer rating: 100% (QA)
1 Cost 30500 as mentioned in question 2 Cost bD bD be js re rD bfreD 13 13 2000 ... View the full answer
Related Book For
Intermediate Accounting
ISBN: 978-1118147290
15th edition
Authors: Donald E. Kieso, Jerry J. Weygandt, and Terry D. Warfield
Posted Date:
Students also viewed these mathematics questions
-
Consider the following mathematical model for a 2- tank mixing (water with salt) system (Figure 1): Figure 1 Q1 ' (t) = 2 + Q2(t) 100 - 701(t) 400, Q1 (0) = 20 Q2 ' (t) = 301(t) 800 - Q2(t) 100, Q2...
-
j. *k. Me Ph COH 1a. Na, NH3 (1) ElO 1b. Br. 1c. aq. NH4Cl workup OMe 1. J 91% MgCl Me 2. DMSO (CF3CO)0 3. DIBAL-H, EtO K 83%
-
In this question you have to write a complete function in C++ MyMedia Publishers uses two parallel arrays to keep track of the number of subscriptions for each of their 50 publications. Array...
-
Using the Trust Services Principles and Criteria for the Online Privacy Principle, develop an online privacy policy for Alltel Stadium that could be posted on the stadiums website for customers to...
-
1. Would you recommend that Joan Collins bring this issue before the CEO and the Board of Directors and recommend implementation? Why or why not? 2. If you believe that Collins should recommend...
-
Consider the Schmitt trigger in Figure P15.51. The saturated output voltages of the op-amp are \(V_{H}=+10 \mathrm{~V}\) and \(V_{L}=-10 \mathrm{~V}\). Assume the diode turn-on voltage is \(0.7...
-
Discuss the main recognition and measurement issues that must be considered when preparing the income statement.
-
Anzola Company had the following data (in millions) for a recent period. Fill in the blanks. There was no beginning or ending inventories. a. Sales $920 b. Direct materials used 350 c. Direct labor...
-
If the series is convergent, (i) find a partial sum to approximate the sum of the series so that the error is guaranteed to be less than 0.01. (ii) Find an upper estimate and a lower estimate for the...
-
Binary Star-Different Masses two stars, with masses M, and M2, are in circular orbits around their center of mass. The star with mass M, has an orbit of radius R the star with mass M2 has an orbit of...
-
You have the plan for the RV account. You want two motorcycles and a trailer to tow behind it. You need $57,000 in that account in 12 years. You are able to place $2,150 in to the account at the end...
-
What type of scenario would warrant the application of a longitudinal design overall a cross-sectional design?
-
Identify a hypothetical population .
-
How would you describe the consumer identity of the Beyhive the Beyonce fans? Are there particular personality or lifestyle characteristics that members of the Beyhive share?
-
Focus upon each of the moments of truth in turn.What specific strategies could P&G employ in each one to increase the probability of a sale and repeat business?
-
What makes a posttest-only design "stronger" in terms of internal validity than a design with a pretest? Is it more appropriate to include random assignment for these designs? Why or why not?
-
If the quantity and quality of a product sold are held constant, how will a reduction in the item's selling price affect its buyers' perceptions of value?
-
Show that every group G with identity e and such that x * x = e for all x G is abelian.
-
At the end of 2014, Frontier Corporation has $360,000 of cumulative temporary differences that will result in reporting future taxable amounts as follows. 2015 ......... $105,000 2016 .........90,000...
-
Access the glossary (Master Glossary) to answer the following. (a) What is the definition of cash? (b) What is the definition of securitization? (c) What are the three contexts that give rise to...
-
Roth Inc. has a deferred tax liability of $68,000 at the beginning of 2015. At the end of 2015, it reports accounts receivable on the books at $90,000 and the tax basis at zero (its only temporary...
-
When a process is out of control, variations in the results of the process are caused by ________ events. A. random B. nonrandom C. planned D. unplanned
-
Watch the three YouTube videos mentioned in the Video Highlights feature of this chapter about Australias myki transportation smart card. Also find a more recent video or article about myki. Describe...
-
What is involved in monitoring stakeholder engagement?
Study smarter with the SolutionInn App