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'