[PDF] - Oracle Nested T ables Another structuring to ol pro vided PDF Document

SLIDE 1 Oracle Nested T ables Another structuring to

l

pro vided in Oracle is the abilit y to ha v e a relation with an attribute whose v alue is not just an

b

ject, but a (m ulti)set

f
b

jects, i.e., a relation.

Keyw
rd

THE allo ws us to treat a nested relation as a regular relation, e.g., in FROM clauses.

Keyw
rds

CAST(MULTISET(...)) let us turn the result

f

a query in to a nested relation. Dening T able T yp es If w e ha v e an

b

ject t yp e, w e can create a new t yp e that is a bag

f

that t yp e b y AS TABLE OF. 1

SLIDE 2 Example Supp

se

w e ha v e a more complicated b eer t yp e: CREATE TYPE BeerType AS OBJECT ( name CHAR(20), kind CHAR(10), color CHAR(10) ); / W e ma y create a t yp e that is a (nested) table

f
b

jects

f

this t yp e b y: CREATE TYPE BeerTableType AS TABLE OF BeerType; / 2

SLIDE 3 No w, w e can dene a relation

f

man ufacturers that will nest their b eers inside.

In

a sense, w e normalize an unnormalized relation, since

ther

data ab

ut

the man ufacturer app ears

nly
nce

no matter ho w man y b eers they pro duce. CREATE TABLE Manfs ( name CHAR(30), addr CHAR(50), beers BeerTableType )

Ho

w ev er, to tell the system ho w to store the littl e beers tables, w e m ust follo w this statemen t, prior to the semicolon, b y a statemen t NESTED TABLE beers STORE AS BeerTable;

The

name

f

the table that stores the tuples for the nested beers relations is arbitrary; here w e used BeerTable. 3

SLIDE 4 Querying With Nested T ables An attribute that is a nested table can b e prin ted lik e an y

ther

attribute.

The

v alue has t w

t

yp e constructors,

ne

for the table,

ne

for the t yp e

f

its tuples. Example List the b eers made b y Anheuser-Busc h. SELECT beers FROM Manfs WHERE name = 'Anheuser-Busch' ;

A

single v alue will b e prin ted, lo

king

something lik e: BeerTableType( BeerType('Bud', 'lager', 'yellow'), BeerType('Lite', 'malt', 'pale'),... ) 4

SLIDE 5 Op erating

n

Nested T ables Use THE to get the nested table itself, then treat it lik e an y

ther

relation. Example Find the ales made b y Anheuser-Busc h. SELECT bb.name FROM THE( SELECT beers FROM Manfs WHERE name = 'Anheuser-Busch' ) bb WHERE bb.kind = 'ale'; 5

SLIDE 6 Casting to Create Nested T ables Create a v alue for a nested table b y using a select- from-where query and \casting" it to the table t yp e. Example

Supp
se

w e ha v e a relation Beers(beer, manf), where beer is a BeerType

b

ject and manf its man ufacturer.

W

e w an t to insert in to Manfs a tuple for P ete's Brewing Co., with all the b eers brew ed b y P ete's (according to Beers) in

ne

nested table. INSERT INTO Manfs VALUES( 'Pete''s', 'Palo Alto', CAST( MULTISET( SELECT bb.beer FROM Beers bb WHERE bb.manf = 'Pete''s' ) AS BeerType ) ); 6

SLIDE 7 T ransactions = units

f

w

rk

that m ust b e: 1. Isolate d = app ear to ha v e b een executed when no

ther

DB

p

erations w ere b eing p erformed.

✦

Often called serializable b eha vior.

✦

In mo dern DBMS's, serializa bil i t y is

ften
ne
f

sev eral

ptions

for ho w b eha vior is restricted. 2. A tomic = either all w

rk

is done,

r

none

f

it. 7

SLIDE 8 Commit/Ab

rt

Decision Eac h transaction ends with either: 1. Commit = the w

rk
f

the transaction is installe d in the database; previously its c hanges ma y b e in visibl e to

ther

transactions. 2. A b

rt

= no c hanges b y the transaction app ear in the database; it is as if the transaction nev er

ccurred.

✦

ROLLBACK is the term used in SQL and the Oracle system.

In

