Introduction

On Monday, 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.
We then introduced bags (multi-sets) and described how the relational algebra operations work on bags.
We begin today by looking at some extensions to the relational algebra.

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.
By default we assume sorting is in ascending order. If we want to be specific we can write: starName asc or starName desc.
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$

In-Class Exercise

Give the relational algebra for the following queries. Assume we have relations Employee(eid, fname, lname, address, birth_date, salary), WorksOn(emp_id, proj_id, hours), and Project(pid, proj_name, manager_id).

Return the table LastNameAddress(lname, address) of the distinct (last_name, address) pairs sorted descending by last_name.
Return the average age as avg_age and average salary as avg_sal of employees. Assume we have a built-in function time() that gives the current time and that time() - birth_date returns a time in a format that can be averaged.
Return a table MostTimeConsuming(fname, lname, pname, hours), the names of employees and the names of the projects they spend the most hours on. If an employee doesn't work on any projects, they should still be output but with nulls for the last two columns.

Post your solutions to the Oct 25 In-Class Exercise Thread.

Datalog

The logical query language we will consider is called datalog ("database logic").
A datalog program will consist of a series of if-then rules which can be used to represent knowledge about some domain (i.e., be a database for that domain).
These rules will express the idea that from certain combinations of tuples in certain relations we may infer some other tuple must be in some other relation, or must be in the answer to a query.
To get started with datalog, we introduce its building blocks...

Predicates and Atoms

Relations in Datalog are represented by predicates which have a fixed number of input slots.
An atom is a predicate which has been supplied with arguments for each of these input slots. For example, `P(x_1, ..., x_n)`.
A predicate can take as arguments either constants like the number 7 or a string like "hi there" or a fixed value of some type, variables like `x`, `y`, `z` ..., or expressions built out of these.
One can think of a predicate `P(x_1, ..., x_n)` as a true false function which returns true if the tuple of atoms `(x_1, ..., x_n)` belongs to the relation that `P` represents, and false otherwise.
For example, if `R` is the relation:
```
A|B
---
1|2
3|4
```
then `R(1,2)` and `R(3,4)` are true atoms and `R(1,1)` is false. `R(1,z)` will return true if `z=2`, and otherwise, return false.

Arithmetic Atoms

Arithmetic atoms are a special kind of atom involving a comparison between two arithmetic expressions. For example, `x < y` or `x+1 ≥ y+4 times z`.
The non-arithmetic atom examples of the previous slide are called relational atoms.

Datalog Rules and Queries

Operations similar to those of the relational algebra are described in Datalog by rules, which consist of
1. A relational atom called the head, followed by
2. The symbol `\leftarrow`, which can be read as "if", followed by
3. A body consisting of one or more atoms called subgoals which are connected by AND and which may be optionally preceded by the operator NOT.

For example,

LongMovie(t,y) <- Movies(t,y,l,g,s,p) AND l ≥ 100

is an example Datalog rule

The head of this rule represents a relation consisting of tuples (t,y) such that there exists a Movies(t,y,l,g,s,p) where `l ≥ 100`. I.e., a movie title and year of a movie that is more than 100 minutes long.
Since we don't use the variables g,s,p in computing LongMovie, we could have made them anonymous by replacing them with underscores, making the rule look like:
```
LongMovie(t,y) <- Movies(t,y,l,_,_,_) AND l ≥ 100 
```
Sometimes we will follow Prolog's convention and use commas in place of AND:
```
LongMovie(t,y) <- Movies(t,y,l,_,_,_) , l ≥ 100 
```
This rule corresponds to the relational algebra query:
`mbox(LongMovie) := pi_{mbox(title, year)}(sigma_{mbox(length) >= 100}(mbox(Movies)))`
A query in Datalog is a collection of one or more rules.
If there is only one relation that appears in the heads of these rules, then the value of this relation is the answer to the query. I.e., in the above, LongMovie is the answer to the query.
When there is more than one rule head, we will assume the relation of the last rule head is the answer to the query, and all the other rule heads are used as assisting in the calculation of this answer.

