Relational Algebra

1
Relational Algebra
2
Outline

• The Original Algebra Syntax and Semantics
• What is the Algebra For?
• Further Points and Additional Operators
• Grouping and Ungrouping

3
Relational Algebra

• The relational algebra is a collection of
  operators that take relations as their operands
  and return a relation as their result
• Original algebra eight operators, in two groups
  of four
• Traditional set operators union, intersect,
  difference, Cartesian product
• Special relational operators restrict, project,
  join, divide
• Possible relational operators are essentially
  unlimited
• The operators are read only

4
The Original Eight Operators (Overview)
5
The Original Eight Operators (Overview) (Cont.)
6
The Original Algebra Semantics
7
Restrict

• Let relation a have attributes X, Y, , Z (and
  possibly others), and let p be a truth-valued
  function whose parameters are, precisely, some
  subset of X, Y, , Z. Then the restriction of a
  according to p -- a WHERE p, -- is a relation
  with the same heading as a and with body
  consisting of all tuples of a such that p
  evaluates to TRUE for the tuple in question
• p boolean expression and predicate, also called
  restriction condition

8
S, P, and SP
9
Restriction Examples
10
Project

• Let relation a have attributes X, Y, , Z (and
  possibly others). Then the projection of relation
  a on X, Y, , Z -- a X, Y, , Z , -- is a
  relation with
• A heading derived from the heading of a by
  removing all attributes not mentioned in the set
  X, Y, , Z
• a body consisting of all tuples X x, Y y , , Z
  z such that a tuple appears in a with X value
  x, Y value y, , and Z value z
• No attribute can be mentioned more than once in
  the attribute name commalist
• An alternative specification is to name the
  attributes to be excluded P ALL BUT WEIGHT

11
Projection Examples
Duplicates are eliminated
12
Join

• When unqualified, join means natural join
• Let relations a and b have attributes X1, X2,,
  Xm, Y1, Y2,, Yn and Y1, Y2,, Yn, X1, X2,,
  Xm
• The Y attributes are the only common attributes
  to a and b
• The (natural) join of a and b, a JOIN b, is a
  relation with heading X, Y, Z and body
  consisting of all tuples X x, Y y, Z z 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
• Note joins are not always between a foreign key
  and a matching primary (or candidate) key

13
The Natural Join S JOIN P
14
Cartesian Product

• The (relational) Cartesian Product of two
  relations a and b, a TIMES b, where a and b have
  no common attribute names, to be a relation with
  a heading that is the (set theory) union of the
  headings of a and b and with a body consisting of
  the set of all tuples t such that t is the (set
  theory) union of a tuple appearing in a and a
  tuple appearing in b
• The cardinality of the result is the product of
  the cardinalities, and the degree of the result
  is the sum of the degrees of the input relations
  a and b
• If the two relations have common attribute names
  ? RENAME

15
Cartesian Product Example
16
?-Join

• Let relation a and b satisfy the requirements for
  Cartesian product (i.e., they have no attribute
  names in common) let a have attributes X let b
  have attributes Y and let X,Y, and ?satisfy the
  requirements for ?-restriction. Then the ?-join
  of relation a on attribute X with relation b on
  attribute Y is defined to be the result of
  evaluating the expression(a TIMES b) where X?Y
• In other words, it is a relation with the same
  heading as the Cartesian product of a and b, and
  with a body consisting of the set of all tuples t
  such that t appears in that Cartesian product and
  the expression X?Y evaluates to TRUE for the that
  tuple t

17
An Example of gt-Join

• ((S RENAME CITY AS SCITY) TIMES (P RENAME CITY
  AS PCITY)) WHERE SCITY gt PCITY

18
Equijoin

• If ?is , the ?-join is called an equijoin
• The result of an equijoin must include two
  attributes with the property that the values of
  those two attributes are equal in every tuple in
  the relation
• If one of those two attributes is projected away
  and the other renamed appropriately (if
  necessary), the result is the natural join
• S JOIN P is equivalent to( (S TIMES (P RENAME
  CITY AS PCITY) ) WHERE CITY
  PCITY) ALL BUT PCITY

19
Divide

• Small Divide uses one relation expression as
  divisor, Great Divide uses two
• For small divide a DIVIDEDBY b PER c
• where a is the dividend, b is the divisor, and c
  is the mediator
• Used to determine who in a relates to the
  complete set in b
• Formal definition (for a revised small divide)
• Let relations a and b have (composite) distinct
  attributes X and Y, and relation c has
  attributes X, Y. Then the division of a by b
  per c is a relation with heading X and body
  consisting of all tuples X x appearing in a
  such that a tuple X x, Y y appears in c for all
  tuples Y y appearing in b

20
Divide Example
21
Divide Example (Cont.)

• Get suppliers who supply all parts
• S JOIN ( S S DIVIDEBY P P PER SP S
  P )
• Another expression
• S WHERE ( (SP RENAME S AS X) WHERE X S) P
  P P
• (SP RENAME S AS X) WHERE X S) P yields
  the set of part numbers for parts supplied by
  that supplier
• That set of part numbers is the compared with the
  set of all currently known part numbers P P
• If and only if the two sets are equal, the
  corresponding supplier tuples appear in the result

22
S, P, and SP
23
Union

• Union operates on two sets and returns a set that
  contains all elements belonging to either
• Both sets must be of the same type - formerly
  known as union compatibility
