But the relation sc hemas m ust b e the same. 2. : Pic - PDF document

\Core" Relational Algebra A small set of set-based op erators that allo w us to manipulate relations in limited but useful w a ys. The op erators are: 1. Union, in tersection, and di�erence: the usual set op erators. ✦ But the relation sc hemas m ust b e the same. 2. : Pic king certain ro ws from a Sele ction relation. 3. : Pic king certain columns. Pr oje ction 4. : Comp osing relations in Pr o ducts and joins useful w a ys. 5. of relations and their attributes. R enaming 1

Selection = ( R ) R � 1 C 2 where is a condition in v olving the attributes of C relation . R 2 Example Relation Sells : bar b eer price Jo e's Bud 2.50 Jo e's Miller 2.75 Sue's Bud 2.50 Sue's Co ors 3.00 � JoeMenu = (Sells) = J bar oe 0 s bar b eer price Jo e's Bud 2.50 Jo e's Miller 2.75 2

Pro jection = ( R ) R � 1 L 2 where is a list of attributes from the sc hema of L . R 2 Example � (Sells) beer ;pr ice b eer price Bud 2.50 Miller 2.75 Co ors 3.00 � Notice elimination of duplicate tuples. 3

Pro duct = � R R R 1 2 pairs eac h tuple of with eac h tuple of t R t R 1 1 2 2 and puts in a tuple . R t t 1 2 Theta-Join . / = R R R 1 2 C is equiv alen t to = ( R � ). R � R C 1 2 4

Example = Sells bar b eer price Jo e's Bud 2.50 Jo e's Miller 2.75 Sue's Bud 2.50 Sue's Co ors 3.00 = Bars name addr Jo e's Maple St. Sue's Riv er Rd. . / BarInfo = Sells Bars = B S ells:bar ar s:name bar b eer price name addr Jo e's Bud 2.50 Jo e's Maple St. Jo e's Miller 2.75 Jo e's Maple St. Sue's Bud 2.50 Sue's Riv er Rd. Sue's Co ors 3.00 Sue's Riv er Rd. 5

Natural Join = R R . / R 1 2 calls for the theta-join of and with the R R 1 2 condition that all attributes of the same name b e equated. Then, one column for eac h pair of equated attributes is pro jected out. Example Supp ose the attribute in relation w as name Bars c hanged to bar , to matc h the bar name in Sells . BarInfo = Sells . / Bars bar b eer price addr Jo e's Bud 2.50 Maple St. Jo e's Miller 2.75 Maple St. Sue's Bud 2.50 Riv er Rd. Sue's Co ors 3.00 Riv er Rd. 6

Renaming ( R ) pro duces a relation iden tical to � R ( A ) S ;::: ;A 1 n but named and with attributes, in order, named S . A ; : : : ; A 1 n Example = Bars name addr Jo e's Maple St. Sue's Riv er Rd. ( Bars ) = � R ( bar ) ;addr bar addr Jo e's Maple St. Sue's Riv er Rd. � The name of the ab o v e relation is . R 7

Com bining Op erations Algebra = 1. Basis argumen ts + 2. W a ys of constructing expressions. F or relational algebra: 1. Argumen ts = v ariables standing for relations + �nite, constan t relations. 2. Expressions constructed b y applying one of the op erators + paren theses. � Query = expression of relational algebra. 8

