DatabaseSystem

Concepts,

6th

Ed.?Silberschatz,

Korth

and

SudarshanSee

www.db-

for

conditions

on

re-useChapter

6:

Formal

Relational

QueryLanguagesLanguages6

.

2?Silberschatz,

Korth

and

Suda

rDatabase

System

Concepts

-6

t

hEditionn

Relational

Algebran

Tuple

Relational

Calculusn

Domain

Relational

CalculusRelational

Algebra6

.

3?Silberschatz,

Korth

and

Suda

rDatabase

System

Concepts

-6

t

hEditionn

Procedural

languagen

Six

basic

operatorsl

select:l

project:l

union:l

set

difference:

–l

Cartesian

product:

xl

rename:n

The

operators

take

one

ortwo

relations

as

inputs

and

producea

new

relation

as

a

result.Select

Operation

–

Examplen

Relation

r?6

.

4?Silberschatz,

Korth

and

Suda

rDatabase

System

Concepts

-6

t

hEditionA=B

^

D>

5

(r)Select

Operation6

.

5?Silberschatz,

Korth

and

Suda

rDatabase

System

Concepts

-6

t

hEditionn

Notation:

p(r)n

p

is

called

the

selection

predicaten

Defined

as:p(r)

=

{t

|

t

r

and

p(t)}Where

p

is

a

formula

in

propositional

calculus

consisting

of(and),

(or),

(not)terms

connected

by

:Each

term

is

one

of:<attribute>where

op

is

one

of:op<attribute>

or

<constant>=,

,

>,

.

<.n

Example

of

selection:dept_name=“Physics”(instructor)Project

Operation

–

Examplen

Relation

r:nA,C

(r)6

.

6?Silberschatz,

Korth

and

Suda

rDatabase

System

Concepts

-6

t

hEditionProject

Operationn

Notation:where

A1,

A2

are

attribute

names

and

r

is

a

relation

name.n

The

result

is

defined

as

the

relation

of

k

columns

obtained

byerasing

the

columns

that

are

not

listedn

Duplicate

rows

removed

from

result,

since

relations

are

setsn

Example:

To

eliminate

the

dept_name

attribute

of

instructorID,

name,

salary

(instructor)6

.

7?Silberschatz,

Korth

and

Suda

rDatabase

System

Concepts

-6

t

hEditionUnion

Operation

–

Examplen

Relations

r,

s:n

rs:6

.

8?Silberschatz,

Korth

and

Suda

rDatabase

System

Concepts

-6

t

hEditionUnion

Operation6

.

9?Silberschatz,

Korth

and

Suda

rDatabase

System

Concepts

-6

t

hEditionn

Notation:

r

sn

Defined

as:rn

For

rs

=

{t

|

t

r

or

t

s}s

to

be

valid.r,

s

must

have

the

same

arity

(same

number

of

attributes)The

attribute

domains

must

be

compatible

(example:

2ndcolumnof

r

deals

with

the

same

type

of

values

as

does

the2ndcolumn

of

s)n

Example:

to

find

all

courses

taught

in

the

Fall

2009

semester,

or

ithe

Spring

2010

semester,

or

in

bothcourse_id

(semester=“Fall”Λ

year=2009

(section))course_id

