chapter 13 databases and the internet n.
Skip this Video
Loading SlideShow in 5 Seconds..
Chapter 13 Databases and the Internet PowerPoint Presentation
Download Presentation
Chapter 13 Databases and the Internet

Loading in 2 Seconds...

play fullscreen
1 / 36

Chapter 13 Databases and the Internet - PowerPoint PPT Presentation

  • Uploaded on

Chapter 13 Databases and the Internet. Spring 2014. Uses for Web-based DB Applications. e-commerce has pushed organizations to develop Web-based database applications To create world-wide markets To deliver information To provide better customer service To communicate with their suppliers

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

Chapter 13 Databases and the Internet

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
uses for web based db applications
Uses for Web-based DB Applications
  • e-commerce has pushed organizations to develop Web-based database applications
    • To create world-wide markets
    • To deliver information
    • To provide better customer service
    • To communicate with their suppliers
    • To provide training for employees
    • To expand the workplace
    • …Many other innovative activities
databases and the www
Databases and the WWW
  • WWW is a loosely organized information resource
  • Some websites use static linked HTML files
    • can become inconsistent and outdated
  • Many organizations provide dynamic access to databases directly from the Web
    • introduces new problems for designers and DBAs
    • combination of communications technology, information retrieval technology and database technology
    • XML a standard for document storage, exchange, and retrieval.
origins of the internet
Origins of The Internet
  • Developed from Arpanet, communications network created in the 1960s by DARPA, US agency, for linking government and academic research institutions
  • Used a common protocol, TCP/IP
  • US National Science Foundation took over management of the network, then referred to as the Internet
  • Navigating and using the Internet required considerable sophistication
world wide web
World Wide Web
  • Tim Berners-Lee proposed a method of simplifying access to Internet resources in 1989
  • Led to the development of the World Wide Web
  • included notions of URL, HTTP, HTML, hypertext, graphical browsers with links
  • Automated finding, downloading, and displaying files on the Internet
browsers and urls
Browsers and URLs
  • Browser-
    • software for searching, retrieving, presenting, and traversing resources on the WWW
    • Ex. Microsoft Internet Explorer, Mozilla Firefox, Google Chrome, Apple Safari and others
  • URL –Uniform Resource Locator
    • Specific type of Uniform Resource Identifier (URI)
    • String giving the location of any type of resource on the Internet-Web pages, mailboxes, downloadable files, etc.
    • Gives protocol, server name, path name to resource
  • Communications protocol
    • Standard for structure of messages
    • HTTP request usually has a few lines of text
      • HTTP method field (GET or POST), URI of resource, HTTP version
      • host header, with host name and HTTP port
      • user agent line, shows the type of the client (optional)
      • types of files the client will accept (optional)
  • HTTP is a stateless protocol
    • No facility for remembering previous interactions
    • Creates a problem for e-commerce, which requires a continuous session with the user
  • Data format used for presenting content on the Internet
  • A markup language because HTML documents contain tags that provide formatting information for the text
    • Ex <HEAD> ,<BODY> , <B> ,<U> ,<I>, <H1> <UL> <LI> and their ending tags
  • HTML document can contain applets, audio files, images, video files, other content
  • Extensible Markup Language - standard for document storage, exchange, and retrieval
  • Created in 1996 by the World Wide Web Consortium (W3) XML Special Interest Group
  • Users can define their own markup language, with their own tags for data items in documents, including databases
  • Can define the structure of heterogeneous databases
  • supports translation of data between different databases
components of xml documents
Components of XML Documents
  • Element-the basic component
  • Contains one or more XML elements
    • each with a start tag showing the name of the element, some character data, and an end tag
  • Can have sub-elements-must be properly nested
  • Can have attributes-names and values shown inside the element’s start tag
  • Attributes occur only once within each element, while sub-elements can occur any number of times
  • Comments can occur anywhere <!---->,
  • can contain entity references-refer to external files, common text, Unicode characters, or reserved symbols
well formed xml document
Well-Formed XML Document
  • Obey rules of XML
    • Starts with XML declaration
    • Root element contains all other elements
    • All elements properly nested
dtd and xml schema
DTD and XML Schema
  • Users can define their own markup language by writing either
    • A Document Type Declaration (DTD)
      • A specification for a set of rules for the elements, attributes, and entities of a document
      • A document that obeys the rules of its associated DTD is type-valid
    • An XML Schema
      • New, more powerful way to describe the structure of documents
      • A document that conforms to an XML schema is schema-valid
dtd rules
DTD Rules
  • DTD is enclosed in <!DOCTYPE name[DTDdeclaration]>
  • each element is declared using a type declaration with structure <!ELEMENT (content type)>
  • In an element declaration, the name of any sub-element can be followed by one of the symbols *, + or ?, to indicate the number of times the sub-element occurs
  • Attribute list declarations for elements are declared outside the element
  • DTD can be external or internal (embedded in instance document)
