Transcript IT420_12WeekReview

IT420: Database Management and
Organization
12 Week Review
5 April 2006
Adina Crăiniceanu
www.cs.usna.edu/~adina
1
12 Week Exam






SQL
SQL Views
SQL Triggers
SQL Stored Procedures
PHP/MySQL
Database Administrator tasks
 Manage database structure
 Concurrency control
Kroenke, Database Processing
2
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
3
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
4
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
5
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
6
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
7
Create trigger
 CREATE TRIGGER trigger_name
ON table_or_view_name
AFTER | BEFORE | INSTEAD OF
INSERT | UPDATE | DELETE
AS
trigger_code
Kroenke, Database Processing
8
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
9
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
10
Create And Execute Stored
Procedures
 CREATE PROCEDURE proc_name
AS proc_code
 exec proc_name [@param1 = ]value1, …
Kroenke, Database Processing
11
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
12
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
13
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
14
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
15
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
16
Lost Update Problem
 T1: R(item)
W(item)
 T2:
R(item)
Commit
W(item) Commit
Kroenke, Database Processing
17
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
18
Serializable Transactions
 Serializable transactions:
 Run concurrently
 Results like when they run separately
 Strict two-phase locking – locking technique to
achieve serializability
Kroenke, Database Processing
19
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
20
Optimistic versus Pessimistic
Locking
 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
21
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
22
ACID Transactions
 Transaction properties:




Atomic - all or nothing
Consistent
Isolated
Durable – changes made by commited transactions
are permanent
Kroenke, Database Processing
23
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
24
Inconsistent-Read Problem
 Dirty reads – read uncommitted data
 T1: R(A), W(A),
 T2:
R(A), W(A), Commit
R(B), W(B), Abort
 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
25
Transaction Isolation Level
Kroenke, Database Processing
26