(semester=“Spring”Λ

year=2010Set

difference

of

two

relationsn

Relations

r,

s:n

r

–

s:6

.

10?Silberschatz,

Korth

and

Suda

rDatabase

System

Concepts

-6

t

hEditionSet

Difference

Operation6

.

11?Silberschatz,

Korth

and

Suda

rDatabase

System

Concepts

-6

t

hEditionn

Notation

r

–

sn

Defined

as:r

–

s

=

{t

|

tr

and

t

s}n

Set

differences

must

be

taken

between

compatible

relations.l

r

and

s

must

have

the

same

arityl

attribute

domains

of

r

and

s

must

be

compatiblen

Example:

to

find

all

courses

taught

in

the

Fall

2009

semester,but

not

in

the

Spring

2010

semestersemester=“Fall”course_id

(

Λ

year=2009(section))

?course_id

((section))semester=“Spring”

Λ

year=2010Examplen

Relations

r,

s:n

r

x

s:6

.

12?Silberschatz,

Korth

and

Suda

rDatabase

System

Concepts

-6

t

hEditionCartesian-Product

Operation6

.

13?Silberschatz,

Korth

and

Suda

rDatabase

System

Concepts

-6

t

hEditionn

Notation

r

x

sn

Defined

as:r

x

s

=

{t

q

|

tr

and

q

s}n

Assume

that

attributes

of

r(R)

and

s(S)

aredisjoint.

(That

is,

R S

=

).n

If

attributes

of

r(R)

and

s(S)

are

not

disjoint,then

renaming

must

be

used.Composition

of

Operationsn

Can

build

expressions

using

multiple

operationsn

Example:

A=C(r

x

s)n

r

x

snA=C(r

x

s)6

.

14?Silberschatz,

Korth

and

Suda

rDatabase

System

Concepts

-6

t

hEditionRename

Operationn

Allows

us

to

name,

and

therefore

to

refer

to,

the

results

ofrelational-algebra

expressions.n

Allows

us

to

refer

to

a

relation

by

more

than

one

name.n

Example:x

(E)returns

the

expression

E

under

the

name

Xn

If

a

relational-algebra

expression

E

has

arity

n,

thenreturns

the

result

of

expression

E

under

the

name

X,

and

with

theattributes

renamed

to

A1

,

A2

,

….,

An

.6

.

15?Silberschatz,

Korth

and

Suda

rDatabase

System

Concepts

-6

t

hEditionExample

Query6

.

16?Silberschatz,

Korth

and

Suda

rDatabase

System

Concepts

-6

t

hEditionn

Find

the

largest

salary

in

the

universityl

Step

1:

find

instructor

salaries

that

are

less

than

someother

instructor

salary

(i.e.

not

maximum)–

using

a

copy

of

instructor

under

a

newname

d4instructor.salary

(instructor.salary

<

d,salary(instructor

xd(instructor)))l

Step

2:

Find

the

largest

salary4salary

(instructor)

–instructor.salary

(instructor.salary

<

d,salary(instructor

xd(instructor)))Example

Queries6

.

17?Silberschatz,

Korth

and

Suda

rDatabase

System

Concepts

-6

t

hEditionn

Find

the

names

of

all

instructors

in

the

Physics

department,

alongwith

the

course_id

of

all

courses

they

have

taughtl

Query

1instructor.ID,course_id

(dept_name=“Physics”

(instructor.ID=teaches.ID(instructor

xteaches)))l

Query

2instructor.ID,course_id

(instructor.ID=teaches.ID

(dept_name=“Physics”(instructor)

xteaches))Formal

Definition6

.

18?Silberschatz,

Korth

and

Suda

rDatabase

System

Concepts

-6

t

hEditionn

A

basic

expression

in

the

relational

algebra

consists

of

either

oneof

the

following:l

A

relation

in

the

databasel

A

constant

relationn

Let

E1

and

E2

be

relational-algebra

expressions;

the

following

areall

relational-algebra

expressions:l

E1

E2l

E1

–

E2l

E1

x

E2lp

(E1),

P

is

a

predicate

on

attributes

in

E1s(E1),

S

is

a

list

consisting

of

some

of

the

attributes

inx

(E1),

x

is

the

new

name

for

the

result

of

E1llAdditional

Operations6

.

19?Silberschatz,

Korth

and

Suda

rDatabase

System

Concepts

-6

t

hEditionWe

define

additional

operations

that

do

not

add

any

power

to

therelational

algebra,

but

that

simplify

common

queries.n

Set

intersectionn

Natural

joinn

Assignmentn

Outer

joinSet-Intersection

Operation6

.

20?Silberschatz,

Korth

and

Suda

rDatabase

System

Concepts

-6

t

hEditionn

Notation:

r

sn

Defined

as:n

r

s

=

{

t

|

tr

and

t

s

}n

Assume:l

r,

s

have

the

same

arityl

attributes

of

r

and

s

are

compatiblen

Note:

r

s

=

r

–

(r

–

s)Set-Intersection

Operation

–

Examplen

Relation

r,

s:n

rs6

.

21?Silberschatz,

Korth

and

Suda

rDatabase

System

Concepts

-6

t

hEditionr.D

?=S

i

lsb.eDr

s

c(hra

tz,

xK

or

tsh

)a)n

