Single Table Queries
If 2 columns are adjacent to each other, I use a line across the group of columns I wish to display. To illustrate that they make it into the answer table, an arrow leading to the outside world is added.
Note: Students would typically omit all those columns not directly involved in this query (Artist, PDate, RecCo and Media in this query), but would be encouraged to at least lable the table rectangle.
The double line filter acts as the DISTINCT keyword, and is a duplicate filter. Typically, it is assumed that queries result in distinct answer tables, but sometimes it is useful to explicitly state this.
The ORDER BY symbol merely rearranges the rows in the answer table. To indicate both of these types of filters, I place filters across the data streams that emerge from the query to form the answer table. This is inkeeping with the notion that they are largely cosmetic alterations of the answer table, and done just priot to display.
A..Z order is alphabetical, Z..A is reverse alphabetical, 9..1 is descending, 1..9 is ascending. Alternatively, students could write ASC or DESC beside corresponding data streams.
Selections involve filters with answer tables being populated by a subset of the whole table (those tuples that satisfy the filter clauses). Typically we use a combination of a selection and a projection (column and row subsetting) when formain an answer table.
OR conditions are placed on subsequent lines close to the related column name. In the example above, we can easily deduce that this must be an OR because the Artist column cannot contain both values simultaneously. AND conditions are drawn on the same line as each other.
This notation is simple for students to draw, and allows quite complex questions to be transcribed quickly, particularly if they omit those columns not involved in the query.
SQL has many built-in funcitons, and provides operators that allow a full range of derivations on table data.
Grouping functions agglomerate data (ie. compress many rows to become one). The double box is related to the double line filter of the distinct. We attach a having clause in much the same way that we attach a derivation box. The '#' symbol is used to indicate a tally or count, the 'backwards E' to stand for has elements (or not null) and the 'crossed-out backwards E' to represent has no elements. The key words null and not null work equally well here.
For the most part, diagramming simple queries is unnecessary. Most students can easily visualise data streams and derivations from single tables. Diagrams are, however, used in class to model these situations in order to learn the symbols used in later diagrams.