[PDF] - Outerjoin = with tuples padded with R S R . / S PDF Document

SLIDE 1 Outerjoin R

.

/ S = R . / S with dangling tuples padded with n ulls and included in the result.

A

tuple is dangling if it do esn't join with an y

ther

tuple. R = A B 1 2 3 4 S = B C 2 5 2 6 7 8 R

.

/ S = A B C 1 2 5 1 2 6 3 4 NULL NULL 7 8 1

SLIDE 2 Outerjoin in SQL2 A n um b er

f

forms are pro vided.

Can

b e used either stand-alone (in place

f

a select-from-where)

r

to dene a relation in the FROM-clause. R NATURAL JOIN S R JOIN S ON condition e.g., condition: R :B = S:B R CROSS JOIN S R OUTER JOIN S

The

latter can b e mo died b y: 1. Optional NATURAL in fron t

f

JOIN. 2. Optional ON condition at end. 3. Optional LEFT, RIGHT,

r

FULL b efore OUTER.

✦

LEFT = pad dangling tuples

f

R

nly;

RIGHT = pad dangling tuples

f

S

nly

. 2

SLIDE 3 Oracle Outerjoin Ain't no suc h thing.

But

paren thesized select-from-where allo w ed in a FROM clause.

✦

Really a w a y to dene a view and use it in a single query . Example Find the a v erage

v

er all bars

f

the maxim um price the bar c harges for a b eer. Sells(bar , beer , price) SELECT AVG(maxPrice) FROM (SELECT bar, MAX(price) AS maxPrice FROM Sells GROUP BY Bar); Problem Can w e express the

uterjoin

in Oracle SQL as some more complicated expression? 3

SLIDE 4 Constrain ts Commercial relational systems allo w m uc h more \ne-tuning"

f

constrain ts than do the mo deling languages w e learned earlier.

In

essence: SQL programming is used to describ e constrain ts. Outline 1. Primary k ey declarations (co v ered). 2. F

reign-k

eys = referen tial in tegrit y constrain ts.

✦

E.g., if Sells men tions a b eer, then w e should b e able to nd that b eer in Beers. 3. A ttribute- and tuple-based c hec ks = constrain ts within relations. 4. SQL2 Assertions = global constrain ts.

✦

Not found in Oracle 7.3.2. 5. Oracle T riggers.

✦

A substitute for assertions. 6. SQL3 triggers and assertions. 4

SLIDE 5 F

reign

Keys In relation R a clause that \attribute A references S (B )" sa ys that whatev er v alues app ear in the A column

f

R m ust also app ear in the B column

f

relation S .

B

m ust b e declared the primary k ey for S . Example CREATE TABLE Beers ( name CHAR(20) PRIMARY KEY, manf CHAR(20) ); CREATE TABLE Sells ( bar CHAR(20), beer CHAR(20) REFERENCES Beers(name), price REAL ); 5

SLIDE 6

Alternativ

e: add another elemen t declaring the foreign k ey , as: CREATE TABLE Sells ( bar CHAR(20), beer CHAR(20), price REAL, FOREIGN KEY beer REFERENCES Beers(name) );

Extra

elemen t essen tial if the foreign k ey is more than

ne

attribute. 6

SLIDE 7 What Happ ens When a F

reign

Key Constrain t is Violated?

Tw
w

a ys: 1. Insert a Sells tuple referring to a nonexisten t b eer.

✦

Alw a ys rejected. 2. Delete

r

up date a Beers tuple that has a beer v alue some Sells tuples refer to. a) Default: reject. b) Casc ade : Ripple c hanges to referring Sells tuple. Example

Delete

\Bud." Cascade deletes all Sells tuples that men tion Bud.

Up

date \Bud" ! \Budw eiser." Change all Sells tuples with \Bud" in beer column to b e \Budw eiser." 7

SLIDE 8 c) Set Nul l : Change referring tuples to ha v e NULL in referring comp

nen

ts. Example

Delete

\Bud." Set-n ull mak es all Sells tuples with \Bud" in the beer comp

nen

t ha v e NULL there.

Up

date \Bud" ! \Budw eiser." Same c hange. 8

SLIDE 9 Selecting a P

licy

Add ON [DELETE, UPDATE] [CASCADE, SET NULL] to declaration

f

foreign k ey . Example CREATE TABLE Sells ( bar CHAR(20), beer CHAR(20), price REAL, FOREIGN KEY beer REFERENCES Beers(name) ON DELETE SET NULL ON UPDATE CASCADE );

