Relational Calculus

Chittaranjan Pradhan

Database Management Relational Calculus

Tuple Relational

System 14 Calculus (TRC)

Safe Expressions
Queries

Relational Calculus Domain Relational

Calculus (DRC)
Queries

Chittaranjan Pradhan
School of Computer Engineering,
KIIT University
14.1
Relational Calculus
Relational Calculus
Chittaranjan Pradhan

Relational Calculus

Tuple Relational
Relational Calculus Calculus (TRC)
Safe Expressions
Relational calculus is non-procedural Queries

Domain Relational
Calculus (DRC)
In relational calculus, a query is solved by defining a solution Queries

relation in a single step

Relational calculus is mainly based on the well-known

propositional calculus, which is a method of calculating with
sentences or declarations

Various types of relational calculus are:

• Tuple Relational Calculus (TRC)
• Domain Relational Calculus (DRC)

14.2
Relational Calculus
Tuple Relational Calculus (TRC)
Chittaranjan Pradhan

Relational Calculus

Tuple Relational
Calculus (TRC)
Tuple Relational Calculus (TRC) Safe Expressions
Queries
A tuple variable is a variable that takes on tuples of a particular Domain Relational
relation schema as values Calculus (DRC)
Queries

A tuple relational calculus query has the form:

{T/ P(T)}
The result of this query is the set of all tuples t for which the
formula P(T) evaluates to TRUE with T = t
Sailors (sid, sname, rating, age)

Query: Find all the sailors with a rating above 4

{S/S ∈ Sailors ∧ S.rating > 4}

14.3
Relational Calculus
Tuple Relational Calculus (TRC)...
Chittaranjan Pradhan

Tuple Relational Calculus (TRC)

Relational Calculus
Let Rel → be a relation name, R, S → be the tuple variables, a
Tuple Relational
→ an attribute of R, b → an attribute of S, op → operator in the Calculus (TRC)
Safe Expressions
set {<, ≤, >, ≥, =, 6=}. An Atomic formula is one of the following: Queries

Domain Relational
Calculus (DRC)
• R ∈ Rel Queries

• R.a op S.b
• R.a op Constant or Constant op R.a
To represent the join and division of relational algebra by
relational calculus, we need quantifiers such as: existential for
join and universal for division

A quantifier quantifies or indicates the quantity of something

The existential quantifier (∃) states that at least one instance of

a particular type of thing exist
Similarly, the universal quantifier(∀) states that some condition
applies to all or to every row of some type
14.4
Relational Calculus
Tuple Relational Calculus (TRC)...
Chittaranjan Pradhan

Relational Calculus
Tuple Relational Calculus (TRC) Tuple Relational
Calculus (TRC)
A formula is recursively defined by using the following rules: Safe Expressions
Queries

• Any atomic formula Domain Relational

Calculus (DRC)
• If p and q are formulae, then ¬ p, p ∧ q, p ∨ q, or p ⇒ q Queries

are also formulae

• If p is a formula that contains T as a variable, then ∃ T(p)
and ∀ T(p) are also formulae
The quantifiers ∃ and ∀ are said to bind the tuple variable R;
whereas a variable is said to be free in a formula if the formula
does not contain an occurrence of a quantifier that binds it

In most of the queries, the output is shown by using the free

variables

14.5
Relational Calculus
Safe Expressions
Chittaranjan Pradhan

Relational Calculus

Tuple Relational
Calculus (TRC)
Safe Expressions Safe Expressions
Queries

Whenever we use universal quantifiers or existential quantifiers Domain Relational

Calculus (DRC)
in a calculus expression, we must make sure that the resulting Queries

expression makes sense

A safe expression in relational calculus is one that is

guaranteed to yield a finite number of tuples as its result;
otherwise, the expression is called unsafe

That means, an expression is said to be safe if all values in its

result are from the domain of the expression

14.6
Relational Calculus
Queries
Chittaranjan Pradhan

Sailor Database
Relational Calculus
Sailors(sid, sname, rating, age)
Tuple Relational
Boats(bid, bname, color) Calculus (TRC)
Safe Expressions
Reserves(sid, bid, day) Queries

Domain Relational
Calculus (DRC)
Queries
Query: Find the names & ages of sailors with a rating above 4
{T/∃S ∈ Sailors (S.rating >4 ∧ T.sname=S.sname ∧ T.age=
S.age)}

Query: Find the sailor name, boat id & reservation date for each
reservation
{T/∃R ∈ Reserves ∃S ∈ Sailors (R.sid = S.sid ∧
T.sname=S.sname ∧ T.bid=R.bid ∧ T.day=R.day)}

Query: Find the names of sailors who have reserved boat 111
{T/∃R ∈ Reserves ∃S ∈ Sailors (R.sid = S.sid ∧ R.bid=111 ∧
T.sname=S.sname)}
14.7
Relational Calculus
Queries...
Chittaranjan Pradhan

Relational Calculus

Tuple Relational
Query: Find the names of sailors who have reserved a green boat Calculus (TRC)
Safe Expressions

{T/∃S ∈ Sailors ∃R ∈ Reserves(R.sid = S.sid ∧ Queries

T.sname=S.sname ∧ ∃B ∈ Boats (B.bid=R.bid ∧ Domain Relational

Calculus (DRC)
B.color=’green’))} Queries

This query can also be written as:

{T/∃S ∈ Sailors ∃R ∈ Reserves ∃B ∈ Boats(R.sid = S.sid ∧
B.bid=R.bid ∧ B.color=’green’ ∧ T.sname=S.sname)}

