it420 database management and organization
Download
Skip this Video
Download Presentation
IT420: Database Management and Organization

Loading in 2 Seconds...

play fullscreen
1 / 26

IT420: Database Management and Organization - PowerPoint PPT Presentation


  • 99 Views
  • Uploaded on

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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' IT420: Database Management and Organization ' - howard


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
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
12 Week Exam
  • SQL
  • SQL Views
  • SQL Triggers
  • SQL Stored Procedures
  • PHP/MySQL
  • Database Administrator tasks
    • Manage database structure
    • Concurrency control

Kroenke, Database Processing

sql views
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

create view command
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

uses for sql views
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

updateable views
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

triggers
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

create trigger
Create trigger
  • CREATE TRIGGER trigger_name

ON table_or_view_name

AFTER | BEFORE | INSTEAD OF

INSERT | UPDATE | DELETE

AS

trigger_code

Kroenke, Database Processing

stored procedures
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

stored procedure advantages
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

create and execute stored procedures
Create And Execute Stored Procedures
  • CREATE PROCEDURE proc_name

AS proc_code

  • exec proc_name [@param1 = ]value1, …

Kroenke, Database Processing

dba tasks
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

managing database structure
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

concurrency control
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

atomic transactions
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

concurrent transaction
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

lost update problem
Lost Update Problem
  • T1: R(item) W(item) Commit
  • T2: R(item) W(item) Commit

Kroenke, Database Processing

inconsistent read problem
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

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

Kroenke, Database Processing

deadlock
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

optimistic versus pessimistic locking
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

declaring lock characteristics
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

acid transactions
ACID Transactions
  • Transaction properties:
    • Atomic - all or nothing
    • Consistent
    • Isolated
    • Durable – changes made by commited transactions are permanent

Kroenke, Database Processing

consistency
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

inconsistent read problem1
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

transaction isolation level
Transaction Isolation Level

Kroenke, Database Processing

ad