Transcript slides

Databases
Dan Otero (oterod@cs)
Alex Loddengaard (lodbot@cs)
Basic Data Storage
• Memory
– Small (.25 - 4 GB)
– Non-persistent
• Text Files
– Inefficient
– Difficult to organize
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
Databases (cont.)
• Applications
– Amazon.com, Facebook, IMDB, digg.com,
banks, Google, etc
• Implementations
– MySQL, Postgre, Oracle, Microsoft SQL
Server
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:
Database Structure
• Column types
– INT, VARCHAR (String), DOUBLE…
• Schemas
– What tables exist?
– What types of columns are in each table?
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
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
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
Create (INSERT)
Insert a row into the actors table containing id=5, name=“Nicole
Kidman”, DOB=“06/20/1967” gender=“F”
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
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’;
Update (UPDATE)
Change Tom Cruise’s gender
UPDATE actors
SET gender = ‘F’
WHERE name = ‘Tom Cruise’;
Change Tom Cruise’s gender if he is a man
UPDATE actors
SET gender = ‘F’
WHERE name = ‘Tom Cruise’
AND gender = ‘M’;
Delete (DELETE)
Delete Tom Cruise from the table
DELETE FROM actors
WHERE name = ‘Tom Cruise’;
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
The code…
• First
– Install MySQL
• http://dev.mysql.com/downloads/mysql/5.0.html
– Install MySQL’s JDBC driver
• http://dev.mysql.com/downloads/connector/j/3.1.html
– 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
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
Relationships
Actor IDs are “Primary Keys” in the actor table
Actor IDs are “Foreign Keys” in the movie table
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
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
Useful Links
• IMDB Database:
– http://www.imdb.com/interfaces
– Download the plain text interface and
import it into MySQL (see Google)
• Sun’s Data Access Object Pattern
– http://java.sun.com/blueprints/corej2eepatt
erns/Patterns/DataAccessObject.html