Exercise Database Management Systems (CO3021) - Chapter 4: Algorithms for Query Processing and Optimization

Question 4.1. List and describe typical steps when a query is processed.
Question 4.2. Differentiate a query tree from a query graph.
Question 4.3. Why does a SQL query need to be translated into relational algebra expressions?
Question 4.4. Describe external sorting and calculate its cost. List some applications of sorting in query
processing.
Question 4.5. A file of 4096 blocks is to be sorted with an available buffer space of 64 blocks. How many
passes will be needed in the merge phase of the external sort-merge algorithm?
Question 4.6. How are SELECT operations implemented? Give an example.
Question 4.7. How are JOIN operations implemented? Give an example.
Question 4.8. How are PROJECT operations implemented? Give an example.
Question 4.9. How are aggregate operations implemented? Give an example.
Question 4.10. How are SET operations implemented?

3 trang xuanthi 30/12/2022 520

Download

Bạn đang xem tài liệu "Exercise Database Management Systems (CO3021) - Chapter 4: Algorithms for Query Processing and Optimization", để tải tài liệu gốc về máy hãy click vào nút Download ở trên.

File đính kèm:

exercise_database_management_systems_co3021_chapter_4_algori.pdf

Nội dung text: Exercise Database Management Systems (CO3021) - Chapter 4: Algorithms for Query Processing and Optimization

4.11.8. Retrieve the names of employees whose salary is greater than the salary of all the employees in department 5. 4.11.9. For each department that has more than 5 employees, retrieve the department number and the number of its employees who are making more than 40,000. 4.11.10. For each employee who works for the department that has more than 10 employees, retrieve the employee name and department name if he/she has a salary higher than 30,000. Question 4.12. What is an execution plan? Give an example of a query and its execution plan. Question 4.13. What is a heuristic optimizer? What are its heuristic rules? Question 4.14. What is a cost-based optimizer? How is it different from a heuristic optimizer? Question 4.15. Describe cost components for a cost function to estimate a query execution cost. What kind of databases uses each cost component? Question 4.16. Differentiate pipelining from materialization. Demonstrate their differences. Question 4.17. Given the three following relations: Supplier(Supp#, Name, City, Specialty) Project(Proj#, Name, City, Budget) Order(Supp#, Proj#, Part-name, Quantity, Cost) and a SQL query: SELECT Supplier.Name, Project.Name FROM Supplier, Order, Project WHERE Supplier.City = ‘New York City’ AND Project.Budget > 10000000 AND Supplier.Supp# = Order.Supp# AND Order.Proj# = Project.Proj#; 4.17.1. Write the relational algebraic expression that is equivalent to the above query and draw a query tree for the expression. 4.17.2. Apply the heuristic optimization transformation rules to find an efficient query execution plan for the above query. Assume that the number of the suppliers in New York is larger that the number of the projects with the budgets more than 10000000$. Question 4.18. Draw query trees step by step to obtain a final optimized query tree using heuristic optimization for each query in Question 4.11. Question 4.19. Using the characteristics of the EMPLOYEE and DEPARTMENT data files as described below, describe an optimized execution plan based on a decision of the cost-based optimizer for each query in Question 4.11. 2