CIS 2210 - Database Management and Design

Chapter 15: Database Connectivity and Web Technologies

Objectives:

This lesson discusses material from chapter 15. Objectives important to this lesson:

  1. Connectivity
  2. Technologies: ODBC, OLE, ADO.NET. JDBC
  3. Web to database middleware
  4. Services on web application servers
  5. XML
  6. Cloud computing
Concepts:

Chapter 15

Connectivity

The next to last chapter in the text discusses methods of connecting a DBMS to stored data, wherever that data may be. A DBMS must have access to data, or it won't do much. The methods used to access the data can vary according to the available route to the data. Is it on the local machine? Is it on our network? Is it in the cloud somewhere? Each answer can dictate a different connection method. When the database is on the computer you are using, it is much simpler than when it is stored on a network device.

On page 681, the text presents a three layered view of data access. That view is presented upside down. Think about a request to access the database from an application, and it would flow like this:

  • top layer - an interface between an application trying to use the database and the middle layer; the request that comes out of this layer will be formatted in one of several protocols, which are discussed below
  • middle layer - where transformation and translation of the data request take place, changing from an understood language supported in the top layer to the language of the actual database used to create the data layer; the middleware may pass the request to a system, or may read the data directly
  • data layer - where the actual data is stored, in the language of the database and of the storage medium; there may be server software that functions at this level

Technologies: ODBC, OLE, ADO.NET. JDBC

When the request comes from an application, it flows from the application to the middleware, to the database. Responses flow in the opposite direction. On page 682, the text lists the five interfaces that it will discuss. The list is a bit heavy in Microsoft products.

  • Native SQL connectivity - whatever method your DBMS provides for access through its proprietary interface; this method may provide the best access to features the vendor provides in its own interface, but the method and interface will only be useful for data stored with that DBMS
  • Microsoft’s Open Database Connectivity (ODBC), Data Access Objects (DAO), and Remote Data Objects (RDO)
    • ODBC - allows Windows programs to use SQL with databases through an Application Programming Interface (API); the text states that this is the most widely supported interface
      • DAO - also uses an API; when used with MS Access, can provide more features of that application
      • RDO - interface better suited to server-based databases such as MS SQL, Oracle, and DB2
      • DAO and RDO use services from ODBC
    • Applications access ODBC through an API, a driver manager manages connections to the database, and an ODBC driver does the communications with the database.
    • To establish an ODBC connection, you must create a Data Source Name for it. That process requires an ODBC driver, a unique name for the connection, and driver parameters including a server name, the name and location of the database, a user name, and credentials to access the database.
    • The graphic on page 684 shows the flow of connections from three Microsoft applications to three kinds of databases
  • Microsoft’s Object Linking and Embedding for Databases (OLE-DB) - used for connection to relational and non-relational databases; based on Microsoft Component Object Model (COM); objects used in this method may be consumers (that request and use data), or providers (that connect to a data source and provide data)

    To make it more confusing, providers can be plain data providers, or service providers that are inserted between the consumer and the (data) provider to perform more management services, or a selection of services.

    Scripting language support is not included, so another layer is added called ActiveX Data Objects (ADO) which provides a path for scripts to access OLE-DB, then to access the database.

  • Microsoft’s ActiveX Data Objects (ADO.NET) - used to access data through .NET environments, through an extension of the ADO/OLE-DB method; allows manipulation of a dataset, which is a downloaded copy of a database, which is manipulated then synchronized with the master copy of the database; this allows processing of data pulled across the Internet, which is then placed into the original source
  • Oracle’s Java Database Connectivity (JDBC) - concerns the use of Java, the programming language, not Javascript, the scripting language; Java programs are supposed to work on any platform equipped to run it, regardless of the local operating system; running a Java program works through a locally installed API: the application calls the API, which calls the JDBC driver, which calls appropriate middleware, which accesses the database

Web to Database Middleware

