程序代写案例-CSCI235
School of Computing and Information Technology Session: Autumn 2021
University of Wollongong Lecturer: Janusz R. Getta

CSCI235 Database Systems
Assignment 2
12 April 2021


Scope
This assignment includes the tasks related to processing of database transactions.

The outcomes of this assignment are due by Saturday 8 May, 2021, 7.00 pm (sharp).

Please read very carefully information listed below.

This assignment contributes to 20% of the total evaluation in a 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.

It is recommended to solve the problems before attending the laboratory classes in order to
efficiently use supervised laboratory time.

A submission marked by Moodle as "late" is treated as a late submission no matter how
many seconds it is late.

A policy regarding late submissions is included in the subject outline.

A submission of compressed files (zipped, gzipped, rared, tared, 7-zipped, lhzed, … etc) is
not allowed. The compressed files will not be evaluated.

All files left on Moodle in a state "Draft(not submitted)" will not be evaluated.

It is expected that all tasks included within Assignment 2 will be solved individually
without any cooperation with the other students. If you have any doubts, questions, etc.
please consult your lecturer or tutor during lab classes or office hours. Plagiarism will result
in a FAIL grade being recorded for the assessment task.









Task 1 (6 marks)
Concurrent processing of database transactions

Consider the database transactions listed below.

T1 T2 T3
a = read(x) b = read(x) d = read(z)
write(x,a+1) c = read(y) write(z,2)
write(y,a+2) write(z,b*c) commit
commit commit


Assume that the initial values of the persistent data items x, y, and z are the following.
x = 1, y = 2, and z = 1.

(1) (2 marks)
Show a sample concurrent execution of the transactions T1, T2, and T3 that is not
view serializable.

Prove, that the execution is not view serializable.

When visualizing the concurrent executions use a technique of two-dimensional
diagrams presented to you during the lecture classes, for example, see a presentation
10 Introduction to Transaction Processing (1), slide 9.


(2) (2 marks)
Show a sample concurrent execution of the transactions T1, T2, and T3 that is not
conflict serializable and that is view serializable.

Prove, that the execution is not conflict serializable and that it is view serializable.

When visualizing the concurrent executions use a technique of two-dimensional
diagrams presented to you during the lecture classes, for example, see a presentation
10 Introduction to Transaction Processing (1), slide 9.

(2) (2 marks)
Show a sample concurrent execution of the transactions T1, T2, and T3 that is conflict
serializable and that is not order-preserving conflict serializable.

Prove, that the execution is conflict serializable and that it is not order-preserving
conflict serializable.

When visualizing the concurrent executions use a technique of two-dimensional
diagrams presented to you during the lecture classes, for example, see a presentation
10 Introduction to Transaction Processing (1), slide 9.
Deliverables
A file solution1.pdf with:
(1) a visualization of a sample concurrent processing of the transactions T1, T2, and T3
that is not view serializable and a proof that the processing is not view serializable.

(2) visualization of a sample concurrent execution of the transactions T1, T2, and T3
that is not conflict serializable and that is view serializable and a proof that the
execution is not conflict serializable and that it is view serializable.

(3) visualization of a sample concurrent execution of the transactions T1, T2, and T3
that is conflict serializable and that is not order-preserving conflict serializable and a
proof that the execution is conflict serializable and that it is not order-preserving
conflict serializable.






Task 2 (6 marks)
Serialization graph testing, 2PL, and Timestamp ordering scheduler

Consider a concurrent execution of database transactions T1, T2, and T3 when the
execution is not controlled by any scheduler.

T1 T2 T3
a=read(x)
b=read(y)
write(x,a+1)
c=read(z)
write(x,b+2)
write(y,c+1)
d=read(z)
write(z,d-2)

(1) (2 marks)
Assume, that the transactions attempt to interleave their operations in the same way as
in the execution above. Show a sample concurrent execution of the transactions T1,
T2, and T3 that is controlled by 2PL scheduler.

Assume, that to simplify the problem we use only a general concept of a lock and we
do not distinguish between shared locks and exclusive locks.

When visualizing the concurrent executions use a technique of two-dimensional
diagrams presented to you during the lecture classes, for example, see a presentation
10 Introduction to Transaction Processing (1), slide 9.

(3) (2 marks)
Assume, that the transactions attempt to interleave their operations in the same way as
in the execution above. Show a sample concurrent execution of the transactions T1,
T2, and T3 that is controlled by timestamp ordering scheduler.

When visualizing the concurrent executions use a technique of two-dimensional
diagrams presented to you during the lecture classes, for example, see a presentation
10 Introduction to Transaction Processing (1), slide 9.

Show the data items accessed by the transactions together with the timestamps left by
the transactions on the data items.

(3) (2 marks)
Assume, that the transactions attempt to interleave their operations in the same way as
in the execution above. Show a sample concurrent execution of the transactions T1,
T2, and T3 that is controlled by serialization graph testing scheduler.

Draw a conflict serialization graph.

When visualizing the concurrent executions use a technique of two-dimensional
diagrams presented to you during the lecture classes, for example, see a presentation
10 Introduction to Transaction Processing (1), slide 9.

Deliverables
A file solution2.pdf with:
(1) visualization of a sample concurrent execution of the transactions T1, T2, and T3 that
is controlled by serialization graph testing scheduler and a conflict serialization graph,

