VIEWS: 1 PAGES: 26 POSTED ON: 6/29/2012 Public Domain
CS 385 Database Theory and Applications Lecture Set # 4 Blase B. Cindric University of Mount Union Relational Algebra (Sec. 5.1) A set of operators (either unary or binary) on relations, giving relations Forms the basis of Database Query operations in Relational DBs 2 types of operators in Relational Algebra: Set Operations -- from Discrete Mathematics Relational Operations -- custom designed for DBs CS 385 DB Theory & Applications 79 Set Operations in Relational Algebra There are four set operators: union intersection difference Cartesian product For each of these operations, the closure property holds (operands are relations, and results are relations) CS 385 DB Theory & Applications 80 Relational Operations in Relational Algebra There are five relational operators: selection (sometimes also called restriction) projection join (actual symbol is the bow-tie operator) divide := relational assignment Closure also holds for each of these…. This is important, because we can nest several operations inside one another CS 385 DB Theory & Applications 81 Details of Set Operations For , , and , the operands must be type compatible (same degree for both relations, and corresponding attributes must come from the same domains) R S all tuples in either R or S or both R S all tuples in both R and S R S all tuples in R and NOT in S R S concatenation of each tuple in R with every tuple in S CS 385 DB Theory & Applications 82 Examples of Set Operations Relation R: Relation S: A B C A B C a1 b1 c1 a1 b2 c3 a1 b2 c3 a2 b2 c2 a1 b1 c2 a3 b2 c1 a3 b2 c1 R S: A B C a1 b1 c1 a1 b2 c3 a1 b1 c2 a3 b2 c1 a2 b2 c2 CS 385 DB Theory & Applications 83 Examples of Set Operations Relation R: Relation S: A B C A B C a1 b1 c1 a1 b2 c3 a1 b2 c3 a2 b2 c2 a1 b1 c2 a3 b2 c1 a3 b2 c1 R S: A B C a1 b2 c3 a3 b2 c1 R S: A B C a1 b1 c1 a1 b1 c2 CS 385 DB Theory & Applications 84 Examples of Set Operations Relation T: Relation U: F G H P Q f1 g1 h1 p1 q1 f2 g2 h1 p1 q2 p2 q2 T U: F G H P Q f1 g1 h1 p1 q1 f1 g1 h1 p1 q2 f1 g1 h1 p2 q2 f2 g2 h1 p1 q1 f2 g2 h1 p1 q2 f2 g2 h1 p2 q2 CS 385 DB Theory & Applications 85 Relational Operations Select (also called Restrict) general form: CONDITION ( Relation ) semantics: result of this operation contains only those tuples in Relation for which CONDITION is true all attributes of Relation are included in the result CS 385 DB Theory & Applications 86 Examples of Selection Consider the relation Student: StNum Name Phone Dorm GPA 111111 Helga 7788 Ketcham 3.7 222222 Sven 6677 McCready 1.8 333333 Lars 7878 Shields 2.4 444444 Ivan 7878 Shields 3.5 555555 Fritz 7610 McCready 3.1 GPA >= 3.5 ( Student ) StNum Name Phone Dorm GPA 111111 Helga 7788 Ketcham 3.7 444444 Ivan 7878 Shields 3.5 CS 385 DB Theory & Applications 87 Examples of Selection Dorm = “Ketcham” OR Dorm = “McCready” ( Student ) StNum Name Phone Dorm GPA 111111 Helga 7788 Ketcham 3.7 222222 Sven 6677 McCready 1.8 555555 Fritz 7610 McCready 3.1 GPA >= 3.5 AND Dorm = “Shields” ( Student ) StNum Name Phone Dorm GPA 444444 Ivan 7878 Shields 3.5 CS 385 DB Theory & Applications 88 Relational Operations Project general form: ATTR_LIST ( Relation ) semantics: result of this operation contains only those attributes specifically listed in ATTR_LIST all tuples of Relation are included in the result, unless this would cause duplication of tuples in the result CS 385 DB Theory & Applications 89 Examples of Projection Again, consider the relation Student: StNum Name Phone Dorm GPA 111111 Helga 7788 Ketcham 3.7 222222 Sven 6677 McCready 1.8 333333 Lars 7878 Shields 2.4 444444 Ivan 7878 Shields 3.5 555555 Fritz 7610 McCready 3.1 StNum, GPA ( Student ) StNum GPA 111111 3.7 222222 1.8 333333 2.4 444444 3.5 555555 3.1 CS 385 DB Theory & Applications 90 Examples of Projection Dorm ( Student ) Dorm Ketcham McCready Shields Phone ( Student ) Phone 7788 6677 7878 7610 CS 385 DB Theory & Applications 91 Relational Operations A common operation in relational databases is to join two relations that share a common attribute, linking only those tuples from each relation that have the same value in their shared attribute. Effectively, we have a table lookup operation, matching tuples from the first relation only with tuples from the second relation that match Example: List the phone #’s of all students who have registered for RE 777, Section 03 Two relations (Student and Regis) Phone # data is only in the Student relation Course # and Section are only in the Regis relation CS 385 DB Theory & Applications 92 Telephone #’s for RE 777 Section 03 Student: StNum Name Phone Dorm GPA 111111 Helga 7788 Ketcham 3.7 222222 Sven 6677 McCready 1.8 333333 Lars 7878 Shields 2.4 444444 Ivan 7878 Shields 3.5 555555 Fritz 7610 McCready 3.1 Regis: StNum CourseNum Section 111111 CS 385 01 111111 PE 999 02 111111 RE 777 03 222222 PE 999 15 222222 RE 777 03 444444 CS 385 01 444444 PE 999 04 444444 MA 888 07 CS 385 DB Theory & Applications 93 Telephone #’s for RE 777 Section 03 Step 1: Form the Cartesian Product of the Student and Regis relations Step 2: Select the tuples from step 1 whose StNum values are the same in both Student and Regis Step 3: Select only those tuples from the result of step 2 that have RE 777 as the CourseNum value, and 03 as the Section value Step 4: Project the Name and Phone attributes from the result of step 3 CS 385 DB Theory & Applications 94 Telephone #’s for RE 777 Section 03 Step 1: Student relation has 5 tuples, with 5 attributes each Regis relation has 8 tuples, with 3 attributes each So … Student Regis contains 40 tuples, each with 8 attributes Consult the distributed page for today’s lecture to see actual contents of Student Regis …. CS 385 DB Theory & Applications 95 Telephone #’s for RE 777 Section 03 Step 2: Student.StNum = Regis.StNum ( Student Regis ) Student. Regis. StNum Name Phone Dorm GPA StNum CourseNum Section 111111 Helga 7788 Ketcham 3.7 111111 CS 385 01 111111 Helga 7788 Ketcham 3.7 111111 PE 999 02 111111 Helga 7788 Ketcham 3.7 111111 RE 777 03 222222 Sven 6677 McCready 1.8 222222 PE 999 15 222222 Sven 6677 McCready 1.8 222222 RE 777 03 444444 Ivan 7878 Shields 3.5 444444 CS 385 01 444444 Ivan 7878 Shields 3.5 444444 PE 999 04 444444 Ivan 7878 Shields 3.5 444444 MA 888 07 CS 385 DB Theory & Applications 96 Telephone #’s for RE 777 Section 03 Step 3: CourseNum = “RE 777” AND Section = “03” ( Student.StNum = Regis.StNum ( Student Regis ) ) Student. Regis. StNum Name Phone Dorm GPA StNum CourseNum Section 111111 Helga 7788 Ketcham 3.7 111111 RE 777 03 222222 Sven 6677 McCready 1.8 222222 RE 777 03 CS 385 DB Theory & Applications 97 Telephone #’s for RE 777 Section 03 Step 4: Name, Phone ( CourseNum = “RE 777” AND Section = “03” ( Student.StNum = Regis.StNum ( Student Regis ) ) ) Name Phone Helga 7788 Sven 6677 CS 385 DB Theory & Applications 98 The Natural Join Operation Linking relations based on the values of their shared field(s) is such a common operation that a special operator has been included in the relational algebra just for this purpose Join general form: Relation1 Relation2 semantics: result of this operation contains one copy of each attribute in both relations (note: only one copy of the shared attributes), and one tuple for each pair of tuples from Relation1 and Relation2 that have the same value(s) in their shared attribute(s) CS 385 DB Theory & Applications 99 Examples of Join Operations Relation T: Relation U: A B C C D E a1 b1 c1 c3 d3 e1 a2 b1 c2 c2 d4 e2 a2 b2 c2 c1 d1 e3 a3 b1 c3 c1 d2 e4 T U: A B C D E a1 b1 c1 d1 e3 a1 b1 c1 d2 e4 a2 b1 c2 d4 e2 a2 b2 c2 d4 e2 a3 b1 c3 d3 e1 CS 385 DB Theory & Applications 100 Examples of Join Operations Relation R: Relation S: A B C B C D a1 b1 c1 b1 c2 d3 a2 b1 c2 b1 c2 d4 a2 b2 c2 b1 c3 d1 a3 b1 c3 b3 c3 d2 R S: A B C D a2 b1 c2 d3 a2 b1 c2 d4 a3 b1 c3 d1 CS 385 DB Theory & Applications 101 Natural Join Operation If the two relations have no attributes in common, what happens? The join operation is identical to the Cartesian Product of the two relations What if the two relations being joined have exactly the same attributes? The join operation is identical to the Intersection of the two relations CS 385 DB Theory & Applications 102 Relational Operations Relational Assignment general form: Destination := Expression semantics: Expression is evaluated, and the result is stored as the new value of Destination Destination may be either a temporary name for a query result, or an actual base relation in this latter case, relational assignment represents a database modification operation example: see the next section on the divide operator… CS 385 DB Theory & Applications 103