ch04relationalalgebra-110310232134-phpapp01

8/2/2019 ch04relationalalgebra-110310232134-phpapp01

1/38

Chapter 4

Relational Algebra

Pearson Education 2009


2/38

2

Chapter 5 - Objectives

x Meaning of the term relational completeness.

x How to form queries in relational algebra.

x How to form queries in tuple relational calculus.

x How to form queries in domain relational

calculus.

x Categories of relational DML.



3/38

3

Introduction

x Relational algebra and relational calculus areformal languages associated with the relationalmodel.

x

Informally, relational algebra is a (high-level)procedural language and relational calculus anon-procedural language.

x However, formally both are equivalent to one

another.x A language that produces a relation that can be

derived using relational calculus is relationallycomplete.



4/38

4

Relational Algebra

x Relational algebra operations work on one ormore relations to define another relationwithout changing the original relations.

x Both operands and results are relations, sooutput from one operation can become input toanother operation.

x Allows expressions to be nested, just as inarithmetic. This property is called closure.



5/38

5

Relational Algebra

x Five basic operations in relational algebra:Selection, Projection, Cartesian product,Union, and Set Difference.

x These perform most of the data retrievaloperations needed.

x Also have Join, Intersection, and Divisionoperations, which can be expressed in terms of5 basic operations.



6/38

6

Relational Algebra Operations



7/38

7

Relational Algebra Operations



8/38


9/38

9

Example - Selection (or Restriction)

x List all staff with a salary greater than 10,000.

salary > 10000 (Staff)



10/38

10

Projection

x col1, . . . , coln(R)Works on a single relation R and defines a

relation that contains a vertical subset of R,

extracting the values of specified attributes andeliminating duplicates.



11/38

11

Example - Projection

x Produce a list of salaries for all staff, showing only

staffNo, fName, lName, and salary details.

staffNo, fName, lName, salary(Staff)



12/38

12

Union

x R SUnion of two relations R and S defines a relation

that contains all the tuples of R, or S, or both R

and S, duplicate tuples being eliminated.R and S must be union-compatible.

x If R and S have Iand Jtuples, respectively, union

is obtained by concatenating them into one relationwith a maximum of (I+ J) tuples.



13/38

13

Example - Union

x List all cities where there is either a branch office

or a property for rent.

city(Branch) city(PropertyForRent)



14/38

14

Set Difference

x R S

Defines a relation consisting of the tuples that

are in relation R, but not in S.

R and S must be union-compatible.



15/38

15

Example - Set Difference

x List all cities where there is a branch office but no

properties for rent.




16/38

16

Intersection

x R SDefines a relation consisting of the set of all

tuples that are in both R and S.

R and S must be union-compatible.

x Expressed using basic operations:

R S = R (R S)



17/38

17

Example - Intersection

x List all cities where there is both a branch office

and at least one property for rent.




18/38

18

Cartesian product

x R X S

Defines a relation that is the concatenation of

every tuple of relation R with every tuple of

relation S.



19/38

19

Example - Cartesian product

x List the names and comments of all clients who

have viewed a property for rent.

(clientNo, fName, lName(Client)) X (clientNo, propertyNo, comment(Viewing))



20/38

Cartesian Product

20


21/38

21

Example - Cartesian product and Selection

x Use selection operation to extract those tuples whereClient.clientNo = Viewing.clientNo.

Client.clientNo = Viewing.clientNo(( clientNo, fName, lName(Client)) ( clientNo, propertyNo, comment(Viewing)))

x Cartesian product and Selection can be reduced to a singleoperation called a Join.



22/38

22

Join Operations

x Join is a derivative of Cartesian product.

x Equivalent to performing a Selection, using joinpredicate as selection formula, over Cartesianproduct of the two operand relations.

x One of the most difficult operations to implement

efficiently in an RDBMS and one reason whyRDBMSs have intrinsic performance problems.



23/38

23

Join Operations

x Various forms of join operation

Theta join

Equijoin (a particular type of Theta join)

Natural join

Outer join

Semijoin



24/38

24

Theta join ( -join)x R FS

Defines a relation that contains tuples

satisfying the predicate F from the Cartesian

product of R and S.

The predicate F is of the form R.ai S.biwhere may be one of the comparisonoperators (, , =, ).



25/38

25

Theta join ( -join)x Can rewrite Theta join using basic Selection and

Cartesian product operations.

R FS = F(R S)x Degree of a Theta join is sum of degrees of the

operand relations R and S. If predicate F containsonly equality (=), the term Equijoin is used.



26/38

26

Example - Equijoin



(clientNo, fName, lName(Client)) Client.clientNo = Viewing.clientNo(clientNo, propertyNo, comment(Viewing))



27/38

27

Natural join

x R S

An Equijoin of the two relations R and S over all

common attributes x. One occurrence of each

common attribute is eliminated from the result.Hence the degree is the sum of the degrees of the

relations R and S less the number of attributes in

x



28/38

28

Example - Natural join



(clientNo, fName, lName(Client))(clientNo, propertyNo, comment(Viewing))



29/38

29

Outer join

x To display rows in the result that do not havematching values in the join column, use Outer

join.

x R S

(Left) outer join is join in which tuples fromR that do not have matching values in

common columns of S are also included inresult relation.



30/38

30

Example - Left Outer join

x Produce a status report on property viewings.

propertyNo, street, city(PropertyForRent)Viewing



31/38

31

Semijoin

x R F S

Defines a relation that contains the tuples of R that

participate in the join of R with S.

x Can rewrite Semijoin using Projection and Join:

R F S = A(R F S)



32/38

32

Example - Semijoin

x List complete details of all staff who work at the

branch in Glasgow.

Staff Staff.branchNo=Branch.branchNo(city=Glasgow(Branch))



33/38

33

Division

x R S Defines a relation over the attributes C that consists of set

of tuples from R that match combination ofevery tuple inS.

x Expressed using basic operations:

T1C(R)T2C((S X T1) R)TT1 T2



34/38

34

Example - Division

x Identify all clients who have viewed all properties

with three rooms.

(clientNo, propertyNo(Viewing)) (propertyNo(rooms = 3 (PropertyForRent)))



35/38

35

Aggregate Operations

x AL(R)Applies aggregate function list, AL, to R to

define a relation over the aggregate list.

AL contains one or more(, ) pairs.

x Main aggregate functions are: COUNT, SUM,

AVG, MIN, and MAX.



36/38

36

Example Aggregate Operations

x How many properties cost more than 350 per month to

rent?

R(myCount) COUNTpropertyNo (rent > 350(PropertyForRent))



37/38

37

Grouping Operation

xGAAL(R)Groups tuples of R by grouping attributes, GA,

and then applies aggregate function list, AL, to

define a new relation.AL contains one or more

(, ) pairs.

Resulting relation contains the grouping

attributes, GA, along with results of each of theaggregate functions.



38/38

38

Example Grouping Operation

x Find the number of staff working in each branch and the

sum of their salaries.

R(branchNo, myCount, mySum)branchNo COUNT staffNo,SUM salary (Staff)


ch04relationalalgebra-110310232134-phpapp01

Documents