Manipulating the Content of a Relational Database

1
Manipulating the Content of a Relational Database

• The Data Manipulation Language (DML) for a
  relational database inserts/removes/modifies
  content rather than structure (which is the
  responsibility of the DDL).
• This is what most people refer to when talking
  about a query language
• SQL was not designed from scratch, but was rather
  composed out of preceeding (and more formal)
  languages
• Relational Algebra
• Tuple Relational Calculus

2
Relational Algebra

• The relational algebra is truly an algebra, with
  the same sorts of structure, axioms, etc. as the
  algebra you learned in public school.
• Its commonly used internally by query engines
  due to those axioms, etc. and the facility that
  they provide for rewriting a query in to
  alternative (potentially more efficient) forms.

3
Query Processing Schematic
4
Core Relational Algebra Operators

• Select - s(ltselection-conditiongt)(ltrelation-namegt)
  
• Output only involves tuples for which the
  selection predicate evaluates as true
• Project - p(ltselection-listgt)(ltrelation-namegt)
• Output only involves attributes listed by the
  operator

5
Core Relational Algebra Operators

• Union - ltrelation1gt È ltrelation2gt
• Output involves all tuples appearing in either
  operand
• Difference - ltrelation1gt ltrelation2gt
• Out involves only those tuples appearing the the
  first operand but not the second
• Cross Product - ltrelation1gt X ltrelation2gt
• Output involves the combining of each tuple in
  the first operand with each tuple in the second

6
Derived Operators

• Join - ltrel1gt Ä ltrel2gt
• Form a cross product, then those tuples that
  match on common attributes
• Intersection - ltrel1gt Ç ltrel2gt
• Output involves only those tuples appearing in
  both operands
• Equivalent to ltrel1gt (ltrel1gt ltrel2gt)
• Division - ltrel1gt / ltrel2gt
• Output is a set of tuple fragments such that each
  fragment derives from a set of tuples in the
  first operand such that they completely overlap
  the second operand