This preview shows page 1-2-3-4-5-6-7-8-9-65-66-67-68-69-70-71-72-73-74-130-131-132-133-134-135-136-137-138 out of 138 pages.
Chapter 6 Relational Algebra and Relational Calculus CS 6360 Database Design Chris Irwin Davis Ph D Email cid021000 utdallas edu Phone 972 883 3574 O ce ECSS 4 705 Chapter 6 Outline Unary Relational Operations SELECT PROJECT Relational Algebra Operations from Set Theory Binary Relational Operations JOIN DIVISION Additional Relational Operations 2 Chapter 6 Outline Examples of Queries in Relational Algebra Relational Calculus The Tuple Relational Calculus The Domain Relational Calculus 3 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 Tuple Relational Calculus Domain Relational Calculus 4 Unary Relational Operations SELECT and PROJECT The SELECT Operation 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 6 The SELECT Operation Select the EMPLOYEE tuples whose department is 4 7 The SELECT Operation Select the EMPLOYEE tuples whose department is 4 Select the EMPLOYEE tuples whose salary is greater than 30 000 8 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 9 The SELECT Operation Selectivity The fraction of tuples selected by a selection of the condition 10 The SELECT Operation Selectivity The fraction of tuples selected by a selection of the condition The SELECT operation commutative 11 The SELECT Operation Selectivity The fraction of tuples selected by a selection of the condition The SELECT operation commutative Cascade SELECT operations into a single operation with AND condition 12 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 13 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 14 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 15 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 16 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 17 PROJECT and SQL SELECT In SQL the PROJECT condition is typically specified in the SELECT clause of a query For example the following operation 18 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 19 Sequences of Operations and the RENAME Operation Sequences of Operations and the RENAME Operation In line expression 21 Sequences of Operations and the RENAME Operation In line expression Sequence of operations 22 Sequences of Operations and the RENAME Operation In line expression Sequence of operations Rename attributes in intermediate results RENAME operation 23 RENAME 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 24 Sequences of Operations and the RENAME Operation 25 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 27 UNION R S Includes all tuples that are either in R or in S or in both R and S Duplicate tuples eliminated 28 UNION 29 SET DIFFERENCE or MINUS or EXCEPT R S Includes all tuples that are in R but not in S 30 INTERSECTION R S Includes all only tuples that are in both R and S Can be expressed in terms of union and difference 31 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 32 UNION INTERSECTION and MINUS 33 SQL Implementation In SQL there are three operations UNION INTERSECT and EXCEPT that correspond to the set operations described here Some SQL implementations notwithstanding In addition there are multiset operations UNION ALL INTERSECT ALL and EXCEPT ALL that do not eliminate duplicates 34 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 35 Cartesian Product with and Suppose that we want to retrieve a list of names of each female employee s dependents 36 Binary Relational Operations JOIN and DIVISION Binary Operation JOIN The JOIN Operation Denoted by Combine related tuples from two relations into single relation with longer tuples General JOIN condition of the form condition AND condition AND AND condition Example 38 JOIN Example 39 JOIN 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
View Full Document
Unlocking...