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
  corporations 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
Intranet Architecture
Common Intranet Services

• 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

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 clients 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 Microsofts implementation of a superset
  of the SQL Access Group Call-Level-Interface
  (CLI) standard for database access.

Figure 14.6
17
(No Transcript)
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 browsers 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 browsers 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 Microsofts 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 Databases 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
  pages 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
51
The CH14-6C.CFM Script Output
Figure 14.22
52
Using A Web-to-DB Production Tool ColdFusion

• Deleting Data
• 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 forms 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.

53
Script 14.7A Delete Query Record Selection
Screen
54
The CH14-7A.CFM Script Output
Figure 14.23
55
Script 14.7B Delete Query Show Record Screen
56
The CH14-7B.CFM Script Output
Figure 14.24
57
Delete Query Result Confirmation Screen
Script 14.7C
58
The CH14-7C.CFM Script Output
Figure 14.25
59
The Delete Record Validation
Figure 14.26
60
Internet DB SystemsSpecial Considerations

• What Data Types Are Supported?
• 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?

61
Internet DB SystemsSpecial Considerations

• Data Security
• 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.

62
Internet DB SystemsSpecial Considerations

• Transaction Management
• 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.

63
Internet DB SystemsSpecial Considerations

• Denormalization of Database Tables
• 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.