Database

Databases

A program that store and retrieves (large mount of structured) data.
user <---> web servers <---> data servers

Tables

Link: columns

ID	votes	user	date	title	url　　　　<---row
5	207	12	1009	lol	http

Type of Database

• relational database (SQL)
  postgresql -- raddit
  MySQL -- Facebook
  SQLite --
  Oracle --
  
• Google app engine's datastore
• Dynamo amazon
• NoSQL mongo couch

SQL

Structured Query Language invented in 1970s.

SELECT * FROM links WHERE id = 5 ORDER BY id DESC;ASC

Take python as an example

import sqlite3
def query():
    cursoor = db.excute("select * from links;") # make a cursor for the database
    results = cursor.fetch_all()
    for link_tuple in cursor:                   # can get a tuple of each row
        link = Link(*link_tuple)
    return results

Joins

Links:

ID	votes	user	date	title	url
5	207	12	1009	lol	http

User:

ID	name	passwd	date
5	wasabi	12	1009

select id from user where name=wasabi;
select * from links where user=5;

select links.* from links, user where links.user=user.id and user.name="wasabi"

Indexes

sequential scans (slow with a lot of data)
links = [ link1, link2, link3...]
index can increase the speed of queries

index = [key:value,.....

Examples

explain analyze select name from hotels where id = 192343;
create index hotel_id on hotel(id);     # that will boost the speed of
drop index hotel_id;

Indexes for sorting

hash table - not sorted, constant time lookup

tree - sorted looks up are slower $logn$

Scaling Database

Replicating database share

ACID

• atomicity
• consistency
• isolation
• durability