get help with homework

Part III. Query Optimization (39 points)
A. Your company often does part lookups. Lately, you have received complaints that these take a
long time. You are tasked to solve this problem. To investigate, you run a query that performs a
lookup for a single part.
Q.PIII.A1. Run and report the runtime the following query 2000 times:
SELECT P_Type. P_Size
WHERE PName = ‘dodger moccasin’;
Hint: Do not time each individual query execution. Your start and end time variables should be
outside of the FOR loop in Java, timing the 2000 inserts as a unit.
To speed up the queries you decide to create an index:
Q.PIII.A2. Create an index that would improve the performance of the above query.
Q.PIII.A3. Re-run the same query 2000 times and report the new runtime.
Q.PIII.A4. Was there a difference between the two runtimes? Explain why or why not.
B. Create the following index on the discount column from the LINEORDER table:
Someone in your company entered the wrong discount and revenue amount for each order (all of
the values were off by one). You can fix this by running the following queries.
Query 1:
SET LO_Discount = LO_Discount + 1
WHERE LO_Shipmode IN (‘RAIL’, ‘FOB’, ‘MAIL’, ‘REG AIR’, ‘AIR’, ‘SHIP’);
Query 2:
SET LO_Revenue = LO_Revenue + 100
WHERE LO_Shipmode IN (‘RAIL’, ‘FOB’, ‘MAIL’, ‘REG AIR’, ‘AIR’, ‘SHIP’);
Q.PIII.B1. For each query, report the runtime and the number of records updated.
Q.PIII.B2. Was there a difference between the runtime of the two queries? Explain why or
why not.
C. Your company deals with many international customers. Therefore, each time someone wants
to look up the total revenue for an international customer, a conversion rate must be calculated.
To save your employees time, you decide to create a materialized view.
Q.PIII.C1. Create a materialized view that stores the total revenue for each customer, and
the conversion rate for at least 5 different (actual) currencies of your choice.
You can assume that original amount is in USD. Also note that you can retrieve the revenue from
the LINEORDER table, which is different than the TotalOrderPrice stored in CUSTOMER_T2.
Part IV. Extra Credit (This is optional. Up to +16 points on the final project)
A. Database Decay Paper (+8 points)
Write a good summary and a good discussion for the following research paper:
M. Stonebraker et al., Database Decay and How to Avoid It, 2016.
If you are on DePaul’s network, you can access the paper through the IEEE digital library at the
following link:
If you are not on DePaul’s network, you can access the paper through DePaul’s library with the
following steps:
– With “IEEE” in the search box, select “IEEE Xplore”
– Once on the IEEE website, search “Database Decay and How to Avoid It”. It should be
the first paper that is returned.
What is a “good” summary?
– Around ½ – 1 page
– What was the research question? What were the findings/results? What are the
implications of the results?
– Focus on content, not length
What is a “good” discussion?
– Relate the paper to class and/or your work/interests
– How does this paper relate to the real-world?
– Anything else insightful
B. SQL (+3 points)
Write the additional SQL queries you did not answer from Part II. (+1 point for each correct
query, no partial credit).
C. ScorchedEarth Function (+5 points)
Write a PL/SQL function that will self-destruct the database (“This message will self-destruct in
10 seconds”). We will drop all of the triggers, procedures, functions and tables defined in the
You can find triggers in ALL_TRIGGERS table (note: you should only be dropping triggers
where YOU are the TABLE_OWNER!). ALL_PROCEDURES stores both functions and
procedures (again, do not attempt to drop system owned entries – you won’t have permission to
do so, but let’s not scare the DBAs unnecessarily). USER_TABLES contains the list of all user

Rate this post