1 / 31

Chapter 8 Databases

Chapter 8 Databases. 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. RDBMS.

saber
Download Presentation

Chapter 8 Databases

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

More Related