d

Suda

rDatabase

System

Concepts

-

6

t

h

rEd.iAt,i

o

nr.B,

r.C,

r.D,

s.6E.

2

2(nNotation:

rsNatural-Join

Operationn

Let

r

and

sberelations

on

schemas

R

and

S

respectively.Then,

rsis

a

relation

on

schema

R S

obtained

as

follows:l

Consider

each

pair

of

tuples

tr

from

r

and

ts

from

s.l

If

tr

and

ts

have

the

same

valueon

eachoftheattributesinS,

add

a

tuple

t

to

the

result,where4thasthesamevalueastronr4thasthesamevalueastsonsn

Example:R

=

(A,

B,

C,

D)S

=

(E,

B,

D)l

Result

schema

=

(A,

B,

C,

D,

E)l

r

s

is

defined

as:r.B

=

s.BNatural

Join

Examplen

Relations

r,

s:n

rs6

.

23?Silberschatz,

Korth

and

Suda

rDatabase

System

Concepts

-6

t

hEditionNatural

Join

and

Theta

Joinn

Find

the

names

of

all

instructors

in

the

Comp.

Sci.

departmenttogether

with

the

course

titles

of

all

the

courses

that

the

instructteachlname,

title

(

dept_name=“Comp.

Sci.”

(instructorteachescourse))n

Natural

join

is

associativel

(instructorinstructorteaches)(teachescoursecourse)is

equivalent

ton

Natural

join

is

commutativel

instructteachesteachesinstructorn

The

theta

join

operation

ris

equivalent

tos

is

defined

as(r

x

s)l

r

s

=6

.

24?Silberschatz,

Korth

and

Suda

rDatabase

System

Concepts

-6

t

hEditionAssignment

Operation6

.

25?Silberschatz,

Korth

and

Suda

rDatabase

System

Concepts

-6

t

hEditionn

The

assignment

operation

(

)

provides

a

convenient

way

toexpress

complex

queries.l44Write

query

as

a

sequential

program

consisting

ofa

series

of

assignmentsfollowed

by

an

expression

whose

value

isdisplayed

as

a

result

of

the

query.l

Assignment

must

always

be

made

to

a

temporaryrelation

variable.Outer

Join6

.

26?Silberschatz,

Korth

and

Suda

rDatabase

System

Concepts

-6

t

hEditionn

An

extension

of

the

join

operation

that

avoids

loss

of

information.n

Computes

the

join

and

then

adds

tuples

form

one

relation

thatdoes

not

match

tuples

in

the

other

relation

to

the

result

of

the

jon

Uses

null

values:l

null

signifies

that

the

value

is

unknown

or

does

not

existl

All

comparisons

involving

null

are

(roughly

speaking)

false

bdefinition.4We

shall

study

precise

meaning

of

comparisons

withnulls

laterOuter

Join

–

Examplen

Relation

instructor1n

Relation

teaches1IDcourse_id10101CS-10112121FIN-20176766BIO-101dept_nameComp.

Sci.FinanceMusicID101011212115151nameSrinivasanWuMozart6

.

27?Silberschatz,

Korth

and

Suda

rDatabase

System

Concepts

-6

t

hEditionn

Left

Outer

JoinninstructorteachesOuter

Join

–

Examplen

JoininstructorteachesIDdept_name10101SrinivasanComp.

Sci.CS-10112121WuFinanceFIN-201course_idnameID101011212115151dept_nameComp.

Sci.FinanceMusiccourse_idCS-101FIN-201nullnameSrinivasanWuMozart6

.

28?Silberschatz,

Korth

and

Suda

rDatabase

System

Concepts

-6

t

hEditionOuter

Join

–

Examplen

Full

Outer

Joinninstructorteachesn

Right

Outer

JoinninstructorteachesID101011212176766dept_nameComp.

Sci.Financenullcourse_idCS-101FIN-201BIO-101nameSrinivasanWunullIDdept_name10101SrinivasanComp.

Sci.CS-10112121WuFinanceFIN-20115151MozartMusicnull76766nullnullBIO-101course_idname6

.

29?Silberschatz,

Korth

and

Suda

rDatabase

System

Concepts

-6

t

hEditionOuter

Join

using

Joinsn

Outer

join

can

be

expressed

using

basic

operationsl

e.g.

r

s

can

be

written

as(r

s)

U

(r

–

∏R(r

s) x

{(null,

…,

null)}6

.

30?Silberschatz,

Korth

and

Suda

rDatabase

System

Concepts

-6

t

hEditionNull

Values6

.

31?Silberschatz,

Korth

and

Suda

rDatabase

System

Concepts

-6

t

hEditionn

It

is

possible

for

tuples

to

have

a

null

value,

denoted

by

null,

fosome

of

their

attributesn

null

signifies

an

unknown

value

or

that

a

value

does

not

exist.n

The

result

of

any

arithmetic

expression

involving

null

is

null.n

Aggregate

functions

simply

ignore

null

values

(as

in

SQL)n

For

duplicate

elimination

and

grouping,

null

is

treated

like

anyother

value,

and

two

nulls

are

assumed

to

be

the

same

(as

inSQL)Null

Values6

.

32?Silberschatz,

Korth

and

Suda

rDatabase

System

Concepts

-6

t

hEditionl

If

false

was

used

instead

of

unknown,

thennot(A<

5)would

not

be

equivalent

toA>=n

Comparisons

with

null

values

return

the

special

truth

value:unknown5n

Three-valued

logic

using

the

truth

value

unknown:l

OR:

(unknown

or

true)(unknown

or

false)=

true,=

unknown(unknown

or

unknown)

=

unknownl

AND:(true

and

unknown)(false

and

unknown)=

unknown,=

false,(unknown

and

unknown)

=

unknown(not

unknown)

=

unknownl

NOT:l

In

SQL

“P

is

unknown”

evaluates

to

true

if

predicate

Pevaluates

to

unknownn

Result

of

select

predicate

is

treated

as

false

if

it

evaluates

tounknownDivision

Operator6

.

33?Silberschatz,

Korth

and

Suda

rDatabase

System

Concepts

-6

t

hEditionn

Given

relations

r(R)

and

s(S),

such

that

Slargest

relation

t(R-S)

such

thatR,

rs

is

thet

x

s

rn

E.g.

letID,

course_idcourse_idr(ID,

course_id)

=s(course_id)

=

((takes

)

anddept_name=“Biology”(course)then

rs

gives

us

students

who

have

taken

all

courses

in

theBiology

departmentn

Canwrite

r

s

astemp1temp2R-S(r

)((temp1

x

s

)

–R-SR-S,S

(r

))result

=

temp1

–

temp2l

The

result

to

the

right

of

the

is

assigned

to

the

relationvariable

on

the

left

of

the

.l

May

use

variable

in

subsequent

expressions.Extended

Relational-Algebra-Operation6

.

34?Silberschatz,

Korth

and

Suda

rDatabase

System

Concepts

-6

t

hEditionn

Generalized

Projectionn

Aggregate

FunctionsGeneralized

Projectionn

Extends

the

projection

operation

by

allowing

arithmetic

functions

tbe

used

in

the

projection

list.n

E

is

any

relational-algebra

expressionn

Each

of

F1,

F2,

…,

Fn

are

are

arithmetic

expressions

involvingconstants

and

attributes

in

the

schema

of

E.n

Given

relation

instructor(ID,

name,

dept_name,

salary)

where

salaryis

annual

salary,

get

the

same

information

but

with

monthly

salaryID,

name,

dept_name,

salary/12

(instructor)6

.

35?Silberschatz,

Korth

and

Suda

rDatabase

System

Concepts

-6

t

hEditionAggregate

Functions

and

Operationsn

Aggregation

function

takes

a

collection

of

values

and

returns

asingle

value

as

a

result.avg:min:max:sum:average

valueminimum

valuemaximum

valuesum

of

valuescount: number

of

valuesn

Aggregate

operation

in

relational

algebraE

is

any

relational-algebra

expressionl

G1,

G2

…,

Gn

is

a

list

of

attributes

on

which

to

group

(canbe

empty)l

Each

Fi

is

an

aggregate

functionl

Each

Ai

is

an

attribute

namen

Note:

Some

books/articles

use

instead

of

(Calligraphic

G)6

.

36?Silberschatz,

Korth

and

Suda

rDatabase

System

Concepts

-6

t

hEditionAggregate

Operation

–

Examplen

Relation

r:A

B

C77310nsum(c)

(r)sum(c

)276

.

37?Silberschatz,

Korth

and

Suda

rDatabase

System

Concepts

-6

t

hEditionAggregate

Operation

–

Examplen

Find

the

average

salary

in

each

departmentdept_nameavg(salary)

(instructor)avg_salary6

.

38?Silberschatz,

Korth

and

Suda

rDatabase

System

Concepts

-6

t

hEditionAggregate

Functions

(Cont.)n

Result

of

aggregation

does

not

have

a

namel

Can

use

rename

operation

to

give

it

a

namel

For

convenience,

we

permit

renaming

as

part

of

aggregateoperationdept_nameavg(salary)

as

avg_sal

(instructor)6

.

39?Silberschatz,

Korth

and

Suda

rDatabase

System

Concepts

-6

t

hEditionModification

of

the

Database6

.

40?Silberschatz,

Korth

and

Suda

rDatabase

System

Concepts

-6

t

hEditionn

The

content

of

the

database

may

be

modified

using

thefollowing

operations:l

Deletionl

Insertionl

Updatingn

All

these

operations

can

be

expressed

using

the

assignmentoperatorMultiset

Relational

Algebra6

.

41?Silberschatz,

Korth

and

Suda

rDatabase

System

Concepts

-6

t

hEditionn

Pure

relational

algebra

removes

all

duplicatesle.g.

after

projectionn

Multiset

relational

algebra

retains

duplicates,

to

match

SQLsemanticsl

SQL

duplicate

retention

was

initially

for

efficiency,

but

isnow

a

featuren

Multiset

relational

algebra

defined

as

followsl

selection:

has

as

many

duplicates

of

a

tuple

as

in

theinput,

if

the

tuple

satisfies

the

selectionl

projection:

one

tuple

per

input

tuple,

even

if

it

is

a

duplicl

cross

product:

If

there

arem

copies

of

t1

in

r,

and

ncopies

of

t2

in

s,

there

are

m

x

n

copies

of

t1.t2

in

r

x

sl

Other

operators

similarly

defined4E.g.

union:

m

+

n

copies,

intersection:

min(m,

n)copiesdifference:

min(0,

m

–

n)

copiesSQL

and

Relational

Algebran

select

A1,

A2,

..

Anfrom

r1,

r2,

…,

rmwhere

Pis

equivalent

to

the

following

expression

in

multiset

relationalalgebraA1,

..,

An

(P

(r1

x

r2

x

..

x

rm))n

select

A1,

A2,

sum(A3)from

r1,

r2,

…,

rmwhere

Pgroup

by

A1,

A2is

equivalent

to

the

following

expression

in

multiset

relationalalgebraA1,

A2sum(A3)

(P

(r1

x

r2

x

..

xrm)))6

.

42?Silberschatz,

Korth

and

Suda

rDatabase

System

Concepts

-6

t

hEditionSQL

and

Relational

Algebran

More

generally,

the

non-aggregated

attributes

in

the

select

clausemay

be

a

subset

of

the

group

by

attributes,

in

which

case

theequivalence

is

as

follows:select

A1,

sum(A3)from

r1,

r2,

…,

rmwhere

Pgroup

by

A1,

A2is

equivalent

to

the

following

expression

in

multiset

relationalalgebraA1,sumA3(

A1,A2

sum(A3)

as

sumA3(..

x

rm)))P

(r1

x

r2

x6

.

43?Silberschatz,

Korth

and

Suda

rDatabase

System

Concepts

-6

t

hEditionTuple

Relational

Calculus6

.

44?Silberschatz,

Korth

and

Suda

rDatabase

System

Concepts

-6

t

hEditionTuple

Relational

Calculus6

.

45?Silberschatz,

Korth

and

Suda

rDatabase

System

Concepts

-6

t

hEditionn

A

nonprocedural

query

language,

where

each

query

is

of

the

form{t

|

P

(t

)

}n

It

is

the

set

of

all

tuples

t

such

that

predicate

P

is

true

for

tn

t

is

a

tuple

variable,

t

[A

]

denotes

the

value

of

tuple

t

on

attrin

t

r

denotes

that

tuple

t

is

in

relation

rn

P

is

a

formula

similar

to

that

of

the

predicate

calculusPredicate

Calculus

Formula6

.

46?Silberschatz,

Korth

and

Suda

rDatabase

System

Concepts

-6

t

hEdition1.2.3.4.Set

of

attributes

and

constantsSet

of

comparison

operators:

(e.g.,

,

,

,

,

,

)Set

of

connectives:

and

(

),

or

(v)?

not

(

)Implication

(

):

xx yy,

if

x

if

true,

then

y

is

truex

v

y5.Set

of

quantifiers:t

r

(Q

(t

))”there

exists”

a

tuple

in

t

in

relasuch

that

predicate

Q

(t

)

is

trueQ

is

true

“for

all”

tuples

t

in

relattr

(Q

(t

))Example

Queries6

.

47?Silberschatz,

Korth

and

Suda

rDatabase

System

Concepts

-6

t

hEditionn

Find

the

ID,

name,

dept_name,

salarysalary

is

greater

than

$80,000n{t

|

t

instructorfor

instructors

whosen

As

in

the

previous

query,

but

output

only

the

ID

attribute

valuen80000)}{t

|

s

instructor

(t

[ID

]

=

s

[ID

]

s

[salary

]nnNotice

that

a

relation

on

schema

(ID)

is

implicitly

defined

bythe

queryt

[salary

]

80000}Example

Queries6

.

48?Silberschatz,

Korth

and

Suda

rDatabase

System

Concepts

-6

t

hEditionn

Find

the

names

of

all

instructors

whose

department

is

in

theWatson

building=

2009v

u

section

(t[year]

=

2010)}[course_id

]

=

u

[course_id

]u

[semester]

=

“Spring”un{t

|

s“u

[building]

=

“Watson”

))}n

