IT420: Database Management and Organization

1
IT420 Database Management and Organization

• 12 Week Review
• 5 April 2006
• Adina Crainiceanu
• 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

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

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

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 users 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

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

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

8
Create trigger

• CREATE TRIGGER trigger_name
• ON table_or_view_name
• AFTER BEFORE INSTEAD OF
• INSERT UPDATE DELETE
• AS
• trigger_code

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., SQLPlus, Query Analyzer

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

11
Create And Execute Stored Procedures

• CREATE PROCEDURE proc_name
• AS proc_code
• exec proc_name _at_param1 value1,

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

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

14
Concurrency Control

• Concurrency control ensure that one users work
  does not inappropriately influence another users
  work
• No single concurrency control technique is ideal
  for all circumstances
• Trade-offs need to be made between level of
  protection and throughput

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

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

17
Lost Update Problem

• T1 R(item) W(item) Commit
• T2 R(item) W(item) Commit

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

19
Serializable Transactions

• Serializable transactions
• Run concurrently
• Results like when they run separately
• Strict two-phase locking locking technique to
  achieve serializability

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

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

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

23
ACID Transactions

• Transaction properties
• Atomic - all or nothing
• Consistent
• Isolated
• Durable changes made by commited transactions
  are permanent

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

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

26
Transaction Isolation Level