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

Header 1	Header 2	Header 3
row 1, cell 1	row 1, cell 2	row 1, cell 3
row 2, cell 1	row 2, cell 2	row 2, cell 3

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

• 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

• sort the names Alphabetically.

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