DAVID M. KROENKE and DAVID J.

AUER

DATABASE CONCEPTS, 7th Edition

Chapter Seven
Database Processing Applications
 Chapter Objectives
• Understand and be able to set up Web
database processing
• Learn the basic concepts of Extensible
Markup Language (XML)

KROENKE and AUER - DATABASE CONCEPTS (7th Edition)

Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-2
 The Database Processing
Environment I

Figure 7-2: Components of a Database System

KROENKE and AUER - DATABASE CONCEPTS (7th Edition)
Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-3
 The Database Processing
Environment

Figure 7-3: The Database Processing Environment

KROENKE and AUER - DATABASE CONCEPTS (7th Edition)
Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-4
 Processing Constraints
• Enforcing referential integrity
• Cascading deletion
• Cascading modifications
• Data type constraints
• Data size constraints
• Data value constraints
• Null constraints
• Uniqueness constraints

KROENKE and AUER - DATABASE CONCEPTS (7th Edition)

Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-5
 Triggers and Stored Procedures

• A trigger is a stored procedure that is

automatically invoked by the DBMS
when a specified activity occurs.
– BEFORE, AFTER, and INSTEAD OF
• A stored procedure is a module
similar to the subroutine or function
that performs database actions.
– Stored in the database itself

KROENKE and AUER - DATABASE CONCEPTS (7th Edition)

Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-6
 Internet Application Processing
Environment

Figure 7-4: The Web Database Processing Environment

KROENKE and AUER - DATABASE CONCEPTS (7th Edition)
Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-7
 APIs
• Every DBMS product has an application
programming interface (API).
– An API is a collection of objects, methods and
properties for executing DBMS functions from program
code.
– Each DBMS has its own API, and APIs vary from one
DBMS product to another.
• To simplify this situation, the computer industry
has developed standards for database access.
• API interface standards are used to make it
easier for programmers—they write to the
interface standard instead of the DBMS API.
– ODBC – Open Database Connectivity
– JDBC – Java Database Connectivity

KROENKE and AUER - DATABASE CONCEPTS (7th Edition)

Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-8
 OBDC
• The Open Database Connectivity (ODBC)
standard was developed in the early 1990s.
– Provides a DBMS-independent means for processing
relational database data

KROENKE and AUER - DATABASE CONCEPTS (7th Edition)

Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-9
 N-Tier Architecture
• Tiers refer to the number of computers
involved in the Web database application.
– The workstation with Web browser is the first
tier.
– Two-tier architecture means that the Web
server and the DBMS are on the same server.
– Three-tier architecture means that the Web
server and the DBMS are on separate servers.

KROENKE and AUER - DATABASE CONCEPTS (7th Edition)

Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-10
 AMP, LAMP and WAMP
• AMP refers to a combination of the
Apache Web server, the MySQL DBMS,
and the PHP or Pearl programming
language.
• LAMP is AMP running on the Linux
operating system.
• WAMP is AMP running on the Windows
operating system.

KROENKE and AUER - DATABASE CONCEPTS (7th Edition)

Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-11
 Typical Web Server Setups
Microsoft Linux or Unix
Windows
Web Server Internet Information Apache
Server (IIS)

Scripting ASPX Pages with PHP

