Relational Algebra and Relational Calculus

1
Lecture 13 (10/03/2005)

• Relational Algebra and Relational Calculus

2
Aggregation over Nulls and Duplicates

• Aggregate functions ignore nulls
• If all aggregated values are null? return null
• SUM, AVERAGE, MIN and MAX include duplicates
• COUNT is not clear
• COUNT ? includes duplicates
• COUNT_DISTINCT ? excludes duplicates
• Grouping over null attributes
• ? a new group

3
Binary Relational Operations

• Theta-JOIN Operation
• R ltjoin conditiongtS where R(A1, A2, , An) and
  S(B1, B2, , Bm)
• The result of a join operation is a relation
  Q(A1, A2, , An, B1, B2, , Bm) and in this order
  
• with nm attributes
• Contains one tuple for each combination of tuples
  (one from R and one from S) whenever the
  combination satisfies the ltjoin conditiongt
• Theta is any operator
• Difference from Cartesian product?
• For every employee, find any other employee that
  earns more than s/he does.

4
Binary Relational Operations

• EQUIJOIN Operation
• The most common use of join involves join
  conditions with equality comparisons only
• In the result of an EQUIJOIN we always have one
  or more pairs of attributes (whose names need not
  be identical) that have identical values in
  every tuple
• Theta is the operator
• NATURAL JOIN Operation (additional attribute)
• Because one of each pair of attributes with
  identical values is extra, a new operation called
  natural joindenoted by was created to get rid
  of the second (superfluous) attribute in an
  EQUIJOIN condition
• The standard definition of natural join requires
  that the two join attributes, or each pair of
  corresponding join attributes, have the same name
  in both relations
• If this is not the case, a renaming operation
  is applied first

5
Additional Relational Operations

• The OUTER JOIN Operation
• In regular JOIN tuples without a matching (or
  related) tuple are eliminated
• Inner join
• Tuples with null in the join attributes are also
  eliminated
• This amounts to loss of information
• Outer joins, can be used when we want to keep all
  the tuples in R, or all those in S, or all those
  in both relations in the result of the join
• Regardless of whether or not they have matching
  tuples

6
Additional Relational Operations

• The left outer join operation
• Keeps every tuple in the first or left relation R
  in R S if no matching tuple is found in
  S, then the attributes of S in the join result
  are filled or padded with null values
• A similar operation, right outer join, keeps
  every tuple in the second or right relation S in
  the result of R S
• A third operation, full outer join, denoted by
  keeps all tuples in both the left and the
  right relations when no matching tuples are
  found, padding them with null values as needed

7
Additional Relational Operations

• For every employee, find any other employee that
  earns more than s/he does.
• Display all EMPLOYEEs including managers (with
  their managed DEPARTMENTs)
• Next slide
• Display all DEPARTMENTs names with their
  controlled PROJECTs (display names) including
  projects with no controlling DEPARTMENTS
• Assume new PROJECTs are not assigned DEPARTMENTs
  initially
• Display all DEPARTMENTs names with their
  controlled PROJECTs (display names) including
  projects with no controlling DEPARTMENTS
• Assume new PROJECTs are not assigned DEPARTMENTs
  initially
• Assume Accounting Dept has no controlled projects

8
(No Transcript)
9
Additional Relational Operations

• Recursive Closure Operations
• Another type of operation that, in general,
  cannot be specified in the basic relational
  algebra is recursive closure
• This operation is applied to a recursive
  relationship
• Retrieve all SUPERVISEES of an EMPLOYEE e at all
  levels
• That is, all employees e directly supervised by
  e all employees e directly supervised by each
  employee e all employees e directly
  supervised by each employee e and so on
• Although it is possible to retrieve employees at
  each level and then take their union, we cannot,
  in general, specify a query such as retrieve the
  supervisees of James Borg at all levels
  without utilizing a looping mechanism
• The SQL3 standard includes syntax for recursive
  closure

10
SSN
SUPERSSN

11
Reading Assignment

• Read about relational calculus
• Tuple and Domain
• Assignment 2
• Tuesday 10/11
• Exam 1
• Wednesday 10/19

12
Sailor Schema

