## Database System Concepts

### Chapter 6:

### Formal Relational Query Languages

### 10/6/2020

### Relational Calculus

### •

### A

**relational calculus **

### expression creates a new

### relation, which is specified in terms of variables

### that range over

### –

### rows of the stored database relations

### (in tuple calculus)

• {S |S *Sailors* S*.rating* > 7}

### –

### columns of the stored relations

### (in domain calculus)

• {<S,N,R,A>| <S,N,R,A> *Sailors* R > 7}

### Relational Calculus

### •

### In a calculus expression, there is

*no order of *

*operations *

### to specify how to retrieve the query

### result

### –

### a calculus expression specifies only what

### information the result should contain

### –

### this is the main distinguishing feature between

### relational algebra and relational calculus.

### Relational Calculus

### •

### Relational calculus is considered to be a

**nonprocedural **

### or

**declarative **

### language.

### –

### this differs from relational algebra, where we

### must write a

*sequence of operations *

### to specify

### a retrieval request;

### –

### relational algebra can be considered as a

**procedural **

### way of stating a query

### Relational Calculus Building Blocks

• VariablesTRC: Variables are bound to tuples.

DRC: Variables are bound to domain elements (= column values)

• Constants 7, “Foo”, 3.14159, etc. • Comparison operators =, <>, <, >, etc. • Logical connectives - not – and - or - implies - is a member of • Quantifiers

X(p(X)): For every X, p(X) must be true

### Relational Calculus

• English example: Find all sailors with a rating above 7 – Tuple R.C.:

**{S |S ****Sailors** **S.rating** **> 7}**

**“**From all that is, find me the set of things that are tuples in the Sailors

relation and whose rating field is greater than 7.” – Domain R.C.:

**{<S,N,R,A>| <S,N,R,A> ****Sailors** **R** **> 7}**

“From all that is, find me column values S, N, R, and A, where S is an

integer, N is a string, R is an integer, A is a floating point number, such that <S, N, R, A> is a tuple in the Sailors relation and R is

greater than 7.”

sid sname rating age 28 yuppy 9 35.0 31 lubber 8 55.5 44 guppy 5 35.0 58 rusty 10 35.0

### Tuple Relational Calculus

• Query form: {T | p(T)}

– T is a tuple and p(T) denotes a formula in which tuple variable T appears.

• Answer:

– set of all tuples T for which the formula p(T) evaluates to true.

• Formula is recursively defined:

– Atomic formulas get tuples from relations or compare values

• An atomic formula is one of the following:

### R

###

### Rel

### R.a

### op

### S.b

### R.a

### op

### constant, where

### op

### is one of

• A formula can be:

### –

### an atomic formula

### –

### where p and q are formulas

### –

### where variable R

### is a tuple variable

### –

### where variable R

### is a tuple variable

### TRC Formulas

### =

### , , , , ,

*q*

*p*

*q*

*p*

*p*

###

###

###

### ,

### ,

### )

### )

### (

### (

*p*

*R*

*R*

###

### )

### )

### (

### (

*p*

*R*

*R*

###

### Free and Bound Variables

• The use of quantifiers X and X in a formula is said to

bind

### X

in the formula.– A variable that is not bound is free. • Important restriction

{T | p(T)}

– The variable T that appears to the left of `|’ must be the only free variable in the formula p(T).

– In other words, all other tuple variables must be bound using a quantifier.

### Use of

###

### (For every)

### •

###

### x (P(x)):

### only true if P(x) is true for

*every*

### x in the universe:

### e.g.

x ((x.color = “Red”) )➢means everything that exists is red

### •

### Usually we are less grandiose in our assertions:

**x (** **(x ** **Boats) ** **(x.color** **= “Red”))**

### •

###

### is a logical implication

**a **

###

**b **

### means that if a is true, b must be true

**a **

###

**b**

### is the same as

###

### a

###

### b

• If a is true, b must betrue!

– If a is true and b is false, the expression evaluates to false.

• If a is not true, we don’t care about b – The expression is always true.

### a

### T

### F

### T F

### b

### T

### T

### T

### F

### Quantifier Shortcuts

### •

###

### x (

(x Boats) (x.color = “Red”))“For every x in the Boats relation, the color must be Red.”

### Can also be written as:

###

**x**

**Boats(x.color = “Red”)**

### •

###

### x (

### (x

###

### Boats)

###

### (x.color = “Red”))

### “There exists a tuple x in the Boats relation whose

### color is Red.”

### Can also be written as:

### Selection and Projection

• SelectionFind all sailors with rating above 8

{*S* |S *Sailors* *S.rating* > 8}

{*S* | *S1 **Sailors*(*S1.rating* > 8

*S.sname = S1.sname*

*S.age = S1.age*)}

S is a tuple variable of 2 fields (i.e. {S} is a projection of Sailors)

sid sname rating age
28 yuppy 9 35.0
31 lubber 8 55.5
44 guppy 5 35.0
58 rusty 10 35.0
**sname** **age**
• Projection

Find names and ages of sailors with rating above 8.

**S*** S1*
yuppy 35.0

**S1**

**S1**

**S1***rusty 35.0*

**S**

**Sailors**Note the use of to find a tuple in Reserves that `joins with’ the Sailors tuple under consideration.

