CS 405 G: Introduction to Database Systems Instructor: Jinze Liu Fall 2007
Administrative l l Assignment 3 due today. !!!!!! No new assignment this week 3/19/2018 Jinze Liu @ University of Kentucky 2
Administrative l Project l l Due Oct 19 th : Initial design and team setup Due Dec 7 th: Final report , code and executables. Quiz 2, this Friday before class Mid-term, Oct 15 th. 3/19/2018 Jinze Liu @ University of Kentucky 3
Administrative l Final project l l Sample codes of embedded SQL programming and APIs with {C, JAVA} will be available at class webpage You may use any language to implement the final project l l If you choose a language other than the one listed above, you are responsible to figure out whether and how the language supports SQL The database server must be oracle. cs. uky. edu. The database name is orcl. 3/19/2018 Jinze Liu @ University of Kentucky 4
Today’s Topic l l Database Architecture Database programming 3/19/2018 Jinze Liu @ University of Kentucky 5
Centralized Architectures l Centralized DBMS: combines everything into single system including- DBMS software, hardware, application programs and user interface processing software. 3/19/2018 Jinze Liu @ University of Kentucky 6
Two Tier Client-Server Architectures l. Server: provides database query and transaction services to client machines l. Client: provide appropriate interfaces to server. l. Run User Interface (UI) Programs and Application Programs l. Connect to servers via network. 3/19/2018 Jinze Liu @ University of Kentucky 7
Client-Server Interface l The interface between a server and a client is commonly specified by ODBC (Open Database Connectivity) l l Provides an Application program interface (API) Allow client side programs to call the DBMS. 3/19/2018 Jinze Liu @ University of Kentucky 8
Three (n) Tier Client-Server Architecture l Clients WAN l Intermediate layer Web server l Application servers l The intermediate layer is called Application Server or Web Server, or both: Stores the web connectivity software and business logic for applications Acts like a conduit for sending partially processed data between the database server and the client. Additional Features l Database servers 3/19/2018 Jinze Liu @ University of Kentucky Security: encrypt the data at the server and client before transmission 9
Database Programming: Overview l Pros and cons of SQL l l Very high-level, possible to optimize Specifically designed for databases and is called data sublanguage Not intended for general-purpose computation, which is usually done by a host language Solutions l l Augment SQL with constructs from general-purpose programming languages (SQL/PSM) Use SQL together with general-purpose programming languages l 3/19/2018 Database APIs, embedded SQL, JDBC, etc. Jinze Liu @ University of Kentucky 10
Clarification of Terms l John has a my. SQL database server installed in his laptop. He wrote a perl script to connect to the local my. SQL database, retrieve data, and print out reports about his house innovation plan. l l l Client-server model Use APIs provided by my. SQL to access the database Perl supports my. SQL API 3/19/2018 Jinze Liu @ University of Kentucky 11
Clarification of Terms (cont. ) l John went to his office. He has a JAVA program, which connects to a Sql. Server database in his company’s intranet. He use the program to retrieve data and print out reports for his business partner. l l l Client-server model Use APIs provided by Sql. Server to access the database Java supports Sql. Server API using JDBC 3/19/2018 Jinze Liu @ University of Kentucky 12
Clarification of Terms (cont. ) l After job, John went to youtube. com, searched for a video of Thomas train for his children, and downloaded one l l Client-mediate level-sever model “SQL experience a plus” from a job ad linked from youtube’s web site. WAN 3/19/2018 Jinze Liu @ University of Kentucky 13
Impedance mismatch and a solution SQL operates on a set of records at a time l Typical low-level general-purpose programming languages operates on one record at a time F Solution: cursor l l F Open (a result table): position the cursor before the first row Get next: move the cursor to the next row and return that row; raise a flag if there is no such row Close: clean up and release DBMS resources Found in virtually every database language/API • 3/19/2018 With slightly different syntaxes Jinze Liu @ University of Kentucky 14
A Typical Flow of Interactions l l l A client (user interface, web server, application server) opens a connection to a database server A client interact with the database server to perform query, update, or other operations. A client terminate the connection 3/19/2018 Jinze Liu @ University of Kentucky 15
Interfacing SQL with another language l API approach l l SQL commands are sent to the DBMS at runtime Examples: JDBC, ODBC (for C/C++/VB), Perl DBI These API’s are all based on the SQL/CLI (Call-Level Interface) standard Embedded SQL approach l l l SQL commands are embedded in application code A precompiler checks these commands at compile-time and converts them into DBMS-specific API calls Examples: embedded SQL for C/C++, SQLJ (for Java) 3/19/2018 Jinze Liu @ University of Kentucky 16
Example API: JDBC l JDBC (Java Data. Base Connectivity) is an API that allows a Java program to access databases // Use the JDBC package: import java. sql. *; … public class … { … static { // Load the JDBC driver: try { Class. for. Name("oracle. jdbc. driver. Oracle. Driver"); } catch (Class. Not. Found. Exception e) { … } } … } 3/19/2018 Jinze Liu @ University of Kentucky 17
Connections // Connection URL is a DBMS-specific string: String url = ”jdbc: oracle: thin: @oracle. cs. uky. edu: 1521: orcl”; // Making a connection: conn =Driver. Manager. get. Connection(url, username, password) … // Closing a connection: con. close(); For clarity we are ignoring exception handling for now 3/19/2018 Jinze Liu @ University of Kentucky 18
Statements // Create an object for sending SQL statements: Statement stmt = con. create. Statement(); // Execute a query and get its results: Result. Set rs = stmt. execute. Query(”SELECT name, passwd FROM regiusers”); // Work on the results: … // Execute a modification (returns the number of rows affected): int rows. Updated = stmt. execute. Update (”UPDATE regiusers SET passwd = ’ 1234’ WHERE name = ‘sjohn’ ”); // Close the statement: stmt. close(); 3/19/2018 Jinze Liu @ University of Kentucky 19
Query results // Execute a query and get its results: Result. Set rs = stmt. execute. Query(”SELECT name, passwd FROM regiusers”); // Loop through all result rows: while (rs. next()) { // Get column values: String name = rs. string(1); String passwd = rs. get. String(2); // Work on sid and name: … } // Close the Result. Set: rs. close(); 3/19/2018 Jinze Liu @ University of Kentucky 20
Other Result. Set features l l Move the cursor (pointing to the current row) backwards and forwards, or position it anywhere within the Result. Set Update/delete the database row corresponding to the current result row l l Analogous to the view update problem Insert a row into the database l Analogous to the view update problem 3/19/2018 Jinze Liu @ University of Kentucky 21
Prepared statements: motivation Statement stmt = con. create. Statement(); for (int age=0; age<100; age+=10) { Result. Set rs = stmt. execute. Query (”SELECT AVG(GPA) FROM Student” + ” WHERE age >= ” + age + ” AND age < ” + (age+10)); // Work on the results: … } l l l Every time an SQL string is sent to the DBMS, the DBMS must perform parsing, semantic analysis, optimization, compilation, and then finally execution These costs are incurred 10 times in the above example A typical application issues many queries with a small number of patterns (with different parameter values) 3/19/2018 Jinze Liu @ University of Kentucky 22
Transaction processing l Set isolation level for the current transaction l l con. set. Transaction. Isolation. Level(l); Where l is one of TRANSACTION_SERIALIZABLE (default), TRANSACTION_REPEATABLE_READ, TRANSACTION_READ_COMITTED, and TRANSACTION_READ_UNCOMMITTED l Set the transaction to be read-only or read/write (default) l l Turn on/off AUTOCOMMIT (commits every single statement) l l con. set. Read. Only(true|false); con. set. Auto. Commit(true|false); Commit/rollback the current transaction (when AUTOCOMMIT is off) l l con. commit(); con. rollback(); 3/19/2018 Jinze Liu @ University of Kentucky 23
Pros and cons of embedded SQL l Pros l l l More compile-time checking (syntax, type, schema, …) Code could be more efficient (if the embedded SQL statements do not need to checked and recompiled at runtime) Cons l DBMS-specific l l l 3/19/2018 Vendors have different precompilers which translate code into different native API’s Application executable is not portable (although code is) Application cannot talk to different DBMS at the same time Jinze Liu @ University of Kentucky 24
Summary l l Two-tier architecture Three-tier architecture l l Client-server Client, mediate level, server l l SQL C, C++, JAVA, PERL Database programming l l 3/19/2018 Web server, application server Data sublanguage Host language API Embedded SQL Jinze Liu @ University of Kentucky 25
Скачать презентацию CS 405 G Introduction to Database Systems Instructor
29744d9ceed3c984e89d8e206390c9d7.ppt