WebFund 2014W Lecture 7: Difference between revisions

From Soma-notes
Leah (talk | contribs)
No edit summary
Leah (talk | contribs)
No edit summary
 
Line 19: Line 19:
* rows (records)
* rows (records)
* columns (fields)
* 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 ==
== Example ==

Latest revision as of 17:14, 13 March 2014

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