SQL Transactions Overview: Views, Data Manipulation, and Transactions

1. IIT BOMBAY CS640 Information Systems Dr Deepak B Phatak Subrao Nilekani Chair Professor Kanwal Rekhi Building, Department of CSE IIT Bombay Session 7, SQL DML and transactions

2. Session overview • Views • Data manipulation • Changes in database contents • Transactions • Course Project CS634-Session 7, SQL-transactions

3. View definition • To create a view, we use: Create view v as<query expression>; • <Query expression> is any legal expression • The view name is represented by ‘v’ CS634-Session 7, SQL-transactions

4. Sample view Create view h8students as Select (sroll, sname, sroom, scpi) From student Where shostel = 8; CS634-Session 7, SQL-transactions

5. Sample query a view Select sname From h8students Where sroom > 300 And scpi > 9.00; CS634-Session 7, SQL-transactions

6. Sample query a view Select sname From h8students Where sroom > 300 And scpi > 9.00; The create view statement only creates the schema of the view, It is materialized only when a query is executed CS634-Session 7, SQL-transactions

7. Derived relation • Temporary views created as a result of a query Select sh, cpiavg from (select shostel, avg(scpi) From student Group by shostel As result (sh, cpiavg) ); CS634-Session 7, SQL-transactions

8. Modification of values • Database tables can be modified by any one of the following • Insert • Update • Delete CS634-Session 7, SQL-transactions

9. Insertion of rows • Insert record of a new student joining the institute Insert into student Values (‘02D01234’, ‘alekh’, 02, 123,); Insert into student (sroll, shostel) Values (‘01007052’, 08); CS634-Session 7, SQL-transactions

10. Another insertion example Insert into reg Select sroll, ‘CS771’ From reg where ccode = ‘CS634’ And sroll in (select sroll from student Where scpi > 9.5); CS634-Session 7, SQL-transactions

11. Modification of values • Change the grades of all CS634 students to ‘AA’ Update reg Set grade = ‘AA’ Where ccode = ‘CS634’; CS634-Session 7, SQL-transactions

12. Modification of values • Increase faculty salaries by 50% Update faculty Set salary = salary * 1.50; CS634-Session 7, SQL-transactions

13. Deletion • Delete registration records of ‘CS634’ for students with CPI < 6.5 Delete from reg Where sroll in (select sroll from student natural join reg Where scpi < 6.5 and ccode = ‘CS634’); CS634-Session 7, SQL-transactions

14. Another example of deletion - Remove all students of Hostel 8 Delete From Student Where shostel = 8; - Fire all faculty Delete from Faculty; CS634-Session 7, SQL-transactions

15. Authorization • It is obvious that the powerful SQL features for data manipulation may be misused to corrupt the contents of our database • Data Base Administrator (DBA) has full rights of access • Other users are given selective permissions using ‘Grant’ statement. • These can be revoked later CS634-Session 7, SQL-transactions

16. Insertion into views • Modification of values in a view actually means modification of values in the base table (s) • Modifications permitted only in simple views CS634-Session 7, SQL-transactions

17. Schema modifications • Data changes occur regularly • Schema changes are infrequent • Must be handled with great care • Alter table command • When the table structure is changed, what happens to values of existing and new columns? CS634-Session 7, SQL-transactions

18. Authorization • It is obvious that the powerful SQL features for data manipulation may be misused to corrupt the contents of our database • Data Base Administrator (DBA) has full rights of access • Other users are given select permissions using ‘Grant’ statement, which can be revoked later CS634-Session 7, SQL-transactions

19. SQL transactions • Transaction concept • ACID properties • SQL features supporting transactions • Special cases • Long transactions • Distributed transactions • Replication CS634-Session 7, SQL-transactions

20. Transaction concept • A transactionis a unit of program execution that accesses and possibly updates various data items. • A transaction is normally expected to support ACID properties to preserve data integrity CS634-Session 7, SQL-transactions

21. Transaction concept (contd.) • A transaction must see a consistent database. It must also leave behind a consistent database • During transaction execution the database may be inconsistent. • When the transaction is committed, the database must be consistent. CS634-Session 7, SQL-transactions

22. Transaction concept (contd.) • Two main issues to deal with: • Failures of various kinds • hardware failures • system crashes • Concurrent execution of multiple transactions • Different transactions trying to read/update same rows/columns CS634-Session 7, SQL-transactions

