Relational Algebra

1
Relational Algebra

• Chapter 6

2
What will you learn?

• Godds original eight operators
• closure property
• semantics
• what is the Algebra for?
• Additional Operators

3
1. Introduction

• Relational algebra is basically just a set of
  operators that take relations as their operands
  and return a relation as their result.
• Godds original eight operators
• The traditional set operators
• Union(?)
• Intersection(?)
• Difference(?)
• Cartesian product(????)
• The special relational operators
• restrict/select(??)
• Project(??)
• Join(??)
• Divide(?)

4
Fig. 6.1 The original eight operators (overview)
5
2. Closure property

• Closure property
• The output from any given relational operation is
  another relation.
• Nested relational expressions
• RENAME operator
• Rename attributes within a specified relation.
• e.g. S RENAME CITY AS SCITY

6
3. Semantics-- Union
Two relations are type-compatible if they have
the same set of attribute names which are defined
on the same domains

• The union of two (type-compatible) relations A
  and B
• A UNION B tt?A ? t?B
• is a relation with the same heading as each of A
  and B and with a
• body consisting of the set of all tuples t
  belonging to A or B or both.

7
Example of Union
Airport1
airport_name
country_name
Airport1 UNION Airport2
London/Gk Naples Pisa
England Italy Italy
airport_name
country_name
London/Gk Naples Pisa London/Hw Manchester
England Italy Italy England England
Airport2
airport_name
country_name
London/Gk London/Hw Manchester
England England England
8
3. Semantics-- Intersection
The intersection of two (type-compatible)
relations A and B A INTERSECTION B tt?A
? t?B - is a relation with the same heading as
each of A and B and with a body consisting of
the set of all tuples t belonging to both A and B.
9
Example of Intersection
10
3. Semantics-- Difference
The difference of two (type-compatible) relations
A and B in that order A MINUS B
tt?A ? !t?B - is a relation with the same
heading as each of A and B and with a body
consisting of the set of all tuples t belonging
to A and not to B.
11
Example of Difference
12
Example of Union/Intersection/Difference
Fig. 6.2 Union, intersection, and difference
examples
13
3. Semantics - Product
Let relations A and B have headings X and Y
respectively. The Cartesian product of A and B
where they have no common attribute names A
TIMES B aba?A ? b?B - is a relation with
the heading that is the sum of the headings of A
and B and body consisting of the set of all
tuples t such that t is the coalescing of a
tuple a in A and a tuple b in B.
?
14
More about Product
Semantics Product

• The cardinality of the result of A TIMES B is
  the product of the cardinalities of A and B
• The degree of the result is the sum of the
  degrees of A and B
• The Cartesian product is not very important in
  practice
• The result is often large and space-costly but
  produces no more information than there was in
  the input

15
Example of Product
Fig. 6.3  Cartesian product example
16
3. Semantics - Restrict

• The restriction of relation A on the condition
• A WHERE condition
• is a relation with the same heading as A and with
  a
• body consisting
• of the set of all tuples t of A such that the
  condition is true for t

17
Restriction Condition
A WHERE X q Y
X, Y are attributes of A, and must be defined on
the same domain q is any simple scalar
comparison operator like , ?, gt, ? etc. In
fact, the restriction condition can be an
arbitrary Boolean combination of simple
comparisons connected by AND, OR, NOT. Take
note Unlike the traditional set operators,
restrict takes a relation and an expression as
input, and produces a relation as output. Some
concepts like commutativity and associativity no
longer applicable.
18
Example of Restrictions
19
Example of Restrictions

• Fig. 6.4 Restriction examples

20
User-defined Types
21
User-defined Types
22
3. Semantics - Project
The projection of relation A on attributes X, Y,
, Z A X, Y, , Z - is a relation with
the heading X, Y, , Z and with the a
body consisting of the set of all tuples Xx,
Yy, , Zz such that a tuple appears in A with
X-value x, Y-value y, , Z-value z.
23
More about Projection
The projection operator produces the output table
by 1) eliminating all attributes not specified,
and 2) eliminating any duplicated tuples A --
identity projection A -- nullary projection
(both not very useful but legal) We may
project some attributes away instead of the
standard way This operator can be used to 1)
rename attributes, and 2) permute attributes
24
Example of Projections
25
Example of project

• Fig. 6.5 Projection examples

