Ch. 3 Administration
Transaction - correct answer ✔The purpose of a ________ is to combine
multiple SQL statements together into a scenario that would either execute all
of the statements or none of them
; after begin - correct answer ✔What is missing from the below transaction?
BEGIN
UPDATE customer_account SET balance = balance - 500 WHERE
account_id = 1000;
UPDATE store_inventorySET quantity = quantity - 1WHERE store_id = 5 AND
product_name = 'Computer';
INSERT INTO customer_order(account_id, product_name,store_id,
quantity,cost) VALUES (1000, 'Computer',5, 1, 500);
UPDATE store_accountSET balance = balance + 500 WHERE store_id = 5;
COMMIT;
F.
BEGIN;
UPDATE customer_account SET balance = balance - 500 WHERE
account_id = 1000;
UPDATE store_inventorySET quantity = quantity - 1WHERE store_id = 5 AND
product_name = 'Computer';
INSERT INTO customer_order(account_id, product_name,store_id,
quantity,cost) VALUES (1000, 'Computer',5, 1, 500);
,UPDATE store_accountSET balance = balance + 500 WHERE store_id = 5; -
correct answer ✔T or F?
Each SQL statement does not need to end with a semicolon to separate each
out individually in a transaction
BEGIN;
UPDATE customer
SET company='Telus',
support_rep_id=6
WHERE customer_id = 9;
UPDATE customer
SET phone='9991112222', email='daanpeeters@gmail.com'
WHERE first_name = 'Daan'
AND last_name='Peeters';
COMMIT; - correct answer ✔Use a transaction to update the customer table
to set company to Telrus and support_rep_id = 6 where customer_id = 9. then
update customer and set the phone number = 999111222 and email =
'daanpeeters@gmail.com'
where first_name is Daan and last name is Peeters
atomicity, consistency, isolation, and durability. - correct answer ✔ACID
Properties
atomicity - correct answer ✔All changes to data are performed as if they are
a single operation. That is, all the changes are performed, or none of them
are.
,For example, in an application that transfers funds from one account to
another, the atomicity property ensures that, if a debit is made successfully
from one account, the corresponding credit is made to the other account.
consistency - correct answer ✔Data is in a consistent state when a
transaction starts and when it ends.
For example, in an application that transfers funds from one account to
another, the consistency property ensures that the total value of funds in both
the accounts is the same at the start and end of each transaction.
isolation - correct answer ✔The intermediate state of a transaction is
invisible to other transactions. As a result, transactions that run concurrently
appear to be serialized.
For example, in an application that transfers funds from one account to
another, the isolation property ensures that another transaction sees the
transferred funds in one account or the other, but not in both, nor in neither.
durability - correct answer ✔After a transaction successfully completes,
changes to data persist and are not undone, even in the event of a system
failure.
For example, in an application that transfers funds from one account to
another, the durability property ensures that the changes made to each
account will not be reversed.
D.
Atomicity requires that all SQL requests in a transaction should be fully
completed and if not, the entire transaction should be aborted. The transaction
should be viewed as a single logical unit of work that is indivisible. - correct
answer ✔Which of the following criteria is specific to the atomicity property?
, a.)In the event of system failure, no transactions that were done should be
undone.
b.)If any of the transaction parts violates an integrity constraint, the entire
transaction must be aborted.
c.)Data used in one transaction cannot be used in another transaction until the
first transaction is completed.
d.)A transaction should be treated as a single logical unit of work that is
indivisible.
C.
Atomicity requires that all SQL requests in a transaction should be fully
completed and if not, the entire transaction should be aborted. The transaction
should be viewed as a single logical unit of work that is indivisible. - correct
answer ✔Which of the following scenarios reflect the atomicity property?
a.)1. There are 50 desks available for purchase in the product table.2. In one
transaction the customer purchases 10 desks.3. In the same transaction the
customer purchases 10 desks.4. The validation check runs and sees there are
40 desks left.5. The entire transaction is reverted.
b.)
1. An HR employee updates Jeff's salary by increasing it by 2.5%.2. The
general manager updates Sally's salary by increasing it by 5%.3. The HR
employee hasn't finished the transaction yet and had gotten on a call.4. The
general manager updates Jeff's salary by increasing it by 5%.5. The HR
employee's database session timed out.6. The database reverts Jeff's salary
