Database System Concepts

(1)

Database System Concepts Database Applications Embedded SQL Database APIs Web Database Applications

Database System Concepts

Chapter 8(+4): Application Design and Development

Departamento de Engenharia Inform´atica Instituto Superior T´ecnico

1st Semester

2010/2011

Slides (fortemente) baseados nos slides oficiais do livro “Database System Concepts”

c

(2)

Outline

1

Database Applications

2

Embedded SQL

3

Database APIs

ODBC

JDBC

4

Web Database Applications

The World Wide Web

Databases and the Web

(3)

Outline

1

2

Embedded SQL

3

Database APIs

(4)

User Interfaces and Tools

Most database users do not use a query language like SQL

Forms

Graphical user interfaces Report generators Data analysis tools

Applications can embed or execute SQL

Many interfaces are Web-based

Client-side: Javascript, Applets, ...

Server-side: Java Server Pages (JSP), Active Server Pages (ASP), ...

(5)

Application Architectures

Applications can be built using one of two architectures:

Two tier model

Application programs running at user site communicate directly with the database

Three tier model

User programs running at user sites communicate with an application server

The application server in turn communicates with the database

(6)

Two-Tier Architecture

(7)

Three-Tier Architecture

(8)

(9)

Two-tier Model

Example: Java code runs at client site and uses JDBC to

communicate with the server

Benefits:

flexible, need not be restricted to predefined queries

Problems:

Security: all database operations possible More code shipped to client

Not appropriate across organizations, or in large ones like universities

(10)

Three-tier Model

Example: Web client + Java Servlet using JDBC to talk

with database server

Benefits:

Security handled by application at server Simple client

Distributed system

Problems:

Performance issues Network-dependent Only packaged transactions

(11)

Outline

1

2

Embedded SQL

3

Database APIs

(12)

Embedded SQL

The SQL standard defines embeddings of SQL in a variety

of programming languages such as C, Java, and Cobol

A language to which SQL queries are embedded is referred

to as a

host language

, and the SQL structures permitted

in the host language comprise embedded SQL

EXEC SQL

statement is used to identify embedded SQL

request to the SQL preprocessor

EXEC SQL

<

embedded SQL statement

>

;

