Chapter 6 The Relational Algebra and Relational Calculus Copyright 2011 Pearson Education Inc Publishing as Pearson Addison Wesley Chapter 6 Outline Unary Relational Operations SELECT and PROJECT Relational Algebra Operations from Set Theory Binary Relational Operations JOIN and DIVISION Additional Relational Operations Copyright 2011 Ramez Elmasri and Shamkant Navathe Chapter 6 Outline cont d Examples of Queries in Relational Algebra The Tuple Relational Calculus The Domain Relational Calculus Copyright 2011 Ramez Elmasri and Shamkant Navathe The Relational Algebra and Relational Calculus Relational algebra Basic set of operations for the relational model Relational algebra expression Sequence of relational algebra operations Relational calculus Higher level declarative language for specifying relational queries Copyright 2011 Ramez Elmasri and Shamkant Navathe Unary Relational Operations SELECT and PROJECT The SELECT Operation Subset of the tuples from a relation that satisfies a selection condition Boolean expression contains clauses of the form attribute name comparison op constant value or attribute name comparison op attribute name Copyright 2011 Ramez Elmasri and Shamkant Navathe Unary Relational Operations SELECT and PROJECT cont d Example selection condition applied independently to each individual tuple t in R If condition evaluates to TRUE tuple selected Boolean conditions AND OR and NOT Unary Applied to a single relation Copyright 2011 Ramez Elmasri and Shamkant Navathe Unary Relational Operations SELECT and PROJECT cont d Selectivity Fraction of tuples selected by a selection condition SELECT operation commutative Cascade SELECT operations into a single operation with AND condition Copyright 2011 Ramez Elmasri and Shamkant Navathe The PROJECT Operation Selects columns from table and discards the other columns Degree Number of attributes in attribute list Duplicate elimination Result of PROJECT operation is a set of distinct tuples Copyright 2011 Ramez Elmasri and Shamkant Navathe Sequences of Operations and the RENAME Operation In line expression Sequence of operations Rename attributes in intermediate results RENAME operation Copyright 2011 Ramez Elmasri and Shamkant Navathe Relational Algebra Operations from Set Theory UNION INTERSECTION and MINUS Merge the elements of two sets in various ways Binary operations Relations must have the same type of tuples UNION R S Includes all tuples that are either in R or in S or in both R and S Duplicate tuples eliminated Copyright 2011 Ramez Elmasri and Shamkant Navathe Relational Algebra Operations from Set Theory cont d INTERSECTION R S Includes all tuples that are in both R and S SET DIFFERENCE or MINUS R S Includes all tuples that are in R but not in S Copyright 2011 Ramez Elmasri and Shamkant Navathe The CARTESIAN PRODUCT CROSS PRODUCT Operation CARTESIAN PRODUCT CROSS PRODUCT or CROSS JOIN Denoted by Binary set operation Relations do not have to be union compatible Useful when followed by a selection that matches values of attributes Copyright 2011 Ramez Elmasri and Shamkant Navathe Binary Relational Operations JOIN and DIVISION The JOIN Operation Denoted by Combine related tuples from two relations into single longer tuples General join condition of the form condition AND condition AND AND condition Example Copyright 2011 Ramez Elmasri and Shamkant Navathe Binary Relational Operations JOIN and DIVISION cont d THETA JOIN Each condition of the form Ai Bj Ai is an attribute of R Bj is an attribute of S Ai and Bj have the same domain theta is one of the comparison operators Copyright 2011 Ramez Elmasri and Shamkant Navathe Variations of JOIN The EQUIJOIN and NATURAL JOIN EQUIJOIN Only comparison operator used Always have one or more pairs of attributes that have identical values in every tuple NATURAL JOIN Denoted by Removes second superfluous attribute in an EQUIJOIN condition Copyright 2011 Ramez Elmasri and Shamkant Navathe Variations of JOIN The EQUIJOIN and NATURAL JOIN cont d Join selectivity Expected size of join result divided by the maximum size nR nS Inner joins Type of match and combine operation Defined formally as a combination of CARTESIAN PRODUCT and SELECTION Copyright 2011 Ramez Elmasri and Shamkant Navathe A Complete Set of Relational Algebra Operations Set of relational algebra operations is a complete set Any relational algebra operation can be expressed as a sequence of operations from this set Copyright 2011 Ramez Elmasri and Shamkant Navathe The DIVISION Operation Denoted by Example retrieve the names of employees who work on all the projects that John Smith works on Apply to relations R Z S X Attributes of S are a subset of the attributes of R Copyright 2011 Ramez Elmasri and Shamkant Navathe Operations of Relational Algebra Copyright 2011 Ramez Elmasri and Shamkant Navathe Operations of Relational Algebra cont d Copyright 2011 Ramez Elmasri and Shamkant Navathe Additional Relational Operations Generalized projection Allows functions of attributes to be included in the projection list Aggregate functions and grouping Common functions applied to collections of numeric values Include SUM AVERAGE MAXIMUM and MINIMUM Copyright 2011 Ramez Elmasri and Shamkant Navathe Additional Relational Operations cont d Group tuples by the value of some of their attributes Apply aggregate function independently to each group Copyright 2011 Ramez Elmasri and Shamkant Navathe Copyright 2011 Ramez Elmasri and Shamkant Navathe Notation for Query Trees Query tree Represents the input relations of query as leaf nodes of the tree Represents the relational algebra operations as internal nodes Copyright 2011 Ramez Elmasri and Shamkant Navathe Copyright 2011 Ramez Elmasri and Shamkant Navathe Examples of Queries in Relational Algebra Copyright 2011 Ramez Elmasri and Shamkant Navathe Examples of Queries in Relational Algebra Copyright 2011 Ramez Elmasri and Shamkant Navathe Examples of Queries in Relational Algebra cont d Copyright 2011 Ramez Elmasri and Shamkant Navathe Examples of Queries in Relational Algebra cont d Copyright 2011 Ramez Elmasri and Shamkant Navathe Examples of Queries in Relational Algebra cont d Copyright 2011 Ramez Elmasri and Shamkant Navathe Examples of Queries in Relational Algebra cont d Copyright 2011 Ramez Elmasri and Shamkant Navathe Examples of Queries in Relational Algebra cont d Copyright 2011 Ramez Elmasri and Shamkant Navathe Examples of Queries in Relational Algebra cont d Copyright 2011 Ramez
View Full Document
Unlocking...