xml schema
XML Schema
  • Permits more complex structure than DTD
  • Additional fundamental datatypes, UDTs
  • User-created domain vocabulary
  • Supports uniqueness and foreign key constraints
  • Schema lists elements and attributes
    • Elements may be complex, which means they have sub-elements, or simple elements can occur multiple times
    • Attributes or elements can be used to store data values
    • Attributes used for simple values that are not repeated
  • Can validate schema at several websites
three tier architecture
Three-tier Architecture
  • Three major functions for an Internet environment: presentation, application logic, data management
  • Placement of functions depends on architecture of system
  • Three tier architectures completely separate application logic from data management
    • Client handles user interface, the presentation layer- first tier
    • Application server executes application logic -the middle tier
    • Database server forms the third tier
  • Communications network connects each tier to the next
advantages of 3 tier architecture
Advantages of 3-tier Architecture
  • Allows support for thin clients that only handle the presentation layer
  • Independence of tiers; may use different platforms
  • Easier application maintenance on the application server
  • Integrated transparent data access to heterogeneous data sources
  • Scalability
presentation layer
Presentation Layer
  • HTML forms often used at the presentation layer
  • Scripting languages such as Perl, JavaScript, JScript, VBScript, may be embedded in HTML to provide some client-side processing
  • Style sheets specify how data is presented on specific devices-CSS, XSL
application server
Application Server
  • Middle tier - responsible for executing applications
    • Determines the flow of control
    • Acquires input data from presentation layer
    • Makes data requests to database server
    • Accepts query results from database layer
    • Uses them to assemble dynamically generated HTML pages
  • Server-side processing can use different technologies such as Java Servlets, Java Server pages, etc.
  • CGI, Common Gateway Interface, can be used to connect HTML forms with application programs
  • To maintain state during a session, servers may use cookies, hidden fields in HTML forms, and URI extensions.
    • Cookies generated at the middle tier using Java’s Cookie class, sent to the client, where they are stored in the browser cache
data layer
Data Layer
  • Third layer is standard database or other data source
  • Ideally on separate server
oracle web programming
Oracle Web Programming
  • PL/SQL Web Toolkit
    • Handles all three layers using PL/SQL
  • PL/SQL Server Pages (PSP)
    • embed PL/SQL code in HTML pages
  • JDBC
    • Application Programming Interface (API) standard for relational database access from Java
  • And several other methods
pl sql web toolkit
PL/SQL Web Toolkit
  • Allows user to access Oracle from a webpage
    • Using a browser, user sends request to a Web server, passing input values
    • Server sends a message to the database server, invoking a PL/SQL stored procedure in the Oracle database, passing parameters as needed.
    • Stored procedure invokes subprograms in the PL/SQL Web Toolkit, generating a Web page dynamically
    • Generated page is passed to the Web server
    • Web server displays the page in the client’s browser

See Figure 13.8

web toolkit htp package
Web Toolkit htp Package
  • Enter from SQL*Plus in usual way


  • Functions generate HTML tags


  • Inside the body, htp.formOpen (parameter list ) creates a form
  • Form elements created using procedures

htp.formText, htp.formCheckbox, htp.formRadio, htp.formTextarea, and others

pl sql server pages psp
PL/SQL Server Pages (PSP)
  • provide the dynamic database content
  • embed PL/SQL statements in HTML pages
  • Write standard HTML scripts, using the delimiters <% and %> to identify PL/SQL statements
  • Can have Web Toolkit calls in the same application
  • handled by the Oracle PL/SQL Web gateway (mod_plsql)
  • reside on the server side
  • PSP page is loaded into Oracle database space using utility program loadpsp
  • See Figure 13.12
figure 13 12
Figure 13.12

<%@ page language=”PL/SQL”%>

<%@ page contentType=”text/html”%>

<%@ plsql procedure=”StudentList”%>

<% /** This example displays the last name and first name of every

student in the Student table.**/ %>


CURSOR stu_cursor IS

SELECT lastName, firstName

FROM Student

ORDER BY lastName;




<meta http-equiv=”Content-Type”content=”text/html”>

<title>Student List</title>


<body TEXT=”#000000”BGCOLOR=”#FFFF00”>

<h1>Student List</h1>

<table width=”50%”border=”1”>


<th align=”left”>Last Name</th>

<th align=”left”>First Name</th>


<% FOR stu IN stu_cursor LOOP %>


<td> <%= stu.lastName %> </td>

<td> <%= stu.firstName %> </td>


<% END LOOP;%>




  • API-standard for relational database access from Java
  • Includes a set of Java classes and interfaces
  • Oracle and other vendors have extended functionality
  • Applications are platform independent
  • Can run on a variety of servers and DBMSs.
jdbc application steps
JDBC Application Steps
  • At the start of the application, import the Java classes

import java.sql.*;

  • For Oracle database add


  • Load the JDBC drivers. For Oracle,write


  • Connect to the database using the DriverManagerclass -

GetConnection method creates a connection object, which is used for all communication

