1 / 32

COMP 321

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. CREATE TABLE Product

anana
Download Presentation

COMP 321

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

More Related