Relational Algebra

1
Chapter 4

• Relational Algebra

2
Agenda

• Relational Languages
• Relational Algebra

3
Relational Languages

• Procedural language
• Relational algebra
• Non-procedural language
• Relational Calculus
• Other Languages
• Transform-oriented languages
• Graphical languages
• Fourth-generation languages
• Fifth-generation languages

4
Relational AlgebraIntra Relation

• Selection - horizontal partition
• SELECT relation WHERE condition GIVING relation
• ? condition(relation)
• Projection - vertical partition
• PROJECT relation OVER (attribute,
  attribute,...)GIVING relation
• ? attributes(relation)

5
Union-Compatible

• Union - tuples in either of 2 union compatible
  relations
• relation1 UNION relation2 GIVING relation
• relation1 ? relation2
• Difference - tuples in one union compatible
  relation, but not other
• relation1 MINUS relation2 GIVING relation
• relation1 - relation2

6
Union-Compatible

• Intersection - tuples in both of 2 union
  compatible relations
• relation1 INTERSECT relation2 GIVING
  relation
• relation1 ? relation2

7
Any 2 Relations

• Product - Cartesian product of any two relations
• relation1 TIMES relation2 GIVING relation
• relation1 ??relation2
• Join - common domain
• relation1 JOIN relation2 WHERE condition
  GIVING relation
• relation1????condition?relation2
• Division - same pattern
• relation1 divided by relation2 GIVING relation
• relation1 / relation2

8
Types of Join

• Equi - based on equality with 2 identical
  attribute columns
• Theta - a join based on a relational operator
  (,lt,gt,lt,gt,)
• Natural - equijoin with no duplicate column
• Outer - includes tuples without matches (left,
  right, full)

9
Types of Outer Join

• Left outer join - including every tuple of the
  left (first) relation in the solution
• Right outer join - including every tuple of the
  right (second) relation in the solution
• Full outer join - including every tuple of the
  left (first) relation and right (second) relation
  in the solution

10
Types of Join

• Semi-join only the tuple of the left (first)
  relation with match
• relation1 SEMIJOIN relation2 WHERE condition
  GIVING relation
• relation1 lxcondition relation2
• Semi-theta-join
• Semi-natural join

11
Division Examples
12
Relation R Relation R Relation C Relation T1

1 J J 1
1 K K 3
1 L L
2 J
2 K
3 L
3 J
3 K

13
Relation R Relation R Relation C Relation T1

Jones B.S. B.S. Jensen
Jensen B.S. M.S.
Jensen M.S. Ph.D.
Jensen Ph.D.
Smith B.S. B.S. Jensen
Smith M.S. M.S. Smith
Roger B.S.
Roger Ph.D. B.S. Jones
Jensen
Smith
Roger
14
Points To Remember

• Relational Languages
• Relational Algebra

15
Assignment

• Review chapters 1-4, appendix c
• Read Chapters 5-6
• Do Exercise 4.12 (only relational algebra) and
  4.13
• Due date
• Exam date