Query: Find the names of sailors who have reserved at least 2

boats
{T/∃S ∈ Sailors ∃R1 ∈ Reserves ∃R2 ∈ Reserves(S.sid=R1.sid
∧ R1.sid= R2.sid ∧ R1.bid 6= R2.bid ∧ T.sname=S.sname)}

14.8
Relational Calculus
Queries...
Chittaranjan Pradhan

Relational Calculus

Tuple Relational
Calculus (TRC)
Safe Expressions
Queries

Query: Find the names of sailors who have reserved all boats Domain Relational
Calculus (DRC)
Queries
{T/∃S ∈ Sailors ∀B ∈ Boats(∃R ∈ Reserves (S.sid=R.sid ∧
R.bid= B.bid ∧ T.sname= S.sname))}

Query: Find sailors who have reserved all green boats

{S/S ∈ Sailors ∧ ∀B ∈ Boats (B.color=’green’ ⇒(∃ R ∈
Reserves (S.sid=R.sid ∧ R.bid= B.bid))}

14.9
Relational Calculus
Domain Relational Calculus (DRC)
Chittaranjan Pradhan

Relational Calculus
Domain Relational Calculus (DRC)
Tuple Relational
In tuple relational calculus, the variables range over the tuples Calculus (TRC)
Safe Expressions

whereas in domain relational calculus, the variables range over Queries

the domains Domain Relational

Calculus (DRC)
Queries

The domain variables are the ones which range over the
underlying domains instead of over the relations

The domain relational calculus query has the form:

{<x1 , x2 , ... xn > | P(x1 , x2 , ... xn )}
where xi is either a domain variable or a constant and P(x1 , x2 ,
... xn ) is the domain relational calculus formula whose only free
variables are the variables among the xi, 1 ≤ i ≤ n

The result of this query is the set of all tuples <x1 , x2 , ... xn > for
which the formula evaluates to TRUE

14.10
Relational Calculus
Domain Relational Calculus (DRC)...
Chittaranjan Pradhan

Domain Relational Calculus (DRC)

Relational Calculus
Let Rel → be a relation name, X, Y → be the domain variables, Tuple Relational
Calculus (TRC)
op → an operator in the set {<, ≤, >, ≥, =, 6=}. An Atomic Safe Expressions

formula in domain relational calculus is one of the following: Queries

Domain Relational
• <x1 , x2 , ... xn > ∈ Rel Calculus (DRC)
Queries

• X op Y
• X op Constant or Constant op X
A formula is recursively defined by using the following rules:
• Any atomic formula
• If p and q are formulae, then ¬p, p ∧ q, p ∨ q, or p ⇒ q are
also formulae
• If p is a formula that contains X as a domain variable, then
∃X(p) and ∀ X(p) are also formulae
The quantifiers ∃ & ∀ are said to bind the domain variable X.
Whereas a variable is said to be free in a formula if the formula
does not contain an occurrence of a quantifier that binds it
14.11
Relational Calculus
Queries
Chittaranjan Pradhan

Sailor Database
Sailors(sid, sname, rating, age) Relational Calculus

Boats(bid, bname, color) Tuple Relational

Calculus (TRC)
Reserves(sid, bid, day) Safe Expressions
Queries

Domain Relational
Query: Find all sailors with a rating above 7 Calculus (DRC)
Queries

{<I, N, T, A> / <I, N,T, A> ∈ Sailors ∧ T >7}

Query: Find the names of sailors who reserved boat 111

{<N> /∃I, T , A (<I, N, T, A> ∈ Sailors ∧ ∃<Ir, Br, D> ∈ Reserves
(Ir=I ∧ Br=111))}

This query can also be written as:

{<N> /∃I, T , A (<I, N, T, A> ∈ Sailors ∧ ∃<Ir, Br, D> ∈ Reserves
(Ir=I ∧ Br=111))}
or
{<N> /∃I, T , A (<I, N, T, A> ∈ Sailors ∧ ∃D(<I,111, D> ∈
Reserves))}
14.12
Relational Calculus
Queries...
Chittaranjan Pradhan

Query: Find the names of sailors who have reserved a green boat
Relational Calculus

{<N>/∃I, T, A (<I, N, T, A> ∈ Sailors ∧ <I, Br, D> ∈ Reserves ∧ Tuple Relational
Calculus (TRC)
∃<Br, Bn, ’green’> ∈ Boats)} Safe Expressions
Queries

Domain Relational
Calculus (DRC)
Query: Find the names of sailors who have reserved at least 2 Queries
boats
{<N>/∃I, T, A (<I, N, T, A> ∈ Sailors ∧ ∃Br1, Br2, D1, D2(<I, Br1,
D1> ∈ Reserves ∧ <I, Br2, D2> ∈ Reserves ∧ Br1 6= Br2))}

Query: Find the names of sailors who have reserved all boats
{<N>/∃I, T, A(<I, N, T, A> ∈ Sailors ∧ ∀<B, Bn, C> ∈ Boats
(∃<Ir, Br, D> ∈ Reserves ( I=Ir ∧ Br=B)))}

Query: Find sailors who have reserved all green boats

{<I,N,T,A>/<I,N,T,A> ∈ Sailors ∧ ∀<B, Bn, C> ∈ Boats
(C=’green’ ⇒ ∃<Ir, Br, D> ∈ Reserves ( I=Ir ∧ Br=B)))}

14.13