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)
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 | |
|---|---|---|---|---|
| 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