写作CSCI235留学生程序、辅导SQL

” 写作CSCI235留学生程序、辅导SQLSIM-A2-CSCI235-2021S2Page 1 of 5Assignment 2 (15% of total marks)Due date: Thursday, 20 May 2021Scope:The tasks of this assignment cover topics on PLSQL as well as Transactionprocessing and concurrency.Assessment criteria:Marks will be awarded for: Correct, Comprehensive, and Appropriateapplication of the Materials covered in this subject.Please read carefully information listed below.This assignment contributes to 15% of the total assessment mark for the subjectCSCI235.A submission procedure is explained at the end of specification.This assignment consists of 4 tasks and specification of each task starts from a newpage.A policy regarding late submissions is included in the subject outline.For all the implemented tasks, your report or output must include a listing of all PL/SQLstatements processed. To achieve that put the following SQL*Plus commands in allyour scripts:SPOOL file-nameSET ECHO ONSET FEEDBACK ONSET LINESIZE 100SET PAGESIZE 200SET SERVEROUTPUT ONat the beginning of SQL script andSPOOL OFFat the end of SQL script.SIM-A2-CSCI235-2021S2Copyright SCIT, University of Wollongong, 2021Page 2 of 5Assignment Specification:Preliminary actionsThe Assignment 2 folder Contains the SQL scripts dbCreateBook-2021S2.sql. Executethe script dbCreateBook-2021S2.sql to create and to load a sample database. Thedatabase contains information about the products as well as transactions done bycustomers for the sample database. It is strongly recommended that you discover theconceptual schema (UML diagram) of the database. However, there will be no markawarded for producing the conceptual schema.Task 1 (2.0 marks)PL/SQL blockImplement PL/SQL block that modifies the prices of books and videos accordingto the following rules:- if a product belongs to a category book and it has not been purchasedin the last 30 days then decrease its price by 3%,- if a product belongs to a category video and it has been purchased lessthan 5 times in the last 20 days then decrease its price by 5%.To test your PL/SQL block implement SQL script that displays the numbers andprices of products included in Product table. Execute the PL/SQL block anddisplays the numbers and prices for the second time.DeliverablesHand in an SQL script and the report from execution of the script. The reportmust have no errors related to the implementation of your task and it must listall PL/SQL and SQL statements processed.Remember to set ECHO option of SQL*Plus to ON!SIM-A2-CSCI235-2021S2Copyright SCIT, University of Wollongong, 2021Page 3 of 5Task 2 (2.0 marks)PL/SQL ProcedureCreate a table UserChoice(p#, price) to store information about products(number and price) selected by a user. Leave the table UserChoice empty whenfirst created.Implement a stored PL/SQL procedure FindProducts(category, keyword) thatfinds all products categorized by the input parameter category and describedby the input parameter keyword. For example, FindProducts(book, database)will find all books that are described or related to database. The procedure isto store the numbersAnd prices of all selected products in UserChoice table.Consider the following categories of products – book, video and cdrom whenyou test your procedure.To test your procedure, (i) implement SQL script that displays the contents ofempty UserChoice table, (ii) execute CREATE OR REPLACE PROCEDUREstatement to store FindProducts in a data dictionary, and (iii) execute a storedprocedure FindProducts again to display the contents of nonempty UserChoicetable.DeliverablesHand in the SQL script and the report from execution of scripts. The reportmust have no errors related to the implementation of your task and it must listall PL/SQL and SQL statements processed.Remember to set ECHO option of SQL*Plus to ON!SIM-A2-CSCI235-2021S2Copyright SCIT, University of Wollongong, 2021Page 4 of 5Task 3 (2.0 marks)Stored triggerImplement a database trigger such that for each row that is inserted into Pbaskettable, the trigger activates a verification process to verify a value of credit card numberattribute. Whenever a credit card number that is included in a new row has beenalready used by another customer, your trigger should abort the attempted insertoperation.To test your trigger, execute SQL script that contains CREATE OR REPLACE TRIGGERstatement, INSERT statement to insert a row into Pbasket table with a new credit cardnumber, and INSERT statement to insert another row into Pbasket table with a creditcard number already used by another customer.DeliverablesHand in the SQL script And the report from execution of scripts.Remember to set ECHO option of SQL*Plus to ON!SubmissionsThis assignment is due by 9:00 pm (21:00 hours) 20 May 2021, Singapore time.Submit the files solution1.pdf, solution2.pdf, and solutions3.pdf through Moodlein the following way:1) Zip all the files (Solution1.pdf, solution2.pdf, and solution3.pdf into onezipped folder.)2) Access Moodle at https://moodle.uowplatform.edu.au/3) To login use a Login link located in the right upper corner the Web page or inthe middle of the bottom of the Web page4) When successfully logged in, select a site CSCI235 (SP221) DatabaseSystems5) Scroll down to a section Submissions of Assignments6) Click at Submit your Assignment 2 here link.7) Click at a button Add Submission8) Move the zipped file Created in Step 1 above into an area provided in Moodle.You can drag and drop files here to add them. You can also use a link Add9) Click at a button Save changes,10) Click at check box to confirm authorship of a submission,11) When you are satisfied, remember to click at a button Submit assignment.A policy regarding late submissions is included in the subject outline.Only one submission per student is accepted.SIM-A2-CSCI235-2021S2Copyright SCIT, University of Wollongong, 2021Page 5 of 5Assignment 2 is an individual assignment and it is expected that all its tasks will besolved individually without any cooperation with the other students. Plagiarism istreated seriously. Students involved will likely receive zero. If you have any doubts,questions, etc. please Consult your lecturer or tutor during lab classes or over e-mail.End of specification请加QQ：99515681 或邮箱：99515681@qq.com WX：codehelp

“