database stuff

db.txt

SQL VS NOSQL == CAP vs ACID

NOSQL:
CAP : consistency, availability,partition tolerance (Brewer)
distributed
horizontal scale
unstructured
use for maybe data
asynchronous
transactions might seem to work but fail later 
  -- two amazon customers buy the last copy of a book and are told they got it. later amazon finds it only has 1 copy. deal with cust who didn't get book
  -- you have a blog that 10 million users read. you update it. some readers see the new entry before others. no one cares.
BASE (Basically Available, Soft state, Eventual consistency)
less friendly to complex queries

SQL
ACID : atomic,consistent,isolated,durable (transactions, two-phase commit, E.F. Codd)
monolithic
vertical scale
strict structure
synchronous
transactions work or don't work. never seem to work
  -- a sequence of credits and debits to an account always leave the account in the right state
  -- you are a stock exchange with 10 million users. you update a stock price. all users must see the update at the same time (within the laws of physics),
     otherwise there is an opportunity for arbitrage. with modern trading systems, even a difference of a few milliseconds can matter.
     
use for $
support complex queries efficiently

RELATIONAL NORMALIZATION
1st normal : no fields with multiple atomic data (e.g. more than one phone number per person)
from:

people
------
joe	12345,78910
ken	45678

to:
people
------
joe	12345
joe	78910
ken	45678

2nd normal : 1st normal + remove redundancy
from:
people
------
name	dob	 number
joe	1/1/1990 12345
joe	1/1/1990 78910
ken	2/2/1991 45678

we are told twice that joe was born in 1/1/1990. that is redundant

to:
people
------
joe	1/1/1990
ken	2/2/1991

numbers
-------
joe	12345
joe	45678
ken	78910

3rd normal : 2nd normal + no transitive dependencies

from :
people
------
year	name	dob
2000	joe	1/1/1990
2001	ken	2/2/1991

dob is a transitive dependency. it is not defined by id:year
it depends on 'name' rather than 'year'. 1/1/1990 depends on 'joe', not on 'year'

to:

people
------
2000	joe
2001	ken

dob
---
joe	1/1/1990
ken	2/2/1991