Chapter 8 Relational Algebra and Relational Calculus Chapter 8 Outline Unary Relational Operations SELECT PROJECT RENAME Relational Algebra Operations from Set Theory Binary Relational Operations JOIN DIVISION Additional Relational Operations 3 Chapter 8 Outline Examples of Queries in Relational Algebra Examples of Queries in Relational Calculus The Tuple Relational Calculus The Domain Relational Calculus 4 Unary Relational Operations SELECT PROJECT and RENAME The SELECT Operation The SELECT Operation Subset of the tuples from a relation that satisfies a selection condition selection condition R Boolean expression contains clauses of the form attribute name comparison op constant value or attribute name comparison op attribute name 8 10 The SELECT Operation Select the EMPLOYEE tuples whose department is 4 Select the EMPLOYEE tuples whose salary is greater than 30 000 1 1 The SELECT Operation 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 1 3 The SELECT Operation Selectivity Fraction of tuples selected by a selection of the condition The SELECT operation commutative Cascade SELECT operations into a single operation with AND condition 1 4 SELECT and SQL WHERE In SQL the SELECT condition is typically specified in the WHERE clause of a SQL query For example the following operation would correspond to the following SQL query 1 5 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 so the result of the PROJECT operation is a set of distinct tuples and hence a valid relation This is known as duplicate elimination 1 6 The PROJECT Operation Notice that the tuple F 25000 appears only once in the following PROJECT even though this combination of values appears twice in the EMPLOYEE relation 1 7 The PROJECT Operation The number of tuples in a relation resulting from a PROJECT operation is always less than or equal to the number of tuples in R If the projection list is a superkey of R that is it includes some key of R the resulting relation has the same number of tuples as R Moreover as long as list2 contains the attributes in list1 otherwise the left hand side is an incorrect expression It is also noteworthy that commutativity does not hold on PROJECT 1 8 PROJECT and SQL SELECT In SQL the PROJECT condition is typically specified in the SELECT clause of a query For example the following operation would correspond to the following SQL query 1 9 Sequences of Operations and the RENAME Operation In line expression Sequence of operations 2 0 Sequences of Operations and the RENAME Operation Sequences of Operations and the RENAME Operation Rename attributes in intermediate results RENAME operation where the symbol rho is used to denote the RENAME operator S is the new relation name and B1 B2 Bn are the new attribute names The first expression renames both the relation and its attributes The second renames the relation only and The third renames the attributes only 2 2 Relational Algebra Operations from Set Theory 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 2 4 S R Includes all tuples that are either in R or in S or in both R and S Duplicate tuples eliminated Example List SSNs of every employee who works for department 5 or who is a supervisor for some employee who works for department 5 UNION 2 5 SET DIFFERENCE or MINUS or EXCEPT R S Includes all tuples that are in R but not in S 2 6 INTERSECTION R S Includes all only tuples that are in both R and S Can be expressed in terms of union and difference 2 7 INTERSECTION and UNION Notice that both UNION and INTERSECTION are commutative operations that is Both UNION and INTERSECTION can be treated as n ary operations applicable to any number of relations because both are also associative operations that is The MINUS operation is not commutative that is in general 2 8 UNION INTERSECTION and MINUS 2 9 CARTESIAN PRODUCT Operation CARTESIAN PRODUCT CROSS PRODUCT or CROSS JOIN Denoted by Binary set operation Relations do not have to be union compatible Generally not useful by itself Useful when followed by a selection that matches values of attributes 3 0 Cartesian Product with and Suppose that we want to retrieve a list of names of each female employee s dependents 3 1 Binary Relational Operations JOIN and DIVISION Binary Operation JOIN The JOIN Operation Denoted by Combine related tuples from two relations into single relation with General JOIN condition of the form condition AND condition AND longer tuples AND condition Example 3 3 33 The JOIN operation can be specified as a CARTESIAN PRODUCT operation followed by a SELECT operation However JOIN is very important because it is used very frequently when specifying database queries Consider Can be replaced with a single JOIN operation JOIN 3 4 JOIN General Form The general form of a JOIN operation on two relations R A1 A2 An and S B1 B2 Bm is 3 5 THETA JOIN 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 3 6 Variations of JOIN The EQUIJOIN and NATURAL JOIN EQUIJOIN THETA JOIN where only comparison operator used Always have one or more pairs of attributes that have identical values in every tuple Examples ssn mgrssn dno dnumber pno pnumber 3 8 NATURAL JOIN Denoted by 3 9 EQUIJOIN and NATURAL JOIN Suppose we want to combine each PROJECT tuple with the DEPARTMENT tuple that controls the project In the following example first we rename the Dnumber attribute of DEPARTMENT to Dnum so that it has the same name as the Dnum attribute in Project relation and then we apply NATURAL JOIN The same query can be done in two steps by creating an intermediate table DEPT as follows 4 0 EQUIJOIN and NATURAL JOIN The attribute Dnum is called the join attribute for the NATURAL JOIN operation If the attributes on which the natural join is specified already have the same names in both relations renaming is unnecessary For example to apply a natural join on the Dnumber attributes of DEPARTMENT and DEPT LOCATIONS it is sufficient to write 4 1 NATURAL JOIN SQL SELECT FROM department JOIN
View Full Document
Unlocking...