sql: notes on CJ Date SQL & Relational Algebra I: The original operators, part 1 (safari video)

SQL is a language which is a user interface for a DBMS. It is not a DBMS itself.

Relational Closure means that the output of one operator can be input to the next operator.
You can have nested algebraic expressions.

JOIN reads relation values and outputs another relation value

Distinguish "relational operators" from "relational algebra operators"
relational operators are all the SQL operators like update, insert, delete.

relational algebra operators have closure and are read-only.
Thus JOIN, SELECT, UNION are relational algebra operators but INSERT, UPDATE, DELETE are not.

Any operation on a relation that does not produce a relation is not a relational operation, by definition, since it would violate closure.
Avoid operations that violate closure, except for the relational inclusion operation, which returns T/F.

A relation has 2 parts: Header + Body
If you know the headers of 2 relations then you can infer the header of the result of their JOIN.

Join is done on attributes of the same name (or correlation name or renamed name)

Relations are JOINABLE iff their attributes of the same name are of the same type
Relations are JOINABLE iff the set theory union of their headings is a legal heading

in SQL: P join S  !=  S join P because the result of the 2 join operations have different column orders. This is not good.

Intersection is a special case of join where the two input relations have the same heading.

The zero tuple is a tuple that contains no components.
The Cartesian Product is a special kind of join.

Table-Dee is a table (relation) with no attributes and one tuple, the zero tuple.

Table-Dee is an identity w.r.t. the Cartesian product.

0 + x = x + 0 = x means that 0 is an identity w.r.t. addition
1 * x = x * 1 = x means that 1 is an identity w.r.t. multiplication.

r * table-dee = table-dee * r = r

join{r,table-dee} = r
join{} = table-dee

t1 JOIN t2 using (C1...CN) -> resulting table is column ordered with the common columns (C1..CN) first, followed by the other columns of t1 followed by the other columns of t2

recommend:

• Columns of same name should be of the same type.
• If you do this, then you can and should use "natural join"
  natural join means join on columns that have the same name

• Never write code that relies on left to right ordering.
• Use corresponding (it's part of the standard) if your product supports it.
• Make sure corresponding columns have the same name and type.
• Don't use the 'BY' option
• Never specify 'ALL'.
  (ALL was initially added as an option to UNION as a performance tweak to signal that there were no duplicates to search for & eliminate. It was never supposed to produce duplicates.)