Chapter 14 Databases and The Internet

1. Chapter 14Databases and The Internet Database Systems: Design, Implementation, and Management 4th Edition Peter Rob & Carlos Coronel

2. Internet Technologies and Databases • To support the global business reach, IS departments must ensure the integration of databases and their components and universal access to them from anywhere in the world. • Internet technologies provide unparalleled advantages because the Internet is a global network that provides universal access through an interface known as a Web browser, which is easy to use and runs on multiple platforms. • For IS departments, the new frontier is the use of Internet technologies to facilitate database access and to provide services to customers, partners, employees, and the general public.

3. Characteristics And Benefits Of Internet Technologies Table 14.1

4. A Sample Of Applications That Use Internet Technology Table 14.2

5. Table 14.3

6. Basic Components Of The World Wide Web Figure 14.1

7. Intranets and Extranets • Intranets • An Intranet is a locally owned and operated Internet whose access is carefully controlled. Its objective is to enhance company operations through improved data access management. • Intranets provide a platform for the development of new systems in a timely and cost-effective manner. • Intranets are relatively easy to set up and to implement at the technical level. Once implemented, intranet services tend to grow exponentially. • Extranets • If an intranet extends beyond a single corporate entity, it is known as an extranet. • An extranet extends the intranets to the corporation’s value chain.

8. Intranet/Extranet Components Figure 14.2

9. Intranets and Extranets • Intranet/Extranet Advantages • Open standards • Platform independence and portability • Support for multiple data sources and types • Process distribution and scalability • Ease of use • Shorter development times and reduced costs • Development tools that are integrated through the use of open standards • The universal client provides a common interface to all services • Communications infrastructure

10. Web server Electronic mail Document search File Transfer Protocol (FTP) News or discussion groups Workflow and team collaboration Web-to-database services Transaction processing Directory, security, and authentication services Firewalls and proxy servers Load balancing and caching Web-to-host access Intranet Architecture Common Intranet Services

11. A Multitier Intranet Architecture Figure 14.3

12. Intranet Architecture • Server-Side Extensions: Web-to-Database Middleware • A server-side extension is a program that interacts directly with the Web server to handle specific types of requests. • A database server-side extension program is also known as Web-to-database middleware.

13. Figure 14.4 Web-To-Database Middleware (ColdFusion)

14. Intranet Architecture • Web Server Interfaces • Two Web well-defined server interfaces: • Common Gateway Interface (CGI) • The CGI uses script files that perform specific functions based on the client’s parameters that are passed to the Web servers. • The script file is a small program containing commands written in some programming language (e.g., PERL, C++, or Visual Basic) • Application programming interfaces (APIs) • APIs are a newer Web server interface standard that is much more efficient and faster than CGI scripts. • APIs are implemented as shared code or as dynamic-link libraries (DLLs).

15. The API And CGI Web Server Interfaces Figure 14.5

16. Intranet Architecture • Open Database Connectivity (ODBC) • ODBC is Microsoft’s implementation of a superset of the SQL Access Group Call-Level-Interface (CLI) standard for database access. Figure 14.6

18. Intranet Architecture • The Web Browser • The Web browser is located in the client computer and it is the end user interface to the Web. • The Web browser’s job is to interpret the HTML code that it receives from the Web server and to present the different page components in a standard way. • The browser’s interpretation and presentation capabilities are not sufficient to develop Web-based applications, requiring plug-ins and other client-side extensions.

19. Intranet Architecture • Client-Side Extensions • Plug-ins • A plug-in is an external application that is automatically invoked by the browser when needed. • The plug-in is OS specific. • The plug-in is associated with a data object to allow the Web server to properly handle data that are not originally supported. • Java • Java is an object-oriented programming language developed by Sun Microsystems that run on top of the Web browser software. • Java applications are compiled and stored in the Web server. • Calls to Java routines are embedded inside the HTML page.

20. Intranet Architecture • JavaScript • JavaScript, developed by Netscape, is a scripting language that allows Web authors to design interactive sites. • JavaScript code is embedded in the Web pages. • The embedded JavaScript is downloaded with the Web page and is activated when a specific event takes place. • Active X • Active X is Microsoft’s alternative to Java. It is a specification for writing programs that will run inside the Microsoft client browser. • Active X extends the browser by adding “controls” to Web pages. These controls can be downloaded from the Web server and let the user manipulate data inside the browser.

