Monday, May 21, 2007

Intro to Databases (Week 11)

1. SQL - Transaction Statements

SQL - Transaction Statements control transactions in database access. This subset of SQL is also called the Data Control Language for SQL (SQL DCL).

There are two types of SQL-Transaction Statements :
A. COMMIT statement ----- commit all changes for the current transaction
General Format : COMMIT [WORK]
B. ROLLBACK statement --- roll back all changes for the current transaction
General Format : ROLLBACK [WORK]

* Overview

A SQL Modification Statement has limited effect. A given statement can only directly modify the contents of a single table. The upshot is that operations which require modification of several tables must involve multiple modifcation statements. For example, A bank operation transfers funds from one type of account to another, requiring updates to 2 tables. Transactions provide a way to group these multiple statements in one atomic unit.

A transaction begins with the execution of a SQL-Data statement when there is no current transaction. Execution of a COMMIT Statement or ROLLBACK Statement completes the current transaction.

The DBMS must ensure that the effect of a transaction is not partial. All changes in a transaction must be made persistent, or no changes from the transaction must be made persistent.

* Transactional Isolation
In most cases, transactions are executed under a client connection to the DBMS. Multiple client connections can initiate transactions at the same time. This is known as concurrent transactions.

In the relational model, each transaction is completely isolated from other active transactions. At transaction isolation level Serializable, a transaction is fully isolated from changes made by other sessions. Queries issued under Serializable transactions cannot see any subsequent changes.

2. SQL-Schema Statements

SQL-Schema Statements provide maintenance of catalog objects for a schema -- tables, views and privileges. This subset of SQL is also called the Data Definition Language for SQL (SQL DDL).

There are six types of SQL-Schema Statements:
A. CREATE TABLE Statement
B. CREATE VIEW Statement
C. DROP TABLE Statement
D. DROP VIEW Statement
E. GRANT Statement
F. REVOKE Statement

-- REVOKE Statement
The REVOKE Statement revokes access privileges for database objects previously granted to other users.
General Format : REVOKE privilege-list ON [TABLE] object-list FROM user-list
The REVOKE Statement revokes each privilege in privilege-list for each object in object-list from each user in user-list. All privilleges must have been previously granted.
Example :
REVOKE SELECT ON s, sp FROM PUBLIC
REVOKE SELECT , INSERT, UPDATE(color) ON p FROM art, nan
REVOKE SELECT ON supplied_parts FROM sam

2. Object-Oriented Database Management System (OODBMS)

-- Overview --
An OODBMS is the result of combining object oriented programming principles with database management principles. An OODBMS should be able to store objects that are nealry indistinguishable from the kind of objects supported by the target programming language with as little limitation as possible. Persistent objects should belong to a class and can have one or more atomic types or other objects as attributes. The normal rules of inheritance should apply with all their benefits including polymorphism, overriding inherited methods and dynamic binding. Each objet has an object identifier (OID) which used as a way of uniquely identifying a particular object. OIDs are permanent, system generated and not based on any of the member data within the object.

A primary feature of an OODBMS is that accessing objects in the database is done in a transparent manner such that interaction with persistent objects is no different from interacting with in-memory objects. This is very different from using an RDBMSs in that there is no need to interact via a query sub-language like SQL nor is there a reaon to use a Call Level Interface suchas ODBC, ADO or JDBC.

-- List of OODBMS
Object Store, O2, Gemstone, Versant, Ontos, DB/Explorer ODBMS, Ontos, Poet, Objectivity/DB, EyeDB, Ozone, Zope, FramerD, XL2

1 comment:

Anonymous said...

europass schidt workers webster spillover vfure lois dresses bestselling enhanced underline
lolikneri havaqatsu