Reading

Chapter 23

Definitions

Database: organized collection of data. Information is organized into tables that contain records.

Database Management Systems (DBMS): are systems that are used to store and organize the data. Examples of such systems include Oracle, Microsoft SQL Server, DB2, PostgresSQL, Ingres, FoxPro, dBase, Informix and MySQL.

Relational Databases: Most popular database systems today. SQL is the language used with relational databases to perform queries.

Queries: are requests for information that satisfy certain criteria.

Java and Databases

Java programs communicate with databases using the JDBC API. A JDBC driver implements the interface to a particular database. The separation of the API from drivers allows programmers to change the database without modifying the code.

ODBC is a C-based interface to SQL-based database engines. It provides a consistent interface for communicating with a database.

Relational Databases

It stores data in tables, which are composed of rows and columns. Tables can have primary keys. A primary key is a column in a table that has a unique value that cannot be duplicated in other rows.

Table: is a collection of records.

Record: is a collection of fields that belong together.

Field: is a piece of data.

Example Record:

Lastname Firstname MI ID Phone Email

Example Table of Records

Shereen	Khoja	None	42	2008	shereen@pacificu.edu
Doug	Ryan	D	72	2020	ryandj@pacificu.edu
Michelle	Hribar	None	24	2024	None

Database Example

Create a database to store information on professors, students and courses.

Main concepts:

- Primary key

- Foreign key

- Joining

- Referential integrity

- Entity-relationship diagram (ER)

SQL Queries

SELECT * FROM table

SELECT column1, column2 FROM table

SELECT * FROM table WHERE column1 LIKE ‘S%’

INSERT INTO table (column1, column2) VALUES (value1, value2)

DELETE FROM table WHERE column1 = ‘Shereen’

Databases and Servers

Most servers will use some sort of database.

MySQL

We will be using MySQL to create the databases and will connect to them using Java. You could use any of the popular databases and connect to them using Java as long as you have the correct drivers.

MySQL is the most popular open source SQL database management system. http://www.mysql.com/

The limit on table size is 8 million terabytes (2^63 bytes).

Using MySQL

MySQL is a command line database. Start MySQL by typing mysql in the command window. This will give you the prompt: mysql>

You can use some simple queries to find out the version and the current date:

SELECT VERSION(), CURRENT_DATE;

You must end all mysql queries with a semicolon.

Use the command mysql> SHOW DATABASES; to display all current databases on the system.

The goal for today is to get you familiar and up and running with MySQL. Open up the MySQL reference manual and go to section 3.3.1 and follow the tutorial. If you prefer, you could skip the tutorial and create the books database that is in the book.