# Chapter 4

Document Sample

```					Chapter 4

Relational Algebra and
Relational Calculus
Introduction
 Relationalalgebra and relational calculus are
formal query languages of the relational model.
 Relational algebra is a procedural language.
 Relational calculus is a non-procedural language.
 Both are equivalent to one another.
 Both have formal strong foundation on logic.
 Query  languages != programming languages
 A language that produces a relation that can be
derived using relational calculus is relationally
complete.

Chapter 4, CSC484                                      2
Relational Algebra
 Relational algebra operations work on one or
more relations to define another relation without
changing the original relations.
 Set language: All tuples are manipulated without
looping
 Both operands and results are relations, so output
from one operation can become input to another
operation.

 Allowsexpressions to be nested, just as in
arithmetic. This property is called closure.
Chapter 4, CSC484                                      3
Relational Algebra
   5 basic operations in relational algebra: Selection,
Projection, Cartesian product, Union, and Set
Difference.

   These perform most of the data retrieval operations
needed.

   Derived operations: Intersection, Join, Semijoin, and
Division operations.
 They can be expressed in terms of 5 basic
operations.
Chapter 4, CSC484                                      4
Relational Algebra Operations

Chapter 4, CSC484             5
Relational Algebra Operations

Chapter 4, CSC484             6
Selection (or Restriction)
 predicate
(R)
Works on a single relation R and defines a
relation that contains only those tuples (rows) of
R that satisfy the specified condition (predicate).
No duplicates in the result (Why?)
Schema of result is identical to the schema of the
input relation.

Chapter 4, CSC484                                 7
Example - Selection (or Restriction)
 List   all staff with a salary greater than \$10,000.

salary > 10000 (Staff)

Chapter 4, CSC484                                     8
Projection
 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.
It eliminates duplicates.
Some DBMSs do not eliminate duplicates,
unless the user asks for it
Schema of results contains the fields in the
projection list.

Chapter 4, CSC484                                 9
Example - Projection
 Produce   a list of salaries for all staff, showing only
staffNo, fName, lName, and salary details.

staffNo, fName, lName, salary(Staff)

Chapter 4, CSC484                                      10
Union
R   S
Union 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.
Same number of fields
Corresponding fields have the same domain

 Schema          of the result is 1st relation schema.
Chapter 4, CSC484                                       11
Example - Union
 List  all cities where there is either a branch
office or a property for rent.

city(Branch)  city(PropertyForRent)

Branch(city)                            Result(city)
Prop..(city)
London                                  London
Aberdeen
Aberdeen            London
=   Aberdeen
Glasgow                                 Glasgow
Glasgow
Bristol                                 Bristol

Chapter 4, CSC484                                         12
Set Difference
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.

of the result is 1st relation schema.
 Schema
Same definition applies to all the set operations.

Chapter 4, CSC484                                   13
Example - Set Difference
 Listall cities where there is a branch office but no
properties for rent.

city(Branch) – city(PropertyForRent)

Chapter 4, CSC484                                     14
Intersection
R S
Defines a relation consisting of the set of all
tuples that are in both R and S.
R and S must be union-compatible.

 Expressed  using basic operations:
R  S = R – (R – S)

Chapter 4, CSC484                                  15
Example - Intersection
 Listall cities where there is both a branch office
and at least one property for rent.

city(Branch)  city(PropertyForRent)

Chapter 4, CSC484                                    16
Cartesian product
R XS
Defines a relation that is the concatenation of
every tuple of relation R with every tuple of
relation S.
Each row of R is paired with each row of S.

 Result     schema has one field for each field of R and
S.

Chapter 4, CSC484                                        17
Example - Cartesian Product
   List the names and comments of all clients who have
viewed a property for rent.
(clientNo, fName, lName(Client)) X (clientNo, propertyNo,comment
(Viewing))

Chapter 4, CSC484                                                   18
Example –
Cartesian Product and Selection
   Use selection operation to extract those tuples where
Client.clientNo = Viewing.clientNo.
Client.clientNo = viewing.clientNo((clientNo,fName,lName(Client)) 
(clientNo,propertyNo,comment(Viewing)))

 Cartesian product and Selection can be reduced to a single
operation called a Join.
Chapter 4, CSC484                                                       19
Join Operations
 Join   is a derivative of Cartesian product.

 Equivalent  to performing a Selection, using join
predicate as selection formula, over Cartesian
product of the two operand relations.

 One   of the most difficult operations to implement
efficiently in an RDBMS and one reason why
RDBMSs have intrinsic performance problems.

Chapter 4, CSC484                                    20
Join Operations
forms of join operation
 Various
Theta join
Equijoin (a particular type of Theta join)
Natural join
Outer join
Semijoin

Chapter 4, CSC484                             21
Theta join (-join)
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.bi
where  may be one of the comparison
operators (<, , >, , =, ).

Chapter 4, CSC484                                 22
Theta join (-join)
Cartesian product operations.

R         FS   = F(R  S)

 Degreeof a Theta join is sum of degrees of the
operand relations R and S. If predicate F contains
only equality (=), the term Equijoin is used.

Chapter 4, CSC484                                 23
Example - Equijoin
 Listthe 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))

Chapter 4, CSC484                                           24
Natural Join
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.

Chapter 4, CSC484                               25
Example - Natural Join
 Listthe names and comments of all clients who
have viewed a property for rent.
(clientNo,fName,lName(Client))
(clientNo,propertyNo,comment(Viewing))

Chapter 4, CSC484                              26
Another example of a Natural join
   Identify all clients who have viewed properties
with three or four rooms.
clientNo((Viewing) (propertyNo(rooms = 3 or
rooms = 4 (PropertyForRent))))

   What happens if or is replaced by and ?

Chapter 4, CSC484                                         27
Outer join
 To  display rows in the result that do not have
matching values in the join column, use Outer
join.

R    S
(Left) outer join is join in which tuples from
R that do not have matching values in
common columns of S are also included in
result relation.

Chapter 4, CSC484                                 28
Example - Left Outer join
 Produce         a status report on property viewings.

propertyNo,street,city(PropertyForRent)
Viewing

Chapter 4, CSC484                                       29
Semijoin
R      FS
Defines a relation that contains the tuples of R that
participate in the join of R with S.
Result schema is the schema of the first relation.

R      FS        = A(R   F   S)

Chapter 4, CSC484                                  30
Example - Semijoin
 Listcomplete details of all staff who work at the
branch in Glasgow.

Staff Staff.brancNo = Branch.branchNo and branch.city = ‘Glasgow’
Branch

Chapter 4, CSC484                                                  31
Division
   RS
 Not supported as a primitive operator
 Let R have 2 fields, x and y; S has only field y:
R  S is the set of all x values in R such that the y
values associated with an x value in R contains all y
values in S.
 In general, x and y can be any lists of fields.
   Expressed using basic operations:
T1  x(R)
T2  x((S X T1) – R)
T  T1 – T2
Chapter 4, CSC484                                          32
Example - Division
 Identifyall clients who have viewed all properties
with three rooms.
(clientNo,propertyNo(Viewing))  (propertyNo(rooms = 3
(PropertyForRent)))

Chapter 4, CSC484                                        33
Relational Calculus
 Twoversions: tuple relational calculus (TRC) and
domain relational calculus (DRC).
 Calculususes variables, constants, operators
(comparison and logical), and quantifiers.
 TLC: variables range over tuples (tuple variable)
 DRC: variables range over domain elements (domain
variable).

 Expressions in relational calculus are called
formulas (or predicates).
 An answer to a formula is a set of tuples that make the
formula evaluate to true.
Chapter 4, CSC484                                           34
Tuple Relational Calculus
 Query          has the form: {S | p(S)}
S is a tuple variable and p(S) is a formula.
It finds the set of all tuples S such that P(S) is
true.
Tuple variable is a variable that ‘ranges over’
a named relation: ie., variable whose only
permitted values are tuples of the relation.
 Specify    range of a tuple variable S as the
Staff relation as: Staff(S)
   { S | Staff(S) }  Get all tuples of Staff relation
Chapter 4, CSC484                                    35
Example - Tuple Relational Calculus
   To find details of all staff earning more than
\$10,000:
{S | Staff(S)  S.salary > 10000}

   To find a particular attribute, such as salary,
write:
{S.salary | Staff(S)  S.salary > 10000}

 Queries           are evaluated on instances of Staff.
Chapter 4, CSC484                                      36
Tuple Relational Calculus
 Can  use two quantifiers to tell how many instances
the predicate applies to:
 Existential quantifier \$ (‘there exists’)
 Universal quantifier " (‘for all’)
 Tuple variables qualified by " or \$ are called
bound variables, otherwise called free variables.
 Variables to the left of ‘|’ must be the only free
variables in the formula p(…).
Otherwise, the answer is either T or F

Chapter 4, CSC484                                   37
Tuple Relational Calculus
   Existential quantifier used in formula that
must be true for at least one instance, such as:
Staff(S)  (\$B)(Branch(B)  (B.branchNo =
S.branchNo)  B.city = ‘London’)

   Means ‘There exists a Branch tuple that has
the same branchNo as the branchNo of the
current Staff tuple, S, and is located in
London’.

Chapter 4, CSC484                                  38
Tuple Relational Calculus
   Universal quantifier is used in statements about
every instance, such as:
("B) (B.city  ‘Paris’)

   Means ‘For all Branch tuples, the city is not
Paris’.

 Can also use ~(\$B) (B.city = ‘Paris’) which means
‘There are no branches in Paris’.
 ("B) (P(B))  ~(\$B) (~P(B))

Chapter 4, CSC484                             39
Tuple Relational Calculus
   A (well-formed) formula is made out of atoms:
 R(Si), where Si is a tuple variable and R is a relation
 Si.a1  Sj.a2, where ai is an attribute
 Si.a1  c , where c is a constant
   Can recursively build up formulas from atoms:
 An atom is a formula
 If F1 and F2 are formulas, so are their conjunction, F1  F2;
disjunction, F1  F2; and negation, ~F1
 If F is a formula with free variable X, then (\$X)(F) and ("X)(F)
are also formulas.

Chapter 4, CSC484                                                    40
Example - Tuple Relational Calculus
a)   List the names of all managers who earn
more than \$25,000.
{S.fName, S.lName | Staff(S) 
S.position = ‘Manager’  S.salary > 25000}

b)   List the staff who manage properties for
rent in Glasgow.
{S | Staff(S)  (\$P) (PropertyForRent(P)  (P.staffNo =
S.staffNo)  P.city = ‘Glasgow’)}

Chapter 4, CSC484                                         41
Example - Tuple Relational Calculus
c)     List the names of staff who currently do not
manage any properties.
{S.fName, S.lName | Staff(S)  (~(\$P)
(PropertyForRent(P)(S.staffNo = P.staffNo)))}
Or
{S.fName, S.lName | Staff(S)  ("P)
(~PropertyForRent(P) 
~(S.staffNo = P.staffNo)))}

Chapter 4, CSC484                                     42
Example - Tuple Relational Calculus
   List the names of clients who have viewed a
property for rent in Glasgow.
{C.fName, C.lName | Client(C)  ((\$V)(\$P)
(Viewing(V)  PropertyForRent(P)  (
C.clientNo = V.clientNo) 
(V.propertyNo=P.propertyNo)P.city =‘Glasgow’))}

Chapter 4, CSC484                                   43

```
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
 views: 2 posted: 9/1/2012 language: Unknown pages: 43
How are you planning on using Docstoc?