Outline So far, we studied schema design. CS 235: How to - PDF document

Outline • So far, we studied schema design. CS 235: • How to manipulate data? • Relational algebra Introduction to Databases – Elegant theoretical framework – Not so elegant in practice – SQL Svetlozar Nestorov • Relational operators Lecture Notes #7 Core Relational Algebra Selection • A small set of operators that allows us to • R 1 = σ C ( R 2 ) – where C is a condition involving the attributes of relation R 2 . manipulate relations in limited but useful ways. • Example: 1. Union, intersection, and difference : the usual set bar beer price Relation Sells : operators. Spoon Amstel 4 • Relation schemas must be the same. Spoon Guinness 7 2. Selection : Pick certain rows from a relation. Whiskey Guinness 7 3. Projection : Pick certain columns. Whiskey Bud 5 4. Products and joins : Combine relations in useful SpoonMenu = σ bar=Spoon ( Sells ) ways. bar beer price 5. Renaming of relations and their attributes. Spoon Amstel 4 Spoon Guinness 7 Projection Product • R 1 = π L ( R 2 ) • R = R 1 × R 2 – where L is a list of attributes from the schema of R 2 . – pairs each tuple t 1 of R 1 with each tuple t 2 of • Example R 2 and puts in R a tuple t 1 t 2 . π beer,price ( Sells ) • Theta-Join: R = R 1 C R 2 beer price – is equivalent to R = σ C ( R 1 × R 2 ). Amstel 4 Guinness 7 Bud 5 • Notice elimination of duplicate tuples. 1

Example Natural Join Sells = Bars = • R = R 1 R 2 bar beer price – Equivalent to: Spoon Amstel 4 name addr Spoon Guinness 7 1. theta-join of R 1 and R 2 with the condition that all attributes Spoon Wells of the same name be equated. Whiskey Guinness 7 Whiskey Rush 2. one column for each pair of equated attributes is projected Whiskey Bud 5 out. BarInfo = Sells Sells.bar=Bars.name Bars • What is the formula? bar beer price name addr • Example: Spoon Amstel 4 Spoon Wells – Suppose the attribute name in relation Bars was Spoon Guinness 7 Spoon Wells changed to bar , to match the bar name in Sells . Whiskey Guinness 7 Whiskey Rush – BarInfo = Sells Bars Whiskey Bud 5 Whiskey Rush Natural Join Example Renaming • BarInfo = Sells Bars • ρ S(A1,…,An) ( R ) produces a relation identical to R but named S and with attributes, in bar beer price addr order, named A 1 ,…,A n . Spoon Amstel 4 Wells • Example: bar addr Spoon Guinness 7 Wells ρ R ( bar,addr ) ( Bars ) = Spoon Wells Whiskey Guinness 7 Rush Whiskey Rush Whiskey Bud 5 Rush • The name of the second relation is R . Combining Operations Operator Precedence • The normal way to group operators is: • Any algebra is defined as: 1. Unary operators σ , π , and ρ have highest precedence. – basis arguments 2. Next highest are the multiplicative operators, , C , and × . – ways of constructing expressions 3. Lowest are the additive operators, ∪ , ∩ , and —. • But there is no universal agreement, so we always put • For relational algebra: parentheses around the argument of a unary operator, – Arguments = variables standing for relations + and it is a good idea to group all binary operators with finite, constant relations. parentheses enclosing their arguments. – Expressions constructed by applying one of • Example: the operators + parentheses. Group R ∪ σ S T as R ∪ ( σ ( S ) T ). • Query = expression of relational algebra. 2

Expressions and Schemas Example 1 • If ∪ , ∩ , — applied, schemas are the same, so the result • Find the bars that are either on Wells has the same schema. Street or sell Bud for less than $6. • Projection: use the attributes listed in the projection. • Selection: no change in schema. Sells(bar, beer, price) • Product R × S : use attributes of R and S . Bars(name, addr) – But if they share an attribute A , prefix it with the relation name, as R.A , S.A . • Theta-join: same as product. • Natural join: use attributes from each relation; common attributes are merged anyway. • Renaming: whatever it says. Example 2 Linear Notation for Expressions • Find the bars that sell two different beers • Invent new names for intermediate relations, and assign them values that are algebraic at the same price. expressions. Sells(bar, beer, price) • Renaming of attributes implicit in schema of new relation. Example • Find the bars that are either on Wells Street or sell Bud for less than $6. Sells(bar, beer, price) Bars(name, addr) R1(name) := π name ( σ addr = Wells (Bars)) R2(name) := π bar ( σ beer=Bud AND price<6 (Sells)) R3(name) := R1 ∪ R2 3