Download Oracle Nested Tables: Creating, Querying, and Operating on Nested Relations and more Slides Database Management Systems (DBMS) in PDF only on Docsity!
Oracle Nested Tables
Another structuring to ol provided in Oracle is the ability to have a relation with an attribute whose value is not just an ob ject, but a (multi)set of ob jects, i.e., a relation.
Keyword THE allows us to treat a nested relation as a regular relation, e.g., in FROM clauses.
Keywords CAST(MULTISET(...)) let us turn the result of a query into a nested relation.
De ning Table Typ es
If we have an ob ject typ e, we can create a new typ e that is a bag of that typ e by AS TABLE OF.
Example
Supp ose we have a more complicated b eer typ e:
CREATE TYPE BeerType AS OBJECT ( name CHAR(20), kind CHAR(10), color CHAR(10) ); /
We may create a typ e that is a (nested) table of ob jects of this typ e by:
CREATE TYPE BeerTableType AS TABLE OF BeerType; /
Querying With Nested Tables
An attribute that is a nested table can b e printed like any other attribute.
The value has two typ e constructors, one for the table, one for the typ e of its tuples.
Example
List the b eers made by Anheuser-Busch.
SELECT beers FROM Manfs WHERE name = 'Anheuser-Busch' ;
A single value will b e printed, lo oking something like: BeerTableType( BeerType('Bud', 'lager', 'yellow'), BeerType('Lite', 'malt', 'pale'),... )
Op erating on Nested Tables
Use THE to get the nested table itself, then treat it like any other relation.
Example
Find the ales made by Anheuser-Busch.
SELECT bb.name FROM THE( SELECT beers FROM Manfs WHERE name = 'Anheuser-Busch' ) bb WHERE bb.kind = 'ale';
Transactions
= units of work that must b e:
- Isolated = app ear to have b een executed when no other DB op erations were b eing p erformed. F Often called serializable b ehavior. F In mo dern DBMS's, serializa bil i ty is often one of several options for how b ehavior is restricted.
- Atomic = either all work is done, or none of it.
Commit/Ab ort Decision
Each transaction ends with either:
- Commit = the work of the transaction is installe d in the database; previously its changes may b e invisibl e to other transactions.
- Abort = no changes by the transaction app ear in the database; it is as if the transaction never o ccurred. F ROLLBACK is the term used in SQL and the Oracle system.
In the ad-ho c query interface (e.g., Oracle's SQLplus), transactions are single queries or mo di cation statements. F Oracle allows SET TRANSACTION READ ONLY to b egin a multistatement transaction that do esn't change any data, but needs to see a consistent \snapshot" of the data.
In program interfaces (e.g., Pro*C or PL/SQL), transactions b egin whenever the database is accessed, and end when either a COMMIT or ROLLBACK statement is executed.
If the order of statements is 1, 3, 4, 2, then it app ears to Sally that Jo e's minimum price is greater than his maximum price.
Fix the problem by grouping Sally's two statements into one transaction, e.g. with one PL/SQL statement.
Example: Problem With Rollback
Supp ose Jo e executes statement 4 (insert Heineken), but then, during the transaction thinks b etter of it and issues a ROLLBACK statement.
If Sally is allowed to execute her statement 1 ( nd max) just b efore the rollback, she gets the answer $3.50, even though Jo e do esn't sell any b eer for $3.50.
Fix by making statement 4 a transaction, or part of a transaction, so its e ects cannot b e seen by Sally unless there is a COMMIT action.
X = READ COMMITTED: this transaction can only read committed data.
Example
If transactions are as ab ove, Sally could see the original Sells for statement 1 and the completely changed Sells for statement 2.
X = REPEATABLE READ: if a transaction reads data twice, then what it saw the rst time, it will see the second time (it may see more the second time). F Moreover, all data read at any time must b e committed; i.e., REPEATABLE READ is a strictly stronger condition than READ COMMITTED.
Example
If 1 is executed b efore 3, then 2 must see the Bud and Miller tuples when it computes the min, even if it executes after 3. But 2 may see the Heineken tuple, even if 1 didn't.
Indep endence of Isolation Levels
Isolation levels describ e what a transaction T with that isolation level sees.
They do not constrain what other transactions, p erhaps at di erent isolation levels, can see of the work done by T.
Example
If transaction 3-4 (Jo e) runs serializable , but transaction 1-2 (Sally) do es not, then Sally might see NULL as the value for b oth min and max, since it could app ear to Sally that her transaction ran b etween steps 3 and 4.
Authorization in SQL
File systems identify certain access privileges on les, e.g., read, write, execute.
In partial analogy, SQL identi es nine access privileges on relations, of which the most imp ortant are:
- SELECT = the right to query the relation.
- INSERT = the right to insert tuples into the relation | may refer to one attribute, in which case the privilege is to sp ecify only one column of the inserted tuple.
- DELETE = the right to delete tuples from the relation.
- UPDATE = the right to up date tuples of the relation | may refer to one attribute.
Revoking Privileges
Your privileges can b e revoked.
Syntax is like granting, but REVOKE ... FROM instead of GRANT ... TO.
Determining whether or not you have a privilege is tricky, involving \grant diagrams" as in text. However, the basic principles are: a) If you have b een given a privilege by several di erent p eople, then all of them have to revoke in order for you to lose the privilege. b) Revo cation is transitive. If A granted P to B , who granted P to C , and then A revokes P from B , it is as if B also revoked P from C.
