# Database System Concepts. Chapter 6: Formal Relational Query Languages 10/6/2020

26

(1)

## Database System Concepts

(2)
(3)

### (in tuple calculus)

• {S |S Sailors  S.rating > 7}

### (in domain calculus)

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

(4)

(5)

(6)

### Relational Calculus Building Blocks

• Variables

TRC: 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

(7)

### Relational Calculus

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

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> SailorsR > 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

(8)

### Tuple Relational Calculus

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

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

– 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

(9)

• An atomic formula is one of the following:

### is one of

• A formula can be:

(10)

### 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.

(11)

### 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”))

(12)

### b

• If a is true, b must be

true!

– 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.

(13)

### x (

(x  Boats)  (x.color = “Red”))

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

### x

Boats(x.color = “Red”)

(14)

### Selection and Projection

• Selection

Find all sailors with rating above 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 S rusty 35.0 Sailors

(15)

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

{S | SSailors  S.rating > 7 

R(RReserves  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

(16)

### 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)

17

### 表达式的安全性

• 表达式的安全性 – 元组关系演算有可能会产生无限关系，这样的表达式是不 安全的。如{t | (t r)}，求所有不在r中的元组 • 表达式安全性判定 – 引入公式P的域概念，用dom(P)表示 dom(P) = 显式出现在P中的值 + 在P中出现的关系的元组中出现的值 – 如果出现在表达式{t | P(t)}结果中的所有值均来自dom(P)， 则称{t | P(t)}是安全的 – {t | (t  r)}？

(18)
(19)

1

2

n

1

2

n

1

2

n

(20)

### 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>instructors  80000}

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

– {< i> |  n, d, s < i, n, d, s>instructors  80000}

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

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

(21)

### 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 >sections = “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 ] 

(22)

### 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 >  coursedn

=“Biology”

(23)

(24)

1

2

(25)

### Exercises

• Practice Exercises:

– 6.2 6.8

• Exercises:

(26)

Updating...

## References

Updating...

Related subjects :