23. Example of fund transfer • Transaction to transfer Rs. 50 from account A to account B: 1. Read(A) 2. A := A – 50 3. Write(A) • Read(B) • B := B + 50 • Write(B) CS634-Session 7, SQL-transactions

24. Example of fund transfer 1. Read(A) 2. A := A – 50 • Write(A) • Read(b) • B := B + 50 • Write(B) Update Accounts Set Bal = Bal - 50 Where acode = ‘A’; Update Accounts Set Bal = Bal + 50 Where acode = ‘B’; CS634-Session 7, SQL-transactions

25. ACID properties • Atomicity. Either all operations of the transaction are properly reflected in the database or none are. • Consistency. Execution of a transaction in isolation preserves the consistency of the database. CS634-Session 7, SQL-transactions

26. Atomicity and consistency • Atomicity requirement — if the transaction fails after step 3 and before step 6, the system should ensure that its updates are not reflected in the database, else an inconsistency will result. • Consistency requirement – the sum of a and b is unchanged by the execution of the transaction. CS634-Session 7, SQL-transactions

27. Acid properties (contd.) • Isolation. Although multiple transactions may execute concurrently, each transaction must be unaware of other concurrently executing transactions. CS634-Session 7, SQL-transactions

28. Isolation • Intermediate transaction results must be hidden from other concurrently executed transactions. • That is, for every pair of transactions tiand tj, it appears to tithat, either tjfinished execution before ti started, or tj started execution after ti finished. CS634-Session 7, SQL-transactions

29. Possible wrong effects of concurrency Assume Deepak has Rs 14500 in ‘A’ T1 (Deepak) 1. Read(A) 4. A := A – 50 6. Write(A) 7. Read(B) 8. B := B + 50 9. Write(B) T2 (Pratibha) 2. Read (A) 3. A := A - 14500 5. Write (A) CS634-Session 7, SQL-transactions

30. Isolation • If, during the transaction T1, another transaction T2 reads the value of ‘A’ and attempts to modify it • Wrong balance for ‘A’ will result at the end of these transactions • Transaction may be illegal CS634-Session 7, SQL-transactions

31. Isolation • Isolation requirement — if another transaction is allowed to access the partially updated database, it will see an inconsistent database (the sum A + B will be less than what it should be) • Can be ensured trivially by running transactions serially, that is one after the other. However, executing multiple transactions concurrently has significant benefits. CS634-Session 7, SQL-transactions

32. Acid properties (contd.) • Durability. After A transaction completes successfully, the changes it has made to the database persist, even if there are system failures. CS634-Session 7, SQL-transactions

33. Durability • Durability requirement — once the user has been notified that the transaction has completed (i.e., The transfer of the Rs. 50 has taken place), the updates to the database by the transaction must persist despite failures. CS634-Session 7, SQL-transactions

34. SQL transactions • Normal syntax • Special prescription CS634-Session 7, SQL-transactions

35. SQL transactions (contd.) • In most database systems, each SQL statement that executes successfully is automatically committed. • Each transaction would then consist of only a single statement • Automatic commit can usually be turned off, allowing multi-statement transactions, but how to do so depends on the database system CS634-Session 7, SQL-transactions

36. SQL transactions • A transaction is A sequence of queries and update statements executed as A single unit • Transactions are started implicitly and terminated by one of • Commit work: makes all updates of the transaction permanent in the database • Rollback work: undoes all updates performed by the transaction. CS634-Session 7, SQL-transactions

37. SQL transactions • If any step of a transaction fails, all work done by the transaction can be undone by rollback work. • Rollback of incomplete transactions is done automatically, in case of system failures CS634-Session 7, SQL-transactions

38. SQL transactions (contd.) • Another option in SQL, enclose statements within begin atomic … … end; CS634-Session 7, SQL-transactions

39. Course project • Campus Activity Information System • Hostel activities • Sports activities • Cultural activities • Academic activities • Educational • Research and Development • Events, for example • Mood Indigo • Techfest • Ecell Activities • Other activities CS634-Session 7, SQL-transactions

40. Activities • Each group will give its choice by January 30 • Project allocation, and formation of the teams will be announced after the tutorial on 31st January • Submission Schedule for project work • System Requirement Specification • Draft SRS due on 5 February • Final SRS due on 15 February • Mid Semester Examination?? • Monday 19 February, 1700 Hrs CS634-Session 7, SQL-transactions