Find

the

set

of

all

courses

taught

in

the

Fall

2009

semester,

or

inthe

Spring

2010

semester,

or

bothn{t

|

s

section

(t

[course_id

]

=

s

[course_id

]s

[semester]

=

“Fall”

s

[year]instructor

(t

[name

]

=

s

[name

]department

(u

[dept_name

]

=

s[dept_name]uExample

Queries6

.

49?Silberschatz,

Korth

and

Suda

rDatabase

System

Concepts

-6

t

hEditionn{t

|

s=

2009usection

(t

[course_id

]

=

s

[course_id

]s

[semester]

=

“Fall”

s

[year]section

(t[course_id

]

=

u

[course_id

]u

[semester]

=

“Spring”

un

Find

the

set

of

all

courses

taught

in

the

Fall

2009

semester,

and

ithe

Spring

2010

semesteru

section

(t[course_id

]

=

u

[course_id

]u

[semester]

=

“Spring”

u[year]

=

2010)}n

Find

the

set

of

all

courses

taught

in

the

Fall

2009

semester,

but

ninthe

Spring

2010

semestern{t

|

s

section

(t

[course_id

]

=

s

[course_id

]s

[semester]

=

“Fall”

s

[year]=

2009[year]

=

2010)}Safety

of

Expressions6

.

50?Silberschatz,

Korth

and

Suda

rDatabase

System

Concepts

-6

t

hEditionn

It

is

possible

to

write

tuple

calculus

expressions

that

generateinfinite

relations.n

For

example,

{

t

|

tr

}

results

in

an

infinite

relation

ifdomain

of

any

attribute

of

relation

r

is

infiniten

To

guard

against

the

problem,

we

restrict

the

set

of

allowableexpressions

to

safe

expressions.n

An

expression

{t

|

P

(t

)}

in

the

tuple

relational

calculus

is

safevery

component

of

t

appears

in

one

of

the

relations,

tuples,

orconstants

that

appear

in

Pl

NOTE:

this

is

more

than

just

a

syntax

condition.4E.g.

{

t

|

t

[A]

=

5

true

}

is

not

safe

---

it

definean

infinite

set

with

attribute

values

that

do

not

appear

in

arelation

or

tuples

or

constants

in

P.Universal

Quantification6

.

51?Silberschatz,

Korth

and

Suda

rDatabase

System

Concepts

-6

t

hEditionn

Find

all

students

who

have

taken

all

courses

offered

in

theBiology

departmentl{t

|

r

student(t

[ID]

=

r

[ID])(

ucourse

(u[dept_name]=“Biology”stakes

(t

[ID]

=

s

[ID

]s

[course_id]

=

u[course_id]))}l

Note

that

without

the

existential

quantification

onstudent,

the

above

query

would

be

unsafe

if

the

Biologydepartment

has

not

offered

any

courses.Domain

Relational

Calculus6

.

52?Silberschatz,

Korth

and

Suda

rDatabase

System

Concepts

-6

t

hEditionDomain

Relational

Calculus6

.

53?Silberschatz,

Korth

and

Suda

rDatabase

System

Concepts

-6

t

hEditionn

A

nonprocedural

query

language

equivalent

in

power

to

the

tuplerelational

calculusn

Each

query

is

an

expression

of

the

form:{

x1,

x2,

…,

xn

|

P

(x1,

x2,

…,

xn)}l

x1,

x2,

…,

xn

represent

domain

variablesl

P

represents

a

formula

similar

to

that

of

the

predicatecalculusExample

Queries6

.

54?Silberschatz,

Korth

and

Suda

rDatabase

System

Concepts

-6

t

hEditionn

Find

the

ID,

name,

dept_name,

salarysalary

is

greater

than

$80,000l

{<

i,

n,

d,

s>

|

<i,

n,

d,

s>for

instructors

whoseinstructor

s

80000}n

As

in

the

previous

query,

but

output

only

the

ID

attribute

valuel

{<

i>

|

<

i,n,

d,

s>

instructor

s

80000}n

Find

the

names

of

all

instructors

whose

department

is

in

theWatson

building{<

n

>

|

i,

d,

s

(<

i,

n,

d,

s

>

instructorb,

a

(<

d,

b,

a>

department b

=“Watson”

))}Example

Queries6

.

55?Silberschatz,

Korth

and