• SAILOR (Sid, Sname, Rating, Age)
• BOAT (Bid, Bname, Color)
• RESERVES (Sid, Bid, Date)
• 1- Find names of sailors whove reserved boat
  103
• 2- Find names of sailors whove reserved a red
  boat
• 3- Find sailors whove reserved a red or a green
  boat

13
Sailor Schema

• SAILOR (Sid, Sname, Rating, Age)
• BOAT (Bid, Bname, Color)
• RESERVES (Sid, Bid, Date)
• 4- Find sailors whove reserved a red and a green
  boat
• 5- Find sailors whove made at least two
  reservations
• 6- Find sailors whove reserved at least two
  different boats
• 7- Find the names of sailors whove reserved all
  boats
• 8- Find the names of sailors whove reserved all
  Interlake boats
• 9- Find the names of sailors over 20 who have not
  reserved a red Boat

14
Find names of sailors whove reserved boat 103

• Reserved103 ? ?Sid(? Bid103 (Reserves))
• Result ? Reserved103 SidSid Sailor
• Or, Result ? Reserved103 Sailor (Natural Join)

15
Find names of sailors whove reserved a red boat

• Information about boat color only available in
  Boats so need an extra join
• RedBoats ? ?Sid(? ColorRed (Boats))
• ReservedRed ? ?Sid(Reserves BidBid RedBoats)
• Result ? ?name(ReservedRed SidSid Sailor)

16
Find sailors whove reserved a red or a green boat

• Can identify all red or green boats, then find
  sailors whove reserved one of these boats
• RGBoats ? ?Sid(? ColorRed or Green (Boats))
• ReservedRG ? ?Sid(Reserves BidBid RGBoats)
• Result ? ?name(ReservedRG SidSid Sailor)
• Using Union?

17
Find sailors whove reserved a red and a green
boat

• Must identify sailors whove reserved red boats,
  sailors whove reserved green boats, then find
  the intersection
• RedBoats ? ?Sid(? ColorRed (Boats))
• ReservedRed ? ?Sid(Reserves BidBid RedBoats)
• GreenBoats ? ?Sid(? ColorGreen (Boats))
• ReservedGreen ? ?Sid(Reserves BidBid
  GreenBoats)
• ReservedRG ? ReservedRed ? ReservedGreen)
• Result ? ?name(ReservedRG SidSid Sailor)

18
Find sailors whove made at least two reservations

• Must identify sailors whove reserved red boats,
  sailors whove reserved green boats, then find
  the intersection
• CountReservations ? SIDFCount(BID) (Reserves)
• TwoRes ? ?Sid(? Count_BIDgt2 (CountReservations))
• Result ? ?name (TwoRes SidSid Sailor)

19
Find sailors whove reserved at least two
different boats

• Res1(Sid1,Bid1) ? ?Sid, Bid(Reserves)
• Res2(Sid2,Bid2) ? ?Sid, Bid(Reserves)
• Reserved2 ? Res1 Sid1Sid2 Res2)
• SailorsReserved2 ? ?Sid1(? Bid1ltgt Bid1
  (Reserved2))
• Result ? ?name(SailorsReserved2 Sid1Sid
  Sailor)

20
Find the names of sailors whove reserved all
boats

• Reserved ? ?Sid, Bid(Reserves)
• AllBoats ??Bid(Boat)
• SailorsReservedAll ? (Resreved AllBoats)
• Result ? ?name(SailorsReserved2All SidSid
  Sailor)

21
Find the names of sailors whove reserved all
Interlake boats

• Reserved ? ?Sid, Bid(Reserves)
• AllBoats ??Bid(? nameInterlake Boat)
• SailorsReservedAll ? (Resreved AllBoats)
• Result ? ?name(SailorsReserved2All SidSid
  Sailor)

22
Find the names of sailors over 20 who have not
reserved a Red Boat

• SailorsOver20 ? ?Sid (? agegt20 Sailor)
• RedBoats ??Bid(? colorRed Boat)
• SailorsReservedRed ? ?Sid(Reserves BidBid
  RedBoats)
• SailorsOver20NoRed ? SailorsOver20 -
  SailorsReservedRed
• Result ? ?name(SailorsOver20NoRed SidSid
  Sailor)