Database Concepts
8th Edition
David M. Kroenke • David J. Auer • Scott L. Vandenberg• Robert C. Yoder
Instructor’s Manual
Prepared by David J. Auer
Chapter 7
Database Processing Applications
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.
Instructor’s Manual to accompany:
Database Concepts (8th Edition)
David M. Kroenke • David J. Auer • Scott L. Vandenberg• Robert C. Yoder
Chapter Seven – Database Processing Applications
CHAPTER OBJECTIVES
• To understand the nature and characteristics of the data environment that surrounds
Internet technology database applications
• To learn the purpose, features, and facilities of ODBC
• To understand HTML and PHP
• To be able to construct Web database applications pages using PHP
• To understand the importance of XML
• To learn the basic concepts involved in using the SQL SELECT … FOR XML statement
• To understand the importance of JSON
CHAPTER ERRATA
There are no known errors at this time. Any errors that are discovered in the future will
be reported and corrected in the online DBC e08 Errata document, which will be
available at http://www.pearsonhighered.com/kroenke.
THE ACCESS WORKBENCH
Solutions to the Access Workbench exercises may be found in Solutions to all Sections:
The Access Workbench, which is a separate document within the Instructor’s Manual.
NOTES ON MICROSOFT WINDOWS 10
This book uses the Microsoft Windows 10 operating system as the basis for screenshots
and step-by-step instructions. However, with Windows 10, Microsoft has introduced a
continuous update system that has already resulted in some fundamental differences in
how different versions of Windows 10 look and operate.
For example, in the original version of Microsoft Windows 10, clicking the Windows
Start button (or pressing the Windows key on the keyboard) displayed the menu shown
in Figure 1. In this menu, we need to click the All apps button in order to see the All
apps menu shown in Figure 2.
Chapter Seven – Database Processing Applications
© 2018 Pearson Education, Inc. Page 4 of 100
Figure 1 – Windows 10 Main Menu
Figure 2 – Windows 10 All Apps Menu
The All apps button
The File Explorer button
The File Explorer icon
The All apps menu
Chapter Seven – Database Processing Applications
© 2018 Pearson Education, Inc. Page 5 of 100
Figure 3 – Windows 10 Anniversary Update Main Menu with All Apps Menu Included
Microsoft then released the Windows 10 Anniversary Update (Feature update to
Windows 10, version 1607) (see the blog discussion at
https://blogs.windows.com/windowsexperience/2016/08/02/how-to-get-the-windows-10–
anniversary-update/#K1CZuiw4auiuE9A5.97 ). One of the changes introduced in the
Anniversary Update was a major change to the menu system. Now, as shown in
Figure 3, the All apps menu is immediately available when the Start button is used (or
when the keyboard Windows key is pressed).
The All apps menu
The File Explorer button
The File Explorer icon
Chapter Seven – Database Processing Applications
TEACHING SUGGESTIONS
• This chapter introduces the advanced topics of database processing over the Web,
XML, and JSON. There is more information on each of them in David M. Kroenke
and David J. Auer, Database Processing: Fundamentals, Design, and
Implementation (14th Edition), Upper Saddle River: Prentice Hall (2016).
• See Online Appendix I, “Getting Started with Web Sites, PHP, and the NetBeans
IDE” for information on setting up the Microsoft IIS Web server, PHP, and the
NetBeans IDE. Make sure that all components are installed correctly before
attempting the material in this chapter!
• See Online Appendix K, “Big Data” for more information on XML and JSON.
• The use of Web access to databases is critical to contemporary Web sites. Have
your students do the end of the chapter work on Web access to databases.
• Demonstrate in class how to create a System DSN. All workstations have an ODBC
Data Source Administrator available, so you should be able to do this with only a
notebook or workstation in your classroom. Note the possibility of two programs
existing—one for 32 bit programs and one for 64-bit programs as noted in the text.
Be sure you are using the correct version! If possible, assign each of your students
the task of creating their own System DSN.
• This chapter is basically divided into two major parts. The first is a mostly theoretical
discussion of database access tools such as ODBC and ADO.NET. The second is a
practical, hands-on introduction to Web database applications using PHP. This
second part introduces basic PHP constructs the student needs to display database
information on Web pages.
• The text does not include any introduction to HTML. Check to see what level of
expertise your students have with HTML. If the overall level is low, consider
introducing HTML before covering PHP (See http://www.w3.org/MarkUp/Guide/ for
an introduction, and http://www.w3.org/MarkUp/Guide/Advanced.html for more
advanced topics.) A good HTML project is to have your students create—for each
database project you assign—a project HTML page to link to each of the Web pages
asked for in the project. If you assign more than one database project, you can also
have them create a master HTML page to link to each of the project HTML pages.
• If you have time, talk more about database system security. The text uses the user
login and database account created in Chapter 6 to access the SQL Server 2016
database. Be sure that you have created the necessary accounts and granted the
correct permissions!
Chapter Seven – Database Processing Applications
ANSWERS TO REVIEW QUESTIONS
7.1 Describe how three-tier architecture is used in Web-based database applications (use
Figure 7-3).
As shown in Figure 7-3, three-tier architecture refers to the structure of a Web-based database
application where the three tiers are:
7.2 Summarize the issues involved in processing a form as described in this chapter.
7.3 Describe, in your own words, the nature of traditional database processing applications.
Chapter Seven – Database Processing Applications
7.4 What is a trigger, and how is it used?
7.5 Name three types of triggers.
7.6 What is a stored procedure, and how is it used?
7.7 Describe why the data environment is complicated.
7.8 Name the three major components of a Web database application.
7.9 As explained in this chapter, what are the two major functions of a Web server?
7.10 Explain the difference between two-tier and three-tier architecture.
7.11 What is IIS, and what functions does it serve?
7.12 What do the abbreviations ASP and JSP stand for?
7.13 What is ASP.NET?
7.14 What is Apache, and what functions does it serve?
Chapter Seven – Database Processing Applications
7.15 What are AMP, LAMP, and WAMP?
7.16 Explain the relationship among ODBC, OLE DB, and ADO.
7.17 Name the components of the ODBC standard.
7.18 What role does the driver manager serve?
7.19 What role does the DBMS driver serve?
7.20 What is a single-tier driver?
7.21 What is a multiple-tier driver?
Chapter Seven – Database Processing Applications
7.22 Explain the differences between the three types of ODBC data sources.
7.23 Which ODBC data source type is recommended for Web servers?
7.24 What is an API, and what function does it server?
7.25 What is Hypertext Markup Language (HTML), and what function does it serve?
7.26 What are HTML document tags, and how are they used?
7.27 What is the World Wide Web Consortium (W3C)?
7.28 Why is index.html a significant file name?
Chapter Seven – Database Processing Applications
7.29 What is PHP, and what function does it serve?
7.30 How is PHP code designated in a Web page?
7.31 How are comments designated in PHP code?
7.33 What is an Integrated Development Environment (IDE), and how is it used?
7.34 What IDE is generally used in a Windows environment?
7.35 What is the NetBeans IDE?
Chapter Seven – Database Processing Applications
7.36 Show a snippet of PHP code for creating a connection to a database. Explain the
meaning of the code.
The PHP code uses the ODBC files source HSD to connect to the database using the user login
7.37 Show a snippet of PHP code for creating a recordset. Explain the meaning of the code.
7.38 Show a snippet of PHP code for displaying the contents of a recordset. Explain the
meaning of the code.
Chapter Seven – Database Processing Applications
© 2018 Pearson Education, Inc. Page 13 of 100
<th>Date</th>
<th>Time</th>
<th>Location</th>
<th>Title</th>
</tr>”;
// Table data
while($RecordSetRow = odbc_fetch_array($RecordSet))
{
echo “<tr>”;
echo “<td>” . $RecordSetRow[‘Date’] . “</td>”;
echo “<td>” . $RecordSetRow[‘Time’] . “</td>”;
echo “<td>” . $RecordSetRow[‘Location’] . “</td>”;
echo “<td>” . $RecordSetRow[‘Title’] . “</td>”;
echo “</tr>”;
}
echo “</table>”;
?>
This is actually a combination of HTML and PHP code that was used to format the Web page
presentation of the data in $RecordSet. It then uses PHP to obtain specific data values from
$RecordSet.
7.39 Show a snippet of PHP code for disconnecting from the database. Explain the meaning
of the code.
7.40 With respect to HTTP, what does stateless mean?
7.41 Under what circumstances does statelessness pose a problem for database processing?
7.42 In general terms, how are sessions managed by database applications when using
HTTP?
Chapter Seven – Database Processing Applications
7.43 What are the problems in interpreting tags such as <h1>…</h1> in HTML?
7.44 What does XML stand for?
7.45 How does XML differ from HTML?
7.46 Explain why XML is extensible.
7.47 In general terms, explain why XML is important for database processing.
7.48 What is the purpose of the FOR XML expression in an SQL statement?
7.49 What is the purpose of XML Web Services?
7.50 What does JSON stand for? What is the purpose of JSON?
Chapter Seven – Database Processing Applications
ANSWERS TO EXERCISES
7.51 In this exercise, you’ll create a Web page in the DBC folder and link it to the HSD Web
page in the HSD folder.
A. Figure 7-30 shows the HTML code for a Web page for the DBC folder. Note that
the page is called index.html, the same name as the Web page in the HSD
folder. This is not a problem because the files are in different folders. Create the
index.html Web page in the DBC folder.
Chapter Seven – Database Processing Applications
<p style=”text-align: center; font-weight: bold”>
David M. Kroenke, David J. Auer, Scott L. Vandenberg, and Robert C. Yoder
Chapter Seven – Database Processing Applications
B. Figure 7-31 shows some additional HTML to be added near the end of the code
for the HSD Web page in the file index.html in the HSD folder. Update the HSD
index.html file with the code.
<!DOCTYPE html>
<html>
<head>
Chapter Seven – Database Processing Applications
© 2018 Pearson Education, Inc. Page 18 of 100
<!– New Code Added Here –>
<p>Example 2:&nbsp;&nbsp;&nbsp;
<a href=”NewSeminarForm.html”>
Add a New Seminar to the SEMINAR Table
</a>
</p>
<hr />
<!– NEW CODE STARTS HERE –>
<p style=”text-align: center”>
<a href=”http://localhost/DBC/index.html”>
Return to the Database Concepts Home Page
</a>
</p>
<hr />
<!– NEW CODE ENDS HERE –>
</body>
</html>
C. Try out the pages. Type http://localhost/DBC into your Web browser to display
the DBC home page. From there, you should be able to move back and forth
between the two pages by using the hyperlinks on each page. Note: You may
need to click the Refresh button on your Web browser when using the HSD
home page to get the hyperlink back to the DBC home page to work properly.
Chapter Seven – Database Processing Applications
!DOCTYPE html>
<html>
<head>
Chapter Seven – Database Processing Applications
<hr />
<!– NEW CODE STARTS HERE –>
7.52 Create a Web page for Heather Sweeney Designs to display all the data in the
CUSTOMER table. Add a hyperlink to the HSD home page to access the page.