Comprehensive Review of Database Management Concepts and SQL Functionality

1. IT420: Database Management and Organization 12 Week Review 5 April 2006 Adina Crăiniceanu www.cs.usna.edu/~adina

2. 12 Week Exam • SQL • SQL Views • SQL Triggers • SQL Stored Procedures • PHP/MySQL • Database Administrator tasks • Manage database structure • Concurrency control Kroenke, Database Processing

3. SQL Views • SQL view is a virtual table that is constructed from other tables or views • It has no data of its own, but obtains data from tables or other views • It only has a definition • SELECT statements are used to define views • A view definition may not include an ORDER BY clause • Views can be used as regular tables in SELECT statements Kroenke, Database Processing

4. CREATE VIEW Command • CREATE VIEW command: CREATE VIEW CustomerNameView AS SELECT CustName AS CustomerName FROM CUSTOMER; • To see the view use: SELECT * FROM CustomerNameView ORDER BY CustomerName; Kroenke, Database Processing

5. Uses for SQL Views • Security: hide columns and rows • Display results of computations • Hide complicated SQL syntax • Provide a level of isolation between actual data and the user’s view of data • three-tier architecture • Assign different processing permissions to different views on same table • Assign different triggers to different views on same table Kroenke, Database Processing

6. Updateable Views • Views based on a single table • No computed columns • All non-null columns present in view • Views with INSTEAD OF triggers defined on them • Views based on a single table, primary key in view, some non-null columns missing from view • Updates for non-computed columns ok • Deletes ok • Inserts not ok Kroenke, Database Processing

7. Triggers • Trigger: stored program that is executed by the DBMS whenever a specified event occurs • Associated with a table or view • Three trigger types: BEFORE, INSTEAD OF, and AFTER • Each type can be declared for INSERT, UPDATE, and/or DELETE • Resulting in a total of nine trigger types Kroenke, Database Processing

8. Create trigger • CREATE TRIGGER trigger_name ON table_or_view_name AFTER | BEFORE | INSTEAD OF INSERT | UPDATE | DELETE AS trigger_code Kroenke, Database Processing

9. Stored Procedures • A stored procedure is a program that is stored within the database and is compiled when used • In Oracle, it can be written in PL/SQL or Java • In SQL Server, it can be written in TRANSACT-SQL • Stored procedures can receive input parameters and they can return results • Stored procedures can be called from: • Programs written in standard languages, e.g., Java, C# • Scripting languages, e.g., JavaScript, VBScript • SQL command prompt, e.g., SQL*Plus, Query Analyzer Kroenke, Database Processing

10. Stored Procedure Advantages • Greater security as store procedures are always stored on the database server • SQL can be optimized by the DBMS compiler • Code sharing resulting in: • Less work • Standardized processing • Specialization among developers Kroenke, Database Processing

11. Create And Execute Stored Procedures • CREATE PROCEDURE proc_name AS proc_code • exec proc_name [@param1 = ]value1, … Kroenke, Database Processing

12. DBA Tasks • Managing database structure • Controlling concurrent processing • Managing processing rights and responsibilities • Developing database security • Providing for database recovery • Managing the DBMS • Maintaining the data repository Kroenke, Database Processing

13. Managing Database Structure • Participate in database and application development • Assist in requirements stage and data model creation • Play an active role in database design and creation • Facilitate changes to database structure • Seek community-wide solutions • Assess impact on all users • Provide configuration control forum • Be prepared for problems after changes are made • Maintain documentation Kroenke, Database Processing

14. Concurrency Control • Concurrency control: ensure that one user’s work does not inappropriately influence another user’s work • No single concurrency control technique is ideal for all circumstances • Trade-offs need to be made between level of protection and throughput Kroenke, Database Processing

15. Atomic Transactions • A transaction, or logical unit of work (LUW), is a series of actions taken against the database that occurs as an atomic unit • Either all actions in a transaction occur - COMMIT • Or none of them do - ABORT Kroenke, Database Processing

16. Concurrent Transaction • Concurrent transactions: transactions that appear to users as they are being processed at the same time • In reality, CPU can execute only one instruction at a time • Transactions are interleaved • Concurrency problems • Lost updates • Inconsistent reads Kroenke, Database Processing

17. Lost Update Problem • T1: R(item) W(item) Commit • T2: R(item) W(item) Commit Kroenke, Database Processing

18. Inconsistent-Read Problem • Dirty reads – read uncommitted data • T1: R(A), W(A), R(B), W(B), Abort • T2: R(A), W(A), Commit • Unrepeatable reads • T1: R(A),R(A), W(A), Commit • T2: R(A), W(A), Commit Kroenke, Database Processing

19. Serializable Transactions • Serializable transactions: • Run concurrently • Results like when they run separately • Strict two-phase locking – locking technique to achieve serializability Kroenke, Database Processing

20. Deadlock • Deadlock: two transactions are each waiting on a resource that the other transaction holds • Preventing deadlock • Allow users to issue all lock requests at one time • Require all application programs to lock resources in the same order • Breaking deadlock • Almost every DBMS has algorithms for detecting deadlock • When deadlock occurs, DBMS aborts one of the transactions and rollbacks partially completed work Kroenke, Database Processing

21. Optimistic versus PessimisticLocking • Optimistic locking assumes that no transaction conflict will occur: • DBMS processes a transaction; checks whether conflict occurred: • If not, the transaction is finished • If yes, the transaction is repeated until there is no conflict • Pessimistic locking assumes that conflict will occur: • Locks are issued before a transaction is processed, and then the locks are released Kroenke, Database Processing

22. Declaring Lock Characteristics • Most application programs do not explicitly declare locks due to its complication • Mark transaction boundaries and declare locking behavior they want the DBMS to use • Transaction boundary markers: BEGIN, COMMIT, and ROLLBACK TRANSACTION • Advantage • If the locking behavior needs to be changed, only the lock declaration need be changed, not the application program Kroenke, Database Processing

23. ACID Transactions • Transaction properties: • Atomic - all or nothing • Consistent • Isolated • Durable – changes made by commited transactions are permanent Kroenke, Database Processing

24. Consistency • Consistency means either statement level or transaction level consistency • Statement level consistency: each statement independently processes rows consistently • Transaction level consistency: all rows impacted by either of the SQL statements are protected from changes during the entire transaction • With transaction level consistency, a transaction may not see its own changes Kroenke, Database Processing

25. Inconsistent-Read Problem • Dirty reads – read uncommitted data • T1: R(A), W(A), R(B), W(B), Abort • T2: R(A), W(A), Commit • Unrepeatable reads • T1: R(A),R(A), W(A), Commit • T2: R(A), W(A), Commit • Phantom reads • Re-read data and find new rows Kroenke, Database Processing

26. Transaction Isolation Level Kroenke, Database Processing