{S | SSailors S.rating > 7

R(RReserves R.sid = S.sid R.bid = 103)}

### Joins

Find sailors rated > 7 who’ve reserved

boat #103

sid sname rating age 22 dustin 7 45.0 31 lubber 8 55.5 58 rusty 10 35.0

sid bid day
22 101 10/10/96
58 103 11/12/96
*S*
*S*
*S*
*R*
*R*

What if there was another tuple {58, 103, 12/13/96} in the Reserves relation?

**Sailors**

### Joins (continued)

### Notice how the parentheses control the

### scope of each quantifier’s binding.

### {S | S

###

### Sailors

###

### S.rating > 7

###

###

_{R(R}

###

_{Reserves}

###

_{R.sid = S.sid}

###

_{B(B}

###

_{Boats}

###

_{B.bid = R.bid}

###

_{B.color}

_{= ‘red’}

_{))}}

### Find sailors rated > 7 who’ve reserved a

### red boat

### What does this expression compute?

sid sname rating age 22 dustin 7 45.0 31 lubber 8 55.5 58 rusty 10 35.0

sid bid day 22 101 10/10/96 58 103 11/12/96

**Sailors**

17

### 表达式的安全性

• 表达式的安全性 – 元组关系演算有可能会产生无限关系，这样的表达式是不 安全的。如_{{t | }

_{(t }

_{r)}}，求所有不在

_{r}中的元组 • 表达式安全性判定 – 引入公式P的域概念，用

*表示*

**dom(P)***显式出现在*

**dom(P) =***中的值 + 在*

**P***中出现的关系的元组中出现的值 – 如果出现在表达式{*

_{P}**t | P(t)**}结果中的所有值均来自

*， 则称*

**dom(P)**_{{}

_{t | P(t)}_{}}是安全的 – {t | (t r)}？

### Domain Relational Calculus

### •

### A nonprocedural query language equivalent

### in power to the tuple relational calculus

### •

### Each query is an expression of the form:

### {

###

*x*

1*, x*

2*, …, *

*x*

*n*

###

### |

*P *

### (

*x*

1### ,

*x*

2*, …, *

*x*

*n*

### )}

### –

*x*

_{1}

### ,

*x*

_{2}

*, …, *

*x*

_{n}### represent domain variables

### –

*P*

### represents a formula similar to that of the

### Example Queries

• Find the *ID, name, dept_name, salary *for instructors whose
salary is greater than $80,000

– {*< i, n, d, s> *| *< i, n, d, s>* *instructor* *s* 80000}

• As in the previous query, but output only the *ID* attribute
value

– {*< i> * | *n, d, s < i, n, d, s>* *instructor* *s* 80000}

• Find the names of all instructors whose department is in the Watson building

– {*< n > *| *i, d, s (< i, n, d, s* > *instructor *

### Example Queries

• Find the set of all courses taught in the Fall 2009 semester, or in the Spring 2010 semester, or both

– {*<c> *| *a, s, y, b, r, t *( <*c, a, s, y, b, t* > *section **s *= “Fall”*y = 2009* )
v *a, s, y, b, r, t *( <*c, a, s, y, b, t* >*section* *s *= “Spring”*y= *2010)}

– This case can also be written as

{*<c> *| *a, s, y, b, r, t *( <*c, a, s, y, b, t* > *section *

( (*s *= “Fall” *y = 2009* ) v (*s *= “Spring” *y = *2010))}

• Find the set of all courses taught in the Fall 2009 semester, and in the

Spring 2010 semester

– {*<c> *| *a, s, y, b, r, t *( <*c, a, s, y, b, t* > *section *
*s *= “Fall” *y = 2009* )

*a, s, y, b, r, t *( <*c, a, s, y, b, t* > *section* ]

### Universal Quantification

• Find all students who have taken all courses offered in the Biology department

– {< *i* > | *n, d, tc* ( < *i, n, d, tc* > *student *

( *ci, ti, dn, cr* ( < *ci, ti, dn, cr* > *course* *dn*

=“Biology”

### Exercises

### –

### (I)

### Let the following relation schemas be given:

### Let relations

*r*

### (

*R*

### ) and

*s*

### (

*S*

### ) be given. Give an expression in

### the

**tuple relational calculus **

### that is equivalent to each of

### the following:

### Exercises

### –

### (II)

### Let R = (A, B, C), and let r

_{1}

### and r

_{2}

### both be relations on

### schema

*R*

### . Give an expression in the

**domain relational **

**calculus**

### that is equivalent to each of the following:

### Exercises

• Practice Exercises:

– 6.2 6.8

• Exercises: