Chapter 13 Databases and the Internet

Chapter 13Databases 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 • To provide training for employees • To expand the workplace • …Many other innovative activities

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

HTTP • 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

HTML • 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

XML • 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 • 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 • Obey rules of XML • Starts with XML declaration • Root element contains all other elements • All elements properly nested

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 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 • 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 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 • 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 • 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 • 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 • Third layer is standard database or other data source • Ideally on separate server

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 • 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 • Enter from SQL*Plus in usual way CREATE OR REPLACE PROCEDURE pagenameIS BEGIN • Functions generate HTML tags HTP.HTMLOPEN; HTP.HEADOPEN; HTP.TITLE(‘ ‘); HTP.HEADCLOSE; HTP.BODYOPEN; HTP.HEADER(n,‘ ‘ ); HTP.PARA; HTP.PRINT(' '); HTP.BODYCLOSE; HTP.HTMLCLOSE • 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) • 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

JDBC • 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 • At the start of the application, import the Java classes import java.sql.*; • For Oracle database add importoracle.jdbc; • Load the JDBC drivers. For Oracle,write Class.forName(“oracle.jdbc.driver.OracleDriver”); • 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 • 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 • 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 • 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 • 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 • 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 • 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 • 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 • 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 • (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

Chapter 13 Databases and the Internet