Outerjoin = with tuples padded with R S R . / S - PDF document

Outerjoin � = with tuples padded with R S R . / S dangling . / n ulls and included in the result. A tuple is dangling if it do esn't join with an y � other 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 7 8 NULL 1

Outerjoin in SQL2 A n um b er of forms are pro vided. Can b e used either stand-alone (in place of a � select-from-where) or to de�ne a relation in the FROM -clause. R NATURAL JOIN S condition R JOIN S ON e.g., condition: = R :B S:B R S CROSS JOIN R OUTER JOIN S The latter can b e mo di�ed b y: � 1. Optional in fron t of JOIN . NATURAL 2. Optional condition at end. ON 3. Optional LEFT , RIGHT , or b efore FULL OUTER . ✦ = pad dangling tuples of only; R LEFT = pad dangling tuples of only . S RIGHT 2

Oracle Outerjoin Ain't no suc h thing. But paren thesized select-from-where allo w ed in � a clause. FROM ✦ Really a w a y to de�ne a view and use it in a single query . Example Find the a v erage o v er all bars of 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 outerjoin in Oracle SQL as some more complicated expression? 3

Constrain ts Commercial relational systems allo w m uc h more \�ne-tuning" of 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 oreign-k eys = referen tial in tegrit y constrain ts. ✦ E.g., if men tions a b eer, then w e Sells 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

F oreign Keys In relation a clause that \attribute references R A ( B )" sa ys that whatev er v alues app ear in the S A column of m ust also app ear in the column of R B relation . S m ust b e declared the primary k ey for . B 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

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 one attribute. 6

What Happ ens When a F oreign Key Constrain t is Violated? Tw o w a ys: � 1. Insert a tuple referring to a nonexisten t Sells b eer. ✦ Alw a ys rejected. 2. Delete or up date a tuple that has a Beers v alue some tuples refer to. beer Sells a) Default: reject. b) Casc ade : Ripple c hanges to referring tuple. Sells Example Delete \Bud." Cascade deletes all Sells � tuples that men tion Bud. Up date \Bud" \Budw eiser." Change all � ! tuples with \Bud" in column to Sells beer b e \Budw eiser." 7

c) : Change referring tuples to ha v e Set Nul l in referring comp onen ts. NULL Example Delete \Bud." Set-n ull mak es all tuples Sells � with \Bud" in the comp onen t ha v e beer NULL there. Up date \Bud" \Budw eiser." Same c hange. � ! 8

Selecting a P olicy Add ON [DELETE, UPDATE] [CASCADE, SET NULL] to declaration of 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 olicy 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

A ttribute-Based Chec ks F ollo w an attribute b y a condition that m ust hold for that attribute in eac h tuple of its relation. F orm: ( condition ) . CHECK � ✦ Condition ma y in v olv e the c hec k ed attribute. ✦ Other attributes and relations ma y b e in v olv ed, but in sub queries. only ✦ Oracle 7.3.2: No sub queries al lowe d in ondition . c Condition is c hec k ed only when the asso ciated � attribute c hanges (i.e., an insert or up date o ccurs). 10

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 on is lik e a foreign-k ey constrain t, beer � except: ✦ The c hec k o ccurs only when w e add a tuple or c hange the b eer in an existing tuple, not when w e delete a tuple from Beers . 11

T uple-Based Chec ks Separate elemen t of table declaration. F orm: lik e attribute-based c hec k. � But condition can refer to an y attribute of the � relation. ✦ Or to other relations/attri butes in sub queries. ✦ Again: Oracle 7.3.2 forbids the use of sub queries. 12

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

T riggers Often called ev en t-condition-acti on rules. = a class of c hanges in the DB, e.g., Event � \insert in to Beers ." = a test as in a where-clause for Condition � whether or not the trigger applies. = one or more SQL statemen ts. A ction � Oracle v ersion and SQL3 v ersion; not in � SQL2. Di�er from c hec ks or SQL2 assertions in that: � 1. Ev en t is programmable, rather than implied b y the kind of c hec k. 2. Condition not a v ailable in c hec ks. 14

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

Options 1. Can omit REPLACE . E�ect is that it is an OR error if a trigger of this name exists. 2. can b e BEFORE . AFTER 3. can b e or INSERT DELETE UPDATE OF < attribute > ON . 4. can b e omitted, with an FOR EACH ROW imp ortan t e�ect: the action is done once for the relation(s) consisting of all c hanges. 16

Notes More information in on-line do cumen t or- � plsql.html There are t w o sp ecial v ariables and old , new � represen ting the new and old tuple in the c hange. ✦ mak es no sense in an insert, and old new mak es no sense in a delete. Notice: in w e use and without WHEN new old � a colon, but in actions, a preceding colon is needed. The action is a PL/SQL statemen t. � ✦ Simplest form: surround one or more SQL statemen ts with and END . BEGIN ✦ Ho w ev er, select-from-where has a limited form. Dot and cause the de�nition of the trigger run � to b e stored in the database. ✦ Oracle triggers are elemen ts of the database, lik e tables or views. 17

Example Main tain a list of 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 > old.price + 1.00) BEGIN INSERT INTO RipoffBars VALUES(:new.bar); END; . run 18