Chapter 8 Databases

1. Chapter 8Databases

2. Objectives • Explain the structure of a relational database • Use SQL for creating, maintaining, and accessing relational databases • Use Java/JDBC for accessing relational databases • Explain and apply basic principles of good database design

3. RDBMS • A Relational Database Management System (RDBMS) provides data storage and access for web applications

4. Relation • relation is a mathematical term that refers to an ordered set of values drawn from different domains Ex: a relation on numbers x letters x symbols (55, A, #)

5. Database Structure • A database includes one or more tables • Each table represents one type of entity • Example: Tables in a Library Database Patron Loan (transaction) Recording Book

6. Database Structure • Each table field represents an entity attribute • Each table row represents one entity Car table: row field

7. Structured Query Language • SQL is a standard language for creating and maintaining relational databases • SQL statement types: • data definition: create databases and tables • data manipulation: add, modify, delete data • data control: set access permissions

8. Basic SQL Statements • Data definition • CREATE, DROP • Data manipulation • SELECT, INSERT, UPDATE, DELETE • Data control • GRANT, REVOKE

9. CREATE • Create a database or a table CREATE DATABASE ehsl CREATE ehsl.player (playerNr int PRIMARY KEY,name VARCHAR(30),isCurrent BOOLEAN NOT NULL)

10. Basic SQL Data Types • INTEGER • DECIMAL(T, R) • T=total digits, R=right digits (after '.') • FLOAT • CHAR(N) N characters • VARCHAR(N) up to N characters • BOOLEAN • DATE • TIME

11. DROP • DROP can be used to delete an entire database or a table DROP ehsl DROP ehsl.player

12. SELECT • SELECT retrieves data from a database SELECT field-list FROM database.table WHERE condition ORDER BY field-list • field-list is a comma-separated list of fields from the named table (* means "all fields") • condition is a Boolean condition using field names and/or constants

13. SELECT • Example SELECT * FROM ehsl.player SELECT playerNr, name FROM ehsl.player WHERE isCurrent=TRUE SELECT playerNr, name, status FROM ehsl.player WHERE playerNr >= 90001 ORDER BY status, name

14. INSERT • INSERT adds a new row to a table INSERT INTO ehsl.player VALUES (23752, 'Jane Doe', TRUE)

15. UPDATE • UPDATE changes one or more rows UPDATE database.tableSET field-assignment-listWHERE condition UPDATE ehsl.player SET isCurrent=TRUEWHERE playerNr=33256

16. DELETE • DELETE removes one or more rows from a table DELETE FROM database.tableWHERE condition DELETE FROM ehsl.playerWHERE playerNr=33523

17. Warning • UPDATE and DELETE without a WHERE clause will affect all rows! UPDATE ehsl.playerSET isCurrent=true DELETE FROM ehsl.player Change all rows! Delete all rows!

18. GRANT • GRANT can be used to give access permissions to users GRANT ALL PRIVILEGES ON database.tableTO user-name • user-name is formatted as user@host, for example 'jonesac'@'localhost'

19. REVOKE • REVOKE can be used to eliminate access permissions REVOKE ALL PRIVILEGES on database.tableFROM user-name REVOKE ALL PRIVILEGES on ehsl.playerFROM 'jonesac'@'localhost'

20. Create User • The CREATE command can also be used to create new users CREATE USER user-nameIDENTIFIED BY password CREATE USER 'jonesac'@'localhost'IDENTIFIEC BY 'abc123#'

21. JDBC • Java Database Connectivity (JDBC) is a Java API that allows Java programs to interact with relational database management systems • Interaction also requires a database driver, which translates JDBC commands to procedure calls on the RDBMS RDBMS Application Program Driver JDBC

22. JDBC – Load Driver • The first step is to load the database driver • Usually provided by the RDBMS vendor String driverClassName = "com.mysql.jdbc.Driver"; try { Class.forName(driverClassName); } catch (ClassNotFoundException cnfe) { …. }

23. JDBC – Execute Query (1/2) • To execute an SQL Query: String query = "..."; Vector<String> colNames = new Vector<String>, result = new Vector<String>; try { Connection con = DriverManager.getConnection( dbUrl, dbUserId, dbPassword); Statement st = con.createStatement(); ResultSet rs = st.executeQuery(query); ResultSetMetaData md = rs.getMetaData();

24. JDBC – Execute Query (2/2) • To execute an SQL Query: // get column names for (int i = 1; i <= md.getColumnCount(); i++) { colNames.addColumnName(md.getColumnName(i)); } // get field values while (rs.next()) { for (int i = 1; i<=md.getColumnCount(); i++) { result.addFieldValue(rs.getString(i)); } } con.close(); } catch (SQLException s) { ... } access the next row of the table access the next field of the row

25. JDBC – Execute Command • To execute an SQL command: int result = 0; String command = "..."; try { con = DriverManager.getConnection( dbUrl, dbUserId, dbPassword); stmt = con.createStatement(); result = stmt.executeUpdate(command); con.close(); } catch (SQLException s) { ... } result = number of rows affected (inserted, modified, or deleted)

26. JDBC Design • An effective design for database access: • JSP: user interface presentation • Java Servlet: application logic • Java Bean: database access (JDBC) Java Bean access RDBMS using JDBC JSP create user interface dependency RDBMS Java Servlet process / prepare data

27. Database Design Principles • Each field should contain a single value • Repeated fields with empty values should be made a separate table • Each table should represent only one entity

28. Example: Registration multiple valued field • WRONG: • Better: empty fields

29. Example: Registration • Better still: multiple entities: class registration / class name

30. Example: Registration • RIGHT:

31. Review • Relational Database / RDBMS • SQL • Data Definition • Data Manipulation • Data Control • JDBC • Database Driver • Query Execution • Command Execution • Database Design Principles