Outline

• Algebraic Query Languages
• Quiz

Introduction

• On Wednesday, we finished talking about high level data models and how to map them into the relational model.
• We then said we were going to consider two abstract database programming languages: one algebraic and the other logic based.
• The algebraic language will be a supped up version of the relational algebra we've considered earlier. It often serves as internal language used by a DBMS when coming up with query plans and optimizing such plans.
• The logical query language is called datalog. It can be thought of as a stripped down version of Prolog and influenced the design of the query language SQL.

Relational Operations on Bags

• We are going to consider relations that are bags (aka multisets).
• A bag is like a set but where we allow for repeated elements, we don't care about the order of elements.
• So {1,2,3} and {2,1,3,3} are two different bags.
• But {1,2,3,3} and {2,1,3,3} are the same.
• Below is an example relational instance which is a bag:

  A|B
  ---
  1|2
  3|4
  1|2
  1|2
  

• Using bags makes it easier to handle operations like the union of two relation as we can just concatenate the tuples of the two relations.
• Using bags makes it easier to handle projections as we can just output the desired columns of a relation without having to worry about repeats.
• Some aggregate operations like averages might only make sense if you use the bag interpretation of relations.

Set operations on Bags

• Suppose `R` and `S` are bags of tuples and that `t` appears `n` times in `R` and `m` times in `S`. Then the number of occurences of `t` in:
  • `R cup S` is `n+m`.
  • `R cap S` is `min(n,m)` times.
  • `R - S` is `max(0, n-m)`.

Projection and Selection on Bags

• The operation `pi_{A_1,..,A_m}(R)` will have the same number of tuples as `R`: It will have 1 tuple `t[A_1,..,A_m]`, `t` restricted to the attributes `A_1,..,A_m`, for each tuple `t` in `R`. For example, let `R` be

  A|B
  ---
  1|2
  3|4
  1|2
  1|2
  

  then `pi_A(R)` is:

  A
  _
  1
  3
  1
  1
  

• To apply a selection on bags, we apply the selection condition to each tuple independently. So if `R` is

  A|B|C
  -----
  1|2|5
  3|4|6
  1|2|7
  1|2|7
  

  then `sigma_{C >= 6}(R)` is

  A|B|C
  -----
  3|4|6
  1|2|7
  1|2|7
  

Products and Joins on Bags

• The Cartesian product of bags `R` and `S`, `R times S`, consists of each tuple in `R` paired with each tuple in `S` regardless of whether the tuples were duplicates. So if `R` and `S` are respectively:

  A|B
  ___
  1|2
  1|2
  
  and
  
  B|C
  ---
  2|3
  4|5
  4|5
  

  their Cartesian product is:

  A|R.B|S.B|C
  -----------
  1|  2|  2|3
  1|  2|  2|3
  1|  2|  4|5
  1|  2|  4|5
  1|  2|  4|5
  1|  2|  4|5
  

• The natural join of `R` and `S` would be the appropriate selection of rows from the Cartesian product where the join attributes agree, and then appropriately projecting the result:

  A|B|C
  -----
  1|2|3
  1|2|3
  

• Similarly, a theta join is the result of doing the cartesian product on bags followed by the appropriate selection on bags. So given `R` and `S` as above, $R \bowtie_{R.B < S.B}S$ yields:

  A|R.B|S.B|C
  -----------
  1|  2|  4|5
  1|  2|  4|5
  1|  2|  4|5
  1|  2|  4|5
  

Quiz

Which of the following is true?

1. Our relational mapping algorithm did not map many many relationships to new relations.
2. A weak entity set has some of its key attributes in a supporting entity set.
3. Associations in UML are the same concept as entity sets in the E/R model.

Extended Operators of Relational Algebra

• In the Sep 11 Lecture we introduced the classical relational algebra in terms of sets.
• We have now seen how to modify each of its base operations to bags.
• SQL has several other operations that have proved quite important in applications, so we consider an extension of the relational algebra to handle these operations.

