relational_algebra examples ppt

8/19/2019 Relational_Algebra Examples Ppt

1/35

Relational Algebra

Archana Gupta

CS 157


2/35

What is Relational Algebra?

Relational Algebra is formal description of

how relational database operates.

t is a procedural !uer" language# i.e. user

must define both $how% and $what% toretrie&e.

t consists of a set of operators that

consume either one or two relations asinput. An operator produces one relation

as its output.


3/35

ntroduction to Relational Algebra

ntroduced b" '. (.

Codd in 1)7*.

Codd proposed such

an algebra as a basis

for database !uer"

languages.


4/35

+erminolog"

Relation , a set of tuples.

+uple , a collection of attributes whichdescribe some real world entit".

Attribute , a real world role pla"ed b"a named domain.

-omain , a set of atomic &alues.

Set , a mathematical definition for acollection of obects which contains noduplicates.


5/35

Algebra /perations

0nar" /perations , operate on one

relation. +hese include select# proect

and rename operators.

inar" /perations , operate on pairs

of relations. +hese include union# set

difference# di&ision# cartesian product#

e!ualit" oin# natural oin# oin andsemi,oin operators.


6/35

Select /perator

+he Select operator selects tuples that satisfies apredicate2 e.g. retrie&e the emplo"ees whosesalar" is 3*#***

б Salary = 30,000 (Employee)

Conditions in Selection4

Simple Condition4 (attribute)(comparison)(attribute)

(attribute)(comparison)(constant)

Comparison4 =,≠,≤,≥,

Condition4 combination of simple conitions !it" #$%, &', $&


7/35

Select /perator 'ample

6ame Age Weight

arr" 38 9*

Sall" :9 ;8

George :) 7*

elena 58 58


8/35

∏ retrie&es a column. -uplication

is not permitted.

e.g.# name of emplo"ees4

∏ name>'mplo"ee

e.g.# name of emplo"ees earning more

than 9*#***4∏ name>=Salar"@9*#***>'mplo"ee


9/35


10/35

=Salary>80,000>'mplo"ee


11/35

Cartesian # " that can be constructed

from gi&en sets# and B# such that

belongs to and " to B.

t defines a relation that is the

concatenation of e&er" tuple of relation R

with e&er" tuple of relation S.


12/35

Cartesian


13/35

Rename /perator

n relational algebra# a rename is a unar"

operation written as D a / b >' where4

a and b are attribute names

' is a relation+he result is identical to ' ecept that the b field in

all tuples is renamed to an a field.

'ample# rename operator changes the name of

its input table to its subscript#Demployee>'mp

Changes the name of 'mp table to emplo"ee


14/35

Rename /perator 'ample

6ame Salar"

arr" 9*#***

Sall" )*#***George 7*#***

elena 58#:9*


15/35

0nion /perator

+he union operation is denoted 0 as in settheor". t returns the union >set union of twocompatible relations.

(or a union operation r 0 s to be legal# were!uire that#

r and s must ha&e the same number ofattributes.

+he domains of the correspondingattributes must be the same.

As in all set operations# duplicates areeliminated.


16/35

0nion /perator 'ample

! "!

Su#an $ao

%ame#h Shah

&ar'ara (one#

Amy or)

(immy Wang

! "!

(ohn Smith

%icar)o &ro*n

Su#an $ao

ranci# (ohn#on

%ame#h Shah

Student


17/35

ntersection /perator

-enoted as ∩ . (or relations R and S# intersectionis R ∩ S.

-efines a relation consisting of the set of alltuples that are in both R and S.

R and S must be union,compatible.

'pressed using basic operations4

R ∩ S E R F >R F S


18/35

ntersection /perator 'ample

! "!

Su#an $ao

%ame#h Shah

&ar'ara (one#

Amy or)

(immy Wang

! "!

(ohn Smith

%icar)o &ro*n

Su#an $ao

ranci# (ohn#on

%ame#h Shah

Student


19/35

Set -ifference /perator

(or relations R and S#

Set difference R , S# defines a relation

consisting of the tuples that are in relationR# but not in S.

Set difference S F R# defines a relation

consisting of the tuples that are in relationS# but not in R.


20/35

Set -ifference /perator 'ample

! "!

Su#an $ao

%ame#h Shah

&ar'ara (one#

Amy or)

(immy Wang

! "!(ohn Smith

%icar)o &ro*n

Su#an $ao

ranci# (ohn#on

%ame#h Shah

Student


21/35

-i&ision /perator

+he di&ision operator taes as input tworelations# called the di&idend relation >r onscheme ' and the di&isor relation >s onscheme S) such that all the attributes in S also

appear in ' and S is not empt". +he output ofthe di&ision operation is a relation on scheme ' with all the attributes common with S.


22/35

-i&ision /perator 'ample

Stu)ent a#-

re) .ata'a#e

re) .ata'a#e

re) 1ompiler

2ugene .ata'a#e

Sara .ata'a#e

