Design Problem OK; let's design a relational DB sc hema for - PDF document

Design Problem OK; let's design a relational DB sc hema for b eers- bars-drink ers. Drink ers ha v e unique names and addresses. � They lik e one or more b eers and frequen t one or more bars. They ha v e phones, usually one but sometimes more or none. Bars ha v e unique names and addresses. They � serv e one or more b eers and are frequen ted b y one or more drink ers. They c harge a price for eac h b eer they serv e, whic h ma y v ary from b eer to b eer. Beers ha v e unique names and man ufacturers. � Man ufacturers ha v e unique names and addresses. Beers are serv ed b y one or more bars and are lik ed b y one or more drink ers. 1

Relational Algebra A small set of op erators that allo w us to manipulate relations in limited, but easily implemen table and 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 2

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 3

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. � 4

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 5

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:B ar ar s:N ame 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. 6

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. 7

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 � 8

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. � 9

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 [ [ 10

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. � 11

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 12

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 13

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(bar) := � ( � (Bars)) = M name addr aple S t: R2(bar) := � ( � (Sells)) bar = B ice< $3 beer ud AN D pr R3(bar) := R1 R2 [ 14

Example Find the bars that sell t w o di�eren t b eers at the same price. Sells(bar, beer, price) S1(bar,beer1,pric e) := Sells S2(bar,beer,price ,beer 1) := S1 . / Sells S3(bar) = � ( � (S2)) bar beer 6 = beer 1 15