DATABASE DESIGN I - 1DL300 Autumn 2012 An Introductory Course on Database Systems http://www.it.uu.se/edu/course/homepage/dbastekn/ht12/ Uppsala Database Laboratory Department of Information Technology, Uppsala University, Uppsala, Sweden Silvia Stefanova, UDBL - IT - UU 2012-11-14 1
Introduction to Relational Algebra Elmasri/Navathe ch 6 Padron-McCarthy/Risch ch 10 Silvia Stefanova Department of Information Technology Uppsala University, Uppsala, Sweden Silvia Stefanova, UDBL - IT - UU 2012-11-14 2
Outline 1. Why and what about the Relational Algebra 2. Unary operations • Select operation • Project operation 3. Set operations • Union • Intersection • Set difference (Minus) 4. Cartesian product 5. Join operator • Join • Natural join • Outer join 6. Exercises Silvia Stefanova, UDBL - IT - UU 2012-11-14 3
Relational algebra • Relational algebra is a procedural language • Operations in relational algebra takes two or more relations as arguments and return a new relation. • Relational algebraic operations: – Operations from set theory: • Union, Intersection, Difference, Cartesian product – Operations specifically introduced for the relational data model: • Select, Project, Join • It have been shown that the select, project, union, difference, and cartesian product operations form a complete set. That is any other relational algebra operation can be expressed in these. Manivasakan Sabesan- UDBL - IT - UU 2012-11-14 4
Outline 1. Why and what about the Relational Algebra 2. Unary operations • Select operation • Project operation 3. Set operations • Union • Intersection • Set difference (Minus) 4. Cartesian product 5. Join operator • Join • Natural join • Outer join 6. Exercises Silvia Stefanova, UDBL - IT - UU 2012-11-14 5
Select operation • The select operator , σ , selects a specific set of tuples from a relation according to a selection condition (or selection predicate P). • Notation: σ <select condition> (R) • The result relation has the same number of attributes as the initial relation • Example: σ pnumber=9012034455 (Person) Manivasakan Sabesan- UDBL - IT - UU 2012-11-14 6
Relations to work on Teachers FK, course_taught_by FK, course_to_attend id name phone Courses 1 Susan 100 cname code tid 2 Barbara 200 Database Design 1 DB1 1 3 Ricardo 200 Database Design 2 DB2 1 Distributed Systems DS 3 Attending student course Students 4 DB1 id sname sphone 4 DS 2 Barbara 200 7 DS 4 Jimmy NULL 7 DB2 7 Anna 800 FK, student_attends Silvia Stefanova, UDBL - IT - UU 2012-11-14 7
SELECT operation - examples ? Retrieve (select) teachers whose phone number is > = 200 ? Retrieve (select) students with the name ‘Jimmy’ ? Retrieve (select) teachers with name ‘Barbara’ and phone number is > = 200 Manivasakan Sabesan- UDBL - IT - UU 2012-11-14 8
Project operation • The project operator , Π , picks out (or projects) listed columns from a relation and creates a new relation consisting of these columns. • Notation: Π <attribute list> (R) • The result relation is a new relation consisting of the projected columns • It doesn’t return the duplicates • Example: Π Lname,Fname,Salary (Employee) Manivasakan Sabesan- UDBL - IT - UU 2012-11-14 9
Relational algebra expressions- examples ? Retrieve (select) the names of the teachers whose phone number is > = 200 Is it possible in the example above to do the projection before ? the selection ? Why or why not ? Manivasakan Sabesan- UDBL - IT - UU 2012-11-14 10
Outline 1. Why and what about the Relational Algebra 2. Unary operations • Select operation • Project operation 3. Set operations • Union • Intersection • Set difference (Minus) 4. Cartesian product 5. Join operator • Join • Natural join • Outer join 6. Exercises Silvia Stefanova, UDBL - IT - UU 2012-11-14 11
Set operations • Relations are required to be union compatible to be able to take part in the union, intersection and difference operations. • Two relations R1 and R2 is said to be union-compatible if: R1 D1 × D2 ×... × Dn and R2 D1 × D2 ×... × Dn i.e. if they have the same degree and the same domains . Silvia Stefanova, UDBL - IT - UU 2012-11-14 12
Union operation • The union of two union-compatible relations R and S is the set of all tuples that either occur in R, S, or in both. • Notation: R S • The duplicates don’t exist in the union. • Example: Students Teachers = id sname sphone id name phone id sname sphone 2 Barbara 200 1 Susan 100 2 Barbara 200 4 Jimmy NULL 2 Barbara 200 4 Jimmy NULL 7 Anna 800 3 Ricardo 200 7 Anna 800 1 Susan 100 3 Ricardo 200 Silvia Stefanova, UDBL - IT - UU 2012-11-14 13
Intersect operation • The intersection of two union-compatible sets R and S, is the set of all tuples that occur in both R and S. • Notation : R ∩ S • Example: Students ∩ Teachers = id sname sphone id name phone id sname sphone 1 Susan 100 2 Barbara 200 2 Barbara 200 4 Jimmy NULL 3 Ricardo 200 7 Anna 800 Silvia Stefanova, UDBL - IT - UU 2012-11-14 14
Difference operation • The difference of two union-compatible relations R and S is the set of all tuples that occur in R but not in S. • Notation: R - S • Example: Students - Teachers = id sname sphone id name phone id sname sphone 1 Susan 100 2 Barbara 200 2 Barbara 200 4 Jimmy NULL 3 Ricardo 200 7 Anna 800 Silvia Stefanova, UDBL - IT - UU 2012-11-14 15
Outline 1. Why and what about the Relational Algebra 2. Unary operations • Select operation • Project operation 3. Set operations • Union • Intersection • Set difference (Minus) 4. Cartesian product 5. Join operator • Join • Natural join • Outer join 6. Exercises Silvia Stefanova, UDBL - IT - UU 2012-11-14 16
Cartesian product • The Cartesian product of R and S combines all tuples from R with all tuples from S. • Notation: R x S • Example: Courses x Teachers cname code tid id name phone Database Design 1 DB1 1 1 Susan 100 Database Design 2 DB2 1 2 Barbara 200 Distributed Systems DS 3 3 Ricardo 200 Silvia Stefanova, UDBL - IT - UU 2012-11-14 17
Cartesian product • The result is a new relation with (3+3) attributes and (3*5) tuples Courses x Teachers cname code tid id name phone Database Design 1 DB1 1 1 Susan 100 Database Design 1 DB1 1 2 Barbara 200 Database Design 1 DB1 1 3 Ricardo 200 Database Design 2 DB2 1 1 Susan 100 Database Design 2 DB2 1 2 Barbara 200 Database Design 2 DB2 1 3 Ricardo 200 Distributed Systems DS 3 1 Susan 100 Distributed Systems DS 3 2 Barbara 200 Distributed Systems DS 3 3 Ricardo 200 Silvia Stefanova, UDBL - IT - UU 2012-11-14 18
Outline 1. Why and what about the Relational Algebra 2. Unary operations • Select operation • Project operation 3. Set operations • Union • Intersection • Set difference (Minus) 4. Cartesian product 5. Join operator • Join • Natural join • Outer join 6. Exercises Silvia Stefanova, UDBL - IT - UU 2012-11-14 19
Join • The join operator creates a new relation by combining tuples from two relations that satisfy a condition • Notation: R <join condition> S • Example: tid=id Teachers Courses cname code tid id name phone Database Design 1 DB1 1 1 Susan 100 Database Design 2 DB2 1 2 Barbara 200 Distributed Systems DS 3 3 Ricardo 200 Silvia Stefanova, UDBL - IT - UU 2012-11-14 20
Join The result is a relation with (3+3) attributes Courses tid=id Teachers cname code tid id name phone Database Design 1 DB1 1 1 Susan 100 Database Design 1 DB1 1 2 Barbara 200 Database Design 1 DB1 1 3 Ricardo 200 Database Design 2 DB2 1 1 Susan 100 Database Design 2 DB2 1 2 Barbara 200 Database Design 2 DB2 1 3 Ricardo 200 Distributed Systems DS 3 1 Susan 100 Distributed Systems DS 3 2 Barbara 200 Distributed Systems DS 3 3 Ricardo 200 Silvia Stefanova, UDBL - IT - UU 2012-11-14 21
Join ? Receive the same result by using x and σ ! Why do we need if this can be done by x and σ ? ? Manivasakan Sabesan- UDBL - IT - UU 2012-11-14 22
Theta join • Notation: R C S C is the join condition which has the form Ar As , where is one of {=, <, >, ≤, ≥, ≠}. Several terms can be connected as C1 AND C2 AND … Ck • A join operation with this kind of general join condition is called Theta join . R A≤F S R S A≤F = A B C D E F A B C D E F 1 2 3 2 3 4 1 2 3 2 3 4 6 7 8 7 3 5 1 2 3 7 3 5 9 7 8 7 8 9 1 2 3 7 8 9 6 7 8 7 8 9 9 7 8 7 8 9 Silvia Stefanova, UDBL - IT - UU 2012-11-14 23
Equijoin • Notation: R C S • In C the only comparison operator is = R B=C S R S B=C = A B C D E A B C D E a 2 2 d e a 2 2 d e a 4 4 d e a 4 4 d e 9 d e Silvia Stefanova, UDBL - IT - UU 2012-11-14 24
Recommend
More recommend
