联系方式

  • QQ:99515681
  • 邮箱:99515681@qq.com
  • 工作时间:8:00-23:00
  • 微信:codinghelp2

您当前位置:首页 >> Database作业Database作业

日期:2021-05-16 10:29

SIM-A2-CSCI235-2021S2

Copyright SCIT, University of Wollongong, 2021

Page 1 of 5

Assignment 2 (15% of total marks)

Due date: Thursday, 20 May 2021

Scope:

The tasks of this assignment cover topics on PLSQL as well as Transaction

processing and concurrency.

Assessment criteria:

Marks will be awarded for:

? Correct,

? Comprehensive, and

? Appropriate

application 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 subject

CSCI235.

A submission procedure is explained at the end of specification.

This assignment consists of 4 tasks and specification of each task starts from a new

page.

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/SQL

statements processed. To achieve that 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.


SIM-A2-CSCI235-2021S2

Copyright SCIT, University of Wollongong, 2021

Page 2 of 5

Assignment Specification:

Preliminary actions

The Assignment 2 folder contains the SQL scripts dbCreateBook-2021S2.sql. Execute

the script dbCreateBook-2021S2.sql to create and to load a sample database. The

database contains information about the products as well as transactions done by

customers for the sample database. It is strongly recommended that you discover the

conceptual schema (UML diagram) of the database. However, there will be no mark

awarded for producing the conceptual schema.

Task 1 (2.0 marks)

PL/SQL block

Implement PL/SQL block that modifies the prices of books and videos according

to the following rules:

- if a product belongs to a category ‘book’ and it has not been purchased

in the last 30 days then decrease its price by 3%,

- if a product belongs to a category ‘video’ and it has been purchased less

than 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 and

prices of products included in Product table. Execute the PL/SQL block and

displays the numbers and prices for the second time.

Deliverables

Hand in an SQL script and the report from execution of the script. The report

must have no errors related to the implementation of your task and it must list

all PL/SQL and SQL statements processed.

Remember to set ECHO option of SQL*Plus to ON!


SIM-A2-CSCI235-2021S2

Copyright SCIT, University of Wollongong, 2021

Page 3 of 5

Task 2 (2.0 marks)

PL/SQL Procedure

Create a table UserChoice(p#, price) to store information about products

(number and price) selected by a user. Leave the table UserChoice empty when

first created.

Implement a stored PL/SQL procedure FindProducts(category, keyword) that

finds all products categorized by the input parameter category and described

by the input parameter keyword. For example, FindProducts(‘book’, ‘database’)

will find all books that are described or related to ‘database’. The procedure is

to store the numbers and prices of all selected products in UserChoice table.

Consider the following categories of products - book, video and cdrom when

you test your procedure.

To test your procedure, (i) implement SQL script that displays the contents of

empty UserChoice table, (ii) execute CREATE OR REPLACE PROCEDURE

statement to store FindProducts in a data dictionary, and (iii) execute a stored

procedure FindProducts again to display the contents of nonempty UserChoice

table.

Deliverables

Hand in the SQL script and the report from execution of scripts. The report

must have no errors related to the implementation of your task and it must list

all PL/SQL and SQL statements processed.

Remember to set ECHO option of SQL*Plus to ON!


SIM-A2-CSCI235-2021S2

Copyright SCIT, University of Wollongong, 2021

Page 4 of 5

Task 3 (2.0 marks)

Stored trigger

Implement a database trigger such that for each row that is inserted into Pbasket

table, the trigger activates a verification process to verify a value of credit card number

attribute. Whenever a credit card number that is included in a new row has been

already used by another customer, your trigger should abort the attempted insert

operation.

To test your trigger, execute SQL script that contains CREATE OR REPLACE TRIGGER

statement, INSERT statement to insert a row into Pbasket table with a new credit card

number, and INSERT statement to insert another row into Pbasket table with a credit

card number already used by another customer.

Deliverables

Hand in the SQL script and the report from execution of scripts.

Remember to set ECHO option of SQL*Plus to ON!

Submissions

This 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 Moodle

in the following way:

1) Zip all the files (Solution1.pdf, solution2.pdf, and solution3.pdf into one

zipped folder.)

2) Access Moodle at http://moodle.uowplatform.edu.au/

3) To login use a Login link located in the right upper corner the Web page or in

the middle of the bottom of the Web page

4) When successfully logged in, select a site CSCI235 (SP221) Database

Systems

5) Scroll down to a section Submissions of Assignments

6) Click at Submit your Assignment 2 here link.

7) Click at a button Add Submission

8) 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 Add…

9) 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-2021S2

Copyright SCIT, University of Wollongong, 2021

Page 5 of 5

Assignment 2 is an individual assignment and it is expected that all its tasks will be

solved individually without any cooperation with the other students. Plagiarism is

treated 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


版权所有:留学生编程辅导网 2020 All Rights Reserved 联系方式:QQ:99515681 电子信箱:99515681@qq.com
免责声明:本站部分内容从网络整理而来,只供参考!如有版权问题可联系本站删除。