Lectures‎ > ‎Week 5:June 27-July 3‎ > ‎

01-SQL Databases, Mobile and Android

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
  • Deliverable:
    • 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

Databases

Learning Objectives

  • 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


Intro

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

Creating Queries

SQL Databases: Sqlite3

SQLite console demo

$ sqlite3 brandeis
.tables
.databases
.mode column
.headers on

create table students(id integer primary key, name string, phone integer, major string);

insert into students values ("john smith", "666-1212", "computers");
insert into students values ("karla smith", "234-1212", "computers");
insert into students values ("karla jones", "666-4444", "history");
select * from students;
meta databases
select * from sqlite_master;
.exit

  • Now, lets create a second table. Will be used for a join. Try a User table!

Second table

$ sqlite3 brandeis

create table majors(id integer primary key,name string);
insert into majors (name) values ("computer science");
insert into majors (name) values ("history");
insert into majors (name) values ("psychology");
select * from majors;
.exit


  • 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

Add the columns for the join

alter table students add column major_id integer;
.schema students

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.

Add column to student table

begin transaction;
create temporary table s_back(name, phone, major_id);
insert into s_back select name, phone, major_id from students;
drop table students;
create table students(name, phone, major_id);
insert into students select name, phone, major_id from s_back;
drop table s_back;
commit;
.schema students


  • Discuss: How do we create a many to many key????

Joins

Show how the join is used

select * from majors, students where students.major_id = majors.id;
update students set major_id = 1;
select * from majors, students where students.major_id = majors.id;
select * from majors, students where students.major_id = majors.id group  by majors.name
select *, count(phone) from majors, students where students.major_id = majors.id group  by majors.name;

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
LINKS

Afternoon Schedule

  • JBS Summer Lecture Series Lecture by Ed McNierny, CTO of OLPC
  • Project Presentations
  • Review of Homework for tomorrow (including PA4)