TRC vs DRC 2003 10 12 1 Queries

TRC vs DRC 2003. 10. 12 한욱신 1

Queries in TRC and DRC o TRC o Q = {t| f(t)} where t is a (free) tuple variable and f(t) is a well-formed formula DRC Q = {x 1, x 2, . . , xk| f(x 1, x 2, . . , xk)} where xi is a (free) domain variable and f(x 1, x 2, . . , xk) is a well-formed formula 2

Well-formed formula o Wffs are constructed using the following rules 1. 2. 3. Each atomic formula is a wff If P, Q are wffs, then so are NOT P, (P AND Q), (P OR Q), and (P Q) If x is a (tuple or domain) variable and P is a wff, then so are x P, and x P 3

Atomic formulas in TRC and DRC o TRC o R(t) where R is a relation name and t is a tuple variable ti[A] tj[B] where ti and tj are tuple variables; A and B are attributes names; is a comparison operator (=, <, >, , , ) ti[A] constant DRC R(x 1, x 2, …, xk) where R is a relation name and xi is a domain variable or constant x y where x and y are domain variables x constant where x is a domain variable 4

Example Schema o o o Sailors(sid, sname, rating, age) Boats(bid, bname, color) Reserves(sid, bid, date) 5

Q 1) Find sailors who’ve reserved at least one boat Relational Algebra sid(Sailors Reserves) TRC {p|( r)( s)(Reserves(r) AND Sailors(s) AND /* initiate tuple variables */ r[sid] = s[sid] AND /* join condition */ p[1 st attr] = s[sid])} /* projection */ DRC {s| ( b)( d)( s 2)( sn)( r)( a)(Reserves(s, b, d) AND Sailors(s 2, sn, r, a) AND /* initiate tuple variables */ s = s 2) /* join condition */ } 6

Q 2) Find sid’s of sailors who’ve reserved a red or a green boat Relational Algebra TRC {p|( r)( s)( b)(Reserves(r) AND Sailors(s) AND Boats(b) AND /* initiate tuple variables */ r[sid] = s[sid] AND /* join condition */ r[bid] = b[bid] AND /* join condition */ (b[color] = ‘red’ OR b[color] = ‘green’) AND /* selection condition */ p[1 st attr] = s[sname])} /* projection */ DRC {s| ( b)( d)( s 2)( sn)( r)( a)( b 2)( bn)( c)(Reserves(s, b, d) AND Sailors(s 2, sn, r, a) AND Boats(b 2, bn, c) AND /* initiate tuple variables */ s = s 2 AND b=b 2 AND /* join condition */ 7 (c = ‘red’ OR c = ‘green’))}

Q 3) Find sid’s of sailors who’ve reserved a red and a green boat Relational Algebra TRC {p|( r)( s)( b)(Sailors(s) AND Boats(b 1) AND Reserves(r 1) AND Boats(b 2) AND Reserves(r 2) AND /* initiate tuple variables */ s[sid]=r 1[sid] AND r 1[bid]=b 1[bid] AND s[sid]=r 2[sid] AND r 2[bid]=b 2[bid] AND (b 1[color]=‘red’ AND b 2[color]=‘green’ AND p[1 st attr] = s[sname])} /* projection */ DRC {s| ( b)( d)( s 2)( sn)( r)( a)( b 2)( bn)( c) )( s 2’)( sn’)( r’)( a’)( b 2’)( bn’)( c’) ( Reserves(s, b, d) AND Sailors(s 2, sn, r, a) AND Boats(b 2, bn, c) AND Sailors(s 2’, sn’, r’, a’) AND Boats(b 2’, bn’, c’) AND /* initiate tuple variables */ s = s 2 AND b=b 2 AND s = s 2’ AND b=b 2’ AND/* join condition */ 8 (c = ‘red’ AND c’ = ‘green’))}