The text discusses several methods used to access data across Internet connections:

  • Server-Side Extensions - Services of all kinds across the Internet can be handled through web servers. The World Wide Web is the most successful platform on the Internet, causing most users to forget that other services exist. A request for a web page is a request for a data object, or a collection of them, managed by a web server. The text mentions that some pages are dynamic, created on the fly based on a user request. To construct such a page, the server must pull data from a database other than its own. To do this, server-side extensions are used to make requests to the appropriate database, retrieving counts of items on hand, shipping options, vendor choices, and other data important to a customer. These extensions are programs that serve as middleware between the web server and the data access methods discussed above. See the illustration on page 694 for an overview of the process.
  • Web Server Interfaces - There are two commonly used methods that allow the web server to communicate through the extensions discussed above.
    • Common Gateway Interface (CGI) - script files that communicate with the middleware; they can be written in any of several languages, provided the server supports that language; scripts run through interpreters, which can slow down requests when traffic is heavy; each request must make a new connection to the database
    • Application Programming Interface (API) - APIs use dynamic link libraries (DLLs), which are compiled code, letting them run faster than scripts; DLLs run in memory, so they run faster, once loaded; an API can share its connection with the database with multiple requests, opening the bottleneck in a way that the CGI scripts cannot
  • Client-Side Extensions - These are extensions to web browsers; they run on the client device, not the server device; the text mentions five types:
    •  plug-in extensions are additional software added to the browser that run when they are needed
    • JavaScript is an embedded script file (interpreted) that is part of a web page
    • Java applets are programs that can be run by a client if the Java runtime environment has been installed;
    • ActiveX controls are like Java applets, but they use code invented by Microsoft, and only run in Microsoft browsers
    • VBScript is a Microsoft script language, and its scripts can be run in web pages or separately
  • Web Application Servers - These are specialized server programs that can perform the middleware duties for the primary web server. The text presents a dozen tasks and features a web application server can perform, showing how useful they can be.
  • Web Database Development - The last item in this section is about making web pages that are meant to interface with a database. The language used to do this can be a blend of SQL and HTML, as shown in the example of ColdFusion code on page 700. The text also mentions that PHP can be used for this kind of web page coding.

Extensible Markup Language

We have discussed the use of XML in other chapters in regard to its purpose in documenting the features and characteristics of a database. Chapter 15 describes its use in the creation of web forms and in making references to fields in database files. The text shows us some examples on page 703, stressing the proper nesting of tags, such as nesting tags naming a field inside tags that name the table that field is in.

The text describes document types that are agreed upon by business partners, written in XML, and used to facilitate orders from one company to another. The text refers to this kind of document needing a document type definition (DTD) that must be shared between these business partners. Using this kind of agreed upon format prevents misunderstandings, and makes it possible to process orders and invoices much faster.

Regular transactions of this type can benefit from definitions of such documents in an XML schema document (XSD), which is a meta-language description of a DTD document used in a partner system. This is very much like a schema for a database, which is used to describe all of the objects used in it. Look at the example on page 707. It should remind you of a set of declarations of fields in a database, even though it is written in XML. The chapter goes on at length about various XML related acronyms. If you are creating a system based on this, you will need to keep a reference handy, because the terminology is not very memorable.

Cloud Computing Services

The text provides a definition from NIST of cloud computing on pages 709 and 710:

  • a computing model
  • for enabling ubiquitous, convenient, on-demand network access
  • to a shared pool of configurable computer resources (e.g. networks, servers, storage applications, and services)
  • that can be rapidly provisioned and released
  • with minimal management effort or server provider interaction

The bottom line is that a vendor is selling/leasing services that are available as needed from devices that are owned or operated by that vendor. It should be obvious that such a model depends heavily on reliable, fast, error free data access, typically over an Internet connection. If your business does not have access to that kind of Internet connection, cloud computing is not for you. If you have that kind of connection available, the graphic on page 710 shows marketing graphics from three vendors that give you an overview of their offered services.

The text mentions several features of this kind of service that make cloud services attractive to a business:

  • no hardware cost for the client, except for client side machines
  • no problems when you scale up such a service, and it may scale down automatically; shared infrastructure means the client does not have to build it
  • web interfaces may mean little software cost for the clients

Clouds come in several types:

Cloud Type
What's Different
Public Services on this cloud are for sale/lease to the general public. Amazon and Google provide this type of service in their clouds. This cloud is managed only by the provider.
Private
Services on this cloud are for a specific organization. It was built by them or for them, but it is only used by them. It may be most useful to international organizations. This cloud may be managed by the organization or by a third party.
Community This cloud is for a collection of organizations that share a common interest. Governments and schools may have this kind of cloud. This cloud may be managed by the organizations involved or by a third party.

Cloud services come in several types as well:

Cloud Service Type
What does it mean?
Software as a Service (SaaS)
Applications are run in the cloud from client devices. Devices can be simple web computers, smart devices, or fully functional computers. The application's may be the same for all clients of that service. Examples: Office 365 and Google Docs.
Platform as a Service (PaaS)
Clients can develop their own applications with tools from the vendor, and can use the vendor's network to deploy those applications to the client's workstations and devices. Examples: Microsoft Azure with .NET, Google Application Engine with Python or Java.
Infrastructure as a Service (IaaS)
In this one, the client can choose to add or remove storage, servers, processors, and personal computers that work across the WAN link. An example would be the servers that are available from Amazon Web Services.

On page 715, the text presents a table of advantages and disadvantages of cloud computing. It is a young technology, so not every detail has been worked out, and not every service works all of the time. Pay attention to the disadvantages such as security issues, hidden costs, loss of control and integration problems.

The last topic in the chapter is SQL Data Services. This is an offshoot of cloud computing that provides database development and access in a cloud based package. As in the examples above, this saves the client from having to obtain the DBMS, but the client still has to develop the databases and application that my use them.