Consider the following relational schema and SQL query. The schema captures information about employees, departments, and...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Consider the following relational schema and SQL query. The schema captures information about employees, departments, and company finances (organized on a per department basis). Emp (eid: integer, did: integer, sal: integer, hobby: char (20)) Dept (did: integer, dname: char (20), floor: integer, phone: char (10)) Finance (did: integer, budget: real, sales: real, expenses: real) Consider the following query: SELECT D.dname, F.budget FROM Emp E, Dept D, Finance F WHERE E.did = D.did AND D.did = F.did AND E.sal > 100,000 AND E.hobby IN ('diving', 'soccer'); The system's statistics indicate that employee salaries range from 50,000 to 150,000, and employees enjoy 50 different hobbies. There is a total of 25,000 employees and 1,200 departments (each with corresponding financial record in the Finance relation) in the database. Each relation fits 100 tuples in a page. Suppose there exists a clustered B+ tree index on (Dept.did) and a clustered B+ tree index on (Emp.salary), both of size 50 pages. a) Compute the reduction factors and the estimated result size in number of tuples. (2 marks) b) Compute the cost in number of disk I/O's of the plans shown below. Assume that sorting of any relation (if required) can be done in 2 passes. NLJ is a Page-oriented Nested Loops Join. Assume that did is the candidate key, and that 50 tuples of a resulting join between Emp and Dept fit in a page. Similarly, 50 tuples of a resulting join between Finance and Dept fit in a page. Any selections/projections not indicated on the plan are performed "on the fly" after all joins have been completed. (8 marks, 2 marks per plan) NLJ Dept NLJ 1) SMJ 2) HJ D Emp Emp (Heap scan) (Heap scan) Finance Dept Finance (Heap scan) (Heap scan) 3) HJA (Heap scan) (Heap scan) 4) NLJ SMJ HJ Finance (Heap scan) Finance (Heap scan) Emp Dept OB.sal>100,000 Dept (Heap scan) (Heap scan) (Heap scan) Emp (Index scan (Emp.salary)) Consider the following relational schema and SQL query. The schema captures information about employees, departments, and company finances (organized on a per department basis). Emp (eid: integer, did: integer, sal: integer, hobby: char (20)) Dept (did: integer, dname: char (20), floor: integer, phone: char (10)) Finance (did: integer, budget: real, sales: real, expenses: real) Consider the following query: SELECT D.dname, F.budget FROM Emp E, Dept D, Finance F WHERE E.did = D.did AND D.did = F.did AND E.sal > 100,000 AND E.hobby IN ('diving', 'soccer'); The system's statistics indicate that employee salaries range from 50,000 to 150,000, and employees enjoy 50 different hobbies. There is a total of 25,000 employees and 1,200 departments (each with corresponding financial record in the Finance relation) in the database. Each relation fits 100 tuples in a page. Suppose there exists a clustered B+ tree index on (Dept.did) and a clustered B+ tree index on (Emp.salary), both of size 50 pages. a) Compute the reduction factors and the estimated result size in number of tuples. (2 marks) b) Compute the cost in number of disk I/O's of the plans shown below. Assume that sorting of any relation (if required) can be done in 2 passes. NLJ is a Page-oriented Nested Loops Join. Assume that did is the candidate key, and that 50 tuples of a resulting join between Emp and Dept fit in a page. Similarly, 50 tuples of a resulting join between Finance and Dept fit in a page. Any selections/projections not indicated on the plan are performed "on the fly" after all joins have been completed. (8 marks, 2 marks per plan) NLJ Dept NLJ 1) SMJ 2) HJ D Emp Emp (Heap scan) (Heap scan) Finance Dept Finance (Heap scan) (Heap scan) 3) HJA (Heap scan) (Heap scan) 4) NLJ SMJ HJ Finance (Heap scan) Finance (Heap scan) Emp Dept OB.sal>100,000 Dept (Heap scan) (Heap scan) (Heap scan) Emp (Index scan (Emp.salary))
Expert Answer:
Posted Date:
Students also viewed these databases questions
-
Consider the following relational schema and SQL query. The schema captures information about employees, departments, and company finances (organized on a per department basis). Emp(eid: integer,...
-
Consider the following relational schema and briefly answer the questions that follow: 1. Define a table constraint on Emp that will ensure that every employee makes at least $10,000. 2. Define a...
-
Your client has offered a 5-year, $1,000 par value bond with a 10 percent coupon. Interest on this bond is paid quarterly. 1) If your client is to earn a nominal rate of return of 12 percent,...
-
What is the present value of $1,450 per year, at a discount rate of 9 percent, if the first payment is received 6 years from now and the last payment is received 20 years from now?
-
What are information quality and environmental quality? Are they related? Why is low-quality information said to drive out high-quality information?
-
A retired person has $700,000 in a retirement account. An insurance company is offering to give the retired person an annuity of $61,029 at the end of each year for the next 20 years in exchange for...
-
Situation 3: A group of new machines was purchased on February 17, 2021, under a royalty agreement with the following terms: The purchaser, Keller Corp., is to pay a royalty of $1 to the machinery...
-
Based on Diagram 3.38 and 3.39, answer the following questions: FYI: Questions a -f related to diagram 3.38 and g-h related to both 3.38 and 3.39. a) Explain where this diagram shows that a supplier...
-
Companies that invest in cross-cultural training can avoid costly mistakes in a failed expatriate assignment. What are some common reasons for insufficient predeparture training?
-
In addition to managing expatriate employees, what other functions are important responsibilities for IHR to address?
-
What kind of training should companies be offering to recruit and develop local management talent, particularly in emerging markets?
-
One reason for expatriate assignment failurean employees premature return from an international assignmentis culture shock. What is culture shock? Provide some examples of culture shock contributing...
-
How does a global management team help a company to coordinate host country and cross-border business?
-
What is meant by the statement, conducting business globally exposes U.S. companies to an environment involving bribery that they are not exposed to in the united states? beyond the question above,...
Study smarter with the SolutionInn App