What is Relational Calculus?

What is Relational Calculus?

Relational calculus is the Non-Procedural Query Language. It is a query system wherein queries are expressed as formulas consisting of several variables and an expression involving these variables. Such formulas describe the properties of the required result relation without specifying the method of evaluating it. So, in relational calculus, there are no definitions of how to calculate the query; a relational calculus defines what is to fetch quite than how to fetch it.

Example: Consider the three tables

1. S (Suppliers) TableThe S table contains for each supplier, a supplier no., name, status code, and location.
2. P (Parts) TableThe P table contains for each part, a part number, name, color, weight, and location where the part is stored.
3. SP (Shipments) TableThe SP table contains for each shipment, a supplier no., a part number and the quantity shipped as shown in the figure:

S Table

P Table

SP Table

Types of Relational Calculus

There are two types of relational calculus which are as follows.

Tuple Relational Calculus (TRC)

Tuple Relational Calculus is the Non-Procedural Query Language. It was originally proposed by Dr.E.F. Codd in 1972. It defines the desired record without giving a particular procedure for obtaining the records.

Syntax of Tuple Relational Calculus (TRC)

{T | P (T)} or {T | Condition (T)}

Where

T is the following tuples

P (T) is the condition/formulas used to retrieve T.

A formula in tuple relational calculus is made out of atoms. An atom has one of the following structures:

1. s ∈ r, where s is tuple variable, and r is the relation.
2. s[x]θ u[y], where s and u are tuple variables, x is an attribute on which s is described, y is an attribute on which u is described, and θ is a comparison operator(<,≤,>,≥,=,≠). The attributes x and y should have domains that can be compared by q.
3. s[x]θ c, where s is a tuple variable, x is an attribute on which s is described, q is a comparison operator, and c is the constant from the domain of attribute x.

A formula is built from atoms using the following rules:

1. 1. An atom is the formula.

If P1 is a formula, then P1 and (P1) are also formulae.

1. If P1 and P2 are formulae, then P1⋀P2,P1∨P2 and P1⟹P2 are also formulae.
2. If P1(s) is a formula including free tuple variables s and r is the relation, then ∃ s ∈ r (P1(s))and ∀ s∈r (P1(s)) are also formulae.

The following formulae are similar:

1. P1⋀P2 is similar to ¬(¬P1∨P2).
2. ∀t∈r (P1(t)) is similar to ¬∃ t∈r(¬P1(t)).
3. P1⟹P2 is similar to ¬P1⋁P2.

The basic construct of tuple calculus is a tuple calculus expression. Tuple calculus expressions are made up of the following constructs or elements.

