Foundations of Database Design Assignment 2: Rule Minimizer
Introduction
Welcome to the SQL query optimization assignment. This assignment covers an integral part of any database system and aims to deepen your understanding of SQL query optimization. As we delve into the complexities of query analysis and optimization techniques, you will learn to apply logic minimization principles to SQL statements, focusing on achieving efficient execution while maintaining the integrity of the results. This assignment will challenge you to think critically about the relational operations within SQL, understand the underlying processing mechanisms, and refine your queries for optimal performance.
Logic Minimization
SQL query optimization plays a crucial role in enhancing the efficiency and speed of data retrieval. For example, Logic minimization is a heuristic optimization that transforms the query-tree by using a set of rules that typically (but not in all cases) improve execution performance. At the heart of this optimization lies the application of logic laws of Relational Algebra, which, when applied to SQL queries, can significantly improve their performance by reducing their complexity. The idea is to represent SQL queries as relational algebra expressions, then apply laws and theorems of Boolean Algebra to manipulate and simplify the logical expressions.
The laws shown in Figure 1, such as the Idempotent Law and Absorption Law, provide a framework for simplifying complex queries by eliminating redundant operations and conditions without affecting the query's outcome. This optimization process not only reduces the computational load on the database system but also streamlines query execution, leading to faster response times and more efficient use of resources. Your goal is to implement them.
Idempotent Law and Absorption Law
The Idempotent Law and Absorption Law are fundamental principles within the realm of logic optimization, particularly relevant in the context of SQL query optimization for relational databases. Let's delve into detailed introductions of both laws:
Figure 1: Different Logic Laws
Idempotent Law
The Idempotent Law is grounded in the principle that an operation is idempotent if, when applied multiple times to any value, it yields the same result as if it were applied once. In the context of SQL optimization, this law facilitates the simplification of queries by identifying and removing redundant operations without altering the outcome of the query. The application of the Idempotent Law in SQL queries primarily involves eliminating unnecessary joins or conditions that do not contribute to the final result set, thereby streamlining the query execution process.
For example, if a SQL SELECT statement joins a table with itself based on the same identifier (“SELECT name FROM person AS p INNER JOIN person as p2 ON p.id = p2.id;"), the Idempotent Law suggests that this redundant join can be eliminated without affecting the result (“SELECT name From person AS p;”). Simplifying such queries not only reduces the computational load on the database system but also enhances the efficiency and speed of data retrieval, contributing to faster response times and more efficient use of resources.
Absorption Law
The Absorption Law, deals with the simplification of logical expressions in a way that certain terms in a complex expression can be "absorbed" into others, rendering them unnecessary. In SQL query optimization, applying the Absorption Law means identifying and removing superfluous conditions within the WHERE clause that do not impact the overall result of the query. This law is particularly useful for condensing SQL statements by eliminating redundant or unnecessary conditions, thereby making the queries more efficient and faster to execute.
An application of the Absorption Law might involve a query where a condition is implied by another, more comprehensive condition (“SELECT title FROM movie WHERE title = 'Avengers' AND (title = 'Avengers' OR released = 2020);”). By removing the redundant condition, the query is simplified without changing its semantics (“SELECT title FROM movie WHERE title = 'Avengers';”). For instance, if a query includes conditions that are logically encompassed by other conditions in the query, those redundant conditions can be omitted as per the Absorption Law.
Assignment 2 Requirement
- Understanding the Existing Code:
○ Familiarize yourself with the provided C++ code snippet and lab 5 within the Optimizer class.
○ Analyze how the `splitString` function is used to parse strings based on a specified delimiter and how it is instrumental in processing SQL query components. This function can be useful to deal with aliases.
○ Review the theory of Breadth-First Search (BFS) and Depth-First Search (DFS), and decide on a way to traverse the parser tree to find opportunities to apply the laws for optimization .
- Implement the `idempotentLawOptimizer` ,`absorptionLawOptimizer` and `eliminateRedundantJoinConditions` Function:
○ Idempotent Law Optimizer: Extend the given code to create a fully functional `idempotentLawOptimizer`. This function should analyze the SQL SELECT statements and identify redundant joins. (1) This function should examine the WHERE clauses of SQL statements and remove unnecessary conditions that do not impact the overall result of the query like repeatedly appearing conditions. This function should evict redundant joins. For instance, if a table is joined with itself on the same identifier, this join can be eliminated. Your implementation should handle different types of joins and conditions, ensuring that the optimization does not alter the query's intended result.
○ Absorption Law Optimizer: Implement a function, `absorptionLawOptimizer`, to apply the Absorption Law in SQL optimization. This function should examine the WHERE clauses of SQL statements and remove unnecessary conditions that do not impact the overall result of the query. For example, if a condition is logically encompassed by another more comprehensive condition, the redundant condition can be omitted.
○ eliminateRedundantJoinConditions: Develop a function, `eliminateRedundantJoinConditions`, to optimize SQL queries by removing redundant conditions in the JOIN or WHERE clauses. This function should compare the conditions in the JOIN clauses with those in the WHERE clause and eliminate any redundant conditions that do not affect the query's result.
○ Ensure all functions maintain the integrity of the query's intended results while optimizing for performance and simplicity.
○ Incorporate comprehensive error checking and handling to manage unexpected input or parse tree structures.
○ Hint: While implementing these functions, you may find it helpful to develop helper functions for bubbling up parser tree nodes, such as `bubbleAllUp` or `bubbleNullUp`, which can assist in simplifying the conditions in the parse tree.You may also create additional helper functions as needed.
- Testing and Debugging:
○ Take advantage of the tests provided under `Google_tests/testOptimizer.cpp` to test your implementation. You can also develop your own test cases.
○ Use the enhanced `idempotentLawOptimizer` , `absorptionLawOptimizer` and `eliminateRedundantJoinConditions` to optimize these test queries and validate the correctness of your implementation.
Output Examples
● Idempotent Law
○ Example Input:
“SELECT * FROM Movie
WHERE title = 'Avengers: End Game' OR title = 'Avengers: End Game';"
Expected Output:
“SELECT * FROM Movie
WHERE title = 'Avengers: End Game'”
● Absorption Law
○ Example Input:
“SELECT title FROM movie
WHERE title = 'Avengers' AND (title = 'Avengers' OR released = 2020);”
Expected Output:
“SELECT title FROM movie
WHERE title = 'Avengers';”
○ Example Input:
“SELECT title FROM movie
WHERE (title = 'Avengers' AND released = 2020) OR title = 'Avengers';”
Expected Output:
“SELECT title FROM movie
WHERE title = 'Avengers';”
Public Tests Output (Tree Structure)
- Idempotent Law: Inner Join Optimization
SELECT name FROM person AS p INNER JOIN person as p2 ON p.id = p2.id;
- Idempotent Law
SELECT * FROM movie WHERE title = 'Avengers: End Game' AND title = 'Avengers: End Game';
- Absorption Law
SELECT title FROM movie WHERE title = 'Avengers' AND (title = 'Avengers' OR released = 2020);
- Absorption Optimization
SELECT title FROM movie WHERE (title = 'Avengers' OR released = 2020) AND title = 'Avengers';
- Redundant Join Condition Optimization
SELECT r.rating FROM REVIEWED AS r JOIN movie AS m ON r.person_id = m.person_id WHERE r.person_id = m.person_id AND m.released = 2019;
- Redundant Join Condition Optimization 2
SELECT r.rating FROM REVIEWED AS r JOIN movie AS m ON r.person_id = m.person_id WHERE r.person_id = m.person_id;
- Redundant Join Condition Optimization 3
SELECT r.rating FROM REVIEWED AS r JOIN movie AS m ON r.person_id = m.person_id WHERE NOT r.person_id = m.person_id;
Submission
In this assignment, you are expected to submit using Autolab. You should submit a "tar" with your "queryOptimization" folder, which should contain the Optimizer.cpp and Optimizer.h (implementation of `idempotentLawOptimizer`, `absorptionLawOptimizer` and `eliminateRedundantJoinConditions` and other helper functions).
How to Submit to Autolab:
- Go to the website https://mvlander.dns.army/courses/test-course/assessments/Assignment.
- Use your Andrew email and your name to sign up.
- Check your email for an activation message and use the access code YATHMX to activate your account.
- Log in to your Autolab account.
- Tar your queryOptimization folder using the command: tar -cvf <YourAndrewID>_Assignment2.tar queryOptimization.
- Navigate to the assignment submission page.
- Click on the "Submit" link for Assignment 2.
- Choose your tarred file and upload it. And you will see the score and feedback (it takes around 260s).
You are allowed only 10 attempts.
Rubric
Your assignment will be graded based on the following criteria:
● Correct implementation of the `idempotentLawOptimizer` function;
● Correct implementation of the `absorptionLawOptimizer` function;
● Correct implementation of the `eliminateRedundantJoinConditions` function;
● Number of Tests passed;
● Number of attempts(More than 10 attempts will be penalized);
