OUTER JOIN

1
OUTER JOIN

• OBJECTIVE Collate all available data, even if
  incomplete
• SYNTAX
• R ? join condition S
• TYPES
• LEFT OUTER JOIN
• Include all tuples from Relation on left, with
  whatever information is available from tuple on
  right
• RIGHT OUTER JOIN
• Opposite of left outer join
• FULL OUTER JOIN
• Include all tuples from both Relations, either
  separately, or merged

2
OUTER UNION

• OBJECTIVE Combine tuples from Relations that are
  not union compatible
• EXAMPLE
• Combine different types of employees into a
  single Relation
• Different from UNION in that degree increases,
  and nulls may be added for non-existent
  attributes
• Key needs to be common between both Relations,
  though all other attributes may differ
• NOTE This operation is actually a special case
  of FULL OUTER JOIN

3
Relational Algebra-Division operation

• OBJECTIVE To find information that requires
  mapping based on multiple tuples (as against
  multiple attribute values of a single tuple)
• EXAMPLES
• Find potential dates who have all my hobbies
• Narrow down suspected terrorists by finding
  evidence of visits to all cities visited by
  mastermind
• Division
• Find instances of divisor in dividend
• Find number of complete sets of hobbies
• Find number of complete sets of cities visited

4
Recursive closure (Fig. 7.17)

• OBJECTIVE Obtaining Relations that depend on
  recursive Relationships. Not directly supported
  by Relational algebra.
• EXAMPLE
• Find all my superiors in an organization (Boss of
  boss of boss of ..)
• A list of all my ancestors (Father, Grandfather,
  Great-Grandfather, Yeti, Lucy, The missing link
  .. )
• SYNTAX Generally take the UNION of multiple JOINs

5
AGGREGATE FUNCTIONS (Fig. 7.16)

• OBJECTIVE Group tuples within Relations and
  compute values for each group
• EXAMPLE
• Find number of students in each department of the
  University
• Find average GPA for each course taught in Winter
  2001
• SYNTAX ltgrouping attribsgt F ltfunction listgt ( R
  )
• Function list is possibly multiple
  ltfunctiongtltattribsgt pairs
• Possible functions are COUNT, AVERAGE, MAXIMUM,
  MINIMUM
• NOTE Set based nature of Relational model still
  holds. Grouping is sub-setting, not ordering into
  lists. Also, result of function is a Relation,
  not a number
• Question Should duplicate tuples be included or
  eliminated?