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

26 

Loading....

Loading....

Loading....

Loading....

Loading....

Full text

(1)

Database System Concepts

Chapter 6:

Formal Relational Query Languages

10/6/2020

(2)
(3)

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}

(4)

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.

(5)

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

(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.:

{S |S SailorsS.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> 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.

• 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

(9)

• 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

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

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

(12)

a

b

is the same as

a

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.

a

T

F

T F

b

T

T

T

F

(13)

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:

(14)

Selection and Projection

• Selection

Find all sailors with rating above 8

{S |S SailorsS.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 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)

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)

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)

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

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

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:

(24)

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:

(25)

Exercises

• Practice Exercises:

– 6.2 6.8

• Exercises:

(26)

Figure

Updating...

References

Updating...

Related subjects :