首页
网站开发
桌面应用
管理软件
微信开发
App开发
嵌入式软件
工具软件
数据采集与分析
其他
首页
>
> 详细
代做 program、代写 SQL 语言程序
项目预算:
开发周期:
发布时间:
要求地区:
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
1. 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 .
2. 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.
3. 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)
1. Idempotent Law: Inner Join Optimization
SELECT name FROM person AS p INNER JOIN person as p2 ON p.id = p2.id;
2. Idempotent Law
SELECT * FROM movie WHERE title = 'Avengers: End Game' AND title = 'Avengers: End Game';
3. Absorption Law
SELECT title FROM movie WHERE title = 'Avengers' AND (title = 'Avengers' OR released = 2020);
4. Absorption Optimization
SELECT title FROM movie WHERE (title = 'Avengers' OR released = 2020) AND title = 'Avengers';
5. 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;
6. 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;
7. 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:
1. Go to the website https://mvlander.dns.army/courses/test-course/assessments/Assignment.
2. Use your Andrew email and your name to sign up.
3. Check your email for an activation message and use the access code YATHMX to activate your account.
4. Log in to your Autolab account.
5. Tar your queryOptimization folder using the command: tar -cvf
_Assignment2.tar queryOptimization.
6. Navigate to the assignment submission page.
7. Click on the "Submit" link for Assignment 2.
8. Choose your tarred file and upload it. And you will see the score and feedback (it takes around 260s).
9. 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);
We strongly recommend starting your work locally and submit your Autolab "final version" as early as possible. Don't leave until the last moment to submit your assignment otherwise you may receive a penalty for late submission. This is because building the C++ project on Autolab can be time-consuming, and there is likely to be a high volume of users as the deadline approaches. You can also test your own test cases before attempting the autolab since everyone has only 10 chances.
Good luck and enjoy!
软件开发、广告设计客服
QQ:99515681
邮箱:99515681@qq.com
工作时间:8:00-23:00
微信:codinghelp
热点项目
更多
cis432代做、代写python/java程...
2024-05-04
eeen3007j代写、c++程序设计代...
2024-05-04
代写data程序、代做c/c++, jav...
2024-05-04
comp2006代做、代写c++程序语言
2024-05-04
comp26020代做、java/c++设计编...
2024-05-04
csci251 advanced programming...
2024-05-03
cs 6290: high-performance co...
2024-05-03
assignment 2: executing and ...
2024-05-03
ecse427/comp310 programmin...
2024-05-03
cs 452 (fall 22): operating...
2024-05-03
comp9414 23t2 assignment 2 ...
2024-05-03
dpst1091 23t1 assignment 2 ...
2024-05-03
program代做、代写python设计编...
2024-05-03
热点标签
finm8007
comp2006
comp26020
comp1721
eeen3007j
cis432
csci251
comp5125m
com398sust
32022
mth6158
comp328
finn41615
2024
mec302
mgmt3004
mgt7158
com160
as.640.440
econ3016
finm7405
econ7021
fin600
infs4205/7205
mktg2510-
f27sb
csse2310/csse7231
rv32i
eecs 113
comp1117b
cs 412
comp 315
econ7300
comp2017
ecs 116
fit5046
com6511
comp30024
acs341
econ1020
isys3014
acc408
comp1047
csc 256
cs 6347
finm7008
comp34212
csmde21
estr2520
comp285/comp220
mds5130/iba6205
finc6010
is3s665
busi2194
125.785
iom209
msin0041
econ339
cmt218
mast10007
comp5349
ecx2953/ecx5953
bios706
comp3310
mth6150
comp30027
comp20005
eec286
busi2211
bff2401
fnce90046
visu2001
mang6554
finc6001
125785
data423-24s1
engi 1331
fint2100
(520|600).666
can202
cs 61b
mast20029
info20003
stat512
econ3208
cmpsc311
engg1340
ecmt1010
fit5216
basc0003
ee3121
acct2002
comp5313
busi2131
ise529
elec372/472
csit940/csit440
cenv6141
comp3027/comp3927
ftec5580
comp1433
msci223
mark203
en3098
eden1000
ece6483
econ4410
mats16302
cs 6476
com6521
comp222
comp3211
comp10002
csc1002
chc6186
cs 161
comp27112
comp282
swen20003
comm1190
elec9764
acfi3308
acct7101
fin6035
comp2048
geog0163
comp2013
coen 146
dts101tc
sehh2042
comp30023
comp4880/8880
cs 455
07
stat0045.
fil-30023
celen085
psyc40005
math40082
are271
comp9311
ee5311
imse2113
comp 2322
acct2102
fnd109
int102
is3s664
is6153
data4000
accfin5034
fit5212
cs536-s24
fit5225
ecos3006
mes202tc
finc5001
stat3061
csc171
cs1b
7ssmm712
bu.450.760
cs170
comp3411
swen90004
cpt206
comp5313/comp4313—large
bl5611
kxo206
comp532
elec207
kxo151
cs 2820
cpt108
math2319
dts204tc
qm222
comp2511
ccs599
infs1001
mat2355
eeee4123
25721
ifn647
pols0010
hpm 573
qbus6860
comp9417
csci 1100
stat0023
cse340
comp2003j
cs 2550
cs360
fin 3080
ierg 4080
cs6238
cit 594
finm7406
hw6
联系我们
- QQ: 9951568
© 2021
www.rj363.com
软件定制开发网!