Download - ch04relationalalgebra-110310232134-phpapp01
-
8/2/2019 ch04relationalalgebra-110310232134-phpapp01
1/38
Chapter 4
Relational Algebra
Pearson Education 2009
-
8/2/2019 ch04relationalalgebra-110310232134-phpapp01
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.
Pearson Education 2009
-
8/2/2019 ch04relationalalgebra-110310232134-phpapp01
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.
Pearson Education 2009
-
8/2/2019 ch04relationalalgebra-110310232134-phpapp01
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.
Pearson Education 2009
-
8/2/2019 ch04relationalalgebra-110310232134-phpapp01
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.
Pearson Education 2009
-
8/2/2019 ch04relationalalgebra-110310232134-phpapp01
6/38
6
Relational Algebra Operations
Pearson Education 2009
-
8/2/2019 ch04relationalalgebra-110310232134-phpapp01
7/38
7
Relational Algebra Operations
Pearson Education 2009
-
8/2/2019 ch04relationalalgebra-110310232134-phpapp01
8/38
-
8/2/2019 ch04relationalalgebra-110310232134-phpapp01
9/38
9
Example - Selection (or Restriction)
x List all staff with a salary greater than 10,000.
salary > 10000 (Staff)
Pearson Education 2009
-
8/2/2019 ch04relationalalgebra-110310232134-phpapp01
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.
Pearson Education 2009
-
8/2/2019 ch04relationalalgebra-110310232134-phpapp01
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)
Pearson Education 2009
-
8/2/2019 ch04relationalalgebra-110310232134-phpapp01
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.
Pearson Education 2009
-
8/2/2019 ch04relationalalgebra-110310232134-phpapp01
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)
Pearson Education 2009
-
8/2/2019 ch04relationalalgebra-110310232134-phpapp01
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.
Pearson Education 2009
-
8/2/2019 ch04relationalalgebra-110310232134-phpapp01
15/38
15
Example - Set Difference
x List all cities where there is a branch office but no
properties for rent.
city(Branch) city(PropertyForRent)
Pearson Education 2009
-
8/2/2019 ch04relationalalgebra-110310232134-phpapp01
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)
Pearson Education 2009
-
8/2/2019 ch04relationalalgebra-110310232134-phpapp01
17/38
17
Example - Intersection
x List all cities where there is both a branch office
and at least one property for rent.
city(Branch) city(PropertyForRent)
Pearson Education 2009
-
8/2/2019 ch04relationalalgebra-110310232134-phpapp01
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.
Pearson Education 2009
-
8/2/2019 ch04relationalalgebra-110310232134-phpapp01
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))
Pearson Education 2009
-
8/2/2019 ch04relationalalgebra-110310232134-phpapp01
20/38
Cartesian Product
20
-
8/2/2019 ch04relationalalgebra-110310232134-phpapp01
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.
Pearson Education 2009
-
8/2/2019 ch04relationalalgebra-110310232134-phpapp01
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.
Pearson Education 2009
-
8/2/2019 ch04relationalalgebra-110310232134-phpapp01
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
Pearson Education 2009
-
8/2/2019 ch04relationalalgebra-110310232134-phpapp01
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 (, , =, ).
Pearson Education 2009
-
8/2/2019 ch04relationalalgebra-110310232134-phpapp01
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.
Pearson Education 2009
-
8/2/2019 ch04relationalalgebra-110310232134-phpapp01
26/38
26
Example - Equijoin
x List the names and comments of all clients who
have viewed a property for rent.
(clientNo, fName, lName(Client)) Client.clientNo = Viewing.clientNo(clientNo, propertyNo, comment(Viewing))
Pearson Education 2009
-
8/2/2019 ch04relationalalgebra-110310232134-phpapp01
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
Pearson Education 2009
-
8/2/2019 ch04relationalalgebra-110310232134-phpapp01
28/38
28
Example - Natural join
x List the names and comments of all clients who
have viewed a property for rent.
(clientNo, fName, lName(Client))(clientNo, propertyNo, comment(Viewing))
Pearson Education 2009
-
8/2/2019 ch04relationalalgebra-110310232134-phpapp01
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.
Pearson Education 2009
-
8/2/2019 ch04relationalalgebra-110310232134-phpapp01
30/38
30
Example - Left Outer join
x Produce a status report on property viewings.
propertyNo, street, city(PropertyForRent)Viewing
Pearson Education 2009
-
8/2/2019 ch04relationalalgebra-110310232134-phpapp01
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)
Pearson Education 2009
-
8/2/2019 ch04relationalalgebra-110310232134-phpapp01
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))
Pearson Education 2009
-
8/2/2019 ch04relationalalgebra-110310232134-phpapp01
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
Pearson Education 2009
-
8/2/2019 ch04relationalalgebra-110310232134-phpapp01
34/38
34
Example - Division
x Identify all clients who have viewed all properties
with three rooms.
(clientNo, propertyNo(Viewing)) (propertyNo(rooms = 3 (PropertyForRent)))
Pearson Education 2009
-
8/2/2019 ch04relationalalgebra-110310232134-phpapp01
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.
Pearson Education 2009
-
8/2/2019 ch04relationalalgebra-110310232134-phpapp01
36/38
36
Example Aggregate Operations
x How many properties cost more than 350 per month to
rent?
R(myCount) COUNTpropertyNo (rent > 350(PropertyForRent))
Pearson Education 2009
-
8/2/2019 ch04relationalalgebra-110310232134-phpapp01
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.
Pearson Education 2009
-
8/2/2019 ch04relationalalgebra-110310232134-phpapp01
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)
Pearson Education 2009