Duplicate Elimination and Aggregation operators

• `delta(R)` is called duplicate elimination and returns the set consisting of one tuple for each distinct tuple in `R`. So if `R` was

  A|B
  ---
  1|2
  3|4
  1|2
  1|2
  

  then `delta(R)` is

  A|B
  ---
  1|2
  3|4
  

• Aggregation operators allow us to compute simple statistics about the tuples stored in a relation.
• We will consider five such operators:
  1. SUM(B) returns the sum of the given column in a relation. For example, for `R` above SUM(B) would given 10.
  2. AVG(A) returns the average value of the given column in a relation. For `R` above, AVG(A) is 1.5.
  3. MIN(A) returns the minimum value in a column. For `R` above, MIN(A) is 1.
  4. MAX(A) returns the maximum value in a column. For `R` above, MAX(A) is 3.
  5. COUNT(A) returns the number of tuples in a relation. For `R` above is 4.
• Aggregation operators will not appear by themselves in the extended relational algebra, but can appear as subscripts on the grouping operator which we describe next.

Grouping

• The grouping operator `gamma_{L}(R)` lets us group the tuples in a relation `R` into distinct groups to which we can then apply our aggregation operations.
• Here `L` is a list consisting of grouping attributes and aggregation attributes. here an aggregation attribute is an aggregation operator applied to column and then renamed to some new column name.
• To compute the result of this operation, we make distinct groups for each distinct set of values of the grouping attributes, then we output one tuple for each of these groups which consists of the values of the grouping attributes together with the values of the aggregate attributes for that group.
• For example, suppose we have the StarsIn(title, year, starName) relation. Then `gamma_{mbox{starName, MIN(year)-> minYear, COUNT(title)->ctTitle}}(StarsIn)`, would output a three column relation: starName, minYear, ctTitle. For each distinct starName in StarsIn we would have one row in the output, with the name of a star, the year of the first movie they appeared in and the total number of movies they have appeared in.

Extending the Projection Operator

• We have already consider the projection operator `pi_L(R)` were we allowed `L` to be a list of attributes.
• The extended projection operator allows `L` to contain two addition kinds of elements:
  1. Expressions of the form `x -> y`, where `x` and `y` are names of attributes. This has the effect of renaming attribute `x` to attribute `y` in the output of operation.
  2. Expressions of the form `E -> z`, where `E` is an expression involving attributes of `R`, constants, arithmetic operators, string operators, etc, and `z` is a new name for the attribute that results.
• For example, suppose `R` is the relation:

  A|B|C
  -----
  0|1|2
  0|1|2
  3|4|5
  

  Then the result of `pi_{A, B+C->X}(R)` is

  A|X
  ---
  0|3
  0|3
  3|9
  

The Sorting Operator and Outer Joins

• Sometimes we want to sort the tuples of a relation by one or more of its attributes.
• For example, we might want to list out stars in some movies in alphabetical order.
• To do this we use the sort operator `tau_L(R)`, where `R` is the relation to be sorted and `L` is a list of attributes to be sorted by.
• For example `tau_{mbox(starName, birthDate)}(mbox(Stars))` would first sort the tuples in Stars according to starName where ties are broken by birthDate value. If both these agree for two tuples there the order of the tuples can be output arbitrarily.
• We also allow in our extended relational algebra for supp'd up joins called outerjoins $R \begin{array}{c}o\\ \bowtie\end{array} S$.
• The meaning of this is the set of tuples which either are in the natural join of `R` and `S` or are formed by tuples which didn't join with any element of the other relation, but where the tuple is padded will nulls (`\bot`) in all attributes that the tuple doesn't possess.
• We also allow left and right variants: $R \begin{array}{c}o\\ \bowtie\end{array}_L S$ and $R \begin{array}{c}o\\ \bowtie\end{array}_R S$ which specify which side we allow tuples to be padded.
• Finally, we allow conditions on the join, for outer theta joins: $R \begin{array}{c}o\\ \bowtie\end{array}_{Cond} S$