RELATIONAL CALCULUSRELATIONAL CALCULUS

Prof. Sin-Min LeeProf. Sin-Min Lee

Department of Computer Department of Computer ScienceScience

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

What is Tuple Relational What is Tuple Relational Calculus?Calculus?

It is a non procedural query language: It is a non procedural query language: Describes the desired information Describes the desired information without giving a specific procedure for without giving a specific procedure for obtaining that information.obtaining that information.

A query in tuple relational calculus is A query in tuple relational calculus is expresses as:expresses as:

{t | P(t)}{t | P(t)} This represents a set of all tuples t such This represents a set of all tuples t such

that predicate P is true for t.that predicate P is true for t.

Sample QueriesSample Queries Finding the branch – name, loan – Finding the branch – name, loan –

number, and amount for loans of number, and amount for loans of over $ 1200over $ 1200

{t | t {t | t loan loan ٨٨ t[amount] > 1200}t[amount] > 1200} In English this query would mean: In English this query would mean:

The set of tuples t where t belongs to The set of tuples t where t belongs to the loan relation and the loan amount the loan relation and the loan amount for each t is greater than $ 1200.for each t is greater than $ 1200.

Sample queries(cont….)Sample queries(cont….) Finding the loan – number for each loan of Finding the loan – number for each loan of

