WebFund 2014W Lecture 7

From Soma-notes
Revision as of 17:07, 13 March 2014 by Leah (talk | contribs)

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

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

  • 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