RELATIONAL OPERATORS
A S# NAME STATUS CITY
S1 Smith Single London
S2 Clark Married London
B S# NAME STATUS CITY
S1 Smith Single London
S3 Jones Single Paris
A WHERE STATUS = 'Single'
S# NAME STATUS CITY
S1 Smith Single London
A [CITY]
CITY
London
A UNION B
S# NAME STATUS CITY
S1 Smith Single London
S2 Clark Married London
S3 Jones Single Paris
A INTERSECT B
S# NAME STATUS CITY
S1 Smith Single London
A MINUS B
S# NAME STATUS CITY
S2 Clark Married London
B MINUS A
S# NAME STATUS CITY
S3 Jones Single Paris
C STATUS DREAM_CITY (Cities people dream about)
Single Paris
Single Rio
Married Pitt
Married Orlando
A NATURAL_JOIN C
S# NAME STATUS CITY DREAM_CITY
S1 Smith Single London Paris
S1 Smith Single London Rio
S2 Clark Married London Pitt
S2 Clark Married London Orlando
(A NATURAL_JOIN C) WHERE CITY=DREAM_CITY (People who live in dream city)
S# NAME STATUS CITY DREAM_CITY
-
B NATURAL_JOIN C
S# NAME STATUS CITY DREAM_CITY
S1 Smith Single London Paris
S1 Smith Single London Rio
S3 Jones Single Paris Paris
S3 Jones Single Paris Rio
(B NATURAL_JOIN C) WHERE CITY=DREAM_CITY (People who live in dream city)
S# NAME STATUS CITY DREAM_CITY
S3 Jones Single Paris Paris
D NAME CITY (People who own houses in cities)
Gates Seattle
Gates N.Y.C.
Siegel Paris
Siegel N.Y.C.
Siegel Rio
E CITY (List of dream cities)
Paris
Rio
D DIVIDE_BY E (People who own hosues in every dream city)
NAME
Siegel
E CITY
Paris
RIO
F NAME
Gates
Siegel
F PRODUCT E (or F TIMES E)
NAME CITY
Gates Paris
Gates Rio
Siegel Paris
Siegel Rio
THE RELATIONAL ALGEBRAIC LANGUAGE
EXAMPLE 1: Restrict the S relation with the condition that supplier is in London, and store the result in a temporary relation T1.
T1 := S WHERE CITY = 'London'
Note: In the above, ':=' is the assignment operator.
EXAMPLE 2: Project T1 to attribute S# and store the result in a temporary relation T2.
T2 := T1 [ S# ]
EXAMPLE 3: Find suppliers in London and store the result in a temporary relation T2.
T2 := (S WHERE CITY = 'London' ) [ S# ]
THE RELATIONAL ALGEBRAIC LANGUAGE
EXAMPLE 4: Find parts supplied by suppliers in London.
( T2 JOIN SP ) [ P# ]
or equivalently
( (S WHERE CITY = 'London' ) JOIN SP ) [ P# ]
Note: Why we did not project on P# before the join?
EXAMPLE 5: Create snapshot relation SR containing parts supplied by suppliers in London.
SR := ( (S WHERE CITY = 'London' ) JOIN SP ) [ P# ]
EXAMPLE 6: What new parts have been added by the suppliers in London during the last six months?
If SR1 is the snapshot relation created six months ago, and SR2 is the new snapshot relation, then the new parts can be obtained by:
SR2 MINUS SR1
THE RELATIONAL ALGEBRAIC LANGUAGE
EXAMPLE 7: What parts are no longer supplied by the suppliers in London?
SR1 MINUS SR2
EXAMPLE 8: What supplier supplies all the parts no longer supplied by the suppliers in London?
( SP [ S#, P# ] ) DIVIDE_BY ( SR1 MINUS SR2 )
Note: For the DIVIDE_BY operator to work, the two relations must be binary and unary.
EXAMPLE 9: Where are the suppliers supplying all the parts no longer supplied by the suppliers in London?
( (( SP [ S#, P# ] ) DIVIDE_BY ( SR1 MINUS SR2 )) JOIN S ) [CITY]