21. Intranet Architecture • VBScript • VBScript is another Microsoft product that is used to extend the browser's functionality. • VBScript is derived from Visual Basic. • VBScript code is embedded inside an HTML page and this code is activated by triggering events such as clicking on a link.

22. Using A Web-to-DB Production Tool: ColdFusion • ColdFusion application middleware can be used to: • Connect to and query a database from a Web page. • Present database data in a Web page, using various formats. • Create dynamic Web search pages. • Create Web pages to insert, update, and delete database data. • Define required and optional relationships. • Define required and optional form fields. • Enforce referential integrity in form fields. • Use simple and nested queries and form select fields to represent business rules.

23. How ColdFusion Works Figure 14.8

24. The RobCor Database’s Relational Schema Figure 14.9

25. Using A Web-to-DB Production Tool: ColdFusion • Creating a Simple Query with CFQuery and CFOutput • Tasks: • Query the database, using standard SQL to retrieve a data set that contains all records found in the VENDOR table. • Format all of the records generated in Step 1 in HTML to let them be included in the page that is returned to the client browser. • See Script 14.1 for the script and Figure 14.10 for the output.

26. Script 14.1 A Simple Query Using CFQUERY And CFOUTPUT

27. The CH14-1.CFM Script Output Figure 14.10

28. CFQUERY With Tabular CFOUTPUT Script 14.2

29. The CH14-2.CFM Script Output Figure 14.11

30. Using A Web-to-DB Production Tool: ColdFusion • Creating a Simple Query with CFQuery and CFTable • Task: • Perform the same query with the result presented in tabular format. • See Script 14-3 and Figure 14.12

31. Script 14.3 CFQUERY With CFTABLE

32. The CH14-3.CFM Script Output Figure 14.12

33. Using A Web-to-DB Production Tool: ColdFusion • Creating a Dynamic Search Page • Two steps to create a dynamic query 1. Create a script that will generate a form 2. Create a script that will execute the query and display the results based on the parameters that are passed to it by the script created in Step 1. • See Script 14.4A and Figure 14.13

34. Script 14.4A Dynamic Search Query: Criteria Entry Form

35. The CH14-4A.CFM Script Output Figure 14.13

36. Script 14.4B The Vendor Search Results

37. The CH14-4B.CFM Script Output1OP4.062 Figure 14.14

38. The Vendor List For the Condition VEN_STATE = “GA” Figure 14.15

39. Using A Web-to-DB Production Tool: ColdFusion • The Web as a Stateless System • The Web is said to be a stateless system because the Web does not reserve memory to maintain an open communications “state” between the client and the server. • The browser does not have computational abilities beyond formatting output text and accepting form field inputs. • To perform processing (e.g., data entry) in the client, the Web defers to other Web-programming languages such as Java, JavaScript, VBScript, etc.

40. Script 14.5A The Insert Query Data Entry Screen

41. Figure 14.16 The CH14-5A.CFM Script Output Figure 14.17 The Insert Query Form: Server-Side Validation Error Message

42. The Insert Query Confirmation Screen Script 14.5B

43. The CH14-5B.CFM Script Output Figure 14.18

44. The Insert Query: ODBC Integrity Violation Error Figure 14.19

45. Using A Web-to-DB Production Tool: ColdFusion • Data Updates • Three pages required to update data • The first page (Script 14-6a) will allow the end user to select the record to be updated. When the user clicks on the Edit button, the second page, produced by Script 14-6b, is called and the first page’s search field value is passed to this second page. • The second page (Figure 14-20) will read the selected record, then display a data entry form to enable the end user to modify the data. • The third page (Script 14-6c) will update the data in the database and present a confirmation message.

46. Script 14.6A The Update Query: Record Selection Screen

47. The CH14-6A.CFM Script Output Figure 14.20

48. Script 14.6B The Update Query Edit Record Screen

49. The CH14-6B.CFM Script Output Figure 14.21

50. Update Query: Result Confirmation Screen Script 14.6C