conn = DriverManager.getConnection("jdbc:oracle:oci8:url”,”yourId”,”yourpassword”);

  • Use SQL to interact with the database and Java for the logic in the application program
  • Close the connection object to disconnect from the database conn.close();
  • See Figure 13.13
figure 13 13
Figure 13.13

import java.sql.*;


class Test{

public static void main(String args[]) {

try {



catch (ClassNotFoundException e) {

System.out.println(“Cannot load OracleDriver”);


Connection conn = null;

Statement stmt = null;

ResultSet rset = null;

try {

conn = DriverManager.getConnection(“jdbc:oracle:oci8”, ”Jones”, ”sesame”);

stmt = conn.createStatement();

rset = stmt.executeQuery(“select stuId, lastName, firstName from Student”);

while (

System.out.println(rset.getString(1)+” “ + rset.getString(2)+” “ +rset.getString(3));





catch (SQLException e) {

System.out.println(“SQL error:“ + e.getMessage());




the connection object
The Connection Object
  • Has 3 JDBC classes for communicating with database
    • Statement -for SQL statements with no parameters
    • PreparedStatement precompiled SQL statement – to be executed many times
    • CallableStatement -for executing stored procedures
  • Has 3 methods to create instances of these classes
    • createStatement returns a new Statement object
    • prepareStatement takes an SQL statement, precompiles it, and stores it in a PreparedStatement object
    • prepareCall for call to a stored procedure; has methods for handling input and output parameters and executing the procedure;returns a CallableStatement
  • Statement object is used for executing SQL statements
    • Methods executeUpdate, executeQuery. etc.
    • executeQuery method executes an SQL statement and returns a ResultSet object
    • ResultSet class has many useful methods; includes a cursor to present one row at a time
    • Within a row, columns can be retrieved using a get method
xml and the semi structured data model
XML and the Semi-structured Data Model
  • Semi-structured data model uses a tree structure
  • Nodes represent complex objects or atomic values
  • An edge represents either relationship between an object and its sub-object, or between an object and its value
  • Leaf nodes, with no sub-objects, represent values
  • Nodes of the graph for a structured XML document are ordered using pre-order traversal, depth-first, left-to-right order
  • There is no separate schema, since the graph is self-describing
  • See Figure 13.14
xml parsers
XML Parsers
  • Read in the XML document as a string
  • parsing technique can use
    • event-driven model - a single pass through document
    • tree-based model- parsing tree built in memory
  • DOM parsing- Document Object Model)
    • W3C standard for the tree-based model
    • Has methods to create nodes, modify nodes, and delete nodes of tree
    • Allows the programmer to navigate the tree structure as needed and supports dynamic data updating
  • SAX parsing (Simple API for XML)
    • Reads document, recognizes each fragment of XML syntax
    • Streams a series of events – document handlers and error handlers
    • Events sent to event handlers for processing the elements
xml data manipulation
XML Data Manipulation
  • XQuery is W3C standard query language for XML data
    • Uses the abstract logical structure of a document as it is encoded in XML Schema or DTD
    • Queries use a path expression, which comes from an earlier language, XPath
    • Consists of the document name and specification of the elements to be retrieved, using a path relationship
    • Can add conditions to any nodes in a path expression
    • Evaluated by reading forward in the document until a node of the specified type and condition is encountered
flwor expressions
FLWOR Expressions
  • XQuery uses a FLWOR expression::FOR, LET, WHERE, ORDER BY, and RETURN clauses
  • Ex

FOR $C IN doc(“CustomerList.xml”)//Customer)

WHERE $C/Type=”Individual”


RETURN <Result> $N/Name, $N/Status </Result>

  • Allows for binding of variables to results
  • Allows for iterating through the nodes of a document
  • Allows joins to be performed
  • Allows data to be restructured
  • XQuery provides many predefined functions, including count, avg, max, min, and sum, which can be used in FLWOR expressions.
xml and relational databases
XML and Relational Databases
  • Relational DBMSs extended their native datatypes to allow storage of XML documents
  • Also possible to use SQL with XPath expressions to retrieve values from the database
  • Existing heterogeneous databases can be queried using standard languages such as SQL, and query results can be placed into an XML instance document
  • Query language has to have facilities that can tag and structure relational data into XML format
xml database conversion
XML-Database Conversion
  • Ultimate goal is Round-Tripping
    • accept data in XML form
    • transform it into a relational database
    • query and update and the database using SQL
    • transform the output back into XML format
  • All without loss of content
oracle xml db
Oracle XML DB
  • standard feature of Oracle
  • User can create databases in which the XMLType is used as a native datatype
  • actually an object type, can be used as a type for a column or for an object table, or for parameters and PL/SQL variables
  • provides methods for accessing and querying XML content, using XML and SQL syntax
  • Follows W3C recommendations for XML
oracle xml developer s kits
Oracle XML Developer’s Kits
  • (XDKs) for PL/SQL, Java, C and C++
  • Include libraries and utilities for using XML with those host languages
  • Allows documents to be parsed
    • The PL/SQL XDK supports DOM parsing only
    • Java, C, and C++ parsers provide both DOM and SAX parsing
  • XML SQL Utility (XSU) allows two-way mapping between XML and SQL
    • Can extract data from XML documents and insert it into database tables or views, using a standard canonical mapping
    • Update or delete data in the database tables or views using SQL
    • Extract data from object-relational tables or views and transform the Results Set into XML