# Transactions Transactions

$(10$p$.)$ Consider the database with the following schema

Product$($prodID$,$ name, price, manufID, stock$)$

Manufacturer$($manufID$,$ name, email$)$

In the initial state, the database contains the tuples

Table $1$: Products

Table $2$: Manufacturers

Let us inspect four transactions happening in our database. We do not know, in which order the

transactions will happen, and they may occur concurrently. The commands inside a transaction will

be executed in the order they are listed.

$T_1($restocking$)$:

For all the products, read the value of the attribute 'stock'. You may assume that this happens

one$-$by$-$one in an arbitrary order.

If the value is less than $50,$ update the value to be increased by $20.$

$T_2($ordering products$)$:

Read the value of 'stock' for the item with id $\text{'}123\text{'}.$

If the value is at least $5,$ decrease the value of 'stock' by $5($ie$.$ the product has been ordered$).$

You can work on the questions on DBeaver but also include some explanations:

$-$ either take screenshots and add explanations in a pdf or comment the sql script with your explanations.

$-$ If you prefer to use Standard SQL in $$pen and paper$$ format or typed in a text editor, you may do so but this is not advisable as you won$$t be able to check it on DBeaver

$3.(10$p$.)$ Consider the database with the following schema

$-$ Product$($prodID$,$ name, price, manufID, stock$)$

$-$ Manufacturer$($manufID$,$ name, email$)$

$**$In the initial state, the database contains the tuples:$**$

$**$Products$**$

$|$ prodID $|$ name $|$ price $|$ manufID $|$ stock $|$

$|---|---|---|---|---|$

$|123|$ABC shampoo$|3\mathrm{.}55|001|50|$

$|133|$ABC toothbrush $|1\mathrm{.}24|\text{'}001|14|$

$|442|$Headphones$|27\mathrm{.}99|\text{'}002|15|$

$|431|$Mouse$|18\mathrm{.}20|$ NULL $|3|$

$**$Manufacturers$**$

$|$ manufID $|$ name $|$ email $|$

$|---|---|---|$

$|001|$ABC$|$customerservice@abc.fi$|$

$|002|$XYZ$|$xyz$-$services@xyz$.$com$|$

Let us inspect four transactions happening in our database.

$-$ We do not know, in which order the transactions will happen, and they may occur concurrently.

$-$ The commands inside a transaction will be executed in the order they are listed.

$**$T$1($restocking$)$:$**$

$-$ For all the products, read the value of the attribute $$stock$.$ You may assume that this happens one$-$by$-$one in an arbitrary order.

$-$ If the value is less than $50,$ update the value to be increased by $20.$

$**$T$2($ordering products$)$:$**$

$-$ Read the value of $$stock$$ for the item with id $123.$

$-$ If the value is at least $5,$ decrease the value of $$stock$$ by $5($ie$.$ the product has been ordered$).$

$-$ Read the value of $$stock$$ for the item with id $431.$

$-$ If the value is at least $5,$ decrease the value of $$stock$$ by $5($ie$.$ the product has been ordered$)\mathrm{.}3$

$**$T$3($updating manufacturers$)$:$**$

$-$ Update the ID of the manufacturer $$ABC$$ to $001.$

$-$ Update all the items with manufID $000$ to have manufID $001.$

$-$ You may assume that this happens one by one in an arbitrary order.

$**$T$4($remove tuples breaking consistency$)$:$**$

$-$ Read the results of the following query

SELECT prodID

FROM Product

WHERE manufID NOT IN $($SELECT manufID FROM Manufacturer$)$;

$-$ Delete all the tuples in Product whose IDs are in the query.

### $($a$)$ If all the ACID principles of transactions hold, which of the following scenarios are possible in the end?

For possible scenarios, explain in which order the relevant transactions and their commands need to occur.

If the scenario is impossible, explain briefly why.

$-**$For the product $123$ the value of $$stock$$ is $65.$

$-**$For the product $123$ the value of $$stock$$ is less than $50.**$

$-**$The table products contains only one tuple.$**$

$-**$The transaction T$2$ is able to order the product $$ABC shampoo$$ but not the product $$Mouse$.**$

$-$

### $($b$)$ If all the ACID principles of transactions hold except atomicity, which of the following scenarios are possible in the end?

For possible scenarios, explain in which order the relevant transactions and their commands need to occur. If the scenario is impossible, explain briefly why.

$-$ The table Products contains only $1$ row.

$-$ The transaction T$2$ is able to order the product $$Mouse$$ but not the product $$ABC shampoo$.$

$**$In the end, the Product table contains the following tuples:$**$

$|$ prodID $|$ name $|$ price $|$ manufID $|$ stock $|$

$|---|---|---|---|---|$

$|123|$ABC shampoo$|3\mathrm{.}55|001|45|$

$|133|$ABC toothbrush $|1\mathrm{.}24|\text{'}001|34|$

$|442|$Headphones$|27\mathrm{.}99|\text{'}002|15|$

### $($c$)$ If all the ACID principles of transactions hold except isolation, which of the following scenarios are possible in the end?

For possible scenarios, explain in which order the relevant transactions and their commands need to occur. If the scenario is impossible, explain briefly why.

$-$ The table Products contains only $1$ row.

$-$ The transaction T$2$ is able to order the product $$Mouse$$ but not the product $$ABC shampoo$.$

$-$ The value of the attribute $$stock$$ for item $431$ is negative.