Op erator Precedence The normal w a y to group op erators is: 1. Unary op erators , , and ha v e highest � � � precedence. 2. Next highest are the \m ultiplicati v e" . / op erators, / , , and � . . C 3. Lo w est are the \additiv e" op erators, [ , \ , and � . � But there is no univ ersal agreemen t, so w e alw a ys put paren theses the argumen t ar ound of a unary op erator, and it is a go o d idea to group all binary op erators with paren theses their argumen ts. enclosing Example Group [ as [ ( � ( S ) ). R � S . / T R . / T 9

Eac h Expression Needs a Sc hema � If [ , \ , � applied, sc hemas are the same, so use this sc hema. � Pro jection: use the attributes listed in the pro jection. � Selection: no c hange in sc hema. � Pro duct � : use attributes of and . R S R S ✦ But if they share an attribute A , pre�x it with the relation name, as :A , S:A . R � Theta-join: same as pro duct. � Natural join: use attributes from eac h relation; common attributes are merged an yw a y . � Renaming: whatev er it sa ys. 10

Example Find the bars that are either on Maple Street or sell Bud for less than $3. Sells(bar, beer, price) Bars(name, addr) [ � R ( name ) � � name bar � � = M addr apleS t: ice< $3 = B pr AN D beer ud Bars Sells 11

Example Find the bars that sell t w o di�eren t b eers at the same price. Sells(bar, beer, price) � bar � beer 6 = beer 1 . / � S ( bar ;beer 1 ;pr ice ) Sells Sells 12

Linear Notation for Expressions � In v en t new names for in termediate relations, and assign them v alues that are algebraic expressions. � Renaming of attributes implicit in sc hema of new relation. Example Find the bars that are either on Maple Street or sell Bud for less than $3. Sells(bar, beer, price) Bars(name, addr) R1(name) := � ( � (Bars)) = M name addr aple S t: R2(name) := � ( � (Sells)) bar = B ice< $3 beer ud AN D pr [ Answer(name) := R1 R2 13

Bag Seman tics A relation (in SQL, at least) is really a or b ag multiset . � It ma y con tain the same tuple more than once, although there is no sp eci�ed order (unlik e a list). � Example: f 1 ; 2 ; 1 ; 3 g is a bag and not a set. � Select, pro ject, and join w ork for bags as w ell as sets. ✦ Just w ork on a tuple-b y-tuple basis, and don't eliminate duplicates. 14

Bag Union Sum the times an elemen t app ears in the t w o bags. � Example: f 1 ; 2 ; 1 g [ f 1 ; 2 ; 3 g = f 1 ; 1 ; 1 ; 2 ; 2 ; 3 g . Bag In tersection T ak e the minim um of the n um b er of o ccurrences in eac h bag. � Example: f 1 ; 2 ; 1 g \ f 1 ; 2 ; 3 ; 3 g = f 1 ; 2 g . Bag Di�erence Prop er-subtract the n um b er of o ccurrences in the t w o bags. � Example: f 1 ; 2 ; 1 g � f 1 ; 2 ; 3 ; 3 g = f 1 g . 15

La ws for Bags Di�er F rom La ws for Sets � Some familiar la ws con tin ue to hold for bags. ✦ Examples: union and in tersection are still comm utativ e and asso ciativ e. � But other la ws that hold for sets do hold not for bags. Example \ ( S [ ) � ( R \ ) [ ( R \ ) holds for sets. R T S T � Let , , and eac h b e the bag f 1 g . R S T � Left side: [ = f 1 ; 1 g ; \ ( S [ ) = f 1 g . S T R T � Righ t side: \ = \ = f 1 g ; R S R T ( R \ ) [ ( R \ ) = f 1 g [ f 1 g = f 1 ; 1 g 6 = f 1 g . S T 16

Extended (\Nonclassical ") Relational Algebra Adds features needed for SQL, bags. 1. Duplicate-eli minati on op erator . � 2. Extended pro jection. 3. Sorting op erator . � 4. Grouping-and-aggregation op erator . � � 5. Outerjoin op erator . . / 17

Duplicate Elimination ( R ) = relation with one cop y of eac h tuple that � app ears one or more times in . R Example = R A B 1 2 3 4 1 2 ( R ) = � A B 1 2 3 4 18

Sorting ( R ) = list of tuples of , ordered according to � R L attributes on list L . � Note that result t yp e is outside the normal t yp es (set or bag) for relational algebra. ✦ Consequence: cannot b e follo w ed b y � other relational op erators. example = R A B 1 3 3 4 5 2 ( R ) = [(5 ; 2) ; (1 ; 3) ; (3 ; 4)]. � B 19

Extended Pro jection Allo w the columns in the pro jection to b e functions of one or more columns in the argumen t relation. Example = R A B 1 2 3 4 ( R ) = � A + B ;A;A + A 1 A 2 A B 3 1 1 7 3 3 20

Aggregation Op erators � These are not relational op erators; rather they summarize a column in some w a y . � Fiv e standard op erators: Sum, Av erage, Coun t, Min, and Max. Grouping Op erator ( R ), where is a list of elemen ts that are either � L L a) Individual ( gr ouping ) attributes or b) Of the form ( A ), where is an aggregation � � op erator and the attribute to whic h it is A applied, is computed b y: 1. Group according to all the grouping R attributes on list L . 2. Within eac h group, compute ( A ), for eac h � elemen t ( A ) on list L . � 3. Result is the relation whose columns consist of one tuple for eac h group. The comp onen ts of that tuple are the v alues asso ciated with eac h elemen t of for that group. L 21

Example Let = R bar b eer price Jo e's Bud 2.00 Jo e's Miller 2.75 Sue's Bud 2.50 Sue's Co ors 3.00 Mel's Miller 3.25 Compute ( R ). � G ( pr ice ) beer ;AV 1. Group b y the grouping attribute(s), in beer this case: bar b eer price Jo e's Bud 2.00 Sue's Bud 2.50 Jo e's Miller 2.75 Mel's Miller 3.25 Sue's Co ors 3.00 22