1 / 20

Databases

This program helps manage and access large amounts of data quickly and easily, with guarantees for data integrity. It uses non-persistent, text-based files and provides basic data storage capabilities.

anthonycoleman
Download Presentation

Databases

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Databases Dan Otero (oterod@cs) Alex Loddengaard (lodbot@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 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

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

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

  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) • Sun’s Data Access Object Pattern • http://java.sun.com/blueprints/corej2eepatterns/Patterns/DataAccessObject.html

More Related