Transcript Chapter 7

Section 9 - Views, etc.
 Part
1:
 Part 2:
 Part 3:
 Part 4:
 Part 5:
 Part 6:
 Part 7:
Views
Security Issues
Transaction Management
Set Operations
Triggers and Stored Procedures
Performance Issues
Database Integrity
1
Part 1: Views
 Views
are virtual tables which are created
from the result set of a SELECT statement
 Views
are not separate copies of the data in
the tables from which they are derived
2
Views -Continued
 A View
is essentially stored SQL Select
statement
 A View is named like a table when created
(e.g. books_and_publishers)
3
Create VIEW Syntax
 CREATE
VIEW view_name
[column_name [,column_name] … ]
AS select_statement;
4
Example - Create View
 CREATE
VIEW books_and_publishers AS
(SELECT title, pub_name
FROM titles t, publishers p
WHERE t.pub_id = p.pub_id);
5
What the User Sees
 SELECT title,
pub_name
FROM books_and_publishers;
 Statement
first executes the SELECT
associated with the View
 User Select is against the View results
(which are transparent to the User)
6
Why Views?
 Saves
the user from complex joins
 Stops the user from seeing secure data
7
Deleting Views from the Database
 DROP VIEW
books_and_publishers;
 Warning: If you DROP a table that the
VIEW depends upon, the VIEW will no
longer work
8
Naming View Columns
 Assign
alias names to View columns is
optional… in most cases.
 By default, VIEW will use the same
columns names returned from the SELECT
 Exceptions: If there is any ambiguity from
a Join operation or View column is
arithmetically derived
 If you rename any columns, you must
rename all of them!
9
Exercise
 Create
a view that displays the authors
names, social security number and the
average position they appear on the books
they've written.
10
Solution
 CREATE
VIEW authors_position
(ssn, lastn, firstn, average_order) AS
SELECT a.au_id, au_lname, au_fname,
AVG(au_ord)
FROM authors a, titleauthors ta
WHERE a.au_id = ta.au_id
GROUP BY a.au_id, au_lname, au_fname;
11
Part 2: Security Issues
 Two
basic SQL security statements…
– GRANT gives a user access rights to a table or
columns on a table
– REVOKE removes user access right to a table
or columns on a table
12
GRANT/REVOKE Syntax
 GRANT
{ALL | privilege_list }
ON table or view name [column_list]
TO {PUBLIC | user_list}
 REVOKE
{ALL | privilege_list }
ON table or view name [column_list]
FROM {PUBLIC | user_list}
13
Examples
 GRANT
INSERT, UPDATE
ON titles
TO perrys, smithj;
 REVOKE
UPDATE
ON titles(advance, price)
FROM jonesk;
14
Views to restrict access
 Payroll
table: name, ssn, dept, salary
 CREATE
VIEW payroll_generalaccess
(name, dept) AS
SELECT name, dept
FROM payroll;
15
Vendors Offer Security Extensions
 ORACLE
has GROUPs
– allows you group user_ids under a group
– allows you to assign access rights to that group
16
Exercise
 Give
the user ids perrys and landeyt the
ability to make changes to existing data on
the authors table (except the au_id column)
and to add new rows of data but not delete
them. Of course, they should be able to see
the data.
17
Part 3: Transaction Management
 A transaction
is a logical unit of work
 Transaction management means ensuring
that a set of SQL statements is treated as a
unit
 Transaction management guarantees the
either all the operations are completed or
none of them are
18
Savings Changes to the Database
 COMMIT
statement saves all the changes
made to the database since the last
COMMIT.
 ROLLBACK statement un-does all changes
made to the database since the last
COMMIT or ROLLBACK.
19
Example
 Banking
transaction:
– Transfer $100 from Savings to Checking
– First reduce the savings balance by $100
– Then increase the checking balance by $100
 What
happens if the system crashes after
reducing the savings balance, but before
increasing the checking balance?
20
Status Checking
 When
SQL is embedded in computer
applications, you must check for the
success or failure of each SQL statement
 You COMMIT changes only after all the
SQL statements have completed
successfully in a transaction.
21
Concurrency Control
 How
do you prevent simultaneous
transactions on the same data from
interfering with each other?
 Concurrency control means making sure
that data is never operated on by another
user until a change is completed
22
The Lost Update Problem
 Steve
calls travel agent for a ticket to Tahiti
 Agent checks computer: One ticket left
 While I think about: Barbara calls her ticket
agent and asks for a ticket to Tahiti
 Her agent check computer: One ticket left
 Steve buys ticket: Agent saves information
 Barbara buys ticket: Agent saves information
 Steve goes to the airport for his ticket…Who?
23
Row Locking is the Solution
 When
Steve's agent bring up the record a
'write' lock is placed
 After, Steve's agent saves the transaction
the lock is removed
 When Barbara's agent tries to save, warned
that the record has changed and must reretrieve it.
 When retrieved again, Barbara will be
informed that the ticket has been sold.
24
Locking in Oracle
 SELECT au_id,
city
FROM authors
WHERE au_id = '144-11-2222'
FOR UPDATE OF city;
 Places
a write lock on the column city for the
row with a Primary key of '144-11-2222'
25
Part 4: Set Operations
 SQL operates
on Sets of row (i.e. Relations)
 We perform mathematical Set operations on
result sets
26
Oracle SET operators
 UNION
– combines all rows returned from each result set
with an implicit DISTINCT
 INTERSECT
– combines only the rows that are in common to
each result set
 MINUS
– Subtracts the rows from the first result set that
are found in the second result set
27
Example
 List
all the authors and editors
SELECT au_lname, au_fname
FROM authors
UNION
SELECT ed_lname, ed_fname
FROM editors;
28
Matching Select Lists
 To
perform SET operations the select lists
must match from ALL result sets
 They
must have the same number of
columns and the data types must match
29
Part 5: Triggers & Procedures
 Triggers
– Automatic updates set up to execute when an
INSERT, UPDATE, or DELETE is performed
against a table
 Stored
Procedures
– Precompiled SQL statements that reside on the
database server and may receive arguments and
return data. May use procedural language
– Generally called from within computer
applications or Triggers
30
Example
 CREATE
TRIGGER record_users
ON authors
FOR UPDATE
AS <procedure_name or SQL script>;
31
Part 6: Performance Issues
 Indexes
 Database
de-normalization
 Vendor specific optimization tools
– e.g. Oracle Analyzer, Hints
 Use
of Views to force limits on result size
32
Part 7: Database Integrity
 Domain
Constraints Integrity
– Set of potential values for a column
 Entity
Integrity
– e.g. No primary keys with NULL status
 Referential
Integrity
– Logical consistency between Primary Keys and
Foreign Keys. No Orphans.
33
Last Slide - Section 9
 Please
complete Assignment 8
34