Languages VBScript or JScript
(Interpreted) (PHP also works)
Object Oriented ASP.NET pages JSP pages with
Languages with Java
(Complied) VisualBasic.Net, (Also see the Mono
C#.Net or others project)

KROENKE and AUER - DATABASE CONCEPTS (7th Edition)

Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-12
 Typical Web DBMS Servers
Microsoft Linux or Unix
Windows

Web Server Internet Information Apache

Server (IIS)

DBMS Microsoft Oracle Database

SQL Server Oracle MySQL

API Interface ODBC JDBC

Standard

KROENKE and AUER - DATABASE CONCEPTS (7th Edition)

Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-13
 ODBC:
Three-tier Web Server Architecture

Figure 7-6: ODBC Three-Tier Web Server Architecture

KROENKE and AUER - DATABASE CONCEPTS (7th Edition)
Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-14
 ODBC Architecture
• The application program, the ODBC
driver manager, and the ODBC
DBMS driver all reside on the Web
server.
• The DBMS driver sends requests to
data sources on the database server.
– A data source is the database, its
associated DBMS, operating system,
and network platform.
KROENKE and AUER - DATABASE CONCEPTS (7th Edition)
Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-15
 ODBC Architecture:
The Application Program
• The application program issues requests:
– To create a connection with a data source
– To issue SQL statements and receive results
– To process errors
– To start, commit and roll back transactions
• ODBC
– Provides a standard means for each of these
requests
– Defines a standard set of error codes and
messages

KROENKE and AUER - DATABASE CONCEPTS (7th Edition)

Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-16
 ODBC Architecture:
The ODBC Driver Manager
• The ODBC driver manager
– Serves as an intermediary between the
application and the DBMS drivers
– When the application requests a
connection, the driver:
• Determines the type of DBMS that
processes a given ODBC data source
• Loads the appropriate driver into memory
(if it is not already loaded)

KROENKE and AUER - DATABASE CONCEPTS (7th Edition)

Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-17
 ODBC Architecture:
The ODBC Driver
• An ODBC driver
– Processes ODBC requests
– Submits specific SQL statements to a given
type of data source
• There is a different driver for each data source type.
• It is the responsibility of the driver to ensure that
standard ODBC commands execute correctly.
– Converts data source error codes and
messages into the ODBC standard codes and
messages

KROENKE and AUER - DATABASE CONCEPTS (7th Edition)

Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-18
 ODBC Architecture:
ODBC Data Sources
• An ODBC data source is an ODBC data
structure that identifies a database and the
DBMS that processes it.
– There are three types of data sources.
• A file data source is a file that can be shared among
database users—the only requirement is that the users
have the same DBMS driver and privilege to access the
database.
• A system data source is one that is local to a single
computer—the operating system and any user on that
system (with proper permissions) can use a system data
source.
• A user data source is available only to the user who
created it.
– Each created data source is given a data source name
(DSN) that is used to reference the data source.
KROENKE and AUER - DATABASE CONCEPTS (7th Edition)
Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-19
 ODBC Architecture:
Creating an ODBC Data Source I

Figure 7-7: The Create New Data Source Dialog Box

KROENKE and AUER - DATABASE CONCEPTS (7th Edition)
Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-20
 ODBC Architecture:
Creating an ODBC Data Source II

(a) Naming the ODBC Data Source

Figure 7-8: The Create New Data Source to SQL Server Dialog Box
KROENKE and AUER - DATABASE CONCEPTS (7th Edition)
Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-21
 ODBC Architecture:
Creating an ODBC Data Source III

(b) Selecting the User Login ID Authentication Method

Figure 7-8:
The Create New Data Source to SQL Server Dialog Box (Cont’d)
KROENKE and AUER - DATABASE CONCEPTS (7th Edition)
Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-22
 ODBC Architecture:
Creating an ODBC Data Source IV

(c) Selecting the Default Database

Figure 7-8:
The Create New Data Source to SQL Server Dialog Box (Cont’d)
KROENKE and AUER - DATABASE CONCEPTS (7th Edition)
Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-23
 ODBC Architecture:
Creating an ODBC Data Source V

(d) Additional Setting Options

Figure 7-8:
The Create New Data Source to SQL Server Dialog Box (Cont’d)
KROENKE and AUER - DATABASE CONCEPTS (7th Edition)
Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-24
 ODBC Architecture:
Creating an ODBC Data Source VI

(e) Testing the Data Source

Figure 7-8:
The Create New Data Source to SQL Server Dialog Box (Cont’d)
KROENKE and AUER - DATABASE CONCEPTS (7th Edition)
Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-25
 ODBC Architecture:
Creating an ODBC Data Source VII

(f) The Successfully Tested Data Source

Figure 7-8:
The Create New Data Source to SQL Server Dialog Box (Cont’d)
KROENKE and AUER - DATABASE CONCEPTS (7th Edition)
Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-26
 ODBC Architecture:
The Completed ODBC Data Source

Figure 7-9: The Completed HSD System Data Source

KROENKE and AUER - DATABASE CONCEPTS (7th Edition)
Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-27
 IIS:
The wwwroot Folder

Figure 7-10: The IIS wwwroot Folder

KROENKE and AUER - DATABASE CONCEPTS (7th Edition)
Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-28
 IIS:
The IIS Management Program

Figure 7-11: Managing IIS

KROENKE and AUER - DATABASE CONCEPTS (7th Edition)
Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-29
 IIS:
The IIS Localstart Web Page

Figure 7-12: The Default IIS Web Page

KROENKE and AUER - DATABASE CONCEPTS (7th Edition)
Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-30
 IIS:
The index.html Default Document

Figure 7-13: The index.html File in Windows 7 IIS Manager

KROENKE and AUER - DATABASE CONCEPTS (7th Edition)
Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-31
 Web Pages:
HTML Code for index.html

KROENKE and AUER - DATABASE CONCEPTS (7th Edition)

Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-32
 HTML Code for index.html
in Notepad

Figure 7-15: The HTML Code for the index.html File in Notepad
KROENKE and AUER - DATABASE CONCEPTS (7th Edition)
Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-33
 Web Pages:
The default.htm Web Page

Figure 7-16: The index.html Web Page in HSD

KROENKE and AUER - DATABASE CONCEPTS (7th Edition)
Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-34
 HTML Code for index.html
in the NetBeans IDE

Figure 7-17:
The HTML Code for the index.html File in the NetBeans IDE
KROENKE and AUER - DATABASE CONCEPTS (7th Edition)
Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-35
 The ReadSeminar.php Code:
Page Setup and Style Section

From Figure 7-18: The HTML and PHP Code for ReadSeminar.php
KROENKE and AUER - DATABASE CONCEPTS (7th Edition)
Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-36
 The ReadSeminar.php Code:
Creating A Connection

From Figure 7-18: The HTML and PHP Code for ReadSeminar.php
KROENKE and AUER - DATABASE CONCEPTS (7th Edition)
Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-37
 The ReadSeminar.php Code:
Creating A Recordset

From Figure 7-18: The HTML and PHP Code for ReadSeminar.php
KROENKE and AUER - DATABASE CONCEPTS (7th Edition)
Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-38
 The ReadSeminar.php Code:
Displaying the Results

From Figure 7-18: The HTML and PHP Code for ReadSeminar.php
KROENKE and AUER - DATABASE CONCEPTS (7th Edition)
Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-39
 The ReadSeminar.php Code:
Disconnecting from the Database

From Figure 7-18: The HTML and PHP Code for ReadSeminar.php
KROENKE and AUER - DATABASE CONCEPTS (7th Edition)
Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-40
 The ReadSeminar.php PHP Code
In the NetBeans IDE

Figure 7-19:
The HTML and PHP Code for ReadSeminar.php in the NetBeans IDE
KROENKE and AUER - DATABASE CONCEPTS (7th Edition)
Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-41
 The ReadSeminar.php Web Page

Figure 7-20: The Results of ReadSeminar.php

KROENKE and AUER - DATABASE CONCEPTS (7th Edition)
Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-42
 The Modified Home Page

Figure 7-22: The Modified HSD Web Home Page

KROENKE and AUER - DATABASE CONCEPTS (7th Edition)
Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-43
 The NewSeminarForm Web Page

Figure 7-23: The NewSeminarForm Web Page

KROENKE and AUER - DATABASE CONCEPTS (7th Edition)
Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-44
 Entering Data in the
NewSeminarForm Web Page

Figure 7-24: Entering Data Values in the NewSeminarForm Web Page

KROENKE and AUER - DATABASE CONCEPTS (7th Edition)
Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-45
 New Data in the SEMINAR Table

Figure 7-25: The New Seminar Data in the SEMINAR Table

KROENKE and AUER - DATABASE CONCEPTS (7th Edition)
Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-46
 Creating Drop-down Boxes

From Figure 7-26: The HTML Code for NewSeminarForm.html

KROENKE and AUER - DATABASE CONCEPTS (7th Edition)
Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-47
 PHP POST METHOD

On NewSeminarForm.html page

On InsertNewSeminar.php page

KROENKE and AUER - DATABASE CONCEPTS (7th Edition)

Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-48
 SQL Injection Attacks
• SQL Injection Attacks are attempts to issue SQL
commands through a Web interface such as a
Web form.
• Consider a Web form that asks for a user’s new
phone number:

• If the value of NewPhone is not checked, then we

may get input values such as:

KROENKE and AUER - DATABASE CONCEPTS (7th Edition)

Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-49
 XML Introduction

• Database processing and document

processing need each other.
– Database processing needs document
processing for expressing database views.
– Document processing needs database
processing for storing and manipulating data.
• As Internet usage increases,
organizations want to make their Web
pages more functional by displaying and
updating data from organizational
databases.
KROENKE and AUER - DATABASE CONCEPTS (7th Edition)
Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-50
 XML
• XML, or Extensible Markup Language, was
developed in the early 1990s.
– XML is a subset of SGML or Standard Generalized
Markup Language.
• Today XML is a hybrid of document processing
and database processing.
– It provides a standardized yet customizable way to
describe the content of documents.
– XML documents can automatically be generated from
database data and vice versa.
• SOAP is an XML-based standard protocol for
sending messages of any type, using any
protocol over the Internet.

KROENKE and AUER - DATABASE CONCEPTS (7th Edition)

Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-51
 XML (Cont’d)
• XML is used for describing, representing and
materializing database views.
• XML is better than HTML because:
– It provides a clear separation between document
structure, content and materialization.
– It is standardized but allows for extension by
developers.
– XML tags accurately represent the semantics of their
data.

KROENKE and AUER - DATABASE CONCEPTS (7th Edition)

Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-52
 SQL for XML Processing

Figure 7-28: An SQL FOR XML Query

KROENKE and AUER - DATABASE CONCEPTS (7th Edition)
Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-53
 Results of
SQL for XML Processing

Figure 7-29: Results of the SQL FOR XML Query

KROENKE and AUER - DATABASE CONCEPTS (7th Edition)
Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-54
 XLM Web Services
• XML Web Services allow application
functionality on one Web server to be
shared and incorporated into Web
applications on other Web servers.

KROENKE and AUER - DATABASE CONCEPTS (7th Edition)

Copyright © 2015 Pearson Education, Inc. Publishing as Prentice Hall 7-55
 DAVID M. KROENKE and DAVID J. AUER

DATABASE CONCEPTS, 7th Edition

All rights reserved. No part of this publication may be reproduced,

stored in a retrieval system, or transmitted, in any form or by any
means, electronic, mechanical, photocopying, recording, or otherwise,
without the prior written permission of the publisher. Printed in the
United States of America.

Copyright © 2015 Pearson Education, Inc.

Publishing as Prentice Hall
 DAVID M. KROENKE and DAVID J. AUER

DATABASE CONCEPTS, 7th Edition

End of Presentation on Chapter Seven

Database Processing Applications