COMP643 Advanced Database Management

Assignment 2

Worth:                     40%

Due:                          Friday, 24th May 2024 5:00 p.m.

Late Penalty:          Work not received by the due time incurs an immediate penalty of up to 25% of

the marks available. No work will be accepted after Sunday 26th May 2024 5:00 p.m.

Submission:            via COMP643 on LEARN

Part 1 - Transaction Management and Concurrency Control (60 points)

Agri Markets sell products to customers. The relational diagram in Figure 1 represents the main entities for Agri’sdatabase.

Figure 1- The Agri Markets Entity Relationship Diagram (ERD)

Note the following important characteristics:

•    A customer may make many purchases, each one represented by an invoice.

o The cus_balance is updated with each credit purchase or payment and represents the amount the customer owes.

o The cus_balance is increased (+) with every credit purchased and decreased (-) with every customer payment.

o The date of last purchase (cus_datelstpur) is updated with each new purchase made by the customer.

o The date of the last payment (cus_datelstpmt) is updated with each payment made by the customer.

•    An invoice represents a set of products purchased by a customer.

o An Invoice can have many invoice lines, one for each product purchased.

o The inv_total represents the total cost of the invoice including taxes.

o The inv_terms can be “30”, “60”, or “90” (representing the number of days of credit) or “cash” or “cheque” or “cc” .

o The inv_status can be “open”, “paid” or “cancel” .

•    A product’s quantity on hand (p_qtyoh) is updated (decreased) with each product sale.

•    A customer may make many payments. The payment type (pmt_type) can be one of the following:

o “cash” for cash payments.

o “cheque” for cheque payments.

o “cc” for credit card payments.

•    The payment details (pmt_details) are used to record data about check or credit card payments.

o The bank, account number, and cheque number for cheque payments.

o The issuer, credit card number, and expiration date for credit card payments.

Note: Not all entities are represented in this example. Use only the attributes indicated.

Q1.     Using this database, write the SQL code to represent each of the following transactions. Use

BEGIN TRANSACTION to group the SQL statements in logical transactions. (10 points)

a.    On 20 Apr 2024, customer 10010 makesacredit purchase (60 days) of two units of

product 23109-HB with a unit price of $9.95 and one unit of product WR3-TT3 with a unit price of $119.95. The tax rate is 10%. The invoice number is 20001, and this invoice has     two product lines.

b.    On 5 May 2024, customer 10010 makes a payment of $100 in cash. The payment ID is 5001.

Q2.     Create a simple transaction log (using the format shown in Lecture 9) to represent the actions and transactions in Q1a. (10 points)

Q3.     Using the two-phase locking protocol, create a chronological list of locking, unlocking and data manipulation activities that would occur during the complete processing of the transaction

described in Q1a. (10 points)

Q4.     For the following schedule, assume that the shared locks are requested immediately before    each read operation, and exclusive locks are requested before every write operation. Unlocks occur immediately after the COMMIT statement.

a.    Add lock and unlock requests (using shared/exclusive locks) and explain why this schedule will cause a deadlock. You will need to rewrite the schedule to show the lock and unlock    requests. (10 points)

b.   Assume that the timestamp for transaction T(i) is i, explain how this deadlock will be handled using wait/die and wound/wait schemes. (10 points)

Q5.     Given the concurrent transactions shown in Figure 2. At time 9, a crash occurred.

Figure 2- Timeline of Concurrent Transactions

a.    Describe in detail the database recovery process using the deferred-write technique for each transaction (5 points).

b.    Describe in detail the database recovery process using the write-through technique for each transaction (5 points).

Part 2 - Database Performance Tuning and Query Optimisation (40 points)

The following relational database schema is used to keep track of customer orders for roses at Lincoln Rose Farm:

Customer (cust_num, cust_name, cust_city, cust_balance)

Orders (order_num, cust_num, order_date, order_total)

Orderline (line_num, order_num, prod_num, line_qty, line_total)

Product (prod_num, prod_desc, prod_price, prod_qoh)

The following SQL query is used to select all customers from Christchurch with a balance greater than 0 who have ordered “Ausblush” rose.

SELECT cust_name

FROM (((customer

INNER JOIN orders on customer.cust_num = orders.cust_num)

inner join orderline on orders.order_num = orderline.order_num)

inner join product on orderline.prod_num = product.prod_num)

where product.prod_desc = "Ausblush"

and customer.cust_city = "Christchurch"

and customer.cust_balance > 0.00;

Assume:

•    There are 10 customers, 10 products, 10 orders, and 22 order lines.

• “Ausblush” has been ordered 7 times.

• There are 6 customers who are based in “Christchurch” who has ordered “Ausblush” .

•    There are 5 customers who are based in “Christchurch” and who has a balance greater than 0.00 who has ordered “Ausblush” .

•    There is only one product with prod_desc equals to “Ausblush” .

•    There are 132 rows of matching cust_num for all customers who are based in “Christchurch” with balance greater than 0.00 who have ordered “Ausblush” .

•    There are 14 rows of matching cust_num and order_num for all customers who are based in “Christchurch” with balance greater than 0.00 who have ordered “Ausblush” .

Q1.  Create two alternative access plans and show their respective I/O costs using the example table shown in Week 10 Lecture. (20 points)

Q2.  You have been asked tooptimise the performance of the query.

a)     Identify and describe what indexes should be created and why? (10 points)

b)     Revise your access plan and the I/O cost for the selected plan from Q1 assuming the indexes you have identified are applied. (10 points)

Part 3 - Database Security (20 points)

Q1.     In Part 2, the database schema is used to process orders for roses. Orders are handled by the sales office. There are currently five staff in this office. Carol, the supervisor, needs to be able to see and update everything. Lim, Jane, and Billy do most of the routine work. They can add, update, and delete orders. They can view existing customers, but they cannot create new customers. Mina is a temporary staff member. She can process orders (add, update, and delete). For customer records, she is only allowed to view the cust_num and cust_name. Large orders above $1000 can only be handled by Carol and Billy.

a.    What privileges should be granted to Carol with respect to the four tables? Explain your answer. (6 points)

b.    List the privileges should be granted to Lim, Jane, and Billy. (4 points)

c.    What privileges should be granted to Mina? (2 points)

d.    What additional privileges do you need to grant Carol and Billy so that only these two can handle large orders above $1000. How do you ensure that the rest of the staff are not able to handle large orders? (5 points)

e.    What steps do you need to take to ensure that the privileges are set correctly? (3 points)