Homework in preparation for this lecture
- Read HA: Chapter 9
- Load in the code (Eventsv2) from the "google:Hello Android Pragmatic" site
- Modify the code so that
- it uses your database definitions for your app
- write methods for inserting information into the appropriate tables
- write two or three methods for getting data out of the tables using some queries
- your main method inserts values into the tables, then makes some queries and out the results
- Check in the code into github
- Generate a screencast with you talking through a demo of the app (on the emulator) and also giving us a tour of the code, with your verbal commentary of the important or interesting parts of the code
- Add links to both to your Homework Log
- Understand SQL language syntax and how to use online resources to learn more, esp.
- Be able to use SQL to create databases, alter databases, backup and restore databases
- Be able to use SQL to insert information into databases
- Be able to use SQL to extract information from databases
- A way to manage large amounts of data. Persistence. Standard access.Different vendors, different features. standardization is hard.
- Stored on disk (or in memory, or elsewhere)
- Tables containing records (or objects, or other things)
- Examples: SQL Databases, DNS database, Hierarchical, Key-value stores, file system, text-based databases etc. etc.
- Not always 'large' amounts: in-memory dbs, program registry in windows, even a simple text file
- Discuss: What makes a database a database then?
SQL Databases: Sqlite3
- Now, lets create a second table. Will be used for a join. Try a User table!
- Hm. What's a join? Every table, implicitly has a key. Known as the "primary key"
- Wikipedia has a nice article on joins - http://en.wikipedia.org/wiki/Join_%28SQL%29
- To create a one-to-many relationship, we use a foreign key
- To create a one-to-one relationship, same thing. Semantics
- One problem with sqlite: can't delete a column in a table
- modify students table, to add a foreign key
But, sqlite doesn't support direct deletion of columns. Instead, create a new table with the column, copy the data, delete the old and copy back.
- Discuss: How do we create a many to many key????
Family of operations to allow tables to be combined
- All are embodied somehow in the where clause!
- 3d Normal form
- Avoiding duplication
BREAK INTO GROUPS:
- Sketch out toy schema/model for iTunes.
- THINK about relationships, which are 1-1, 1-many, many-1, many-many. 20 minute exercise.
- We will have 3 tables
- A list of tracks (title, artist, length)
- A list of Albums (name, date-of-release)
- A list of artists (name, country of birth)
- Sketch it out together
- JBS Summer Lecture Series Lecture by Ed McNierny, CTO of OLPC
- Project Presentations
- Review of Homework for tomorrow (including PA4)