Introduction

Last week, we were describing the E/R Model and UML Model as high level data models suitable for quickly prototyping data before mapping data to the relational model which is more suited for RDBMS implementation.
We described the basic building blocks of the ER model and diagrams based on this model: attributes (ovals), entity sets (rectangles), weak entity sets (double rectangles), relationships (diamonds), isa subclassing (triangles).
We went over how to indicate various kinds of constraints in this model: key constraints on attributes (underline), many-to-one (arrow on one side) constraint on relationships, one-to-one constraints (arrow on both sides), referential constraints (curved arrows), and degree constraints (numbers on relationship line).
We discussed design consideration in making your models: faithfulness to data concepts, redundancy avoidance, simplicity, choosing between attributes, entity sets, an d relationships depending on the kind of data being modeled.
We gave an algorithm to map the E/R model to the relational model.
We then started looking at UML as a second rapid prototyping model for databases.
We described how various data concepts could be mapped to classes, associations, association classes, and subclasses
We said how the different constraints type could be handled in this model, and briefly how the UML to relational mapping works (this was all in analogy with similar E/R concepts).
Today, we begin by presenting one last high-level data model, before switching to talk about query languages for the relational model.

Object Definition Language

ODL (Object Definition Language) is a text-based language for specifying the structure of databases in object-oriented terms.
Like UML, in ODL, there are classes which have a name, attributes, and methods.
The UML analog of associations are called relationships in ODL.
Relationship do not exist independent of classes, but are embedded in classes as an additional family of concepts.

Classes and Attributes in ODL

Classes in ODL are defined with a declaration of the form:
```
class <name> { 
  <list of properties>
};
```

The list of properties give the attributes for the class. Each has a type and a name. For example:

class Movie {
   attribute string title;
   attribute integer year;
   attribute integer length;
   attribute enum Genres {drama, comedy, 
      sciFi, teen} genre;
};

Notice the enumerated type is defined in a similar way to the C way of declaring enum's.

We can also use an analog of C-structs in declarations:

class Star {
   attribute string name;
   attribute Struct Addr {string street, 
       string city} address; 
};

If we want to elsewhere refer to the Addr used in the declaration of Star or the enum as used in the declaration of Movie, we can use Movie::Genre and Star::Addr respectively.

Relationships in ODL

Suppose we want to define a relationship between Star and Movie.

The way to do this is to add relationship properties to each of their declarations:

class Movie {
   attribute string title;
   attribute integer year;
   attribute integer length;
   attribute enum Genres {drama, comedy, 
      sciFi, teen} genre;
   relationship Set<Star> stars;
};

class Star {
   attribute string name;
   attribute Struct Addr {string street, 
       string city} address; 
   relationship Set<Movie> starredIn;
};

As given above the stars and starsIn relationships are not connected. To connect them, we use the inverse keyword. We would change the relationship statements above as follows:

   relationship Set<Star> stars;

becomes

   relationship Set<Star> stars
       inverse Star::starredIn;

and

   relationship Set<Movie> starredIn;

becomes

   relationship Set<Movie> starredIn
       inverse Movie::stars;

Using the keyword set indicates the relationship is many. So if both use set we have a many-many relationship.
If we just use the class name rather than the parameterized Set type, we can make one said a one relationship.

Types, Subclasses, and Keys in ODL

We have already seen that ODL supports enum's, Struct, and parameterized Set types.
It also supports parameterized Bag<T> (multiset), List<T> (order matters), Array<T, n> (fixed number of elements), and Dictionary<T, S> (key-value pairs).
One can say one class is a subclass of another in the same way as in Java, using the extends keyword:
```
class Cartoon extends Movie {
   relationship Set<Star> voices;
};
```
Keys in ODL are expressed after the name in a class definition:
```
class Movie (key (title, year)){ ...
```
The techniques to map ODL to relations are similar to those used for UML and E/R Diagrams.

Quiz

Which of the following is true?

Degree constraints can be indicated in UML diagrams but not in E/R Diagrams.
To map a one-to-one relationship `R` between entity sets `T` and `S` in an E/R model to the relations in the relational model, choose one of the relations that `T` and `S` map to, say `R_S`, and include in `R_S` a foreign key reference to the primary key of `R_T`.
To draw a relationship with an attribute in an E/R model in a UML model you could use just an ordinary association.

Algebraic and Logical Query Languages

We now switch from data modeling to programming for relational databases.
We are going to consider two abstract database programming languages: one algebraic and the other logic-based.
The first will be a supped up version of the relational algebra we've considered earlier.
The second one will be called datalog.

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
```

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$

Finish High Level Models, Algebraic and Logical Query Languages

Outline