Transcript ch14
14 Chapter 14 Databases and The Internet Database Systems: Design, Implementation, and Management 4th Edition Peter Rob & Carlos Coronel Internet Technologies and Databases 14 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. Characteristics And Benefits Of Internet Technologies 14 Table 14.1 A Sample Of Applications That Use Internet Technology 14 Table 14.2 14 Table 14.3 Basic Components Of The World Wide Web 14 Figure 14.1 Intranets and Extranets Intranets 14 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. Intranet/Extranet Components 14 Figure 14.2 Intranets and Extranets Intranet/Extranet Advantages 14 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 Intranet Architecture Common Intranet Services 14 Web server Web-to-database services Electronic mail Transaction processing Document search Directory, security, and authentication services File Transfer Protocol (FTP) News or discussion groups Workflow and team collaboration Firewalls and proxy servers Load balancing and caching Web-to-host access A Multitier Intranet Architecture 14 Figure 14.3 Intranet Architecture Server-Side Extensions: Web-to-Database Middleware 14 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. 14 Figure 14.4 Web-To-Database Middleware (ColdFusion) Intranet Architecture Web Server Interfaces 14 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 dynamiclink libraries (DLLs). The API And CGI Web Server Interfaces 14 Figure 14.5 Intranet Architecture Open Database Connectivity (ODBC) 14 ODBC is Microsoft’s implementation of a superset of the SQL Access Group Call-Level-Interface (CLI) standard for database access. Figure 14.6 14 Intranet Architecture The Web Browser 14 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 Webbased applications, requiring plug-ins and other client-side extensions. Intranet Architecture Client-Side Extensions 14 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. Intranet Architecture 14 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. Intranet Architecture 14 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. Using A Web-to-DB Production Tool: ColdFusion ColdFusion application middleware can be used to: 14 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. How ColdFusion Works 14 Figure 14.8 The RobCor Database’s Relational Schema 14 Figure 14.9 Using A Web-to-DB Production Tool: ColdFusion Creating a Simple Query with CFQuery and CFOutput 14 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. 14 Script 14.1 A Simple Query Using CFQUERY And CFOUTPUT The CH14-1.CFM Script Output 14 Figure 14.10 CFQUERY With Tabular CFOUTPUT 14 Script 14.2 The CH14-2.CFM Script Output 14 Figure 14.11 Using A Web-to-DB Production Tool: ColdFusion Creating a Simple Query with CFQuery and CFTable 14 Task: Perform the same query with the result presented in tabular format. See Script 14-3 and Figure 14.12 14 Script 14.3 CFQUERY With CFTABLE The CH14-3.CFM Script Output 14 Figure 14.12 Using A Web-to-DB Production Tool: ColdFusion Creating a Dynamic Search Page 14 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 14 Script 14.4A Dynamic Search Query: Criteria Entry Form The CH14-4A.CFM Script Output 14 Figure 14.13 14 Script 14.4B The Vendor Search Results The CH14-4B.CFM Script Output1OP4.062 14 Figure 14.14 The Vendor List For the Condition VEN_STATE = “GA” 14 Figure 14.15 Using A Web-to-DB Production Tool: ColdFusion The Web as a Stateless System 14 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. 14 Script 14.5A The Insert Query Data Entry Screen 14 Figure 14.16 The CH14-5A.CFM Script Output Figure 14.17 The Insert Query Form: Server-Side Validation Error Message The Insert Query Confirmation Screen 14 Script 14.5B The CH14-5B.CFM Script Output 14 Figure 14.18 The Insert Query: ODBC Integrity Violation Error 14 Figure 14.19 Using A Web-to-DB Production Tool: ColdFusion Data Updates 14 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. 14 Script 14.6A The Update Query: Record Selection Screen The CH14-6A.CFM Script Output 14 Figure 14.20 14 Script 14.6B The Update Query Edit Record Screen The CH14-6B.CFM Script Output 14 Figure 14.21 Update Query: Result Confirmation Screen 14 Script 14.6C The CH14-6C.CFM Script Output 14 Figure 14.22 Using A Web-to-DB Production Tool: ColdFusion Deleting Data 14 Three pages required to delete data The first page (Script 14-7a) will allow the end user to select the record that is to be deleted. When the user clicks the form’s Delete button, Script 14-7b is invoked, and the DEPT_ID form field value is passed to it. The second page (Script 14-7b) will read the selected record and display its data on the screen. The third page (Script 14-7c) will delete the department row from the database table, using the DEPT_ID form field value passed from its calling program. 14 Script 14.7A Delete Query Record Selection Screen The CH14-7A.CFM Script Output 14 Figure 14.23 14 Script 14.7B Delete Query Show Record Screen The CH14-7B.CFM Script Output 14 Figure 14.24 Delete Query Result Confirmation Screen 14 Script 14.7C The CH14-7C.CFM Script Output 14 Figure 14.25 The Delete Record Validation 14 Figure 14.26 Internet DB Systems: Special Considerations What Data Types Are Supported? 14 How does one store and extract data objects such as documents, pictures, and movies through a Web browser? How much overhead will be created by the storage of binary objects in the database? How robust must the DBMS be to handle binary object transactions? What are the limitations for extended or OLE data types? How many extended or OLE data type fields can tables have? Does the client browser support the data type of the object you are trying to access? Are the necessary plug-ins available? Is there a way to automatically translate documents from their native format to HTML? Does the DBMS support Very Large Databases? What about transaction speed? How many users are going to access the database? How often? Internet DB Systems: Special Considerations Data Security 14 Security can be implemented in the Web server, the database and in the networking infrastructure. At the Web server level, most Web clients and servers can perform secure transactions by using encryption routines at the TCP/IP protocol level. At the SQL level, administrators can use the GRANT and REVOKE commands to assign access restrictions to tables and/or to specific SQL commands. Web-to-database middleware vendors usually have several security mechanisms available to interface with databases. Internet DB Systems: Special Considerations Transaction Management 14 The designers must ensure proper transaction management support at the database server level since the Web does not support the concept of database transaction: The Web cannot maintain an open line between the client and the database server. The mechanics of a recovery from incomplete or corrupted database transactions require that the client must maintain an open communications line with the database server. Internet DB Systems: Special Considerations Denormalization of Database Tables 14 The Web environment does not support multitable (parent-child) data entry. Although implementing the parent/child data entry is not impossible in a Web environment, its final outcome is less than optimum, counterintuitive, less user-friendly, and prone to errors. Web programming languages such as Java, JavaScript, or VBScript can be used to create the required Web interfaces.