Chapter 13 databases and the internet
1 / 34

Chapter 13 Databases and the Internet - PowerPoint PPT Presentation

  • Uploaded on

Chapter 13 Databases and the Internet. Fall 2012. 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

PowerPoint Slideshow about ' Chapter 13 Databases and the Internet' - thane-nielsen

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
Chapter 13 databases and the internet

Chapter 13Databases and the Internet

Fall 2012

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


  • 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

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

    • createStatementreturns a new Statement object

    • prepareStatementtakes an SQL statement, precompiles it, and stores it in a PreparedStatementobject

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

    • executeQuerymethod executes an SQL statement and returns a ResultSet object

    • ResultSetclass has many useful methods; includes a cursor to present one row at a time

    • Within a row, columns can be retrieved using a getmethod

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”

    ORDER BY Name

    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