I need help here~
Posted: Sat May 14, 2022 4:17 pm
I need help here~
CSCI235 - Database Systems 2022 S2 Implementation Task 2 Due date: 19 May 2022 Scope The Implementation of Task 1 is related to the contents of topic on Indexing and PL/SQL. This Implementation is due by Thursday, 19 May 2022, 9:00 pm Singapore time. This task is worth 10% of the total assessment for the subject. Only electronic submission through Moodle at: https://moodle.uowplatform.edu.au/ is accepted. All email submission will be deleted and mark O ("zero") will be awarded. For Task 1, your report or output must include the listing of the execution of your SQL scripts that includes the explain plan statements, the creation of the indexes and the output of the show plan statements. For Task 2, your report or output must include a listing of all PL/SQL statements processed. To achieve the above-mentioned requirements, put the following SQL*Plus commands in all your scripts: SPOOL file-name SET ECHO ON SET FEEDBACK ON SET LINESIZE 100 SET PAGESIZE 200 SET SERVEROUTPUT ON at the beginning of SQL script and SPOOL OFF at the end of SQL script. The submission procedure is explained at the end of this specification. Page 1 of 4 SIM-IT2-CSCI235-202282 Copyright SCIT, University of Wollongong, 2022
Specification Step 1 If you have not done it yet, download the TPCHR sample database and load the sample TPCHR using user account CSCI235. This will make the user account CS CI235 the owner of the TPCHR database. Step 2 In this step we shall use the relational tables included in a sample TPCHR benchmark database owned by the user CSC235. The conceptual schema of the sample database is included in the file tochr.pdf. The objective of this task is to find the smallest number of indexes that improve performance of a given collection of SELECT statements. We do not expect the best а possible improvement in performance for each SELECT statement, however, processing of each SELECT statement must benefit from the existence of at least one of the indexes. An important objective is to minimize the total number of indexes created. Task 1 (5.0 marks) Indexing a. Implement SQL script solution 1. sql that performs the actions listed below. 1. First, the script finds query processing plans for each one of the queries listed below. Use EXPLAIN PLAN statement and a script showplan.sql to display the query processing plans Find all distinct pair of values of receipt-date (L_RECEIPTDATE) and discount (L_DISCOUNT) in the relational table LINEITEM. b. Find the total number of rows in the relational table ORDERS where the total price (O_TOTALPRICE) is greater than the average total price (O_TOTALPRICE). Find the total number of ORDERS submitted by each customer, list the values of an attribute O_CUSTKEY together with the total number of orders submitted. Do not ignore customers who submitted no orders. d. Find the total number of discount (L_DISCOUNT) in the relational table LINEITEM. Find the total number of distinct values in a column O_ORDER PRIORITY in a relational table ORDERS. c. e. 2. Next, the script creates the smallest number of indexes such that processing of each one of the queries listed above benefits from the existence of at least one index. SIM-IT2-CSCI235-202282 Copyright SCIT, University of Wollongong, 2022 Page 2 of 4
3. Next, script lists the query processing plans for each one of the queries listed in a step. Use EXPLAIN PLAN statement and a script showplan.sql to display the query processing plans. 4. Finally, the script drops all indexes created in a step (2). Note, the report from processing of the script must have NO syntax errors! Deliverables A file solutioni.Ist (or solution1.pdf) that contains a report from the processing of a script solution 1.sql. Task 2 (2.0 marks) PLSQL Function Implement a stored PL/SQL function CustWith Highest Order that finds the details of customer who has made the highest number of orders. The function is not required to accept any parameter. The function is to automatically find the details of the customer and returns a string of characters that contains the customer key, customer name, customer address, customer telephone and the number of orders made. Execute the stored PL/SQL function CustWith HighestOrder. A fragment of sample printout is given below: CJSTWITHHIGHES-CRDERS Customer403200478/ | Nal SOSYGU1113474020't = 12-4186-611-!:79/ 41 Deliverables Submit a file solution 2.Ist (or solution2.pdf) with a report from processing of SQL script solution2.sql. The report MUST have no errors and the report MUST list all SQL statements processed. The report MUST include ONLY SQL statements and control statements that implement the specifications of Task 1 and NO OTHER statements. SIM-172-CSCI235-202ZSZ Copyright SCIT. University of Wollorgong, 2022 Page 3 of 2
Task 3 (3.0 marks) PLSQL Procedure Implement a stored PL/SQL procedure showCustomer orders to list the customer number, names of customer, the orders number made, the order date, and the total price of order. The names of the customers must be listed in the ascending order, and the total price of order must be in descending order. If a customer did not make any order, list only the customer number and names. No details on order will be listed. Execute the stored PL/SQL procedure showCustomerorders for the first 10 customers, that is, the customer key less than or equal to 10. A fragment of expected sample printout is given below. - :--":1:1: -.-"95, 3:1:19 !'sNr-'94), 07 517, 1:9-Mar-29 11:1, 1 (AT-'14 2009, 07-1:11--9:, 04-930--93, 24-10--14, 732705, **2-19-1991, -759309, 12 -L.-397, $1:! 9156,718.3 $:8,1:2.7: 319,930.43 002,13.13 98,11.7.1 N-, 461.9 $325,311.63 $":19,0-9.03 :::--"8":1;i:1;!: 161:11, 28-A--! I!':-.: -'49, 1994, 2---::--23, .....(!), 19:: -19"-31, 05-14:--:193, 10 May : -224:31, 14-0---99, 297519, ין - . * 1..53 995, 177.)? 07-09 - Jus-22013: - Cus......: 192004: 25-1--19: 04 Mar - 397 81082), 23 ---:195, 917585, Mar $530,929.) 266,99.35 5.1.1-.63 $217.11.07 SIM-172-CSCI235-2OZZSZ Copyright SCIT. University of Wollongong, Zozz Page 4 of 2
306439, 8951:2, 916775, 2206820, 835-73, 2290087, -201223, 291620, 212870, 71 AC16, 859-08, 073670, 17-May-1997, 01-Dec-1995, 26-Apr-1996, 21-Feb-1996, 19-Aug-1993, 10-cul-1996, 13-04-1996, 22-May-1998, 30-09--1996, 30-Aug-1994, 20-tel-1996, 24-May-1994, 21-van-1998, 39-Mar-1998, 09-01-1993, $234,026.80 $229,991.08 $75,744..25 $190,273.97 $187, 151.50 $177,131,73 $153,250.49 $154,113.20 $152, 662.65 $123,028.08 $105,414.33 $76,178.32 958,724.04 $11,013.89 9-3,444.64 883557, 2774689, 5 - Cuscomer#000000005: 374723, 20-Nov-1996, 2572644, 01-11-1998, -479017, 06-00.-1992, 521'57, 23-Aug-1997, 269922, 19-Mar-1996, - 177350, 03-611-1997, $241,348.35 9201,566.95 $137,297.10 $141,934.18 $122,008.56 947,596.96 Deliverables Submit a file solution 3.Ist (or solution 3.pdf) with a report from processing of SQL script solution2.sql. The report MUST have no errors the report MUST list all SQL statements processed. The report MUST include ONLY SQL statements and control statements that implement the specifications of Task 3 and NO OTHER statements. SIM-IT2-CSCI235-2022S2 Copyright SCIT, University of Wollongong, 2022 Page 5 of 4
- I Need Help Here 1 (64.37 KiB) Viewed 57 times
- I Need Help Here 2 (94.96 KiB) Viewed 57 times
- I Need Help Here 3 (57.43 KiB) Viewed 57 times
Specification Step 1 If you have not done it yet, download the TPCHR sample database and load the sample TPCHR using user account CSCI235. This will make the user account CS CI235 the owner of the TPCHR database. Step 2 In this step we shall use the relational tables included in a sample TPCHR benchmark database owned by the user CSC235. The conceptual schema of the sample database is included in the file tochr.pdf. The objective of this task is to find the smallest number of indexes that improve performance of a given collection of SELECT statements. We do not expect the best а possible improvement in performance for each SELECT statement, however, processing of each SELECT statement must benefit from the existence of at least one of the indexes. An important objective is to minimize the total number of indexes created. Task 1 (5.0 marks) Indexing a. Implement SQL script solution 1. sql that performs the actions listed below. 1. First, the script finds query processing plans for each one of the queries listed below. Use EXPLAIN PLAN statement and a script showplan.sql to display the query processing plans Find all distinct pair of values of receipt-date (L_RECEIPTDATE) and discount (L_DISCOUNT) in the relational table LINEITEM. b. Find the total number of rows in the relational table ORDERS where the total price (O_TOTALPRICE) is greater than the average total price (O_TOTALPRICE). Find the total number of ORDERS submitted by each customer, list the values of an attribute O_CUSTKEY together with the total number of orders submitted. Do not ignore customers who submitted no orders. d. Find the total number of discount (L_DISCOUNT) in the relational table LINEITEM. Find the total number of distinct values in a column O_ORDER PRIORITY in a relational table ORDERS. c. e. 2. Next, the script creates the smallest number of indexes such that processing of each one of the queries listed above benefits from the existence of at least one index. SIM-IT2-CSCI235-202282 Copyright SCIT, University of Wollongong, 2022 Page 2 of 4
3. Next, script lists the query processing plans for each one of the queries listed in a step. Use EXPLAIN PLAN statement and a script showplan.sql to display the query processing plans. 4. Finally, the script drops all indexes created in a step (2). Note, the report from processing of the script must have NO syntax errors! Deliverables A file solutioni.Ist (or solution1.pdf) that contains a report from the processing of a script solution 1.sql. Task 2 (2.0 marks) PLSQL Function Implement a stored PL/SQL function CustWith Highest Order that finds the details of customer who has made the highest number of orders. The function is not required to accept any parameter. The function is to automatically find the details of the customer and returns a string of characters that contains the customer key, customer name, customer address, customer telephone and the number of orders made. Execute the stored PL/SQL function CustWith HighestOrder. A fragment of sample printout is given below: CJSTWITHHIGHES-CRDERS Customer403200478/ | Nal SOSYGU1113474020't = 12-4186-611-!:79/ 41 Deliverables Submit a file solution 2.Ist (or solution2.pdf) with a report from processing of SQL script solution2.sql. The report MUST have no errors and the report MUST list all SQL statements processed. The report MUST include ONLY SQL statements and control statements that implement the specifications of Task 1 and NO OTHER statements. SIM-172-CSCI235-202ZSZ Copyright SCIT. University of Wollorgong, 2022 Page 3 of 2
Task 3 (3.0 marks) PLSQL Procedure Implement a stored PL/SQL procedure showCustomer orders to list the customer number, names of customer, the orders number made, the order date, and the total price of order. The names of the customers must be listed in the ascending order, and the total price of order must be in descending order. If a customer did not make any order, list only the customer number and names. No details on order will be listed. Execute the stored PL/SQL procedure showCustomerorders for the first 10 customers, that is, the customer key less than or equal to 10. A fragment of expected sample printout is given below. - :--":1:1: -.-"95, 3:1:19 !'sNr-'94), 07 517, 1:9-Mar-29 11:1, 1 (AT-'14 2009, 07-1:11--9:, 04-930--93, 24-10--14, 732705, **2-19-1991, -759309, 12 -L.-397, $1:! 9156,718.3 $:8,1:2.7: 319,930.43 002,13.13 98,11.7.1 N-, 461.9 $325,311.63 $":19,0-9.03 :::--"8":1;i:1;!: 161:11, 28-A--! I!':-.: -'49, 1994, 2---::--23, .....(!), 19:: -19"-31, 05-14:--:193, 10 May : -224:31, 14-0---99, 297519, ין - . * 1..53 995, 177.)? 07-09 - Jus-22013: - Cus......: 192004: 25-1--19: 04 Mar - 397 81082), 23 ---:195, 917585, Mar $530,929.) 266,99.35 5.1.1-.63 $217.11.07 SIM-172-CSCI235-2OZZSZ Copyright SCIT. University of Wollongong, Zozz Page 4 of 2
306439, 8951:2, 916775, 2206820, 835-73, 2290087, -201223, 291620, 212870, 71 AC16, 859-08, 073670, 17-May-1997, 01-Dec-1995, 26-Apr-1996, 21-Feb-1996, 19-Aug-1993, 10-cul-1996, 13-04-1996, 22-May-1998, 30-09--1996, 30-Aug-1994, 20-tel-1996, 24-May-1994, 21-van-1998, 39-Mar-1998, 09-01-1993, $234,026.80 $229,991.08 $75,744..25 $190,273.97 $187, 151.50 $177,131,73 $153,250.49 $154,113.20 $152, 662.65 $123,028.08 $105,414.33 $76,178.32 958,724.04 $11,013.89 9-3,444.64 883557, 2774689, 5 - Cuscomer#000000005: 374723, 20-Nov-1996, 2572644, 01-11-1998, -479017, 06-00.-1992, 521'57, 23-Aug-1997, 269922, 19-Mar-1996, - 177350, 03-611-1997, $241,348.35 9201,566.95 $137,297.10 $141,934.18 $122,008.56 947,596.96 Deliverables Submit a file solution 3.Ist (or solution 3.pdf) with a report from processing of SQL script solution2.sql. The report MUST have no errors the report MUST list all SQL statements processed. The report MUST include ONLY SQL statements and control statements that implement the specifications of Task 3 and NO OTHER statements. SIM-IT2-CSCI235-2022S2 Copyright SCIT, University of Wollongong, 2022 Page 5 of 4