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)

With a relational database, you can quickly compare information because of the arrangement of data in columns. The relational database can build completely new tables out of required information from existing tables. In other words, it uses the relationship of similar data to increase the speed and versatility of the database.

By storing this information in another table, the database can create a single small table with the locations that can then be used for a variety of purposes by other tables in the database. A typical large database will contain hundreds or thousands of tables like this all used together to quickly find the exact information needed.

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