26
3. Semantics - Natural Join
Let relations A and B have headings X, Y
and Y, Z (where X, Y and Z are composite
attributes). The (natural) join of A and B A
JOIN B - is a relation with the heading X, Y,
Z and body consisting of the set of all tuples
Xx, Yy, Zz such that a tuple appears in A
with X-value x and Y-value y and a tuple appears
in B with Y-value y and Z-value z.
27
More about Natural Join
A JOIN B JOIN C (A JOIN B) JOIN C A JOIN (B
JOIN C) (associative) A JOIN B B JOIN A
(commutative) If A and B have no attribute names
in common, A JOIN B ? A TIMES B
28
Example of Natural Join
29
Example of Natural Join

• Fig. 6.6 The natural join S JOIN P

30
3. Semantics ?-Join(???)
A TIMES B WHERE R?S e.g. ((S RENAME CITY AS
SCITY) TIMES (P RENAME CITY AS PCITY))
WHERE SCITYgtPCITY

• Fig. 6.7 Greater-than join of suppliers and
  parts on cities

31
3. Semantics equijoin(????)

• If ? is "", the ?-join is called an equijoin.

32
3. Semantics Divide
The division of A by B per C A DIVIDEBY B
PER C
Let relations A , B and C have headings X,Y,
and X, Y (where X and Y are composite
attributes).

• is a relation with the heading X and
• body consisting of all tuples Xx such that a
  tuple Xx, Yy appears in C for all tuples
• Yy appearing in B.

33
Example of Division
34
Example of Division
A DIVIDEBY B PER C

• Fig. 6.8 Division examples

35
3. Semantics Associativity and
Commutativity???????
A UNION B UNION C (A UNION B) UNION C A
UNION (B UNION C) (associative) (Applicable to
INTERSECT and TIMES but not to MINUS) A UNION B
B UNION A (commutative) (Applicable to
INTERSECT and TIMES but not to MINUS) Note that
the Cartesian product operation of set theory is
neither associative nor commutative, but the
relational version as we have defined it IS both.
36
Note

• Some of Godd eight operators are not primitive
• restrict, project,product,union,difference-?mini
  mal set

37
4. Examples

• Fig. 3.8. The Suppliers and parts database(sample
  values)

P111
38
4. Examples

• Get supplier names for suppliers who supply part
  P2.
• Get supplier names for suppliers who supply at
  least one red part.

39
4. Examples

• Get supplier names for suppliers who supply all
  parts.
• Get supplier numbers for suppliers who supply at
  least all those parts supplied by supplier S2.
• Get all pairs of supplier numbers such that the
  two suppliers concerned are "colocated" (i.e.,
  located in the same city).

40
4. Examples

• Get supplier names for suppliers who do not
  supply part P2.

41
5. What is the Algebra for?

• The fundamental intent of the algebra is to allow
  the writing of the relational expressions.
• Defining a scope for retrieval
• Defining a scope for update
• Defining integrity constraint
• Defining derived relvars
• Defining stability requirements
• Defining security constraints

42
5. What is the Algebra for?...

• serve as a convenient basis for optimization
• the expressions serving as a high-level,
  symbolic representation of the users intent, can
  be transformed into the logically equivalent.