Note: this varies by language (for example, the Java embedding uses#SQL { ... };)

(13)

Embedded SQL Example

Find the names and cities of customers with more than a given

amount of dollars in their accounts

EXEC SQL BEGIN DECLARE SECTION; double amount;

char name[50]; char city[50];

(14)

Embedded SQL Example (cont.)

amount= 100; EXEC SQL

declare c cursor for

select depositor.customer name, customer city from depositor, customer, account

where depositor.customer name=customer.customer name and depositoraccount number =account.account number and account.balance>:amount;

(15)

Embedded SQL Example (cont.)

EXEC SQL open c; do {

EXEC SQL fetch c into :name, :city; if (SQLCODE! = 0) break;

printf("%s, %s\n", name, city);

} while (1); EXEC SQL close c;

(16)

Updates through Cursors

Can update tuples fetched by cursor by declaring that the

cursor is for update

declare

c

cursor for

select

∗

from

account

where

branch name

=

’Perryridge’

for update

To update the tuple at the current location of cursor c

update

account

set

balance

=

balance

+ 100

(17)

Dynamic SQL

Allows programs to construct and submit SQL queries at

run time

Example:

char sqlprog[100] = "update account "

"set balance = balance * 1.05 " "where account number = ?"; EXEC SQL prepare dynprog from :sqlprog;

char account[10] = ”A-101”;

EXEC SQL execute dynprog using :account;

The dynamic SQL program contains a “?”, which is a

place holder for a value that is provided when the SQL

program is executed

(18)

Database System Concepts Database Applications Embedded SQL Database APIs ODBC JDBC Web Database Applications

Outline

1

2

Embedded SQL

3

Database APIs

ODBC

JDBC

(19)

Database APIs

API (application-program interface) for a program to

interact with a database server

Application makes calls to

Connect with the database server

Send SQL commands to the database server

Fetch tuples of result one-by-one into program variables

ODBC

(Open Database Connectivity) works with C, C++,

C#, ...

(20)

ODBC

Open DataBase Connectivity (ODBC) standard

Standard for application programs to communicate with a database server

Application program interface (API) to open a connection with a database, send queries and updates,

get back results.

Applications such as GUI, spreadsheets, etc. can use

ODBC

Each database system supporting ODBC provides a

driver

library that must be linked with the client program.

When client program makes an ODBC API call, the code

in the library communicates with the server to carry out

the requested action, and fetch results.

(21)

Connecting to the Database

int ODBCexample()

{

RETCODE error;

HENV env; /*environment*/

HDBC conn; /*database connection*/

SQLAllocEnv(&env);

SQLAllocConnect(env, conn);

SQLConnect(conn, ”tagus.ist.utl.pt”, SQL NTS, ”user”, SQL NTS, ”userpasswd”, SQL NTS); /*program code*/ SQLDisconnect(conn); SQLFreeConnect(conn); SQLFreeEnv(env); }

(22)

Executing SQL Statements

char branchname[80]; float balance;

int lenOut1, lenOut2; HSTMT stmt;

SQLAllocStmt(conn, &stmt);

char sqlquery[] = "select branch name, sum(balance) from account group by branch name"; error = SQLExecDirect(stmt, sqlquery, SQL NTS); if (error == SQL SUCCESS) {

SQLBindCol(stmt, 1, SQL C CHAR, branchname, 80, &lenOut1); SQLBindCol(stmt, 2, SQL C FLOAT, &balance, 0 , &lenOut2); while (SQLFetch(stmt) >= SQL SUCCESS) {

printf ("%s %g", branchname, balance);

} }

(23)

More ODBC Features

Prepared Statements

SQL statements compiled at the database insert into account values(?,?,?) Repeatedly executed with actual values for the placeholders (“?”)

Metadata features

finding all the relations in the database and

finding the names and types of columns of a query result or a relation in the database.

Control over transactions

Can turn off automatic commit on a connection transactions must then be committed or rolled back explicitly

(24)

JDBC

is a Java API for communicating with database

systems supporting SQL

JDBC supports a variety of features for querying and

updating data, and for retrieving query results

JDBC also supports metadata retrieval, such as querying

about relations present in the database and the names and

types of relation attributes

Model for communicating with the database:

Open a connection Create astatementobject

Execute queries using the statement object to send queries and fetch results

(25)

Connecting to the Database

public static void JDBCexample(String userid,String passwd)

{

try {

Class.forName(“com.mysql.jdbc.Driver”);

Connection conn = DriverManager.getConnection( “jdbc:mysql://tagus.ist.utl.pt/database”, userid, passwd); Statement stmt = conn.createStatement();

/*program code*/

stmt.close(); conn.close();

}

catch (SQLException sqle) {

System.out.println("SQLException: "+ sqle);

} }

(26)

Executing SQL Statements

ResultSet rset = stmt.executeQuery(

"select branch name, avg(balance) from account

group by branch name"); while (rset.next()) { System.out.println(rset.getString(“branch name”) + ‘‘ ’’ + rset.getFloat(2)); } try { stmt.executeUpdate(

"insert into account values(’A-9732’, ’Perryridge’, 1200)");

} catch (SQLException sqle) {

System.out.println("Could not insert tuple. "+ sqle);

(27)

Prepared Statements

PreparedStatement

s

;

s

=

conn

.prepareStatement (

"insert into account values(?,?,?)");

s

.setString(

1

,

“A-9732”

);

s

.setString(

2

,

“Perryridge”

);

s

.setFloat(

3

,

1200

);

s

.executeUpdate();

s

.close();

(28)

The World Wide Web Databases and the Web

Outline

1

2

Embedded SQL

3

Database APIs

4

Web Database Applications

The World Wide Web

Databases and the Web

(29)

The World Wide Web

The Web is a distributed information system based on

hypertext

Most Web documents are hypertext documents formatted

via the HyperText Markup Language (HTML)

HTML documents contain

text along with font specifications, and other formatting instructions

hypertext links to other documents, which can be associated with regions of the text.

forms, enabling users to enter data which can then be sent back to the Web server

(30)

HTML and HTTP

HTML provides formatting, hypertext link, and image

display features.

HTML also provides input features

Select from a set of options

Pop-up menus, radio buttons, check lists Enter values

Text boxes

Filled in input sent back to the server, to be acted upon by an executable at the server

HyperText Transfer Protocol (HTTP) used for

communication with the Web server

(31)

Sample HTML Source Code

<html>

<body>

<tr><td>A-101</td><td>Downtown</td><td>500</td></tr> <tr><td>A-102</td><td>Perryridge</td><td>400</td></tr> <tr><td>A-201</td><td>Brighton</td><td>900</td></tr> </table>

<center>The <i>account</i> relation</center> <form action="BankQuery" method=get>

Select account/loan and enter number<br> <select name="type">

<option value="account" selected>Account</option> <option value="Loan">Loan</option>

</select>

</body> </html>

(32)

(33)

Uniform Resources Locators

In the Web, functionality of pointers is provided by

Uniform Resource Locators (URLs).

URL example:

http://dev.mysql.com

/doc/refman/5.0/en/index.html

Thefirst partindicates how the document is to be accessed Thesecond partgives the unique name of a machine on the Internet

Therestof the URL identifies the document within the machine

The local identification can be:

The path name of a file on the machine, or

An identifier (path name) of a program, plus arguments to be passed to the program

(34)

Web Servers

A Web server can easily serve as a front end to a variety of

information services

The document name in a URL may identify an executable

program, that, when run, generates a HTML document

When a HTTP server receives a request for such a document, it executes the program, and sends back the HTML document that is generated

The Web client can pass extra arguments with the name of the document

To install a new service on the Web, one simply needs to

create and install an executable that provides that service.

The Web browser provides a graphical user interface to the information service

Common Gateway Interface (CGI): a standard interface

between web and application server

(35)

HTTP and Sessions

The HTTP protocol is

connectionless

That is, once the server replies to a request, the server closes the connection with the client, and forgets all about the request

In contrast, Unix logins, and JDBC/ODBC connections stay connected until the client disconnects

retaining user authentication and other information Motivation: reduces load on server

operating systems have tight limits on number of open connections on a machine

Information services need session information

E.g. user authentication should be done only once per

session

(36)

Sessions and Cookies

A cookie is a small piece of text containing identifying

information

Sent by server to browser on first interaction

Sent by browser to the server that created the cookie on further interactions

part of the HTTP protocol

Server saves information about cookies it issued, and can use it when serving a request

E.g., authentication information, and user preferences

(37)

Web Interfaces to Databases

Web browsers have become the de-facto standard user

interface to databases

Enable large numbers of users to access databases from anywhere

Avoid the need for downloading/installing specialized code, while providing a good graphical user interface

Examples: banks, airline and rental car reservations, university course registration and grading, etc.

However, static HTML documents are limited:

Cannot customize fixed Web documents for individual users;

Problematic to update Web documents, especially if multiple Web documents replicate data.

(38)

Dynamic Generation of Web Documents

Solution: generate Web documents dynamically from data

stored in a database

Can tailor the display based on user information stored in the database

Example: tailored ads, tailored weather and local news, ... Displayed information is up-to-date

(39)

Common Gateway Interface (CGI)

Standard protocol for interfacing external application

software with an information server

Certain locations are defined to be served by a CGI

program

Whenever a request to a matching URL is received, the

corresponding program is called

However, the overhead of spawning new processes for

every request can be overwhelming

Solution:

use compiled languages use client side scripting

integrate language interpreters into the server optimize the server (use caching, ...)

(40)

Servlets

Java Servlet specification defines an API for

communication between the Web server and application

program

E.g. methods to get parameter values and to send HTML text back to client

Application program (also called a servlet) is loaded into

the Web server

Two-tier model

Each request spawns a new thread in the Web server thread is closed once the request is serviced

Servlet API provides a getSession() method

Sets a cookie on first interaction with browser, and uses it to identify session on further interactions

Provides methods to store and look-up per-session information

(41)

Example Servlet Code

Public class BankQueryServlet extends HttpServlet { public void doGet(HttpServletRequest request,

HttpServletResponse result) throws ServletException, IOException {

String type = request.getParameter(“type00); String number = request.getParameter(“number00);

/*...code to find the loan amount/account balance...*/ /*...using JDBC to communicate with the database...*/ /*...we assume the value is stored in the variable balance...*/

result.setContentType(“text/html00);

PrintWriter out = result.getWriter();

out.println(“<HEAD><TITLE>QueryResult< /TITLE>< /HEAD>00);

out.println(“<BODY >00);

out.println(“Balanceon00+type+number+ “ =00+balance);

out.println(“< /BODY >00);

out.close(); }

(42)

Server-Side Scripting

Server-side scripting simplifies the task of connecting a

database to the Web

Define a HTML document with embedded executable code/SQL queries.

Input values from HTML forms can be used directly in the embedded code/SQL queries.

When the document is requested, the Web server executes the embedded code/SQL queries to generate the actual HTML document.

Numerous server-side scripting languages

JSP, Server-side Javascript, PHP, Jscript, ...

(43)

Client Side Scripting and Applets

Browsers can fetch certain scripts (

client-side scripts

) or

programs along with documents, and execute them in

“safe mode” at the client site

Javascript

Macromedia Flash and Shockwave for animation/games VRML

Applets

Client-side scripts/programs allow documents to be active

E.g., animation by executing programs at the local site E.g. ensure that values entered by users satisfy some correctness checks

Permit flexible interaction with the user.

Executing programs at the client site speeds up interaction by avoiding many round trips to server

(44)

Client Side Scripting and Security

Security mechanisms needed to ensure that malicious

scripts do not cause damage to the client machine

Easy for limited capability scripting languages, harder for general purpose programming languages like Java

Example: Java’s security system ensures that the Java

applet code does not make any system calls directly

Disallows dangerous actions such as file writes

Notifies the user about potentially dangerous actions, and allows the option to abort the program or to continue execution

(45)

http://dev.mysql.com/doc/refman/5.0/en/index.html

http://www.google.pt/search?hl=pt-PT&q=mysql