DBMS 130 Worksheet: Transactions 1 Please read Chapter 17 of the course textbook. Also, please read the Oracle Academy Transaction power point on the M06 Learning Activities page. Q1. What is a database Transaction? Q2. Name and describe these 4 characteristics of Transactions. A C I D Q3. What are at least 3 of the SQL commands for processing Transactions and what does each do? This sub-language of SQL is called TCL (Transaction Control Language. (NOTE: These SQL commands are not supported in APEX.) 1. 2. 3. Database Transactions can occur concurrently by several users. This can cause issues with data consistency. To help resolve this problem, RDBMS can implement Read Consistency. Q4. What is Read Consistency? Q5. What are two problems that could occur without data/read consistency? 1. 2. Q6. What is Data Locking and how/why is it used?

DBMS 130 Worksheet: Transactions 2 Q7. Please review the following SQL statements and answer the following questions. 1. On which line does the first commit occur? 2. Is this first commit implicit or explicit? 3. Which SQL DML statement is rolled back when line 12 is run? 4. Which SQL DML statement is committed on line 16? 5. Is the commit in line 16 implicit or explicit? 6. Assume 10 of the SA_REP employees start with a salary of 1000.00. What is their salary when 17 is run? a. NOTE: For those of you who are familiar with programming, assume there is no infinite loop here.

1 select * from employees; 2 DROP TABLE Employee_Project_Assignment; 3 UPDATE employees 4 SET salary = salary + salary * 1.05 5 WHERE job_id = 'ST_CLERK'; 6 SAVEPOINT clerk_increase; 7 UPDATE employees 8 SET salary = salary + salary * 1.1 9 WHERE job_id = 'SA_REP'; 10 SAVEPOINT sa_rep_increase; 11 SELECT SUM(salary) FROM employees; 12 ROLLBACK TO SAVEPOINT clerk_increase; 13 UPDATE employees 14 SET salary = salary + salary * 1.075 15 WHERE job_id = 'SA_REP'; 16 COMMIT; 17 select * from employees;