\Correct"

p

licy

is a design decision.

✦

E.g., what do es it mean if a b eer go es a w a y? What if a b eer c hanges its name? 9

SLIDE 10 A ttribute-Based Chec ks F

llo

w an attribute b y a condition that m ust hold for that attribute in eac h tuple

f

its relation.

F
rm:

CHECK (condition).

✦

Condition ma y in v

lv

e the c hec k ed attribute.

✦

Other attributes and relations ma y b e in v

lv

ed, but

nly

in sub queries.

✦

Oracle 7.3.2: No sub queries al lowe d in c

ndition.
Condition

is c hec k ed

nly

when the asso ciated attribute c hanges (i.e., an insert

r

up date

ccurs).

10

SLIDE 11 Example CREATE TABLE Sells ( bar CHAR(20), beer CHAR(20) CHECK( beer IN (SELECT name FROM Beers) ), price REAL CHECK( price <= 5.00 ) );

Chec

k

n

beer is lik e a foreign-k ey constrain t, except:

✦

The c hec k

ccurs
nly

when w e add a tuple

r

c hange the b eer in an existing tuple, not when w e delete a tuple from Beers. 11

SLIDE 12 T uple-Based Chec ks Separate elemen t

f

table declaration.

F
rm:

lik e attribute-based c hec k.

But

condition can refer to an y attribute

f

the relation.

✦

Or to

ther

relations/attri butes in sub queries.

✦

Again: Oracle 7.3.2 forbids the use

f

sub queries. 12

SLIDE 13 Example Only Jo e's Bar can sell b eer for more than $5. CREATE TABLE Sells ( bar CHAR(20), beer CHAR(20), price REAL, CHECK(bar = 'Joe''s Bar' OR price <= 5.00) ); 13

SLIDE 14 T riggers Often called ev en t-condition-acti

n

rules.

Event

= a class

f

c hanges in the DB, e.g., \insert in to Beers."

Condition

= a test as in a where-clause for whether

r

not the trigger applies.

A

ction =

ne
r

more SQL statemen ts.

Oracle

v ersion and SQL3 v ersion; not in SQL2.

Dier

from c hec ks

r

SQL2 assertions in that: 1. Ev en t is programmable, rather than implied b y the kind

f

c hec k. 2. Condition not a v ailable in c hec ks. 14

SLIDE 15 Example Whenev er w e insert a new tuple in to Sells, mak e sure the b eer men tioned is also men tioned in Beers, and insert it (with a n ull man ufacturer) if not. Sells(bar , beer , price) CREATE OR REPLACE TRIGGER BeerTrig AFTER INSERT ON Sells FOR EACH ROW WHEN(new.beer NOT IN (SELECT name FROM Beers)) BEGIN INSERT INTO Beers(name) VALUES(:new.beer); END; . run 15

SLIDE 16 Options 1. Can

mit

OR REPLACE. Eect is that it is an error if a trigger

f

this name exists. 2. AFTER can b e BEFORE. 3. INSERT can b e DELETE

r

UPDATE OF <attribute> ON. 4. FOR EACH ROW can b e

mitted,

with an imp

rtan

t eect: the action is done

nce

for the relation(s) consisting

f

all c hanges. 16

SLIDE 17 Notes

More

information in

n-line

do cumen t

r-

plsql.html

There

are t w

sp

ecial v ariables new and

ld,

represen ting the new and

ld

tuple in the c hange.

✦

ld

mak es no sense in an insert, and new mak es no sense in a delete.

Notice:

in WHEN w e use new and

ld

without a colon, but in actions, a preceding colon is needed.

The

action is a PL/SQL statemen t.

✦

Simplest form: surround

ne
r

more SQL statemen ts with BEGIN and END.

✦

Ho w ev er, select-from-where has a limited form.

Dot

and run cause the denition

f

the trigger to b e stored in the database.

✦

Oracle triggers are elemen ts

f

the database, lik e tables

r

views. 17

SLIDE 18 Example Main tain a list

f

all the bars that raise their price for some b eer b y more than $1. Sells(bar , beer , price) CREATE TRIGGER PriceTrig AFTER UPDATE OF price ON Sells FOR EACH ROW WHEN(new.price >

ld.price

+ 1.00) BEGIN INSERT INTO RipoffBars VALUES(:new.bar); END; . run 18