120 Views

Download Presentation
##### RELATIONAL ALGEBRA (II)

**An Image/Link below is provided (as is) to download presentation**

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -

**CS157A**Lecture 9 RELATIONAL ALGEBRA (II) Prof. Sin-Min LEE Department of Computer Science**Unary Relational Operations: SELECT and PROJECT**• The PROJECT Operation • Sequences of Operations and the RENAME Operation • The SELECT Operation**Relational Algebra Operations from Set Theory**• The UNION, INTERSECTION, and MINUS Operations**Binary Relational Operations: JOIN and DIVISION**• The JOIN Operation • The EQUIJOIN and NATURAL JOIN Variations of JOIN • A Complete Set of Relational Algebra Operations • The DIVISION Operation**Additional Relational Operations**• Aggregate Functions and Grouping • Recursive Closure Operations • OUTER JOIN Operations • The OUTER JOIN Operation**SPECIAL RELATIONAL OPERATORS**The following operators are peculiar to relations: - Join operators There are several kind of join operators. We only consider three of these here (others will be considered when we discuss null values): - (1) Condition Joins - (2) Equijoins - (3) Natural Joins - Division**JOIN OPERATORS**Condition Joins: - Defined as a cross-product followed by a selection: R ⋈cS = σc(R S) (⋈ is called the bow-tie) where c is the condition. - Example: Given the sample relational instances S1 and R1 The condition join S ⋈S1.sid<R1.sid R1 yields**Equijoin:**Special case of the condition join where the join condition consists solely of equalities between two fields in R and S connected by the logical AND operator (∧). Example: Given the two sample relational instances S1 and R1 The operator S1 R.sid=Ssid R1 yields**Natural Join**- Special case of equijoin where equalities are implicitly specified on all fields having the same name in R and S. - The condition c is now left out, so that the “bow tie” operator by itself signifies a natural join. - N. B. If the two relations have no attributes in common, the natural join is simply the cross-product.**DIVISION**- The division operator is used for queries which involve the ‘all’ qualifier such as “Find the names of sailors who have reserved all boats”. - The division operator is a bit tricky to explain.**DIVISION**Example: Find the names of sailors who have reserved all boats: (1) A = sid,bid(Reserves). A1 = sid(Reserves) A2 = bid(Reserves) (2) B2 = bid(Boats) B3 is the rest of B. Thus, B2 ={101, 102, 103, 104} (3) Find the rows of A such that their A.sid is the same and their combined A.bid is the set B2. Thus we find A1 = {22} (4) Get the set of A2 corresponding to A1: A2 = {Dustin}**FORMAL DEFINITION OF DIVISION**The formal definition of division is as follows: A/B = x(A) - x((x(A) B) – A)**EXAMPLES OF ALGEBRA QUERIES**In the rest of this chapter we shall illustrate queries using the following new instances S3 of sailors, R2 of Reserves and B1 of boats.**QUERY Q1**Given the relational instances: (Q1) Find the names of sailors who have reserved boat 103 sname((σbid=103Reserves) ⋈ Sailors) The answer is thus the following relational instance {<Dustin>, <Lubber>, <Horatio>}**QUERY Q1 (cont’d)**There are of course several ways to express Q1 in relational algebra. Here is another: sname(σbid=103(Reserves⋈ Sailors)) Which of these expressions should we use? That is a question of optimization. Indeed, when we describe how to state queries in SQL, we can leave it to the optimizer in the DBMS to select the nest approach.**QUERY Q2**(Q2) Find the names of sailors who have reserved a red boat. sname((σcolor=‘red’Boats) ⋈ Reserves ⋈ Sailors)**QUERY Q3**(Q3) Find the colors of boats reserved by Lubber. color((σsname=‘Lubber’Sailors)Sailors ⋈ Reserves ⋈ Boats)**QUERY Q4**(Q4) Find the names of Sailors who have reserved at least one boat sname(Sailors ⋈ Reserves)**QUERY Q5**(Q5) Find the names of sailors who have reserved a red or a green boat. (Tempboats, (σcolor=‘red’Boats) ∪ (σcolor=‘green’Boats)) sname(Tempboats⋈Reserves⋈Sailors)**QUERY Q6**(Q6) Find the names of Sailors who have reserved a red and a green boat. It seems tempting to use the expression used in Q5, replacing simply ∪ by ∩. However, this won’t work, for such an expression is requesting the names of sailors who have requested a boat that is both red and green! The correct expression is as follows: (Tempred, sid((σcolor=‘red’Boats) ⋈Reserves)) (Tempgreen, sid((σcolor=‘green’Boats) ⋈Reserves)) sname ((Tempred∩Tempgreen) ⋈ Sailors)**QUERY Q7**(Q7) Find the names of sailors who have reserved at least two boats. (Reservations, sid,sname,bid(Sailors ⋈Reserves)) (Reservationpairs(1sid1, 2sname, 3bid1, 4sid2, 5sname, 6bid2), ReservationsReservations) sname1σ(sid1=sid2)(bid1bid2)Reservationpairs)**QUERY 8**(Q8) Find the sids of sailors with age over 20 who have not reserved a red boat. sid(σage>20Sailors) - sid((σcolor=‘red’Boats)⋈Reserves⋈Sailors)**QUERY 9**(Q) Find the names of sailors who have reserved all boats. (Tempsids, (sid,bidReserves) / (bidBoats)) sname(Tempsids⋈Sailors**QUERY Q10**(Q10) Find the names of sailors who have reserved all boats called Interlake. (Tempsids, (sid,bidReserves)/(bid(σbname=‘Interlake’Boats))) sname(Tempsids⋈Sailors)**Cartesian Product**• R(A1, A2, ..., Am) and S(B1, B2, ... , Bn) • T(A1, A2, ... , Am, B1, B2, ..., Bn) = R(A1, A2, ..., Am) X S(B1, B2, ..., Bn) • A tuple t is in T if and only if t[A1,A2, ... ,Am] is in R and t[B1, B2, ..., Bn] is in S. - If R has N1 tuples and S has N2 tuples, then T will have N1*N2 tuples.**Cartesian Product**R Rx S S**Natural-Join**• Denoted by |x|. • Binary operation • Creates a Cartesian-product of the arguments then performs selection to force equality on attributes that appear in both relations**Division**• Denoted by • Binary Operation • Used in queries that include the phrase “for all”.**Division (Cont’d)**• Division is an operation on schema R – S • A tuple t is in r s if and only if: • t is in ΠR – S(r) and • For every tuple ts in s, there is a tuple tr in r satisfying both of the following: a. tr[S] = ts[R] b. tr[R – S] = t**Relational Algebra**Fundamental operators • select s • project p • cartesian product • union • set difference - Other operators • natural join JOIN (butterfly symbol) • set intersection • division **A Simple DB**account ac# owner ss# balance 1 bob 123 1000 2 sue 456 2000 3 jane 789 3000 transaction t# ac# type amount outcome date 1 1 W 1500 bounced 5/1/98 2 2 D 1000 ok 5/2/98 3 1 W 100 ok 5/4/98 4 3 D 500 ok 5/7/98 5 2 W 200 ok 5/9/98 account had transaction**Select**eg: s balance>=1500 account result : ac# owner ss# balance 2 sue 456 2000 3 jane 789 3000 Project eg: π owner, ss# account result: owner ss# bob 123 sue 456 jane 789