COMP 321

1. COMP 321 Week 4

2. Overview • Normalization • Entity-Relationship Diagrams • SQL • JDBC/JDBC Drivers • hsqldb • Lab 4-1 Introduction

3. Relational Databases • Store data in tables made up of rows and columns • Columns have data types • Rows represent entries

4. Relational Databases CREATETABLE Product ( Product_Code CHAR(11), Description CHAR(40), Price DECIMAL(10, 2) )

5. Normalization • What’s wrong with this table definition? CREATETABLE Order ( Product_Code CHAR(11), Quantity INTEGER, Description CHAR(40), Price DECIMAL(10, 2) )

6. Normalization • What’s wrong with this table definition? CREATETABLE Order ( Order_Id INTEGER, Product_Code CHAR(11), Quantity INTEGER, Description CHAR(40), Price DECIMAL(10, 2) )

7. Avoid Duplication – Create Two Tables CREATETABLE Order ( Order_Id INTEGERPRIMARYKEY, Product_Code CHAR(11), Quantity INTEGER ) CREATETABLE Product ( Product_Code CHAR(11) PRIMARYKEY, Description CHAR(40), Price DECIMAL(10, 2) )

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

9. Learning Activity 2 • Problem description: • Draw an entity-relationship diagram for the tables you designed for storing orders.

10. SQL • Four basic statements: • SELECT - selects data from tables • INSERT - inserts new data into a table • UPDATE - modifies existing rows in a table • DELETE - removes rows from a table

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

12. 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 State = ‘CA’ • Deletes all customers who live in CA

13. 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) database-independent way

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

15. JDBC

16. JDBC Driver Types • Type 1: JDBC-ODBC Bridge • Type 2: Native API Driver • Type 3: Network Protocol Driver • Type 4: Native Protocol Driver

17. Type 1 - JDBC - ODBC Bridge • Pros: Database independent • Cons: • Windows only • Performance • ODBC driver must be present

18. Type 2 – Native API Driver • Pros: better performance vs. type 1 • Cons: • Client library must be present • Platform-dependant

19. Type 3 - Network Protocol Driver • Pros: • No database library on client • Client is DB-independent • Cons: • Extra layer • DB-specific coding required in middleware

20. Type 4 – Native Protocol Driver • Pros: • Best performance • Pure Java • Cons: • Driver required for each database

21. Using JDBC • Identify type of driver needed • Obtain/Install driver • Add driver to classpath (In Eclipse, configure build path)

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

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

24. JDBC Statements Statement stmt = conn.createStatement(); String cmd = "INSERT INTO Users ('User1', 'Password')"; try { stmt.executeUpdate(cmd); } finally { stmt.close(); }

25. ResultSet Connection conn = null; Statement stmt = conn.createStatement(); try { ResultSet rs = stmt.executeQuery("SELECT * FROM PRODUCT"); try { while (rs.next()) { intid = rs.getInt("ITEMID"); doubleprice = rs.getDouble("PRICE"); String desc = rs.getString("DESCRIPTION"); // Do something with data } } finally { rs.close(); } } finally { stmt.close(); }

26. ResultSet Statement stmt = conn.createStatement(); try { ResultSet rs = stmt.executeQuery("SELECT ID,PRICE,DESC FROM PRODUCT"); try { while (rs.next()) { intid = rs.getInt(1); doubleprice = rs.getDouble(2); String desc = rs.getString(3); // Do something with data } } finally { rs.close(); } } finally { stmt.close(); }

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

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

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

30. 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)

31. Lab 4-1 Database (Hypersonic) Set Up • Due May 30th!