Transcript Slide 1

COMP 321
Week 4
Overview
Normalization
Entity-Relationship Diagrams
SQL
JDBC/JDBC Drivers
hsqldb
Lab 4-1 Introduction
Relational Databases
Store data in tables made up of rows and
columns
Columns have data types
Rows represent entries
Relational Databases
Product_Code Description
Price
116-064
Toaster
24.95
257-535
Hair Dryer
29.95
643-119
Car Vacuum
19.99
CREATE TABLE Product
(
Product_Code CHAR(11),
Description CHAR(40),
Price DECIMAL(10, 2)
)
Normalization
What’s wrong with this table definition?
CREATE TABLE Order
(
Product_Code CHAR(11),
Quantity INTEGER,
Description CHAR(40),
Price DECIMAL(10, 2)
)
Normalization
What’s wrong with this table definition?
CREATE TABLE Order
(
Order_Id INTEGER,
Product_Code CHAR(11),
Quantity INTEGER,
Description CHAR(40),
Price DECIMAL(10, 2)
)
Avoid Duplication – Create Two Tables
CREATE TABLE Order
(
Order_Id INTEGER PRIMARY KEY,
Product_Code CHAR(11),
Quantity INTEGER
)
CREATE TABLE Product
(
Product_Code CHAR(11) PRIMARY KEY,
Description CHAR(40),
Price DECIMAL(10, 2)
)
Learning Activity 1
Problem description:
– Normalize the following database definition.
The intention is to represent an order with
information about the customer, the order, and
multiple line items.
Learning Activity 2
Problem description:
– Draw an entity-relationship diagram for the
tables you designed for storing orders.
SQL
Four basic statements:
o SELECT - selects data from tables
o INSERT - inserts new data into a table
o UPDATE - modifies existing rows in a table
o DELETE - removes rows from a table
SQL (cont’d)
SELECT * FROM Customer
– Selects all columns from Customer table
SELECT City, State FROM Customer
– Selects only the City and State columns
SELECT * FROM Customer WHERE
State = ‘CA’
– Selects all customers who live in CA
SELECT COUNT(*) FROM Customer
WHERE State = ‘CA’
– Counts number of rows where State is CA
SQL (cont’d)
INSERT INTO Customer VALUES (‘John Doe’,
‘Columbus’, ‘OH’)
– Inserts a new customer record
UPDATE Customer SET State = ‘OH’ WHERE
State = ‘CA’
– Moves all customers who live in CA to OH
DELETE FROM Customer
– Deletes all rows from the Customer table
DELETE FROM Customer WHERE
‘CA’
– Deletes all customers who live in CA
State =
JDBC
Java DataBase Connectivity - a set of
classes and interfaces defined in the
java.sql package
Allows Java applications to connect to
databases in a (mostly) databaseindependent way
JDBC (cont’d)
The classes in java.sql are defined in a
generic way, so they can be used with
many databases
The database-specific code is contained in
a driver, which is usually provided by the
database vendor
Drivers are manipulated using the
DriverManager class from java.sql
JDBC
JDBC Driver Types
Type 1: JDBC-ODBC Bridge
Type 2: Native API Driver
Type 3: Network Protocol Driver
Type 4: Native Protocol Driver
Type 1 - JDBC - ODBC Bridge
Pros: Database
independent
Cons:
– Windows only
– Performance
– ODBC driver must be
present
Type 2 – Native API Driver
Pros: better
performance vs. type 1
Cons:
– Client library must be
present
– Platform-dependant
Type 3 - Network Protocol Driver
Pros:
– No database library on
client
– Client is DB-independent
Cons:
– Extra layer
– DB-specific coding
required in middleware
Type 4 – Native Protocol Driver
Pros:
– Best performance
– Pure Java
Cons:
– Driver required for
each database
Using JDBC
Identify type of driver needed
Obtain/Install driver
Add driver to classpath (In Eclipse,
configure build path)
Making a Connection to the DB
Manually load the driver class*:
Class.forName("org.hsqldb.jdbcDriver");
Establish connection
conn = DriverManager.getConnection(
"jdbc:hsqldb:hsql://localhost:9001",
"sa", // username
""); // password
DriverManager takes care of details
* Starting with JDBC4 (part of Java 6), the driver is loaded automatically
Statement Types
Statement: SQL is sent to database
each time
PreparedStatement: compiled version
of statement is cached and executed more
than once
CallableStatement: used to call stored
procedures
JDBC Statements
Statement stmt = conn.createStatement();
String cmd = "INSERT INTO Users ('User1', 'Password')";
try {
stmt.executeUpdate(cmd);
}
finally {
stmt.close();
}
ResultSet
Connection conn = null;
Statement stmt = conn.createStatement();
try {
ResultSet rs = stmt.executeQuery("SELECT * FROM PRODUCT");
try {
while (rs.next()) {
int id = rs.getInt("ITEMID");
double price = rs.getDouble("PRICE");
String desc = rs.getString("DESCRIPTION");
// Do something with data
}
}
finally {
rs.close();
}
}
finally {
stmt.close();
}
ResultSet
Statement stmt = conn.createStatement();
try {
ResultSet rs = stmt.executeQuery("SELECT ID,PRICE,DESC FROM PRODUCT");
try {
while (rs.next()) {
int id = rs.getInt(1);
double price = rs.getDouble(2);
String desc = rs.getString(3);
// Do something with data
}
}
finally {
rs.close();
}
}
finally {
stmt.close();
}
hsqldb
http://hsqldb.org/ - 100% Java Database
Open-source database we will be using for
labs
We will be using the latest version: 2.0.0
RC 9
hsqldb - Installation Instructions
Download and unzip into a local directory
(for example C:\java\db\hsqldb)
Create .cmd file to start hsqldb server
C:\java\db\hsqldb\data\StartHSQLDB.cmd)
cd C:\java\db\hsqldb\data
java -cp ../lib/hsqldb.jar org.hsqldb.server.Server --database.0 file:mydb --dbname.0 xdb
Validate Installation
Start server, and run Testdb class from
documentation
Start with Testdb.java (in Week 4 folder on
Website)
Add hsqldb.jar to build path
hsqldb
CREATE SCHEMA PUBLIC AUTHORIZATION DBA
CREATE MEMORY TABLE SAMPLE_TABLE(ID INTEGER GENERATED BY
DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,STR_COL
VARCHAR(256),NUM_COL INTEGER)
ALTER TABLE SAMPLE_TABLE ALTER COLUMN ID RESTART WITH 8
CREATE USER SA PASSWORD ""
GRANT DBA TO SA
SET WRITE_DELAY 10
SET SCHEMA PUBLIC
INSERT INTO SAMPLE_TABLE VALUES(0,'Ford',100)
INSERT INTO SAMPLE_TABLE VALUES(1,'Toyota',200)
INSERT INTO SAMPLE_TABLE VALUES(2,'Honda',300)
INSERT INTO SAMPLE_TABLE VALUES(3,'GM',400)
INSERT INTO SAMPLE_TABLE VALUES(4,'Ford',100)
INSERT INTO SAMPLE_TABLE VALUES(5,'Toyota',200)
INSERT INTO SAMPLE_TABLE VALUES(6,'Honda',300)
INSERT INTO SAMPLE_TABLE VALUES(7,'GM',400)
Lab 4-1 Database (Hypersonic) Set Up
Due May 30th!