WebFund 2014W Lecture 7
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 | |
|---|---|---|---|---|
| 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