(2) visualization of a sample concurrent execution of the transactions T1, T2, and T3 that
is controlled by 2PL scheduler,

(3) visualization of a sample concurrent execution of the transactions T1, T2, and T3 that
is controlled by timestamp ordering scheduler.






Task 3 (5 marks)
Processing transactions at READ COMMITTED isolation level

Consider an anonymous PL/SQL block listed below.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

DECLARE
max3323456721 NUMBER;
maxall NUMBER;
result NUMBER;
BEGIN
SELECT MAX(amount)
INTO max3323456721
FROM TRANSACTION
WHERE acc_num = 3323456721;

SELECT MAX(amount)
INTO maxall
FROM TRANSACTION;

SELECT maxall - max3323456721
INTO result
FROM DUAL;

DBMS_OUTPUT.PUT_LINE(result);
COMMIT;
END;

(1) (3 marks)
Assume, that the anonymous PL/SQL block is processed as a database transaction at
READ COMMITTED ISOLATION level.

Show a sample concurrent execution of the anonymous PL/SQL block listed above,
such that the anonymous PL/SQL block interleaves the operations with another
transaction and such that a result returned by
DBMS_OUTPUT.PUT_LINE(result) is incorrect. The other transaction is up to
you.

When visualizing the concurrent executions use a technique of two-dimensional
diagrams presented to you during the lecture classes, for example, see a presentation
14 Transaction Processing in Oracle DBMS slide 16.

(2) (2 marks)
Rewrite the anonymous PL/SQL block listed above such that it can be processed at
READ COMMITTED level and show how the improved PL/SQL block interleaves the
operations with another transaction and such that a result returned by
DBMS_OUTPUT.PUT_LINE(result) is always correct.

When visualizing the concurrent executions use a technique of two-dimensional
diagrams presented to you during the lecture classes, for example, see a presentation
14 Transaction Processing in Oracle DBMS slide 16.

Deliverables
A file solution3.pdf with:
(1) visualization of a sample concurrent execution of the anonymous PL/SQL block at
READ COMMITTED level that interleaves the operations with another transaction and
such that a result returned by DBMS_OUTPUT.PUT_LINE(result) is incorrect,

(2) visualization of a sample concurrent execution of an improved function at READ
COMMITTED level that interleaves the operations with another transaction and such
that a result returned by DBMS_OUTPUT.PUT_LINE(result) is incorrect.





Task 4 (3 marks)
Processing transactions at SERIALIZABLE isolation level

A database programmer implemented a stored procedure TRANSFER that transfer a given
amount of money from one account to another. A procedure TRANSFER is listed below.

CREATE OR REPLACE PROCEDURE TRANSFER(
from_acc IN ACCOUNT.account_num%TYPE,
to_acc IN ACCOUNT.account_num%TYPE,
amount IN NUMBER ) IS
from_balance ACCOUNT.balance%TYPE;
BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT balance
INTO from_balance
FROM ACCOUNT
WHERE account_num = from_acc;

IF (from_balance >= amount) THEN
UPDATE ACCOUNT
SET balance = balance - amount
WHERE account_num = from_acc;
COMMIT;

UPDATE ACCOUNT
SET balance = balance + amount
WHERE account_num = to_acc;
COMMIT;
ELSE
DBMS_OUTPUT.PUT_LINE('Not enough funds to transfer');
ROLLBACK;
END IF;

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
ROLLBACK;
END TRANSFER;

A comprehensive testing of a procedure TRANSFER together with the other concurrently
running transactions and operating on the same relational table ACCOUNT revealed, that
from time to time certain amounts of money disappear from the bank accounts. It simply
means, that a procedure TRANSFER is implemented in incorrect way.

Your task is to find and to comprehensively explain a mistake in the implementation of a
procedure TRANSFER and show in what circumstances concurrent processing of the
procedure with other transactions operating on the bank accounts may corrupt the contents
of a relational table ACCOUNT.

Please note, that as always an "educated guess" scores no marks.

Deliverables
A file solution4.pdf with the comprehensive explanations of a mistake in
implementation of a procedure TRANSFER and an example when concurrent processing
of the procedure with other transactions operating on the bank accounts may corrupt the
contents of a relational table ACCOUNT.


Submission
Submit the files solution1.pdf, solution2.pdf, solution3.pdf, and
solution4.pdf through Moodle in the following way:
(1) Access Moodle at http://moodle.uowplatform.edu.au/
(2) 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
(3) When logged select a site CSCI235 (S121) Database Systems
(4) Scroll down to a section SUBMISSIONS
(5) Click at a link In this place you can submit the outcomes of
Assignment 2
(6) Click at a button Add Submission
(7) Move a file solution1.pdf into an area You can drag and drop files
here to add them. You can also use a link Add…
(8) Repeat a step (7) for the files solution2.pdf, solution3.pdf, and
solution4.pdf.
(9) Click at a button Save changes
(10) Click at a button Submit assignment
(11) Click at the checkbox with a text attached: By checking this box, I
confirm that this submission is my own work, … in order to
confirm the authorship of your submission.
(12) Click at a button Continue

End of specification

欢迎咨询51作业君
51作业君 51作业君

Email:51zuoyejun

@gmail.com

添加客服微信: ITCSdaixie