1. Tuple Variable

   A tuple variable is a variable that ‘ranges over’ some named relation, i.e., a variable whose only permitted values are tuples of that relation. For example, Get supplier number for suppliers in Karnal can be expressed as:

   RANGE OF SX is S
   RETRIEVE (SX.S#) WHERE SX.CITY=”KARNAL”.

   The tuple variable here is SX, which ranges over relation S.

   Tuple variables are denoted by uppercase letters. For example,T,U,V, etc. If the tuple variable T represents tuples T (at a given time), then the expression T.A represents the A components of T (at that time), where A is an attribute of the relation over which T ranges.

2. Conditions

   Conditions are of the form x*y, where * is any relational operator =,!=(not equal to), <,≤,>,≥ and at least one of the x & y is an expression of the form T.A, and other is either a similar expression or a constant.

   Example

   1. 1. SX.CITY=”KARNAL”.

    

   1. SX.S#=SPY.S#

3. Well-Formed Formulas (WFFs)

   A WFF is constructed from conditions, Boolean Operators (AND, OR, NOT), and quantifier (∃,∀) according to the following rules:

   • Every condition is WFF.
   • If f is a WFF, then (f) and NOT (f) are also WFFs.
   • If f & g are WFFs, then (f AND g) and (f OR g) are also WFFs.
   • If f is WFF in which T occurs as a free variable, then ∃ T(f)and ∀ T(f) is WFFs.
   • Nothing else is a WFF.

   Example

   1. 1. SX.CITY=”KARNAL”.

    

   1. 1. NOT (SX.CITY=”KARNAL”.

    

   1. 1. SX.S#=SPY.S# AND SPX.P#=”P101″.

    

   1. Free & Bound Variables: Each occurrence of a tuple variable within a WFF is either free or bound.

4. Criteria for Free & Bound Variables

   1. Within a condition, all tuple variable occurrences are free.
   2. Tuple variable occurrence in the WFFs (f), NOT (f) are free/bound according to as they are free/ bound in f.
   3. Tuple variable occurrence in the WFFs (f AND g), (f OR g) are free/bound according to as they are free/ bound in f or g.
   4. Occurrences of T that are free in f are bound in the WFFs ∃ T(f),∀ T(f). Other tuple variable occurrences in f are free/bound in these WFFs as they are free/bound in f.

5. Tuple Calculus Expression

   Form of expression is

   
   T.A,U.B,…………,V.C [WHERE f]Where T, U,………V are tuple variables; A, B,……..C are attributes of the associated relations, and f is a WFF containing exactly T, U,………V as free variables. The value of this expression is a projection of that subset of the Cartesian product T X U X…..X V for which f calculates to true. If “where f” is omitted, then the value is a projection of the entire cartesian product.

   Example

   Let SX is tuple variable range over relation S

   1. 1. SX.S#: It denotes the set of all supplier’s numbers in relation to S.

    

   1. 1. SX.S#, WHERE SX.CITY=”KARNAL”: It denotes that the subset of those supplier number for which city is KARNAL.

    

   1. To make supplier integer for suppliers who supply portion P201.

   SX.S# WHERE (SX.S#=SPX.S# AND SPX.P#=”P201″)

Query Examples for Tuple Relational Calculus

Example: Consider the schema given below:

Deposit (Cust-Name, Account-No)

Loan (Cust-Name, Loan-No, amount)

1. 1. Get information on the loans that have amount>100000.

{t|t∈loan ⋀t[amount]>100000}

1. 1. Find the loan numbers of the loans for which the amount is more than 100000.

{t|∃ s∈loan (t[loan-number]=s[loan-number]⋀s[amount]>100000)}

1. 1. Find the names of the customers who are having a loan or account or both.

{█(t|∃ s∈loan (t[cust-name]=s[cust-name]⋀@∃ u∈deposit (u[cust-name]=s[cust-name])))}

Domain Relational Calculus (DRC)

It was suggested by Lacroix and Pirotte in 1977. The domain relational calculus differs from the tuples calculus in that its variable ranges over domain rather than relations.

Syntax of Domain Relational Calculus (DRC)

{< x₁,x₂,……x_n>|P(x₁,x₂,……x_n)}

Where

x₁,x₂,……x_n represent domain variables.

P symbolize a formula, which is collected of atoms, as in the method of tuple relational calculus.

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

1. < x₁,x₂,……x_n>∈r, where r is a relation on n attributes and x₁,x₂,……x_n are domain variables or domain constants corresponding to the respective domains of n attributes of relation r.
2. xθy,where x and y are domain variables and θ is a comparison operator (<,≤,>,≥,=,=∕=), x and y should have domains which can be compared.
3. xθc,where x is the domain variable, θ is the comparison operator, and c is the constant in the domain of x.

A formula is built from atoms using the following rules:

1. An atom is the formula.
2. If P1 is the formula, then so are ¬ P1 and (P1).
3. If P1 and P2 are formulae, then P1⋀P2,P1∨P2 and P1⟹P2.
4. If P1(t) is a formula in t, where t is a free domain variable, then, ∃ t (P1(t)) and ∀ t(P1(t)) are also formulae.

Expression of the domains calculus are constructed from the following elements:

1. 1. Domain Variables

      Domain variables are denoted by uppercase letters. For example,D,E,F, etc. Each domain variable is constrained to range over some specified domain.

   2. Conditions

      The condition can take two forms:

      1. 1. Simply Comparisons: The form is x * y, same as for the tuple calculus, except that x & y are now domain variables (or constants).
         2. Membership Condition: The form is R (term, term,…..). Here R is a relation, and each “term” is a pair A: V, where A is an attribute of R, and V is either a domain variable or a constant.

      Example

      SP (S#:’S1’, P#: ‘P1’) (which evaluates to true if and only if there exists an SP tuple having S#=’S1’& P#=’P1’)

   3. Well-Formed Formulas (WFFs)

It is similar to tuple calculus.

1. 1. Free & Bound Variables

It is also similar to tuple calculus.

1. 1. Domain Calculus Expression

A domain calculus expression is then an expression of form D, E,….F [WHERE f] where D, E,…..F are domain variables & f is a WFF containing exactly D, E,….F are free variables. The values of this expression is that subset of the Cartesian product D x E x …….x F (where D, E,….F range over all their possible values) for which f evaluated to true or if “WHERE f\””is omitted that entire Cartesian product.

Example

SX

SX WHERE S (S#: SX)

SX WHERE S(S#: SX, CITY:”KARNAL”)

The first of these denotes the set of all supplier numbers; the second denotes the set of all supplier numbers in relation to S, and the third denotes the set of all supplier numbers from relation S for suppliers located in Karnal.

Query Examples for Domain Relational Calculus

Example: Consider the schema given below:

Deposit (Cust-Name, Account-No)

Loan (Cust-Name, Loan-No.amount)

1. Get information on the loans that have amount>100000.{< c,l,a>|< c,l,a>∈loan^a>100000}
2. Find the loan numbers of the loans for which the amount is more than 100000.{< l>| ∃ c,a (< c,l,a>∈loan ⋀a>100000)}
3. Find the names of the customers who are having a loan or account or both.{< c>| ∃ l,a (< c,l,a>∈loan)∨∃ ano(< c,ano>∈deposit)}

Enroll Yourself in Live Training: DBMS Training