Meaning of Datalog Rules

To evaluate a rule we imagine trying to satisfying each subgoal in the body with a tuple of constant values (i.e., each variable has been assigned a fixed value).
We then look at the values that the variables in the head of the rule have been assigned to in the body and view that as the tuple satisfying the head.

Consider

Father("bob", "tony") <-. //notice this rule has an empty body. 
  // We view such a rule as a fact. Sometimes we just write: 
  // Father("bob", "tony").
Father("tony", "john") <-.
Ancestor(x,y) <- Father(x, y).
Ancestor(x,z) <- Father(x, y), Father(y,z).

You might wonder why we don't have the rule:
```
Ancestor(x,z) <- Father(x, y), Ancestor(y,z).
```
This would be a valid Prolog rule, but we don't usually allow a predicate to appear on both sides of a rule in Datalog.
To compute Ancestor(x,z) we can go through our rules from top to bottom, and see Ancestor(x,y) `\leftarrow ` Father(x, y) and so try to satisfy it by trying to satisfy Father(x, z) where we have unified z=y.
We can then cycle down through our list of rules trying to satisfy Father(x, y) and satisfying using the rule Father("bob", "tony") `\leftarrow`. This gives x="bob" and z=y="tony", so the tuple ("bob", "tony") is in Ancestor.
We can also satisfy Ancestor(x,z), using x="tony" and z=y="John" via the second Father fact above.
Finally, we can satisfy Ancestor(x,z) via the second Ancestor rule using x='bob', z="john" and y="tony".
In order to determine a concrete, finite set of tuples in our answer, we require each variable appear safely:
- Every variable that appears anywhere in the rule must appear in some, nonnegated, relational subgoal of the body.
So
```
P(x,y) <- Q(x,z), NOT R(w,x,y), x < y
```
is not safe because of how `y` occurs and how `w` occurs.

Extensional and Intensional Predicates

We often make a distinction between kinds of predicates.
Extensional predicates are predicates whose relation is stored in a database (can be thought of a collection of fact rules).
Intensional predicates are predicates whose relations are computed by applying one or more Datalog rules which are not facts.
The collection of all intensional predicates in a database we call the intensional database (IDB).
The collection of all extensional predicates in a database we call the extensional database (EDB).

Datalog Applied to Bags

So far we have been imagining our Datalog rules as applying to sets.
Just as with the relational algebra, we can imagine them applying to bags.
For example, consider the rule:
`H(x,z) \leftarrow R(x,y), S(y,z)` where relation `R(A,B)` is given by:
```
A|B
---
1|2
1|2
```
and `S(B,C)` is given by
```
B|C
---
2|3
4|5
4|5
```
then we can compute `H` as
```
1|3
1|3
```
by treating the two rows of `R` as distinct when trying to satisfy the clause.

Relational Algebra and Datalog

Each of the relation algebra operations can be implemented using Datalog.
The book shows how to do this for each operation, we only show a few.
The set operation `T := R(vec{a}) cup S(vec(a))` could be implemented via two rules: `T(vec{a}) \leftarrow R(vec{a})`. `T(vec{a}) \leftarrow S(vec{a})`.
The set operation `T := R(vec{a}) cap S(vec(a))` could be implemented via the rule: `T(vec{a}) \leftarrow R(vec{a}), S(vec{a})`.
The set operation `T := R(vec{a}) - S(vec(a))` could be implemented via the rule: `T(vec{a}) \leftarrow R(vec{a}), NOT \ S(vec{a})`.
Projection can be implement via leaving out variable `pi_{vec{a}}(R(vec{a}, vec{b}))` then could be expressed as:
`T(vec{a}) \leftarrow R(vec{a}, vec{b})`
Selection can be implemented using arithmetic atoms, join and products via conjunctions. For example, `sigma_{x>10}(R(x))` could be implemented as:
`T(x) \leftarrow R(x), x> 10`
`R(a) times S(b)` could be implemented as:
`T(a,b) \leftarrow R(a), S(b)`.

Extending the Relational Algebra and Datalog

Outline