3 summary
Download as PPTX, PDF0 likes250 views
The document discusses query processing and optimization techniques. It provides an example of querying a database to find the name of the last member to borrow a book with the title "Zeus". There are several ways to structure the query, but some methods are more optimized than others. Query processing involves translating SQL queries into relational algebra operations, generating execution plans, and executing the queries. Query optimization examines multiple query plans to identify the most efficient plan to retrieve the required data.
More Related Content
Similar to 3 summary (20)
More from Mr Patrick NIYISHAKA (20)
Recently uploaded (20)
3 summary
- 1. Week 6 Query Processing and Optimization Techniques summary Real-life example Query Processing Translating SQL queries into RA Evaluation Plan Query Execution Query Optimization Translation Rules Cost Estimations quiz
- 2. Week 6 Query Processing and Optimization Techniques summary Real-life example quiz Let us take an example of a database with the following tables: Book (Book No, Book Title) Member (Member No, Member Name) Loan(Book No, Member No, Loan Date) Let us say we had lost a book whose title is Zeus and we want to know the name of the member who was its last borrower. Meanwhile we have 300000 members, 10,000,000 books and 500,000,000 records about loans. There are many ways of finding the name of the member who was its last borrower. Making the Cartesian product of Book, Member and Loan before looking for the title Zeus and then the borrower. Making the Cartesian product of Book and Loan before looking the title Zeus. Then make a join of the result with Member for checking who the last borrower was.
- 3. Week 6 Query Processing and Optimization Techniques summary Real-life example quiz Making the Cartesian product of Member and Loan. Then make a join of the result with Book for checking what the book was and who the last borrower was. Looking for the book Zeus in Book, then looking for the book Zeus in Loan this will give the Member No and at the end use the Member No in Member to find the name of the member. Etc. Among the different solutions attempted, some are more optimized than others. For instance, making the Cartesian product of all tables before looking for the title Zeus and then the borrower is the longest way to find the answer since it will generate 300,000*10,000,000*500,000,000 records among which to look for one answer. Finding the shortest way that consume less resources than other ways and executes faster than others is the goal of Query processing and Optimization Techniques.
- 4. Week 6 Query Processing and Optimization Techniques summary Real-life example quiz 1. What is query processing? A. The activities involved in entering in a database. B. The activities involved in creating a database. C. The activities involved in retrieving data from the database. D. The activities involved in modifying a database. 2. What is relational algebra? A. The basic set of operations for the relational model B. SQL C. DML D. The activities involved in modifying a database. 3. Query optimization is a function of many relational database management systems in which multiple query plans for satisfying a query are examined and a good query plan is identified: A. True B. False Enter answer,,,. 1. 2. 3.
- 5. Week 6 Query Processing and Optimization Techniques summary Real-life example quiz 1. What is query processing? A. The activities involved in entering in a database. B. The activities involved in creating a database. C. The activities involved in retrieving data from the database. D. The activities involved in modifying a database. 2. What is relational algebra? A. The basic set of operations for the relational model B. SQL C. DML D. The activities involved in modifying a database. 3. Query optimization is a function of many relational database management systems in which multiple query plans for satisfying a query are examined and a good query plan is identified: A. True B. False Enter answer,,,. 1. C 2. B 3. A
- 6. Week 6 Query Processing and Optimization Techniques summary Real-life example quiz 4. What is not a translation rule? A. Cascade of select B. Commutativity of select C . Cascade of project D. Commuting select with join E. Commuting rename 5. Cost estimations is the process of estimating and comparing costs of executing query using different execution strategies: A. True B. False answer is: 4. 5.
- 7. Week 6 Query Processing and Optimization Techniques summary Real-life example quiz 4. What is not a translation rule? A. Cascade of select B. Commutativity of select C . Cascade of project D. Commuting select with join E. Commuting rename 5. Cost estimations is the process of estimating and comparing costs of executing query using different execution strategies: A. True B. False answer is: 4. E 5. A