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.
!" #Chris Irwin Davis, Ph.D.Email: [email protected]: (972) 883-3574Office: ECSS 4.705Chapter 6: Relational Algebra and Relational CalculusCS-6360 Database Design!" #Chapter 6 Outline■Unary Relational Operations•SELECT (σ)•PROJECT (π)■Relational Algebra Operations from Set Theory■Binary Relational Operations•JOIN•DIVISION■Additional Relational Operations2!" #Chapter 6 Outline■Examples of Queries in Relational Algebra■Relational Calculus•The Tuple Relational Calculus•The Domain Relational Calculus3!" #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 Calculus4Unary 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 47!" #The SELECT (σ) Operation■Select the EMPLOYEE tuples whose department is 4■Select the EMPLOYEE tuples whose salary is greater than $30,0008!" #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 relation9!" #The SELECT (σ) Operation■Selectivity•The fraction of tuples selected by a selection of the condition10!" #The SELECT (σ) Operation■Selectivity•The fraction of tuples selected by a selection of the condition■The SELECT operation commutative11!" #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 condition12!" #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:19Sequences 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 operation23!" #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 Operation25Relational Algebra Operationsfrom Set Theory!" #Relational Algebra Operationsfrom Set Theory■UNION, INTERSECTION, and MINUS •Merge the elements of two sets in various ways•Binary operations•Relations must have the same type of tuples27!" #UNION■R ∪ S■Includes all tuples that are either in R or in S or in both R and S■Duplicate tuples eliminated28!" #UNION29!" #SET DIFFERENCE (or MINUS or EXCEPT)■R – S■Includes all tuples that are in R but not in S30!" #INTERSECTION■R ∩ S■Includes all/only tuples that are in both R and S■Can be expressed in terms of union and difference31!" #INTERSECTION and UNION32■Notice that both UNION and INTERSECTION are commutative operations; that is,■Both UNION and INTERSECTION can be treated asn-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,■!" #UNION, INTERSECTION, and MINUS33!" #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 duplicates34!" #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 attributes35!" #Cartesian Product with σ and π■Suppose that we want to retrieve a list of names of each female employee’s dependents36Binary Relational Operations:JOIN and
View Full Document
Unlocking...