WebFund 2014W Lecture 7

The video from the lecture given on January 29, 2014 is available:

• Small from screencast.com
• Large from screencast.com
• Original from CUOL

Databases

why?

• Persistence - You want to stick around (vs. RAM)
• Concurrency in writes - (vs. files)
• Search - (vs. files)
  • Dynamic data should NOT be stored in files.

SQL(Structured Query Language) vs. NoSQL

• SQL makes queries to databases, assuming queries are highly structured.
• for relational databases.

What is a structure of a relational database? e.g. Tables - relations connect fields between tables

• rows (records)
• columns (fields)

Example

Table: People

ID	First Name	Last Name	Phone	Email
1	Anil	Somayaji	555-1212	soma@scs.carleton.ca
2	Dana	Somayaji	555-1212	dana@example.ca (should check whether it is a valid address)

Validation Problem:

• Where to check for bad data?
• Can do on:
  • Web client (helpful)
  • Web server (must)
  • Database (useful)

Example continued:

Table: Invoices

Inv. ID	Description	Amount	Customer
1	iPad	$700	1 <- Which is Anil in the People table)
2	MacBookPro	$1500	2 <- Which is Dana in the People table)

• One relation: Invoice = Customer ID -> People ID

Could ask for simple queries:

• All records in People with first name == Anil
• Which customer has spent the most in our store?
  • Sum of all invoices for each customer
  • find max

Indexing:

• Simplest: sorts specific fields in a table (create secondary keys)

Secondary key	Primary key
First Name	ID
Anil	1
Dana	2
Annie	3

• We want to sort the names Alphabetically using the secondary key.

Transactions:

• What if I want to change records in multiple tables in a coordinated fashion?
• Airline example
  • Reserve seat
  • Pay
  • Commit: check that payment and reservation both worked first

NoSQL Databases: No transactions, no real relations