Databases

1
Databases

• Dan Otero (oterod_at_cs)
• Alex Loddengaard (lodbot_at_cs)

2
Basic Data Storage

• Memory
• Small (.25 - 4 GB)
• Non-persistent
• Text Files
• Inefficient
• Difficult to organize

3
Databases

• A program that
• Manages possibly huge quantities of data
• Facilitates fast and easy access
• Makes data integrity guarantees
• Has A LOT under the covers

ACID - http//en.wikipedia.org/wiki/ACID
4
Databases (cont.)

• Applications
• Amazon.com, Facebook, IMDB, digg.com, banks,
  Google, etc
• Implementations
• MySQL, Postgre, Oracle, Microsoft SQL Server

5
Database Organization

• A single database has multiple tables
• A table has multiple rows
• Each row has multiple columns
• Each column represents a different data category

Table of actors
6
Database Structure

• Column types
• INT, VARCHAR (String), DOUBLE
• Schemas
• What tables exist?
• What types of columns are in each table?

7
Database Operations (CRUD)

• Consider IMDB
• Create data in a table
• A new actor has just appeared in a film
• Read data from a table
• Somebody has searched for an actor
• Update data in a table
• An actor has appeared in a new movie
• Delete data in a table
• A planned movie is cancelled

8
Structured Query Language(SQL)

• The language that one uses to interface with a
  database
• Allows a user to perform CRUD operations on a
  particular database

9
Warning

• We are skipping a lot
• Creating a database
• Creating a table
• Creating a user and setting permissions
• Use Google to find examples of each of these

10
Create (INSERT)

• Insert a row into the actors table containing
  id5, nameNicole Kidman, DOB06/20/1967
  genderF
• INSERT INTO actors
• VALUES (5, Nicole Kidman, 06/20/1967, F)
• Note that the order of values depends on the way
  the table was created

11
Read (SELECT)

• Get all rows and only the id column from the
  actors table
• SELECT id
• FROM actors
• Get all rows and columns from the actors table
• SELECT
• FROM actors
• Get all rows and columns from the actors table
  whose name field is Tom Cruise.
• SELECT
• FROM actors
• WHERE name Tom Cruise
• Get all rows and columns from the actors table
  whose name field is either Tom Cruise or Katie
  Holmes.
• SELECT
• FROM actors
• WHERE name Tom Cruise
• OR name Katie Holmes

12
Update (UPDATE)

• Change Tom Cruises gender
• UPDATE actors
• SET gender F
• WHERE name Tom Cruise
• Change Tom Cruises gender if he is a man
• UPDATE actors
• SET gender F
• WHERE name Tom Cruise
• AND gender M

13
Delete (DELETE)

• Delete Tom Cruise from the table
• DELETE FROM actors
• WHERE name Tom Cruise

14
How the Database Fits In

• A table can be represented by a Java class (in
  the model)
• Columns in the table represent fields in Java

15
The code

• First
• Install MySQL
• http//dev.mysql.com/downloads/mysql/5.0.html
• Install MySQLs JDBC driver
• http//dev.mysql.com/downloads/connector/j/3.1.htm
  l
• Include the driver as a library in your Eclipse
  project
• Right click on your project and go to
  Properties
• Go to Java Build Path and select the
  Libraries tab
• Click the Add Exernal Jars button and select
  the jar file from the JDBC driver

16
Interacting with MySQL

• Option 1 Command line
• Series of commands - use Google to find them
• Option 2 Management Software
• http//www.phpmyadmin.net/home_page/index.php
• Option 3 Java programs

17
Relationships

• Actor IDs are Primary Keys in the actor table
• Actor IDs are Foreign Keys in the movie table

18
Relationship in SQL

• This is called joining
• Get the lead actor for the Tommy Boy movie
• SELECT a.
• FROM actors a, movies m
• WHERE m.name Tommy Boy
• and a.id m.lead_role
• a and m are variables and the join occurs
  in the second where clause

19
ACID

• Atomicity
• All or none
• Consistency
• Always in a legal state
• Isolation
• Each user is isolated from each other user
• Durability
• Can recover after a crash or power failure

20
Useful Links

• IMDB Database
• http//www.imdb.com/interfaces
• Download the plain text interface and import it
  into MySQL (see Google)
• Suns Data Access Object Pattern
• http//java.sun.com/blueprints/corej2eepatterns/Pa
  tterns/DataAccessObject.html