1 / 26

IT420: Database Management and Organization

IT420: Database Management and Organization. 12 Week Review 5 April 2006 Adina Cr ă iniceanu www.cs.usna.edu/~adina. 12 Week Exam. SQL SQL Views SQL Triggers SQL Stored Procedures PHP/MySQL Database Administrator tasks Manage database structure Concurrency control. SQL Views.

howard
Download Presentation

IT420: Database Management and Organization

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related