the ad-ho c query in terface (e.g., Oracle's SQLplus), transactions are single queries

r

mo dication statemen ts.

✦

Oracle allo ws SET TRANSACTION READ ONLY to b egin a m ultistatemen t transaction that do esn't c hange an y data, but needs to see a consisten t \snapshot"

f

the data.

In

program in terfaces (e.g., Pro*C

r

PL/SQL), transactions b egin whenev er the database is accessed, and end when either a COMMIT

r

ROLLBACK statemen t is executed. 8

SLIDE 9 Example Sells(bar , beer , price)

Jo

e's Bar sells Bud for $2.50 and Miller for $3.00.

Sally

is querying the database for the highest and lo w est price Jo e c harges: (1) SELECT MAX(price) FROM Sells WHERE bar = 'Joe''s Bar'; (2) SELECT MIN(price) FROM Sells WHERE bar = 'Joe''s Bar';

A

t the same time, Jo e has decided to replace Miller and Bud b y Heinek en at $3.50: (3) DELETE FROM Sells WHERE bar = 'Joe''s Bar' AND (beer = 'Miller' OR beer = 'Bud'); (4) INSERT INTO Sells VALUES('Joe''s bar', 'Heineken', 3.50); 9

SLIDE 10

If

the

rder
f

statemen ts is 1, 3, 4, 2, then it app ears to Sally that Jo e's minim um price is greater than his maxim um price.

Fix

the problem b y grouping Sally's t w

statemen

ts in to

ne

transaction, e.g. with

ne

PL/SQL statemen t. 10

SLIDE 11 Example: Problem With Rollbac k Supp

se

Jo e executes statemen t 4 (insert Heinek en), but then, during the transaction thinks b etter

f

it and issues a ROLLBACK statemen t.

If

Sally is allo w ed to execute her statemen t 1 (nd max) just b efore the rollbac k, she gets the answ er $3.50, ev en though Jo e do esn't sell an y b eer for $3.50.

Fix

b y making statemen t 4 a transaction,

r

part

f

a transaction, so its eects cannot b e seen b y Sally unless there is a COMMIT action. 11

SLIDE 12 SQL Isolation Lev els Isolation levels determine what a transaction is allo w ed to see. The declaration, v alid for

ne

transaction, is: SET TRANSACTION ISOLATION LEVEL X ; where:

X

= SERIALIZABLE: this transaction m ust execute as if at a p

in

t in time, where all

ther

transactions

ccurred

either completely b efore

r

completely after. Example Supp

se

Sally's statemen ts 1 and 2 are

ne

transaction and Jo e's statemen ts 3 and 4 are another transaction. If Sally's transaction runs at isolation lev el SERIALIZABLE, she w

uld

see the Sells relation either b efore

r

after statemen ts 3 and 4 ran, but not in the middle. 12

SLIDE 13

X

= READ COMMITTED: this transaction can

nly

read committed data. Example If transactions are as ab

v

e, Sally could see the

riginal

Sells for statemen t 1 and the completely c hanged Sells for statemen t 2. 13

SLIDE 14

X

= REPEATABLE READ: if a transaction reads data t wice, then what it sa w the rst time, it will see the second time (it ma y see more the second time).

✦

Moreo v er, all data read at an y time m ust 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 m ust see the Bud and Miller tuples when it computes the min, ev en if it executes after 3. But 2 ma y see the Heinek en tuple, ev en if 1 didn't. 14

SLIDE 15

X

= READ UNCOMMITTED: essen tially no constrain t, ev en

n

reading data written and then remo v ed b y a rollbac k. Example 1 and 2 could see Heinek en, ev en if Jo e rolled bac k his transaction. 15

SLIDE 16 Indep endence

f

Isolation Lev els Isolation lev els describ e what a transaction T with that isolation lev el sees.

They

do not constrain what

ther

transactions, p erhaps at dieren t isolation lev els, can see

f

the w

rk

done b y T . Example If transaction 3-4 (Jo e) runs serializable , but transaction 1-2 (Sally) do es not, then Sally migh t see NULL as the v alue for b

th

min and max, since it could app ear to Sally that her transaction ran b et w een steps 3 and 4. 16

SLIDE 17 Authorization in SQL

File

systems iden tify certain access privileges

n

les, e.g., read, write, execute.

In

partial analogy , SQL iden ties nine access privileges

n

relations,

f

whic h the most imp

rtan

t are: 1. SELECT = the righ t to query the relation. 2. INSERT = the righ t to insert tuples in to the relation | ma y refer to

ne

attribute, in whic h case the privilege is to sp ecify

nly
ne

column

f

the inserted tuple. 3. DELETE = the righ t to delete tuples from the relation. 4. UPDATE = the righ t to up date tuples

f

the relation | ma y refer to

ne

attribute. 17

SLIDE 18 Gran ting Privileges

Y
u

ha v e all p

ssible

privileges to the relations y

u

create.

Y
u

ma y gran t privileges to an y user if y

u

ha v e those privileges \with gran t

ption."

✦

Y

u

ha v e this

ption

to y

ur
wn

relations. Example 1. Here, Sally can query Sells and can c hange prices, but cannot pass

n

this p

w

er: GRANT SELECT ON Sells, UPDATE(price) ON Sells TO sally; 2. Here, Sally can also pass these privileges to whom she c ho

ses:

GRANT SELECT ON Sells, UPDATE(price) ON Sells TO sally WITH GRANT OPTION; 18

SLIDE 19 Rev

king

Privileges

Y
ur

privileges can b e rev

k

ed.

Syn

tax is lik e gran ting, but REVOKE ... FROM instead

f

GRANT ... TO.

Determining

whether

r

not y

u

ha v e a privilege is tric ky , in v

lving

\gran t diagrams" as in text. Ho w ev er, the basic principles are: a) If y

u

ha v e b een giv en a privilege b y sev eral dieren t p eople, then all

f

them ha v e to rev

k

e in

rder

for y

u

to lose the privilege . b) Rev

cation

is transitiv e. If A gran ted P to B , who gran ted P to C , and then A rev

k

es P from B , it is as if B also rev

k

ed P from C . 19