The Relational AlgebraOverviewSet OperationsSelectProjectJoin"Equi" and "Natural" joinDivision: R1 R2Division Details of R = R1R2Division ExamplesAggregate FunctionsGrouping and AggregatesGrouping Notation and ExampleLooking Ahead01/16/19 F-1The Relational AlgebraTextbook ch. 6.5-6.7© 1997 UW CSE01/16/19 F-2Overview•Operations on whole relations–Inputs are sets, output is a set•Can nest arbitrarily complex expressions•SELECT , PROJECT •Set union , intersection , difference , on compatible relations•Cartesian product and various flavors of JOIN–Division sort of inverse of product•Aggregate functions (unofficial)01/16/19 F-3Set Operations•Set union , intersection , difference –on compatible relations: corresponding columns have same types•Note there is no "set complement"!–What would the complement of a table be??–Databases tend to operate on a "closed world" assumption: "If it's not in the DB, it doesn't exist."–In Prolog: "If you can't prove it, it isn't true."01/16/19 F-4Select•Unary operation•Select a subset of tuples from a relation, based upon a condition–use AND, OR, NOT for compound conditions•Result: a table with same attributes as original: a proper subset–may be given a name (temporary)•Notation:condition(relationname)01/16/19 F-5Project•Unary operation•Select a subset of columns•Result: a table with same number of rows as original–not actually a subset of the original (unlike )–may be given a name (temporary)•Notation:col-list(relat io nnam e)01/16/19 F-6Join•A binary operation on relations•Result is a whole relation•General description: a followed by a .–The condition equates or otherwise relates common attributes between the two relations–Often a superfluous common attribute is removed•Notation (these slides): R1JN join-condition R201/16/19 F-7"Equi" and "Natural" join•Common attributes are compared for equality–no need to specify a join condition–could join on more than one attribute–need to list attributes if names are not the same•"Natural join": Superfluous columns are removed automatically•Notation (our text): R1 * attr-lists R201/16/19 F-8Division: R1 R2•Sort of the reverse of Cartesian product•Like integer division in that any "remainder" is discarded•Main idea: find all the tuples in R1 which are joined to all the values in R2–the R2 attributes are discarded•Same thing can be accomplished with combination of , , 01/16/19 F-9Division Details of R = R1R2•R1 (dividend): attribute set X Y, |R1| rows•R2 (divisor): attribute set Y, |R2| rows•R (quotient):–attribute set X, i.e., the attributes of R1 not in R2–at most |R1|/|R2| rows–A row is in the answer (R) if that row (X attributes) occurs in R1 with each combination of the rows (Y attributes) of R2.01/16/19 F-10Division Examples•Who would have lots to talk about with Bessie? "Find (all) customers who have rented (all) the same movies as Bessie has."•What airlines compete with Horizon Air? "Find the airlines which serve a city also served by Horizon" (not a division query).•Which airline is best positioned to put Horizon Air out of business? "Find the airlines which fly to (all) the cities served by Horizon" (a division query).01/16/19 F-11Aggregate Functions•Technically, not part of R.A.•Actual query languages will implement many of these•(Usually) unary operators, take a whole relation and compute a value•COUNT, AVERAGE, MAX, MIN•Result is returned as a relation with one row and one column–i.e., not as a scalar number01/16/19 F-12Grouping and Aggregates•Rows may be grouped based on attribute values–Think of it as a sort on those attributes•Aggregate functions can be applied to the grouped relation–Computes a value for each group•Result returned as a relation with one row for each group, one column for each aggregate function01/16/19 F-13Grouping Notation and Example•<grouping attributes> <agg. function list> (relation)•"List number of employees and average salary for each department"DNAME COUNT (SSN) AVERAGE(SALARY)SW Support 54 $30,301HW Support 18 $72,600Grounds 5 $89,60001/16/19 F-14Looking Ahead•Order of operations affects efficiency•Example: (R1) *(R2) probably much faster than (R1 * R2)•Large joins can be particularly taxing•Ideally, we do not let this affect how we write queries!•Smart DBMSs do "query optimization" –automatically reorder operations for
View Full Document
Unlocking...