an amount greater than $1200.an amount greater than $1200. {t | {t | s s loan (t[loan – number] = loan (t[loan – number] =

s[loan – number] s[loan – number] ۸۸ s[amount > 1200)}s[amount > 1200)} In English we would read the preceding In English we would read the preceding

statement as “ The set of all tuples t such statement as “ The set of all tuples t such that there exists a tuple s in relation loan that there exists a tuple s in relation loan for which the values of t and s for the loan – for which the values of t and s for the loan – number attribute are equal, and the value number attribute are equal, and the value of s for the amount attribute is greater than of s for the amount attribute is greater than $1200.”$1200.”

Sample Queries(cont….)Sample Queries(cont….) The expression:The expression:

{t | {t | r r customer (r[customer – name] = customer (r[customer – name] = t[customer – name]) t[customer – name]) ٨٨ (( u u branch (u[branch – branch (u[branch – city] = Brooklyn” city] = Brooklyn” s s depositor (t[customer depositor (t[customer – name] = s[customer – name] – name] = s[customer – name] w w account account (w[account – number- = s[account – number] (w[account – number- = s[account – number] ٨٨ w[branch – name] = u[branch – name]))))}w[branch – name] = u[branch – name]))))}

In English, would mean “The set of all customers( i.e In English, would mean “The set of all customers( i.e customer name tuples t) such that for all tuples u in the customer name tuples t) such that for all tuples u in the branch relation, if the value of u on attribute branch – branch relation, if the value of u on attribute branch – city is Brooklyn, then the customer has an account at city is Brooklyn, then the customer has an account at the branch whose name appears in the branch name the branch whose name appears in the branch name attribute of u”attribute of u”

Formal DefinitionFormal Definition As shown earlier, a tuple relational calculus expression As shown earlier, a tuple relational calculus expression

is of the formis of the form {t | P(t)}{t | P(t)} Where P is a formula.Where P is a formula. A formula may contain several tuple variables.A formula may contain several tuple variables. A tuple variable is said to be a free variable unless it is A tuple variable is said to be a free variable unless it is

quantified by a quantified by a З(there exists) or Џ(for all).З(there exists) or Џ(for all). Tuple variables that are quantified by Tuple variables that are quantified by З or Џ are called З or Џ are called

bound variables.bound variables. For ex, in the expression:For ex, in the expression:

t t loan loan ٨٨ s s customer(t[branch – name] = customer(t[branch – name] = s[branch – name])s[branch – name])

t is a free variable and s is a bound variable.t is a free variable and s is a bound variable.

Formal definition(cont….)Formal definition(cont….) A formula in relational calculus is made up A formula in relational calculus is made up

of atoms.of atoms. An atom has one of the following forms.An atom has one of the following forms.1.1. S S Є r, where s is a tuple variable and r is a relation.Є r, where s is a tuple variable and r is a relation.2.2. S[x] © u[y], where s and u are tuple variables, x is S[x] © u[y], where s and u are tuple variables, x is

an attribute on which s is defined, y is an attribute an attribute on which s is defined, y is an attribute on which u is defined, and © is the comparison on which u is defined, and © is the comparison operator. It is required that x and y have domains operator. It is required that x and y have domains that can be compared using ©.that can be compared using ©.

3.3. S[x] © c, where s is a tuple variable, x is an S[x] © c, where s is a tuple variable, x is an attribute on which s is defined, © is a comparison attribute on which s is defined, © is a comparison operator, and c is a constant in the domain of x.operator, and c is a constant in the domain of x.

Building a formulaBuilding a formula

We build up a formula from atoms by using We build up a formula from atoms by using the following rules.the following rules.

An atom is a formula.An atom is a formula. If P1 is a formula, then so are If P1 is a formula, then so are ¬P1 and (P1).¬P1 and (P1). If P1 and P2 are formulae, then so are P1 If P1 and P2 are formulae, then so are P1 ٧٧

P2, P1 P2, P1 ٨٨ P2, and P1 => P2.P2, and P1 => P2. If P1(s) is a formula containing a free tuple If P1(s) is a formula containing a free tuple

variable s, and r is a relation, thenvariable s, and r is a relation, then s s r(P1(s)) and r(P1(s)) and s s (P1(s)) (P1(s))

are also formulaeare also formulae

Safety of Expressions – Safety of Expressions – The concept of domainThe concept of domain A tuple relational calculus may generate an A tuple relational calculus may generate an

infinite relation. For ex:infinite relation. For ex: {t | {t | ¬(t ¬(t loan)} loan)} There are infinitely many tuples that are not in loan.There are infinitely many tuples that are not in loan. To address this issue, we use the concept of domain of To address this issue, we use the concept of domain of

tuple relational formula, P.tuple relational formula, P. The domain of P, denoted dom(P) , is a set of all value The domain of P, denoted dom(P) , is a set of all value

referenced by P.These include values mentioned in P referenced by P.These include values mentioned in P itself, as well as the values that appear in a tuple of a itself, as well as the values that appear in a tuple of a relation mentioned in P.relation mentioned in P.

For ex: dom(t For ex: dom(t loan loan ٨٨ t[amount] > 1200) is the set t[amount] > 1200) is the set containing 1200 as well as set of all values appearing in containing 1200 as well as set of all values appearing in loan.loan.

Domain Relational Domain Relational CalculusCalculus

This is the second form of relational This is the second form of relational calculuscalculus

Uses domain variables that take on Uses domain variables that take on values from an attribute domain, values from an attribute domain, rather than values for an entire rather than values for an entire tuple.tuple.

Closely related to tuple relational Closely related to tuple relational calculus.calculus.

Formal DefinitionFormal Definition

A general expression in Domain A general expression in Domain relational calculus is of the formrelational calculus is of the form

{<x1, x2,…., xn> | P(x1,x2,….,xn)}{<x1, x2,…., xn> | P(x1,x2,….,xn)}

where x1, x2,….,xn represent domain where x1, x2,….,xn represent domain variables. P represents a formula variables. P represents a formula composed of atoms, as was the case composed of atoms, as was the case in tuple relational calculus.in tuple relational calculus.

An atom in Domain An atom in Domain Relational CalculusRelational Calculus

An atom in domain relational calculus has one An atom in domain relational calculus has one of the following forms:of the following forms:

<x1,x2,….,xn> <x1,x2,….,xn> r, where r is a relation on n r, where r is a relation on n attributes and x1, x2, ….,xn are domain values or attributes and x1, x2, ….,xn are domain values or domain constraints.domain constraints.

X © y, where x and y are domain values, and © is a X © y, where x and y are domain values, and © is a comparison operator (<, <=,=,>,>=). We require comparison operator (<, <=,=,>,>=). We require that attributes x and y have domains that can be that attributes x and y have domains that can be compared by ©.compared by ©.

X X © c, where x is a domain variable, © is a © c, where x is a domain variable, © is a comparison operator, and c is a constant in the comparison operator, and c is a constant in the domain of the attribute for which x is a domain domain of the attribute for which x is a domain variable.variable.

Example QueriesExample Queries Find the loan number, branch name, and Find the loan number, branch name, and

amount for loans over $1200.amount for loans over $1200. {<l,b,a> | <l,b,a> {<l,b,a> | <l,b,a> loan loan ٨٨ a > 1200}a > 1200} Find all loan numbers for loans with an amount > Find all loan numbers for loans with an amount >

1200:1200:

{<l> | {<l> | b,a (<l,b,a> b,a (<l,b,a> loan loan ٨٨ a > 1200)}a > 1200)} When we write When we write b in domain calculus, b refers b in domain calculus, b refers

not to a tuple, but rather to a domain value.not to a tuple, but rather to a domain value. The domain of b is unconstrained(unlike as in The domain of b is unconstrained(unlike as in

relational tuple calculus) until the sub formula relational tuple calculus) until the sub formula <l,b,a> <l,b,a> loan constraints b to branch names that loan constraints b to branch names that appear in the loan relation.appear in the loan relation.

Example queries(cont….)Example queries(cont….) Find the names of all customers who have an Find the names of all customers who have an

account at all branches located in Brooklyn:account at all branches located in Brooklyn: {<c> | {<c> | n(<c,n> n(<c,n> customer) customer) ٨٨ x,y,z x,y,z

(<x,y,z> (<x,y,z> branch branch ٨٨ y = “Brooklyn” => y = “Brooklyn” => a,b(<a,x,b> a,b(<a,x,b> account account ٨٨ <c,a> <c,a> depositor))} depositor))}

In English, we interpret this expression as “The set of In English, we interpret this expression as “The set of all(customer name) tuples c such that, for all all(customer name) tuples c such that, for all (branch – name, branch – city, assets) tuples, x,y,z, (branch – name, branch – city, assets) tuples, x,y,z, if the branch city is Brooklyn, then the following is if the branch city is Brooklyn, then the following is true:true:

1.1. There exists a tuple in the relation account with There exists a tuple in the relation account with account number a and branch name x.account number a and branch name x.

2.2. There exists a tuple in the relation depositor with There exists a tuple in the relation depositor with customer c and account number a.”customer c and account number a.”

Safety of ExpressionsSafety of Expressions

As noted in tuple relational calculus As noted in tuple relational calculus expressions in the domain expressions in the domain relational calculus may also relational calculus may also generate an infinite relation. For ex:generate an infinite relation. For ex:

{{<l,b,a> | {{<l,b,a> | ¬ (<l,b,a> ¬ (<l,b,a> loan)} loan)} is unsafe, because it allows values in the is unsafe, because it allows values in the

result that are not in the domain of the result that are not in the domain of the expression.expression.

Safety(cont….)Safety(cont….)

An expression:An expression: {<x1,x2,….,xn> | P(x1,x2,….,xn)}{<x1,x2,….,xn> | P(x1,x2,….,xn)} is safe if all of the following hold.is safe if all of the following hold. All values that appear in tuples of the expression All values that appear in tuples of the expression

are values from dom(P).are values from dom(P). For every “there exists” subformula of the form For every “there exists” subformula of the form Э Э

x(P1(x)), the sub formula is true only if and only if x(P1(x)), the sub formula is true only if and only if there is a value x in dom(P1) such that P1(x) is there is a value x in dom(P1) such that P1(x) is true.true.

For every “for all” subformula of the form For every “for all” subformula of the form x(P1(x)), the subformula is true if and only if x(P1(x)), the subformula is true if and only if P1(x) is true for all values of x from dom(P1).P1(x) is true for all values of x from dom(P1).

IntroductionIntroduction Tuple-Oriented Relational Tuple-Oriented Relational

CalculusCalculus Computational CapabilitiesComputational Capabilities Domain-Oriented Domain-Oriented

Relational CalculusRelational Calculus

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

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.

What is Tuple Variables?What is Tuple Variables?A tuple variable is a A tuple variable is a

variable that variable that “range over” a “range over” a named relation: named relation: that is a variable that is a variable whose only whose only permitted values permitted values are tuples of the are tuples of the relation.relation.

EXAMPLESEXAMPLES

To specify the range of a tuple To specify the range of a tuple variable T as the Staff variable T as the Staff relation, we write:relation, we write:

RANGE OF T IS STAFFRANGE OF T IS STAFF

To express the the query ‘Find To express the the query ‘Find the set of all tuples T such the set of all tuples T such that P(T) is true’ we can writethat P(T) is true’ we can write

{ T | P(T) }{ T | P(T) }

Free and Bound VariablesFree and Bound Variables

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

A tuple variable T occurs A tuple variable T occurs

within a WFF either in thewithin a WFF either in the

context of an attribute context of an attribute

reference of the form T.Areference of the form T.A

(where A is an attribute of the (where A is an attribute of the relation over which T ranges) relation over which T ranges) or as the variable or as the variable immediately following one of immediately following one of the quantifiers EXISTS and the quantifiers EXISTS and FORALLFORALL

RulesRules Within a simple comparison such as Within a simple comparison such as

“ “T.A < U.A” all occurrences are freeT.A < U.A” all occurrences are free Occurrences in the WFFs “(f)” andOccurrences in the WFFs “(f)” and

“ “NOT f” are free or bound according as NOT f” are free or bound according as they are free or bound in f. Occurrences they are free or bound in f. Occurrences in the WFFs “f AND g”, in the WFFs “f AND g”,

“ “f OR g”, and “IF f THEN g” are free or f OR g”, and “IF f THEN g” are free or bound according as they are free or bound according as they are free or bound in f or gbound in f or g

ExampleExample Simple Comparison:Simple Comparison: SX.S= = ‘S1’ SX.S= = ‘S1’ (Range of SX is S) here occurrence (Range of SX is S) here occurrence

SX is freeSX is free Boolean WFFs:Boolean WFFs: - PX.WEIGHT < 15 OR PX.WEIGHT >25- PX.WEIGHT < 15 OR PX.WEIGHT >25 - NOT ( SX.CITY = ‘London’ )- NOT ( SX.CITY = ‘London’ ) here PX and SX are freehere PX and SX are free

QUANTIFIERSQUANTIFIERS

There are two There are two quantifiers:quantifiers:

1/ EXISTS1/ EXISTS

2/ FORALL2/ FORALL

EXISTSEXISTS EXISTS x (f) means EXISTS x (f) means

there exists there exists at at leastleast one valueone value of of the variable x such that the the variable x such that the WFF f evaluates to true. If f is WFF f evaluates to true. If f is a WFF in which variable x is a WFF in which variable x is free and bound in ffree and bound in f

ExampleExampleLet relation R contain the following Let relation R contain the following

tuplestuples

( 1, 2, 3 ); ( 1, 2, 4 ); ( 1, 3, 4 )( 1, 2, 3 ); ( 1, 2, 4 ); ( 1, 3, 4 )

EXISTS T ( T[3] > 1 ) : trueEXISTS T ( T[3] > 1 ) : true

EXISTS T ( T[2] > 3 ) : falseEXISTS T ( T[2] > 3 ) : false

EXISTS T ( T[1] > 1 OR T[3] = 4 EXISTS T ( T[1] > 1 OR T[3] = 4 truetrue

FORALLFORALL

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

ExampleExample

FORALL PX ( PX.COLOR = ‘Red’ FORALL PX ( PX.COLOR = ‘Red’ ))

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

More ExampleMore Example

Let relation R contain the following Let relation R contain the following tuplestuples

( 1, 2, 3 ); ( 1, 2, 3 ); ( 1, 3, 4 )( 1, 2, 3 ); ( 1, 2, 3 ); ( 1, 3, 4 )

FORALL T ( T[1] > 1 )FORALL T ( T[1] > 1 ) falsefalse

FORALL T ( T[2] > 1 ) trueFORALL T ( T[2] > 1 ) true

FORALL T ( T[1] = 1 AND T[3] > 2 )FORALL T ( T[1] = 1 AND T[3] > 2 )

true true

TARGET ITEM TARGET ITEM COMMALISTSCOMMALISTS

Each target item in a target item Each target item in a target item commalist is either a simple variable commalist is either a simple variable name such as name such as T.A [ AS X ]T.A [ AS X ]

Here T is a tuple variable, A is an Here T is a tuple variable, A is an attribute of the associated relation, attribute of the associated relation, and X is an attribute name for the and X is an attribute name for the corresponding attribute in the result corresponding attribute in the result of evaluating the target item of evaluating the target item commalistcommalist

What is the target item?What is the target item?

Target Item that is just a Target Item that is just a variable name T is defined to variable name T is defined to be shorthand for the commalist be shorthand for the commalist of target itemsof target items

T.A1, T.A2, T.A3,……, T.AnT.A1, T.A2, T.A3,……, T.Anwhere A1, A2, … An are all of the where A1, A2, … An are all of the

attributes of the relation attributes of the relation associated with Tassociated with T

ExamplesExamples

- ( - ( SX.S# ) SX.S# )

- ( SX.S# AS SNO )- ( SX.S# AS SNO )

- ( SX.S# AS SNO ) WHERE- ( SX.S# AS SNO ) WHERE

SX.CITY = ‘London’SX.CITY = ‘London’

- ( SX.S# ) WHERE SX.CITY = - ( SX.S# ) WHERE SX.CITY = ‘London’‘London’

Computational Computational CapabilitiesCapabilities

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

Aggregate-Function is COUNT, SUM, Aggregate-Function is COUNT, SUM, AVG, MAX, or MINAVG, MAX, or MIN

Syntax for aggregate-function Syntax for aggregate-function reference:reference:

aggregate-function (expression[,attribute])aggregate-function (expression[,attribute])

* expression is an expression of the * expression is an expression of the tuple calculus, and attribute is that tuple calculus, and attribute is that attribute of that result relation over attribute of that result relation over which the aggregation is to be donewhich the aggregation is to be done

Domain-Oriented Domain-Oriented Relational CalculusRelational Calculus

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

ExamplesExamples- ( - ( SX )SX ) denotes the set of all supplier numbersdenotes the set of all supplier numbers- ( SX ) WHERE S ( S#: SX )- ( SX ) WHERE S ( S#: SX ) denotes the set of all supplier numbers denotes the set of all supplier numbers

in relation Sin relation S- ( SX ) WHERE S ( S#:SX, CITY: ‘London’- ( SX ) WHERE S ( S#:SX, CITY: ‘London’ denotes that subset of those supplier denotes that subset of those supplier

numbers for which the city is Londonnumbers for which the city is London

DEFINITION (cont’d)DEFINITION (cont’d)

Target listTarget list:: A condition in a relational A condition in a relational calculus statement that restricts membership calculus statement that restricts membership in a solution relation.in a solution relation.

Existential quantifierExistential quantifier:: Relational calculus Relational calculus expression affirming the existence of a at least expression affirming the existence of a at least one row to which a condition applies.one row to which a condition applies.

Universal quantifierUniversal quantifier:: Relational calculus Relational calculus expression stating that some condition applies expression stating that some condition applies to every row of some type.to every row of some type.

CONSTRUCTION OF CONSTRUCTION OF RELATIONAL CALCULUSRELATIONAL CALCULUS

In Relational Calculus, queries or expressions, are constructed as In Relational Calculus, queries or expressions, are constructed as well-formed formulas. A well-formed formula (WFF) is an well-formed formulas. A well-formed formula (WFF) is an expression composed ultimately of atomic predicates. A WFF is expression composed ultimately of atomic predicates. A WFF is itself a predicate, which assumes a true or false value depending itself a predicate, which assumes a true or false value depending on the argument values in its constituent atomic predicates.on the argument values in its constituent atomic predicates.

CONSTRUCTION OF CONSTRUCTION OF RELATIONAL CALCULUS RELATIONAL CALCULUS

(cont’d)(cont’d)a b a (a b) (a b) (a b)

T T F T T TT F F F T FF T T F T TF F T F F T

Figure 4.1 Truth tables for logical operators

CONSTRUCTION OF CONSTRUCTION OF RELATIONAL CALCULUS RELATIONAL CALCULUS

(cont’d)(cont’d)For example, if a, b are two WFFs, then (a For example, if a, b are two WFFs, then (a b) is also a b) is also aWFF. (a WFF. (a b) is true for precisely those argument values of b) is true for precisely those argument values of

aaand b for which both a and b are true. Figure 4.1 gives theand b for which both a and b are true. Figure 4.1 gives thebasic set of logical operators that can be used to produce basic set of logical operators that can be used to produce

newnewWFFs from existing WFFs; it also shows the truth values ofWFFs from existing WFFs; it also shows the truth values ofthe new WFF as a consequence of the truth values of thethe new WFF as a consequence of the truth values of thecomponents.components.

WAY OF CONSTRUCTING WAY OF CONSTRUCTING A RELATIONAL CALCULUS A RELATIONAL CALCULUS

EXPRESSIONEXPRESSION

We can construct a relational-calculus expression, We can construct a relational-calculus expression, or WFF, inor WFF, in

the following ways:the following ways:• A predicate P is a WFFA predicate P is a WFF• If P is a WFF, then so are (P) and ~(P)If P is a WFF, then so are (P) and ~(P)• If P1 and P2 are WFF’s, then so are (P1 If P1 and P2 are WFF’s, then so are (P1 P2) and P2) and

(P1 V P2)(P1 V P2)

WAY OF CONSTRUCTING WAY OF CONSTRUCTING A RELATIONAL CALCULUS A RELATIONAL CALCULUS

EXPRESSION (cont’d)EXPRESSION (cont’d)• If P is WFF in which t occurs as a free variable, If P is WFF in which t occurs as a free variable,

thenthen so are so are t(P) and t(P) and t(P) t(P)• nothing else is a WFFnothing else is a WFF

Symbols Symbols and and are called quantifiers. Symbol are called quantifiers. Symbol is calledis calledthe existential quantifier and is interpreted or read as the existential quantifier and is interpreted or read as

“three“three

exists” and exists” and is called the universal quantifier and is called the universal quantifier and interpretedinterpreted

as “for all.”as “for all.”

WAY OF CONSTRUCTING WAY OF CONSTRUCTING A RELATIONAL CALCULUSA RELATIONAL CALCULUS

EXPRESSION (cont’d)EXPRESSION (cont’d)An example of existential quantification:An example of existential quantification:

Q(w, x) = Q(w, x) = ( y, z)(P(w, x, y, z)) ( y, z)(P(w, x, y, z)) (w, x) is a fixed pair(w, x) is a fixed pair If P(w, x, y, z) is true for some choice of a (y, z) pair, If P(w, x, y, z) is true for some choice of a (y, z) pair, Q(w, x) is true.Q(w, x) is true. Otherwise it is false.Otherwise it is false. w, x, y, z are free variables in P(w, x, y, z)w, x, y, z are free variables in P(w, x, y, z) However, y and z are not free variables in the However, y and z are not free variables in the

expression expression (( y, z)(P(w, x, y, z)) y, z)(P(w, x, y, z))

SELECTION VIA A SELECTION VIA A PREDICATEPREDICATE

The following example: The following example:

{t |tank(t) {t |tank(t) tcolor(t) = “red”} tcolor(t) = “red”} The automatic predicate tank(t) responds true The automatic predicate tank(t) responds true

when the tuple t is element of the tank relationwhen the tuple t is element of the tank relation The function tcolor(t) returns the tcolor value The function tcolor(t) returns the tcolor value

of the tuple t, and the atomic predicate of the tuple t, and the atomic predicate tcolor(t) = “red” tests the returned value tcolor(t) = “red” tests the returned value against a constant.against a constant.

Therefore the complete predicate (WFF) Therefore the complete predicate (WFF) isolates the tuples corresponding to red tanks.isolates the tuples corresponding to red tanks.

SELECTION VIA A SELECTION VIA A PREDICATE (cont’d)PREDICATE (cont’d)

The need for two types of atomic predicates:The need for two types of atomic predicates: The first type tests membership in some named The first type tests membership in some named

relation. Each table in the data base gives rise relation. Each table in the data base gives rise to an atomic predicate of the same name, which to an atomic predicate of the same name, which takes one argument. The predicate returns true takes one argument. The predicate returns true when the argument is a tuple within the table, when the argument is a tuple within the table, and it returns false in all other cases. and it returns false in all other cases. For example, tank(t) is true when the tuple t is For example, tank(t) is true when the tuple t is an element of the tank relation, and it’s false an element of the tank relation, and it’s false otherwise.otherwise.

SELECTION VIA A SELECTION VIA A PREDICATE (cont’d)PREDICATE (cont’d)

The second type of atomic predicate permits The second type of atomic predicate permits judgments about the attribute values within tuples. judgments about the attribute values within tuples. When the argument is a tuple that contains an When the argument is a tuple that contains an association over the named attribute, the the association over the named attribute, the the function returns the corresponding value. If the function returns the corresponding value. If the tuple doesn’t contain such an association, then the tuple doesn’t contain such an association, then the function returns the value null. function returns the value null.

For example, tcolor(t) returns the tcolor value of For example, tcolor(t) returns the tcolor value of the tuple the tuple tt, that is, red, blue, or green, if , that is, red, blue, or green, if tt contains contains a tcolor value. Otherwise, tcolor(t) returns null.a tcolor value. Otherwise, tcolor(t) returns null.

Universal queriesUniversal queries

Relational calculus expresses a universal query by Relational calculus expresses a universal query by assigning free variables to the relation providing assigning free variables to the relation providing the candidates. It then introduces a universally the candidates. It then introduces a universally quantified component, stating (quantified component, stating (t)((t is an anchor) t)((t is an anchor) => (t is reachable from the candidate))=> (t is reachable from the candidate))To find the names of species represented in all To find the names of species represented in all tanks that contain representatives of all species tanks that contain representatives of all species with a diet of worms. Follow a stepwise with a diet of worms. Follow a stepwise construction proceeds:construction proceeds:Q = {sname(s) | species(s) Q = {sname(s) | species(s) ((t)(P1(t) => (t)(P1(t) => ( f) f)(fish(f) (fish(f)

snow(f) = snow(s) snow(f) = snow(s) tno(f) = tno(t)))} tno(f) = tno(t)))}

Universal queries (cont’d)Universal queries (cont’d)

if P1(t) is true when t is a tank that contains all the if P1(t) is true when t is a tank that contains all the wormy species. wormy species. P1(t) = tank(t) P1(t) = tank(t) ( (s1)(P2(s1) => (s1)(P2(s1) => ( f1)(fish(f1) f1)(fish(f1)

snow(f1) = snow(s1) snow(f1) = snow(s1) tno(f1) = tno(t))) tno(f1) = tno(t)))P2(s1) is true when s1 is a wormy species. P2(s1) is true when s1 is a wormy species.

Therefore,Therefore,P2(s1) = species(s1) P2(s1) = species(s1) sfood(s1) = “worms” sfood(s1) = “worms”

Substituting P2 into P1 and P1 into Q give a single Substituting P2 into P1 and P1 into Q give a single WFF that WFF that

effectively filters the database for the requested effectively filters the database for the requested species names.species names.

DOMAIN RELATIONAL DOMAIN RELATIONAL CALCULUSCALCULUS

In domain relational calculus the variables range In domain relational calculus the variables range over a pool of values, the union of all domains over a pool of values, the union of all domains in the database schema. The atomic in the database schema. The atomic predicates permit table membership tests on predicates permit table membership tests on assembled tuples, and they allow direct assembled tuples, and they allow direct - -comparisons between the variablescomparisons between the variables

For example, the following expression requests For example, the following expression requests the names of red tanksthe names of red tanks

{x | ( {x | ( w, y) (tank(tno = w, tname = x, tcolor = w, y) (tank(tno = w, tname = x, tcolor = “red”, tvolume “red”, tvolume

= y))}= y))}

DOMAIN RELATIONAL DOMAIN RELATIONAL CALCULUS (cont’d)CALCULUS (cont’d)

{{x | ( w, y) (tank(tno = w, tname = x, tcolor = x | ( w, y) (tank(tno = w, tname = x, tcolor = “red”, tvolume “red”, tvolume

= y))}= y))}

For a fixed candidate x, the variables w and y For a fixed candidate x, the variables w and y range over the pool of elements in the range over the pool of elements in the common domain and attempt to lock on to common domain and attempt to lock on to value such that the assembled tuple appears value such that the assembled tuple appears in the tank relation. If w and y can achieve in the tank relation. If w and y can achieve such values, x is the name of a red tank.such values, x is the name of a red tank.

VARIABLESVARIABLES

A variables is free if it is not within the scope of a A variables is free if it is not within the scope of a quantifier,quantifier,

otherwise, it is a bound variable.otherwise, it is a bound variable.Restricting the scope of tuple variable is important. Restricting the scope of tuple variable is important.

ForForexample, r(a) example, r(a) 5 may trigger the searching of the 5 may trigger the searching of the

entireentireworld, that is, there is no end to values that are not world, that is, there is no end to values that are not

equal to 5.equal to 5.

VARIABLES (cont’d)VARIABLES (cont’d)

We can fix the problem in two ways:We can fix the problem in two ways:• Add a range (scope) declaration before the WFF.Add a range (scope) declaration before the WFF.• Add a predicate into the WFF that expressesAdd a predicate into the WFF that expresses r r R, i.e.., tuple variable R, i.e.., tuple variable rr belong to relation R. belong to relation R.

The above methods guarantee us termination of the The above methods guarantee us termination of the searchsearch

process since we do not have to search beyond the process since we do not have to search beyond the relationrelation

itself.itself.

CONTROL SUB-CONTROL SUB-LANGUAGESLANGUAGES

DefinitionDefinition

The Control Sub-Languages have been provided to allow The Control Sub-Languages have been provided to allow users to access and control an Information System. In users to access and control an Information System. In

their their simplest form they allow users to create, read, update, simplest form they allow users to create, read, update,

andanddestroy information held in that system, and furthermore destroy information held in that system, and furthermore

theytheycan be used to read, create, update, and destroy thecan be used to read, create, update, and destroy thedescription of that system.description of that system.

CONTROL SUB-CONTROL SUB-LANGUAGES (cont’d)LANGUAGES (cont’d)

In addition to these commonplace tasks, the languageIn addition to these commonplace tasks, the languageprovides a set of powerful tools to allow users to provides a set of powerful tools to allow users to

identify theidentify theinformation that they require, thus ensuring that they information that they require, thus ensuring that they

do notdo nothave to bother with unnecessary details.have to bother with unnecessary details.

Design Control Sub-LanguageDesign Control Sub-Language::

It is virtually impossible to design a standard It is virtually impossible to design a standard language whichlanguage which

CONTROL SUB-CONTROL SUB-LANGUAGES (cont’d)LANGUAGES (cont’d)

can be used successfully by all those who might wish tocan be used successfully by all those who might wish tointeract with an Information System. however, it is interact with an Information System. however, it is

possiblepossibleto design a basic language which satisfies the majority ofto design a basic language which satisfies the majority ofusers and to provide extensions to that basic language. users and to provide extensions to that basic language.

ThisThisis the approach which has been adopted with the Control is the approach which has been adopted with the Control

SubSubLanguage.Language.

CONTROL SUB-CONTROL SUB-LANGUAGES (cont’d)LANGUAGES (cont’d)

Manipulating Entity SetsManipulating Entity Sets::

The descriptions above tell us how Control Sub-The descriptions above tell us how Control Sub-LanguageLanguage

provides facilities for a user to transfer information provides facilities for a user to transfer information between abetween a

given entity set and this terminal. This works well given entity set and this terminal. This works well providingproviding

the user whishes to access the whole of a single set.the user whishes to access the whole of a single set.

CONTROL SUB-CONTROL SUB-LANGUAGES (cont’d)LANGUAGES (cont’d)

However, if the user whishes to do more than this, then However, if the user whishes to do more than this, then thethe

user has, implicitly or explicitly, to create a new temporaryuser has, implicitly or explicitly, to create a new temporaryset. This temporary set exists within the user’s work set. This temporary set exists within the user’s work

spacespaceand is linked to the entity sets of the Informationand is linked to the entity sets of the InformationEnvironment by the successive use of the relational Environment by the successive use of the relational

operators.operators.

CONTROL SUB-CONTROL SUB-LANGUAGES (cont’d)LANGUAGES (cont’d)

These operators are:These operators are: Projection: {r[A]:r Projection: {r[A]:r R} R} Union: {t:t Union: {t:t R V t R V t S} S} Difference: {t:t Difference: {t:t R R t tS}S} Cartesian product: {(r || s) : r Cartesian product: {(r || s) : r R R s s S} S} Selection: {r:r Selection: {r:r R R (r[A](r[A]opr)} where opr is V or opr)} where opr is V or

r[B]r[B] Intersection: {t:t Intersection: {t:t R R t t S} S} Join: {r || s): r Join: {r || s): r R R s s S (r[A] S (r[A] s[B])} s[B])} Division: {r[A]: r Division: {r[A]: r R R s[B] s[B] (r[A]} where (r[A]} where

(r[A]) are all tuples in r[A].(r[A]) are all tuples in r[A].

SUMMARYSUMMARY

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