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.)

Ajax: Intro notes

 Summary of Wikipedia page

from http://en.wikipedia.org/wiki/Ajax_(programming) :

Asynchronous Javascript And XML

except doesn't need XML: JSON often used instead
Also, doesn't have to be Asynchronous

Group of Web development techniques/technologies.

Javascript accesses DOM to allow user to dynamically interact with information displayed.

• Data exchanged asynchronously between browser and server via JavaScript and XMLHttpRequest object.
• Avoid full page reloads.

Technologies used:

• presentation uses [HTML or XHTML] + CSS
• Dynamic interactive data uses DOM
• Interchange of data via XML
• Manipulation of data vi XSLT
• Asynchronous communication via XMLHttpRequest object
• JavaScript to tie it all together

Drawbacks

• Dynamically updated web pages difficult to bookmark & save in history
• Web crawlers usually don't execute Javascript so need separate way to get into search engine indices.
• Asynchronous callback-style programming can be complex, hard to test & debug. 

sql: scattered notes on start of C.J.Date "SQL & Relational Theory"

TYPES = set of things we can talk about (like NOUNS)
RELATIONS = true statements about the TYPES (like SENTENCES)

TYPES and RELATIONS are sufficient and necessary to represent all DATA

Information Principle = The entire information content of the database is represented in only one way. Relations are the only way to represent information. There is no documented meaning in a duplicated row.

Use of null violates the Information Principle.

It is a logical flaw to pretend that a TYPE is a certain type of RELATION. (Some Object oriented products do this & thus fail.)

A database with its operators is a Logical System like Euclidean Geometry.

• Base relations correspond to Axioms
• Rules of Inference derive new Truths
• A Query is equivalent to getting the system to prove a Theory.

Optimizers rephrase queries, that is they perform expression transformation.

variable == can be updated

Assignment Principle: After you assign a value v to a variable V, then v==V is True

• All operations are at the level of a set
• Check integrity only after applying the set of updates.

A key is a set of attributes (often a set of 1 attribute), that is, a tuple.

A key must be unique and irreducible.
That is, if you say the key is the combination of [K,L], but [K] by itself is also unique, then [K,L] is reducible, so the key is only [K]. Though [K,L] is a superkey of [K], as is [K] itself.

There can be more than one "candidate key".

There really is no logical reason why you must always choose a "primary key" from among a set of valid "candidate keys".

Entity Integrity Rule: A primary key value can not be null.

A "foreign key" is one that references another table/relation.
Referential Integrity Rule: Every foreign key value must exist in the foreign table. You can't have a foreign key that is not matched.

Use of NULL/UNKNOWN means you need 3 value logic. The 3 values are T, F, Unknown.

T & T = T          T | T = T
T & F = F          T | F = T
T & U = U          T | U = T
F & F = F          F | F = F
F & U = F          F | U = U
U & U = U          U | U = U

not T = F
not F = T
not U = U

Closed World Assumption: Everything stated or implied by the DB is true. Everything else is False.

Open World Assumption: Everything else is UNKNOWN. (This leads to nulls & 3value logic & trouble)

Using Closed World Assumption.

predicate = A function that returns True or False when invoked.
headings correspond to predicates
The relation is a set of tuples that are instantiations of 'true propositions'

tortoise svn: drag & drop with right mouse button to move a file from one directory to another

How to do this wasn't obvious so I'd been doing these file moves from within the Repo Browser.

This morning I finally Googled it to see how it could/should be done, and it was so EASY! Just select the file to move from one directory with the RIGHT mouse button and then when you drag & drop it to a new directory, you can get the option to move & rename it.

http://tortoisesvn.net/mostforgottenfeature.html
http://tortoisesvn.net/docs/release/TortoiseSVN_en/tsvn-dug-copy.html

Note that when you next do a SVN Commit, you need to Commit from the parent directory of the two directories that have changed--the parent of both the source and the destination directory from/to which you moved the file. Otherwise there will be a complaint that you need to do both commits together.