• ((SP JOIN S ) WHERE PP(P2))SNAME
• ((SP WHERE PP(P2) JOIN S )SNAME
• yardstick to measure the expressive power of
  some given language.
• relational compete

43
6. Additional Operators-Semijoin

• A SEMIJOIN B
• The semijoin of A with B is the join of A and B,
  projected over the attributes of A.
• (A join B ) x, y
• E.g.
• Get S, SNAME, STATUS, and CITY for suppliers
  who supply part P2

44

• A INNER JOIN B
• A FULL OUTER JOIN B
• A RIGHT OUTER JOIN B
• A LEFT OUTER JOIN B

45

• (1)
• TAB1 TAB2
• C1 C2 CX CY
• --- ---- ----- ----
  
• A 11 A 21
• B 12 C 22
• C 13 D 23
  
• The following results are desired
• C1 C2 CX CY
• ---- ---- ---- ----
• A 11 A 21
• C 13 C 22
• - - D 23
• Which of the following joins will yield the
  desired results?
• A. SELECT FROM tab1 INNER JOIN tab2 ON
  c1cx
• B. SELECT FROM tab2 FULL OUTER JOIN tab1 ON
  c1cx
• C. SELECT FROM tab2 RIGHT OUTER JOIN tab1
  ON c1cx
• D. SELECT FROM tab1 RIGHT OUTER JOIN tab2
  ON c1cx

46

• (2) Given the two following tables
• POINTS
• Name Points
• Wayne Gretzky 244
• Jaromir Jagr 168
• Bobby Orr 129
• Bobby Hull 93
• Brett Hull 121
• Mario Lemieux 189
• PIM
• Name PIM
• Mats Sundin 14
• Jaromir Jagr 18
• Bobby Orr 12
• Mark Messier 32
• Brett Hull 66
• Mario Lemieux 23
• Joe Sakic 94

47

• Which of the following statements will display
  the name, points and PIM for players in either
  table?
• A. SELECT points.name, points.points,
  pim.name, pim.pim
• FROM points INNER JOIN pim ON
  points.namepim.name
• B. SELECT points.name, points.points,
  pim.name, pim.pim
• FROM points FULL OUTER JOIN
  pim ON points.namepim.name
• C. SELECT points.name, points.points,
  pim.name, pim.pim
• FROM points LEFT OUTER JOIN
  pim ON points.namepim.name
• D. SELECT points.name, points.points,
  pim.name, pim.pim
• FROM points RIGHT OUTER JOIN
  pim ON points.namepim.name

48

• (3) Given the following table definition
• STAFF
• id INTEGER
• name CHAR(20)
• dept INTEGER
• job CHAR(20)
• years INTEGER
• salary DECIMAL(10,2)
• comm DECIMAL(10,2)

49

• A. SELECT dept, COUNT() FROM staff GROUP BY
  dept HAVING comm gt 5000 ORDER BY 2 DESC
• B. SELECT dept, COUNT() FROM staff WHERE
  comm gt 5000 GROUP BY dept, comm ORDER BY 2 DESC
• C. SELECT dept, COUNT() FROM staffF GROUP BY
  dept HAVING MAX(comm) gt 5000 ORDER BY 2 DESC
• D. SELECT dept, comm, COUNT(id) FROM staff
  WHERE comm gt 5000 GROUP BY dept, comm ORDER BY 3
  DESC

50
6. Additional Operators- Semidifference

• The semidifference between A and B (in that
  order), A SEMIMINUS B, is defined to be
  equivalent to
• E.g.
• Get S, SNAME, STATUS, and CITY for suppliers
  who do not supply part P2

51
6. Additional Operators- Extend

• EXTEND takes a relation and returns another that
  is identical to the given one except that it
  includes an additional attribute, values of which
  are obtained by evaluating some specified
  computational expression.

???

• Fig. 6.9 An example of EXTEND

52
6. Additional Operators- Extend

• EXTEND A ADD exp AS Z
• to be a relation
• with heading equal to the heading of A
  extended with the new attribute Z and with
• body cosisting of all tuples t such that t is
  a tuple of A extended with a value for the new
  attribute Z that is computed by evaluation the
  expression exp on that tuple of A
• the cardinality of result is equal to that of
  A, degree equal to that of A plus one.
• examples in p174

53

• Extend S ADD COUNT((SP RENAE S AS X) WHERE XS)
• Aggregate operator
• COUNT,SUM,AVG,MAX,MIN, ALL,ANY
• ltop namegt
• (ltrelational expressiongt ,ltattribute namegt)
• SUM (SP WHERE SS(S1),QTY)
• SUM ((SP WHERE SS(S1))QTY)

54
6. Additional Operators-Summarize
Vertical , column-wise
Fig. 6.11 An example of SUMMARIZE
55
6. Additional Operators-Summarize

• SUMMARIZE A PER B ADD summary AS Z
• the result has cardinality equal to that of B
  and
• degree equal to that of B plus one.
• summary type COUNT,SUM,AVG,MAX,MIN,
  ALL,ANY,COUNTD,SUMD,AVGD

56
????
57
????
?? 1.    ?????????? 2.    ??????????? 3.   
?????????????? 4.    ?????????????????? 5.
   ???????1?3????? 6.    ????????????? 7.   
?????2????? 8.    ??????????????6???????? 9.   
?????????6???????? 10.   ???????????????? 11.
  ????(or ????)????? 12.   ?????????? 13.  
??????
58
Exercises
Reading Chapter 6 (Dates book) Exercises 6.
3,6.11 and five from 6.13 6.50
The End