RELATIONAL ALGEBRA RELATIONAL ALGEBRA (II)(II)

Prof. Sin-Min LEEProf. Sin-Min LEE

Department of Computer Department of Computer ScienceScience

Unary Relational Operations: SELECT and Unary Relational Operations: SELECT and PROJECTPROJECT

The PROJECT OperationThe PROJECT Operation Sequences of Operations and the Sequences of Operations and the

RENAME OperationRENAME Operation The SELECT OperationThe SELECT Operation

Relational Algebra Operations from Set Relational Algebra Operations from Set TheoryTheory

The UNION, INTERSECTION, and MINUS The UNION, INTERSECTION, and MINUS OperationsOperations

The CARTESIAN PRODUCT (or CROSS The CARTESIAN PRODUCT (or CROSS PRODUCT) OperationPRODUCT) Operation

Binary Relational Operations: JOIN and Binary Relational Operations: JOIN and DIVISIONDIVISION

The JOIN OperationThe JOIN Operation The EQUIJOIN and NATURAL JOIN The EQUIJOIN and NATURAL JOIN

Variations of JOINVariations of JOIN A Complete Set of Relational Algebra A Complete Set of Relational Algebra

OperationsOperations The DIVISION OperationThe DIVISION Operation

Additional Relational OperationsAdditional Relational Operations

Aggregate Functions and GroupingAggregate Functions and Grouping Recursive Closure OperationsRecursive Closure Operations OUTER JOIN OperationsOUTER JOIN Operations The OUTER JOIN OperationThe OUTER JOIN Operation

SPECIAL RELATIONAL OPERATORSSPECIAL RELATIONAL OPERATORS

The following operators are peculiar to relations:The following operators are peculiar to relations:

- - Join operatorsJoin operators

There are several kind of join operators. We only consider There are several kind of join operators. We only consider three of these here (others will be considered when we three of these here (others will be considered when we discuss null values):discuss null values):

- (1) Condition Joins- (1) Condition Joins

- (2) Equijoins- (2) Equijoins

- (3) Natural Joins- (3) Natural Joins

- - DivisionDivision

JOIN OPERATORSJOIN OPERATORS Condition Joins: Condition Joins: - Defined as a cross-product followed by a selection:- Defined as a cross-product followed by a selection:

R R ⋈⋈cc S = S = σσcc(R (R S) ( S) ( is called the ⋈ is called the ⋈bow-tie)bow-tie)where c is the condition.where c is the condition.

- - Example:Example:Given the sample relational instances S1 and R1Given the sample relational instances S1 and R1

The condition join S ⋈S1.sid<R1.sid R1 yields

JOIN OPERATORSJOIN OPERATORS Condition Joins: Condition Joins: - Defined as a cross-product followed by a selection:- Defined as a cross-product followed by a selection:

R R ⋈⋈cc S = S = σσcc(R (R S) ( S) ( is called the ⋈ is called the ⋈bow-tie)bow-tie)where c is the condition.where c is the condition.

- - Example:Example:Given the sample relational instances S1 and R1Given the sample relational instances S1 and R1

The condition join S ⋈S1.sid<R1.sid R1 yields

EquijoinEquijoin::Special case of the condition join where the join condition consists solely 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 of equalities between two fields in R and S connected by the logical AND operator (AND operator ( ).∧).∧

ExampleExample: Given the two sample relational instances S1 and R1: Given the two sample relational instances S1 and R1

The operator S1 R.sid=Ssid R1 yields

Natural JoinNatural Join

- Special case of equijoin where equalities are implicitly - Special case of equijoin where equalities are implicitly specified on specified on all all fields having the same name in R and S.fields having the same name in R and S.

- The condition c is now left out, so that the “bow tie” - The condition c is now left out, so that the “bow tie” operator by itself signifies a natural join.operator by itself signifies a natural join.

- - N. B.N. B. If the two relations have no attributes in common, the If the two relations have no attributes in common, the natural join is simply the cross-product.natural join is simply the cross-product.

DIVISIONDIVISION

- The division operator is used for queries which involve the - The division operator is used for queries which involve the ‘all’‘all’