• Given two relations a and b of the same type, the
  union of the two relations, a UNION b, is a
  relation of the same type, with body consisting
  of all tuples t such that t appears in a or b or
  both
• Relations cannot have duplicate tuples we say
  loosely that UNION eliminates duplicates

24
Union Example
25
Intersect and Difference

• Given two relations a and b of the same type, the
  intersection of the two relations, a INTERSECT b,
  is a relation of the same type, with body
  consisting of all tuples t such that t appears in
  both a and b
• Given two relations a and b of the same type, the
  difference of the two relations, a MINUS b, is a
  relation of the same type, with body consisting
  of all tuples t such that t appears in a and not
  in b

26
Intersection and Difference Example
27
Restrict, Union, Intersection, and Difference

• a where p1 OR p2 (a where p1) UNION (a where
  p2)
• a where p1 AND p2 (a where p1) INTERSECT (a
  where p2)
• a where NOT ( p ) a MINUS (a where p)

28
What is the Algebra for?

• The purpose of the algebra is to allow the
  writing of relational expressions
• Applications of the algebra (expressions)
• Defining a scope for retrieval
• Defining a scope for update
• Defining integrity constraints
• Defining derived relvars
• Defining stability requirements (for concurrency
  control)
• Defining security constraints

29
What is the Algebra for? (Cont.)

• The relational expressions serve as a high-level,
  symbolic representation of the users intent
• The algebra serves as a convenient basis for
  optimization
• Example ((SP JOIN S) WHERE P P(P2)) SNAME
  ?((SP WHERE P P(P2)) JOIN S SNAME
• An implemented language can be said to be
  relationally complete if it is at least as
  powerful as the algebra

30
Further Points

• The operations join, intersect, and divide can be
  defined in terms of the other five
• Union, difference, product, restrict, and project
  constitute a primitive or minimal set
• Many operators are associative Union,
  intersect, times, join, but not minus
• (a UNION b) UNION c a UNION (b UNION c)
• Many operators are commutative Union,
  intersect, times, join, but not minus
• a UNION b b UNION a

31
Additional Operators
32
Semijoin and Semidifference

• Let a, b, X, and Y be as defined in Join, then
  the semijoin of a with b, a SEMIJOIN b, is
  defined as (a JOIN b) X, Y
• The result is the tuples of a that have a
  counterpart in b
• Get S, SNAME, STATUS, and CITY for suppliers who
  supply part P2 S SEMIJOIN ( SP WHERE P
  P(P2) )
• The semidifference between a and b, a SEMIMINUS
  b, is defined as a MINUS (a SEMJOIN b)
• The result is the tuples of a that have no
  counterpart in b

33
Extend

• The extend operation is to support computational
  capability
• 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
• EXTEND P ADD ( WEIGHT 454) AS GMWT

34
More EXTEND Examples

• EXTEND S ADD Supplier AS TAG
• EXTEND (P JOIN SP) ADD (WEIGHT QTY) AS SHIPWT
• (EXTEND S ADD CITY AS SCITY) ALL BUT CITY
• EXTEND P ADD (WEIGHT 454 AS GMWT, WEIGHT 16
  AS OZWT
• ( (EXTEND P ADD ( WEIGHT 454) AS GMWT) WHERE
  GMWT gt WEIGHT (10000.0) ) ALL BUT GMWT
• P WHERE (WEIGHT 454) gt WEIGHT (10000.0)

35
Aggregate Operators

• Derive a single scalar value from the values
  appearing in some specified attribute of some
  specified relation
• Include COUNT, SUM, AVG, MAX, MIN, ALL, and ANY
• ltagg op namegt ( ltrelation expgt , ltattribute
  namegt)
• Example
• SUM (SP WHERE S S (S1), QTY)
• SUM ( (SP WHERE S S (S1)) QTY )
• Give the total of all distinct shipment
  quantities for supplier S1

36
Summarize

• SUMMARIZE SP PER P P ADD SUM (QTY) AS TOTQTY
• Evaluate to a relation with attribute P and
  TOTQTY, in which there is one tuple for each P
  value in the projection of P over P, containing
  that P value and the corresponding total
  quantity
• ( EXTEND S S ADD ( ( ( SP RENAME S AS X
  ) WHERE X S) AS Y, COUNT (Y) AS NP ) ) S,
  NP

37
More Summarize Examples

• SUMMARIZE SP PER P P ADD ( SUM (QTY) AS
  TOTQTY, AVG (QTY) AS AVGQTY )
• SUMMARIZE S PER S CITY ADD AVG (STATUS) AS
  AVG_STATUS
• SUMMARIZE P JOIN SP PER P CITY ADD COUNT AS
  NSP

38
Grouping and Ungrouping
39
Grouping

• Required because relations can have attributes
  that are themselves relations
• Provides a map between such relations and flat
  relations
• E.g. SP GROUP P, QTY AS PQ (group SP by S)
• The heading of the new relation S S, PQ
  RELATION P P, QTY QTY
• PQ is a relation-valued attribute
• The body contains exactly one tuple for
  eachdistinct S value in SP
• R GROUP A1, A2, , An AS B has degree equalto
  nR n 1, where nR is the degree of R

40
Ungrouping

• Returns the original relation
• In the example, the original SP relation
• If you group, you can always ungroup, but the
  converse is not necessarily true
• This occurs when the relations being ungrouped
  were not validly grouped in the first place