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...
-
A provincial Minister of Education recently announced that his governments forecast expenditure of $2.68 billion on education next year represents 23.5% of the provincial budget. Rounded to the...
-
As of January 1, 2012, Gammon Corporation had the following balances in its general ledger: a. Collected $42,000 of receivables. b. Accounts Payable as of January 1, 2012, were paid off . c....
-
What is the plain-meaning rule?
-
Formosa Electric sold $400,000, 9%, 10-year bonds on January 1, 2014. The bonds were dated January 1 and paid interest on January 1 and July 1. The bonds were sold at 105. Instructions (a) Prepare...
-
Scenario 7:Accountant 7 is the audit manager of a distressed client. Our client is in true financialtrouble and they want our firm to lend them money. Can we?
-
Using Fig. 4.102 , design a problem that will help other students better understand Thevenin equivalent circuits. Find the Thevenin equivalent at terminals a-b of the circuit in Fig. 4.102. R3 R, R2...
-
Create a program that writes to a file named NumberList.txt; it should use a loop to write the numbers 1 through 100 to the file, each in a separate line, then closes the file. Snap shots of code and...
-
Discuss the pros and cons of how organizational politics could impact the implementation of total quality.
-
Conversely, what expenses are shown on your cash flow statement and not in your P&L? Why are they different?
-
Dynamo Company has the following revenue and cost information: Sales Price = $50 per unit Fixed Costs = $120,000 Variable Costs = $40 per unit What is the break-even point in units?
-
The annual usage of copy paper is 30,000 units per year, at a uniform rate through the year. The ordering cost is $100 per order, and the carrying cost is $10 per unit average inventory per year....
-
Sutton Inc. acquired a 10 year asset to which the half year convention applied. This was the only asset the company placed in service during the year. The taxpayer elected out of Section 179 expense...
-
Your research laboratory uses a sample of hydrochloric acid and obtains unexpected results. Your research advisor asks you to measure the concentration of this solution using a titration. Describe...
-
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...
-
If you make multiple measurements of your height, you are likely to find that the results vary by nearly half an inch in either direction due to measurement error and actual variations in he ight....
-
Loveland, Colorado, is 18 km due south of Fort Collins and 3 1 km due west of Greeley. What is the distance between Fort Collins and Greeley?
-
Migrating geese tend to travel at approximately constant speed, flying in segments that are straight lines. A goose flies 32 km south, then turns to fly 20 km west. Afterward, how far is the goose...
Study smarter with the SolutionInn App