qualifier such as “Find the names of sailors who have qualifier such as “Find the names of sailors who have reserved reserved allall boats”. boats”.

- The division operator is a bit tricky to explain, and perhaps - The division operator is a bit tricky to explain, and perhaps best approached through examples as will be done here.best approached through examples as will be done here.

EXAMPLES OF DIVISIONEXAMPLES OF DIVISION

DIVISIONDIVISIONInterpretation of the division operation A/B:Interpretation of the division operation A/B:

- Divide the attributes of A into 2 sets: A1 and A2.- Divide the attributes of A into 2 sets: A1 and A2.

- Divide the attributes of B into 2 sets: B2 and B3.- Divide the attributes of B into 2 sets: B2 and B3.

- Where the sets A2 and B2 have the same attributes.- Where the sets A2 and B2 have the same attributes.

- For each set of values in B2:- For each set of values in B2:

- Search in A2 for the sets of rows (having the same A1 - Search in A2 for the sets of rows (having the same A1 values) whose A2 values (taken together) form a set which values) whose A2 values (taken together) form a set which is the same as the set of B2’s.is the same as the set of B2’s.

- For all the set of rows in A which satisfy the above - For all the set of rows in A which satisfy the above search, pick out their A1 values and put them in the search, pick out their A1 values and put them in the answer.answer.

DIVISIONDIVISIONExample: Example: Find the names of sailors who have reserved all boats:Find the names of sailors who have reserved all boats:

(1) A = (1) A = sid,bidsid,bid((Reserves)Reserves). A1 = . A1 = sidsid((ReservesReserves) A2 = ) A2 = bidbid((ReservesReserves))

(2) B2 = (2) B2 = bidbid((BoatsBoats) B3 is the rest of B.) B3 is the rest of B.

Thus, B2 ={101, 102, 103, 104}Thus, B2 ={101, 102, 103, 104}

(3)(3) Find the rows of A such that their A.Find the rows of A such that their A.sidsid is the same and their combined is the same and their combined A.A.bidbid is the set B2. is the set B2.

Thus we find A1 = {22}Thus we find A1 = {22}

(4) Get the set of A2 corresponding to A1: A2 = {Dustin}(4) Get the set of A2 corresponding to A1: A2 = {Dustin}

FORMAL DEFINITION OF DIVISIONFORMAL DEFINITION OF DIVISION

The formal definition of division is as follows:The formal definition of division is as follows:

A/B = A/B = xx(A) - (A) - xx((((xx(A) (A) B) – A) B) – A)

EXAMPLES OF ALGEBRA QUERIESEXAMPLES OF ALGEBRA QUERIESIn the rest of this chapter we shall illustrate queries using the In the rest of this chapter we shall illustrate queries using the

following new instances S3 of sailors, R2 of Reserves and following new instances S3 of sailors, R2 of Reserves and B1 of boats.B1 of boats.

QUERY Q1QUERY Q1Given the relational instances:Given the relational instances:

(Q1) Find the names of sailors who have reserved boat 103

sname((σbid=103 Reserves) ⋈ Sailors)

The answer is thus the following relational instance

{<Dustin>, <Lubber>, <Horatio>}

QUERY Q1 (cont’d)QUERY Q1 (cont’d)There are of course several ways to express Q1 in relational There are of course several ways to express Q1 in relational

algebra.algebra.

Here is another:Here is another:

snamesname((σσbid=103bid=103(Reserves(Reserves Sailors))⋈ 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 Q2QUERY Q2(Q2) Find the names of sailors who have reserved a red boat.(Q2) Find the names of sailors who have reserved a red boat.

sname((σcolor=‘red’Boats) ⋈ Reserves ⋈ Sailors)

QUERY Q3QUERY Q3(Q3) Find the colors of boats reserved by Lubber.(Q3) Find the colors of boats reserved by Lubber.

color((σsname=‘Lubber’Sailors)Sailors ⋈ Reserves ⋈ Boats)

QUERY Q4QUERY Q4(Q4) Find the names of Sailors who have reserved at least (Q4) Find the names of Sailors who have reserved at least

one boatone boat

sname(Sailors ⋈ Reserves)

QUERY Q5QUERY Q5 (Q5) Find the names of sailors who have reserved a red or a (Q5) Find the names of sailors who have reserved a red or a

green boat.green boat.

(Tempboats, (σcolor=‘red’Boats) ∪ (σcolor=‘green’Boats))

sname(Tempboats ⋈ Reserves ⋈ Sailors)

QUERY Q6QUERY Q6(Q6) Find the names of Sailors who have reserved a red and a (Q6) Find the names of Sailors who have reserved a red and a

green boatgreen boat..It seems tempting to use the expression used in Q5, It seems tempting to use the expression used in Q5,

replacing simply replacing simply ∪∪ by by ∩∩. However, this won’t work, for . However, this won’t work, for such an expression is requesting the names of sailors who such an expression is requesting the names of sailors who have requested a boat that is have requested a boat that is both red and greenboth red and green! The ! The correct expression is as follows:correct expression is as follows:

((TempredTempred, , sidsid((((σσcolor=‘red’color=‘red’BoatsBoats) ) ⋈⋈ ReservesReserves))))

((TempgreenTempgreen, , sidsid((((σσcolor=‘green’color=‘green’BoatsBoats) ) ⋈⋈ ReservesReserves))))

snamesname (( ((TempredTempred ∩∩ TempgreenTempgreen) ) ⋈ ⋈ SailorsSailors))

QUERY Q7QUERY Q7

(Q7) Find the names of sailors who have reserved at least two (Q7) Find the names of sailors who have reserved at least two boats.boats.

(Reservations, sid,sname,bid(Sailors ⋈ Reserves))

(Reservationpairs(1sid1, 2sname, 3bid1, 4sid2,

5sname, 6bid2), ReservationsReservations)

sname1σ(sid1=sid2)(bid1bid2)Reservationpairs)

QUERY 8QUERY 8(Q8) Find the sids of sailors with age over 20 who have not (Q8) Find the sids of sailors with age over 20 who have not

reserved a red boat.reserved a red boat.

sid(σage>20Sailors) - sid((σcolor=‘red’Boats) ⋈ Reserves ⋈ Sailors)

QUERY 9QUERY 9(Q) Find the names of sailors who have reserved all boats.(Q) Find the names of sailors who have reserved all boats.

(Tempsids, (sid,bidReserves) / (bidBoats))

sname(Tempsids ⋈ Sailors

QUERY Q10QUERY Q10(Q10) Find the names of sailors who have reserved all boats (Q10) Find the names of sailors who have reserved all boats

called Interlake.called Interlake.

(Tempsids, (sid,bidReserves)/(bid(σbname=‘Interlake’Boats)))

sname(Tempsids ⋈ Sailors)

Natural JoinNatural Join

- combines σ, π, - combines σ, π, - very commonly used- very commonly used

Natural Join forms the cross product of its two Natural Join forms the cross product of its two arguments, does a selection to enforce arguments, does a selection to enforce equality of columns with the same name and equality of columns with the same name and removes duplicate columns.removes duplicate columns.

Eg: “show all transactions done by account Eg: “show all transactions done by account owner Bob”owner Bob”

σ σ owner=“Bob”owner=“Bob” (account (account JOINJOIN transaction) transaction)

Rename operationRename operation

What if you need to access the same relation What if you need to access the same relation twice in a query?twice in a query?

egeg. . person(ss#, name, mother_ss#, father_ss#)person(ss#, name, mother_ss#, father_ss#)

““Find the name of Bob’s mother” needs the Find the name of Bob’s mother” needs the “person” table to be accessed twice.“person” table to be accessed twice.

The operation The operation ρ ρ xx (r) (r) evaluates to a second logical evaluates to a second logical

copy of relation r renamed to x.copy of relation r renamed to x.

Rename operation (contd)Rename operation (contd)

eg:eg:

π π mother.namemother.name ( (

(ρ (ρ mothermother (person)) (person))

JOIN JOIN mother.ss# = person.mother_ss#mother.ss# = person.mother_ss#

(( name=“Bobname=“Bob” ” (person)))(person)))