#separator:tab #html:true #tags column:3 RA: for every approach "
  1. ShouldHave: \(\Pi\) ... x \(\Pi\) ...
  2. Actually: \(\Pi + \sigma\)
  3. DidNot: ShouldHave - Actually
  4. \(\Pi\) Everything - \(\Pi\) DidNot
" JOIN USING JOIN USING (sharedAttr) JOIN ON JOIN ON condition = ... WHERE comp ALL (subquery) for all WHERE comp SOME (subquery) if comparison holds for AT LEAST 1 val. in subquery
aka ANY Serialization anomaly Even though each operation performed correctly, end result isn't correct Phantom read rows appear/disappear due to another transaction Non-repeatable read Same rows but content of row has changed Dirty read allows a statement to read data that another transaction has changed but not yet committed READ COMMITTED level doesn't allow dirty read REPEATABLE READ Level NO non-repeatable reads or phantom reads (Psql only) SERIALIZATION Level no serialization anomalies (most strictest, most slow) Transaction components BEGIN TRANSACTION
...
COMMIT or
ROLLBACK SQL Injection "for a value that will be given as a string

...  ' ) ; bad query here  ; -- comments out the rest" psycog2: who commits? the connection commits all the cursors Database deletion considerations different stakeholders w/ varying interests
Measures other than complete erasure can sometimes be a better balance of conflicting interests. These include keeping logs, encryption, and delinking
how are indices stored B-Trees a b-tree node has _ values in sorted order, and _ children k values
k+1 children
k ~ 1000 b-tree: how is k chosen? size of a node = size of a block (memory read from storage) Finding minimal basis
  1. Split the RHS of each FD
  2. Multi-attribute FDs: Try and remove attributes from LHS
  3. For each FD f, if S - {f} implies f, remove it from S
BCNF Definition R is in BCNF if for every non-trivial FD X->Y (Y is not contained in X) that holds in R, X is a superkey BCNF Synthesis            Problematic FD LHS

Remaining attrs      |      FDs closure

Project original FDs to smaller tables.
If closures violate BCNF, split further BCNF Speedups LHS FDs: Superkey
When projecting: immediately check closure to see if it meets BCNF Decomposition goals
  1. No anomalies
  2. !! Lossless join
  3. Dependency preservation
Usually choose 2 out of 3
BCNF Loses Dependency preservation 3NF Loses No anomalies 3NF Definition For each non-trivial FD,

LHS is a superkey (key maybe w/ some extras)

OR:

RHS is ONLY made up of prime attributes (member of any key???) 3NF Synthesis For each FD: create a relation w/ LHS and RHS

If none of these relations has a superkey,
Add a key Keys from FDs ""