Transcript SQL/SyBase Programming

Security, Transactions,
and Views
Security
Achieved through GRANT & REVOKE
Assumes the database can recognize
its users and verify their identity
can also be controlled through the use
of views - subsets of data
usually maintained by the database
administrator or DBA
Views
A view is representation of an existing
table which corresponds to the SELECT
statement that created it. The view can
then be manipulated much like an actual
table.
A view is not a separate table or entity. It’s
more like a mask of the actual table.
Uses of a VIEW
Hiding sensitive data from users
Preserving a previous table schema
Presenting data to users in a desired
format.
Simplify a complex query
Creating a VIEW
CREATE VIEW view_name
[(col_name…)]
AS
SELECT _statement
Dropping a VIEW
DROP VIEW view_name
Only drops the view… not the table.
More about Views
The view displays like any table and the
data you see is the actual data in the
table(s).
A view is more for viewing rather than
updating since an update could disqualify
a record from the view.
Updates made to a view are made to the
table(s) and any changes made to the
table(s) are reflected in the view.
Naming View Columns
Column names are inherited from the
underlying tables.
New names can be assigned
Columns must be renamed when using
arithmetic expressions or when more
than one column has the same name.
Formatting
It is sometimes necessary to alter the
output of a query for the sake of
readability such as in report generation.
This can also be applied to a view
creation which users will share.
SUBSTRING
Returns a part of a character or binary
string.
SUBSTRING (expression, start, length)
SUBSTRING (‘SQL Programming’, 1, 3) =
SQL
CONVERT
Changes one datatype to another.
CONVERT(datatype[length], expression)
CONVERT(char(2), ‘SQL’) = SQ
CONVERT(int, ‘10’) = 10
Using them together...
Select substring(title_id, 1, 2) as alpha
convert(int, substring(title_id, 3, 4))
as num
from titles
Transactions are...
Transaction
- Logical unit of work
Transaction Management
- ensuring that a set of SQL
statements is treated as a unit - an
indivisible entity
Transactions
A transaction is a set of SQL statements that
represent a unit of work or a procedural
operation.
A transaction is not complete unless all off its
steps are followed through.
This can be critical to maintaining data
integrity such as when an account must be
credited while debiting another.
Why transactions?
Transactions are necessary for the purpose of
concurrency control and recovery
concurrency control
- allowing multiple users simultaneous
access
recovery
- allowing the database system to return the
database to a reliable state after a failure.
Concurrency
Lost-update problem
Locking

database system puts a lock on accessed
data so it cannot be altered until lock is
released.
Locking
Since many users may be trying to
access the same data simultaneously
the DBMS has a locking mechanism
which locks data which is in use.
This provides a solution to concurrency
problems which would arise if locking
were not available.
2 Types of Locks
Exclusive
- for UPDATE, INSERT, and DELETE (write
operations)
- no other transaction can acquire lock until
original is released
Shared
- applied during non-update or read operations
- usually SELECT
- prevents write operations from acquiring lock
- allows other read operations to share lock
Recovery
Allows a database to bounce back after
a system failure
must decide
what transactions are incomplete
 which transactions completed but were not
written and must be redone

User-defined
Transactions
Allows user to define any number of
SQL statements as a transaction and
instruct the database to process them
as one unit.
Defining a Transaction
A transaction starts with the keyword
BEGIN
BEGIN
SQL statement
SQL statement
SQL statement
COMMIT
Finishing the
Transaction
If the transaction goes successfully then
the COMMIT command will commit the
changes to the database.
However, if an error occurs the
ROLLBACK command can be used to
restore the database to its state prior to
the transaction.