RELATIONAL OPERATOR II: RELATIONAL CALCULUS

1
RELATIONAL OPERATOR IIRELATIONAL CALCULUS
Lecture 10

• Prof. Sin-Min Lee
• Department of Mathematics and Computer Science

2

• Introduction
• Tuple-Oriented Relational Calculus
• Computational Capabilities
• Domain-Oriented Relational Calculus

3
INTRODUCTION

4

• Relational model is based on the relational
  algebra, but we might equally well have said it
  is based on the relational calculus. While the
  algebra provides a collection of explicit
  operations, the calculus provides a notation for
  formulation the definition of that desired
  relation in terms of those given relations.

5

• The calculus simply describes what the problem
  is, the algebra prescribes a procedure for
  solving that problem. Or we can say the algebra
  and the calculus are precisely equivalent to one
  another, because for every expression of the
  algebra, there is an equivalent expression in the
  calculus.

6
Relational calculus is founded on a branch of
mathematical logic called the predicate calculus.
The fundamental feature of the calculus as
defined in reference is the notion of the tuple
variable.
7
What is Tuple Variables?

• A tuple variable is a variable that range over
  a named relation that is a variable whose only
  permitted values are tuples of the relation.

8
EXAMPLES

• To specify the range of a tuple variable T as the
  Staff relation, we write
• RANGE OF T IS STAFF
• To express the the query Find the set of all
  tuples T such that P(T) is true we can write
• T P(T)

9
Free and Bound Variables

• Each occurrence of a tuple variable within a WFF
  ( Well-Formed Formula) is either free or bound.
  Here we mean an appearance of the variable name
  within the WFF under consideration.

10

• A tuple variable T occurs
• within a WFF either in the
• context of an attribute
• reference of the form T.A
• (where A is an attribute of the relation over
  which T ranges) or as the variable immediately
  following one of the quantifiers EXISTS and
  FORALL

11
Rules

• Within a simple comparison such as
• T.A lt U.A all occurrences are free
• Occurrences in the WFFs (f) and
• NOT f are free or bound according as they
  are free or bound in f. Occurrences in the WFFs
  f AND g,
• f OR g, and IF f THEN g are free or bound
  according as they are free or bound in f or g

12
Example

• Simple Comparison
• SX.S S1
• (Range of SX is S) here occurrence SX
  is free
• Boolean WFFs
• - PX.WEIGHT lt 15 OR PX.WEIGHT gt25
• - NOT ( SX.CITY London )
• here PX and SX are free

13
QUANTIFIERS

• There are two quantifiers
• 1/ EXISTS
• 2/ FORALL

14
EXISTS

• EXISTS x (f) means
  there exists at least one value of the
  variable x such that the WFF f evaluates to
  true. If f is a WFF in which variable x is free
  and bound in f

15
Example

• Let relation R contain the following tuples
• ( 1, 2, 3 ) ( 1, 2, 4 ) ( 1, 3, 4 )
• EXISTS T ( T3 gt 1 ) true
• EXISTS T ( T2 gt 3 ) false
• EXISTS T ( T1 gt 1 OR T3 4 true

16
FORALL

• FORALL x ( f ) means For all values of the
  variable x, the WFF f evaluates are true.

17
Example

• FORALL PX ( PX.COLOR Red )
• This WFF can be read as For all P tuples, PX
  say, the COLOR value in that tuple is Red. ( The
  occurrence of PX in this example is bound)

18
More Example

• Let relation R contain the following tuples
• ( 1, 2, 3 ) ( 1, 2, 3 ) ( 1, 3, 4 )
• FORALL T ( T1 gt 1 ) false
• FORALL T ( T2 gt 1 ) true
• FORALL T ( T1 1 AND T3 gt 2 )
• true

19
TARGET ITEM COMMALISTS

• Each target item in a target item commalist is
  either a simple variable name such as T.A AS X
  
• Here T is a tuple variable, A is an attribute of
  the associated relation, and X is an attribute
  name for the corresponding attribute in the
  result of evaluating the target item commalist

20
What is the target item?

• Target Item that is just a variable name T is
  defined to be shorthand for the commalist of
  target items
• T.A1, T.A2, T.A3,, T.An
• where A1, A2, An are all of the attributes of
  the relation associated with T

21
Examples

• - ( SX.S )
• - ( SX.S AS SNO )
• - ( SX.S AS SNO ) WHERE
• SX.CITY London
• - ( SX.S ) WHERE SX.CITY London

22
Computational Capabilities

• We just simply extend the definition of
  comparands and target-items to include a new
  category, scalar-expression, where the operands
  of such an expression in turn can include
  literal, attribute references, and/or aggregate
  function references.

23

• Aggregate-Function is COUNT, SUM, AVG, MAX, or
  MIN
• Syntax for aggregate-function reference
• aggregate-function (expression,attribute)
• expression is an expression of the tuple
  calculus, and attribute is that attribute of that
  result relation over which the aggregation is to
  be done

24
Domain-Oriented Relational Calculus
25

• In domain-oriented relational calculus, we use
  variables that take their values from domain
  instead of tuples of relations. In the
  domain-oriented relational calculus, we often
  want to test for a membership condition, to
  determine whether values belong to a relation.

26
Examples

• - ( SX )
• denotes the set of all supplier numbers
• - ( SX ) WHERE S ( S SX )
• denotes the set of all supplier numbers in
  relation S
• - ( SX ) WHERE S ( SSX, CITY London
• denotes that subset of those supplier numbers
  for which the city is London

27
SUMMARY

• Tuple-Oriented Relational Calculus
• Computational Capabilities
• Domain-Oriented Relational Calculus