It420 database management and organization
1 / 26

IT420: Database Management and Organization - PowerPoint PPT Presentation

  • Uploaded on

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

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
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

12 week exam
12 Week Exam

  • SQL

  • SQL Views

  • SQL Triggers

  • SQL Stored Procedures


  • 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 CustomerNameView AS

    SELECT CustName AS CustomerName


  • 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


  • 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





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