DOC PREVIEW
UT Dallas CS 6360 - CS-6360_ch06 Relational Algebra

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.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 138 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 138 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 138 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 138 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 138 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 138 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 138 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 138 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 138 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 138 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 138 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 138 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 138 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 138 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 138 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 138 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 138 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 138 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 138 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 138 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 138 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 138 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 138 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 138 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 138 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 138 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 138 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 138 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 138 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

!" #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

UT Dallas CS 6360 - CS-6360_ch06 Relational Algebra

Documents in this Course
Ch22(1)

Ch22(1)

44 pages

Ch21

Ch21

38 pages

Ch19

Ch19

46 pages

Ch18

Ch18

25 pages

Ch17

Ch17

21 pages

Ch15

Ch15

42 pages

Ch09

Ch09

42 pages

Ch05

Ch05

34 pages

Ch22

Ch22

45 pages

Ch21

Ch21

38 pages

Ch19

Ch19

48 pages

Ch18

Ch18

24 pages

Ch17

Ch17

22 pages

Ch16

Ch16

17 pages

Ch15

Ch15

42 pages

Ch09

Ch09

42 pages

Ch08

Ch08

39 pages

Ch07

Ch07

34 pages

Ch06

Ch06

43 pages

Ch05

Ch05

34 pages

Ch04

Ch04

39 pages

Ch03(2)

Ch03(2)

36 pages

Ch02

Ch02

33 pages

Ch08

Ch08

28 pages

Ch07

Ch07

31 pages

Ch06

Ch06

43 pages

Ch05

Ch05

39 pages

Ch04(1)

Ch04(1)

39 pages

Ch03(1)

Ch03(1)

38 pages

Ch02

Ch02

38 pages

Ch01

Ch01

36 pages

Ch24

Ch24

36 pages

Ch21

Ch21

54 pages

Ch19

Ch19

48 pages

Ch18

Ch18

24 pages

Ch17

Ch17

22 pages

Ch03(1)

Ch03(1)

38 pages

Ch02

Ch02

38 pages

Ch01

Ch01

36 pages

Ch24

Ch24

36 pages

Ch21

Ch21

54 pages

Ch19

Ch19

48 pages

Ch18

Ch18

24 pages

Ch17

Ch17

22 pages

Ch08

Ch08

28 pages

Ch07

Ch07

31 pages

Ch06

Ch06

43 pages

Ch05

Ch05

39 pages

Ch04(1)

Ch04(1)

39 pages

Ch08

Ch08

39 pages

Ch07

Ch07

40 pages

Ch06

Ch06

47 pages

Ch05

Ch05

41 pages

Ch04

Ch04

43 pages

Ch03

Ch03

41 pages

Ch02

Ch02

38 pages

Ch01

Ch01

36 pages

Ch21

Ch21

54 pages

Ch19

Ch19

51 pages

Ch18

Ch18

24 pages

Ch08

Ch08

39 pages

Ch07

Ch07

40 pages

Ch06

Ch06

47 pages

Ch05

Ch05

41 pages

Ch04

Ch04

43 pages

Ch03

Ch03

41 pages

Ch02

Ch02

38 pages

Ch01

Ch01

36 pages

Ch21

Ch21

54 pages

Ch19

Ch19

51 pages

Ch18

Ch18

24 pages

Ch17

Ch17

25 pages

lab-manual

lab-manual

215 pages

Ch08

Ch08

39 pages

Ch07

Ch07

40 pages

Ch06

Ch06

47 pages

Ch05

Ch05

41 pages

Ch04

Ch04

43 pages

Ch03

Ch03

41 pages

Ch02

Ch02

38 pages

Ch01

Ch01

36 pages

Ch21

Ch21

54 pages

Ch19

Ch19

51 pages

Ch17

Ch17

25 pages

Ch21

Ch21

54 pages

Ch19

Ch19

51 pages

Ch18

Ch18

24 pages

Ch17

Ch17

25 pages

Ch08

Ch08

39 pages

Ch07

Ch07

40 pages

Ch06

Ch06

47 pages

Ch05

Ch05

41 pages

Ch04

Ch04

43 pages

Ch03

Ch03

41 pages

Ch02

Ch02

38 pages

Ch01

Ch01

36 pages

Ch04(1)

Ch04(1)

43 pages

Ch07

Ch07

40 pages

Ch03

Ch03

42 pages

Ch01

Ch01

36 pages

Ch02

Ch02

38 pages

Ch05

Ch05

41 pages

Ch06

Ch06

47 pages

Ch08

Ch08

39 pages

Ch17

Ch17

25 pages

Ch18

Ch18

24 pages

Ch09

Ch09

42 pages

Ch21

Ch21

54 pages

Ch19

Ch19

51 pages

Ch21

Ch21

54 pages

Ch19

Ch19

51 pages

Ch18

Ch18

24 pages

Ch17

Ch17

25 pages

Ch09

Ch09

42 pages

Ch08

Ch08

39 pages

Ch07

Ch07

40 pages

Ch06

Ch06

47 pages

Ch05

Ch05

41 pages

Ch04(1)

Ch04(1)

43 pages

Ch03

Ch03

42 pages

Ch02

Ch02

38 pages

Ch01

Ch01

36 pages

Load more
Download CS-6360_ch06 Relational Algebra
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view CS-6360_ch06 Relational Algebra and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view CS-6360_ch06 Relational Algebra 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?