Sara .ata'a#e

2ugene 1ompiler

a#-

.ata'a#e

.ata'a#e

Completed -


23/35

6atural oin /perator

6atural oin is a d"adic operator that is writtenas ' ll S where ' and S are relations. +heresult of the natural oin is the set of allcombinations of tuples in ' and S that are

e!ual on their common attribute names.


24/35

6atural oin 'ample

!ame 2mp. .ept!ame

arry 345 inance

Sally 4 Sale#

6eorge 340 inance

arriet 0 Sale#

.ept!ame 7gr

inance 6eorge

Sale# arriet

Pro)uction 1harle#

'mplo"ee

-ept!ame 2mp. .ept!ame 7gr

arry 345 inance 6eorge

Sally 4 Sale# arriet

6eorge 340 inance 6eorge

arriet 0 Sale# arriet

'mplo"ee ll -ept

(or an eample# consider the tables Employee and %ept and their

natural oin4


25/35

Semioin /perator

+he semioin is oining similar to the natural oinand written as ' ⋉ S where ' and S arerelations. +he result of the semioin is onl" theset of all tuples in ' for which there is a tuple in

S that is e!ual on their common attributenames.


26/35

Semioin 'ample

!ame 2mp. .ept!ame

arry 345 inance

Sally 4 Sale#

6eorge 340 inance

arriet 0 Sale#

.ept!ame 7gr

Sale# arriet

Pro)uction 1harle#

'mplo"ee

-ept!ame 2mp. .ept!ame

Sally 4 Sale#

arriet 0 Sale#

'mplo"ee⋉ -ept

(or an eample consider the tables Employee and %ept and their semi oin4


27/35

/uteroin /perator

"et outer join

+he left outer oin is written as ' E S where ' andS are relations. +he result of the left outer oin is theset of all combinations of tuples in ' and S that are

e!ual on their common attribute names# in additionto tuples in ' that ha&e no matching tuples in S.

%ight outer join

+he right outer oin is written as ' E S where ' and S are relations. +he result of the right outer oin

is the set of all combinations of tuples in ' and S that are e!ual on their common attribute names# inaddition to tuples in S that ha&e no matching tuplesin ' .


28/35

Ieft /uteroin 'ample

!ame 2mp. .ept!ame

arry 345 inance

Sally 4 Sale#

6eorge 340 inance

arriet 0 Sale#

.ept!ame 7gr

Sale# arriet

'mplo"ee

-ept

!ame 2mp. .ept!ame 7gr

arry 345 inance 9

Sally 4 Sale# arriet6eorge 340 inance 9


'mplo"ee =: -ept

(or an eample consider the tables Employee and %ept and their left outer oin4


29/35

Right /uteroin 'ample

!ame 2mp. .ept!ame

arry 345 inance

Sally 4 Sale#

6eorge 340 inance

arriet 0 Sale#

.ept!ame 7gr

Sale# arriet

Pro)uction 1harle#

'mplo"ee

-ept


Sally 4 Sale# arriet

arriet 0 Sale# arriet9 9 Pro)uction 1harle#

'mplo"ee := -ept

(or an eample consider the tables Employee and %ept and their right outer oin4


30/35

(ull /uter oin 'ample

!ame 2mp. .ept!ame

arry 345 inance

Sally 4 Sale#

6eorge 340 inance

arriet 0 Sale#

.ept!ame 7gr

Sale# arriet

Pro)uction 1harle#

'mplo"ee

-ept


arry 345 inance 9

Sally 4 Sale# arriet6eorge 340 inance 9


9 9 Pro)uction 1harle#

'mplo"ee =:= -ept

+he outer join or ull outer join in effect combines the results of the left

and right outer oins.(or an eample consider the tables Employee and %ept and their full outer

oin4


31/35


32/35

-i&ision

6ot supported as a primiti&e operator# but useful for

epressing !ueries lie4

in sailors !"o "a*e

reser*e all boats.


33/35

'amples of -i&ision AH

sno pno

s1 p1

s1 p2

s1 p3s1 p4

s2 p1

s2 p2

s3 p2s4 p2

s4 p4

pno

p2

pno

p2

p4

pno

p1

p2

p4

sno

s1

s2s3

s4

sno

s1

s4

sno

s1

A

B1

B2B3

A/B1 A/B2 A/B3


34/35

'pressing AH 0sing asic

/perators-i&ision is not essential op2 ust a useful shorthand.>Also true of oins# but oins are so common that s"stemsimplement oins speciall". -i&ision is 6/+ implemented in

SJI.

+ea4 (or S#ES-.'&%/0S# compute all productssuch that there eists at least one supplier not suppl"ing

it.

1 &alue is is2ualifie if b" attaching y &alue from 3# we obtain

an 1y tuple that is not in #.

))Pr )((( SalesoductsSales sid sid

A −×= π π

+he answer is πsid>Sales , A


35/35

relational_algebra examples ppt

Documents