Introduction
This module will analyze query processing with a focus on SQL transaction flows through a 3-tiered Web architecture. Note I will be adding a case study in this items sub-menu illustration an application of this architecture.
The emergence of the Internet and Web technologies has driven a remarkable acceleration of technology innovation and adoption, globalization and collaboration. Internet and Web technologies have lowered entrance barriers and leveled the business landscape by providing ubiquitous information access and distribution through a standardized, platform independent, accessible and converged communications architecture and infrastructure (Friedman, 2005; Laudon & Laudon, 2004; Pearlson & Saunders, 2006; Satinger, Jackson & Burd, 2002). These technologies have created a turbulent and competitive business environment as the B2B, B2C and C2C supply and value chains continue to be enhanced and shortened (Friedman, 2005; Kamoun, 2007; Nguyen, 2005; Pearcy, Parker & Giunipero, 2008; Porter, 1985; Robbins & Judge, 2007). In order to remain innovative and competitive, organizations must gather and provide relevant information to all of their internal and external constituencies (Pearlson & Saunders, 2006; Robbins & Judge, 2007; Satinger, Jackson & Burd, 2002). Central to achieving this goal is the reliance on today’s Web accessible information systems (IS) built on tiered architectures.
As a basis, tiered architectures provide secure and Web accessible access to the IS’s database core through emergent distributed Web Services (Arsanjani et al., 2008; Bielski, 1999; Erl, 2005; Kamoun, 2007). As Garcia-Molina, Ullman and Widom (2009) cite, there are many variations within today’s enterprise SQL environments. For clarity, this analysis will follow a holistic top down approach by introducing today’s three-tier Web accessible SQL environment followed by a more detailed analysis of SQL transactions at the application database layer interface. To constrain the scope and length of this module, discussion will be restricted to transaction flows through a generic SQL environment rather than detailing specific systems and SQL statements.
The Three Tiered Architecture and SQL Environment
Today’s distributed Web accessible SQL environment may be characterized as a three tier client/server (C/S) architecture with discrete Web, application and database layers (Garcia-Molina, Ullman & Widom, 2009; Steiert, 2007). Successful Information System (IS) development has been shown to be linked to adhering to a standardized architecture as far back as 1968 with the development of Dijkstra’s THE OPERATING system (Brown et al. 2003). A software architecture provides a blueprint that guides not only how the components are constructed but also when in the process they are designed, implemented, maintained and replaced (Brown et al. 2003). As a result, this three tiered architecture is a necessary organizational response to remain agile and maintain relevant systems in today’s transitory, volatile and increasingly mobile business climate (Steiert, 2007). Critical to today’s unsecure networked environment, this layered model also provides the necessary component framework for managing authentication and authorization at increasingly discrete granularities across the architecture’s layers.
The three tier architecture’s functionality can be succinctly described from a server standpoint. Within this framework beginning at the client side, Web servers present an interface to clients and connect clients to the application layer of the enterprise system. The middle tier application servers encapsulate the organization’s business logic and serve as the interface between the database servers and the Web servers. Lastly, the database servers run the database management system (DBMS) and service the application layer’s requests for database queries and updates (Garcia-Molina, Ullman & Widom, 2009).
The three architectural layers are discussed in more detail below however it must be noted that the various layers and components of this model may serve as both clients and servers (Brown et al., 2003; Erl, 2005; Kistijantoro, Morgan, Shrivastava & Little, 2008). As an example the application layer may represent a client to the database layer and server to the Web layer. Additionally, components of a single layer may serve as both a client and a server in different circumstances. An example of this model is today’s Web services and Service Oriented Architectures (SOA) where applications publish their services and resources and in turn avail themselves to other application’s published services and resources (Brown et al., 2003; Erl, 2005). The following analysis of the three layered architecture will be restricted to traditional C/S computing rather delving into emergent SOA functionality. Additionally, client side users and applications will be referred to as agents in accord with this emergent technology. This analysis will trace the transaction flow from the client side agent, to the Web server, through the application server and culminating with the database server however it must be noted that this model is based on continual full-duplex communication throughout the system.
Web Layer Interface and Functionality
In a basic implementation, a Web server provides client-side agents with an interface to the enterprise IS through a Web browser. This Web interface model also provides the necessary support for session management (Van Duyne, Landay & Hong, 2007). Today’s Web servers support and rely on both client-side and server-side processing to achieve their communication functionality (Flanagan, 2002; Garcia-Molina, Ullman & Widom, 2009; Welling & Thomson, 2009; Van Duyne, Landay & Hong, 2007). On the client side, agents participate in two way communication with the Web server by interacting with a browser’s dynamic forms implemented through various technologies (e.g. Javascript, Actionscript and Java) (Beekman & Beekman, 2009; Flanagan, 2002; Van Duyne, Landay & Hong, 2007). This generic communication mechanism allows the agent to submit data that is further processed by the application layer and subsequently presented to the database layer. This data may be ultimately stored in the database, used as comparative data for querying or modifying the database or may itself contain dynamic SQL command statements (Garcia-Molina, Ullman &Widom, 2009). On the server side, the actual Web pages delivered to the client are increasingly constructed dynamically from data base elements (Van Duyne, Landay & Hong, 2007; Welling & Thomson, 2009).
Application Layer Interface and Functionality
The application layer may be comprised of many components and layers and each of these may access multiple databases requiring further integration (Garcia-Molina, Ullman &Widom, 2009; Steiert, 2007). In general, the application layer manages the interaction with clients by interfacing with the Web server and manages information interaction with the database through the database server. Semantically the application layer encapsulates an organization’s business logic in accord with the organization’s established policies and procedures (Garcia-Molina, Ullman &Widom, 2009; Steiert, 2007). It is typical for application tier components to be implemented as objects since objects facilitate integration by providing a uniform application programming interface (API) (Brown et al., 2003; Erl, 2005). Furthermore, the intrinsic nature of object oriented components provides the necessary flexible, scalable and secure environment that can quickly adapt to today’s rapid environmental changes (Brown et al., 2003; Erl, 2005; Steiert, 2007).
Database Layer Interface and Functionality
Similar to the application layer, the database layer may be comprised of many components that include multiple and distributed databases and processes (Garcia-Molina, Ullman & Widom, 2009; Steiert, 2007). This complex layer also requires a framework for efficient management therefore it is further organized into clusters, catalogs and schemas (Garcia-Molina, Ullman & Widom, 2009). Hierarchically, clusters are composed of one or more catalogs and catalogs are composed of one or more schemas (Garcia-Molina, Ullman & Widom, 2009). Schemas are the basic unit of organization in accord with the user’s perspective as they are comprised of the basic DBMS components (e.g. tables, relations, views, etc.) (Garcia-Molina, Ullman & Widom, 2009). A cluster provides users with their basic database resources by controlling authorized access to the catalogs a user may access (Garcia-Molina, Ullman & Widom, 2009).
The SQL Application and Database Layer Interface
To achieve application and database layer integration the system must establish conventions to: (a) communicate SQL commands and results between the two layers, (b) accommodate the impedance mismatch that results from vagaries in procedural and relational data types and processing, and (c) manage connections and sessions between the database and many concurrent short-lived processes (Garcia-Molina, Ullman & Widom, 2009, p. 372).
Application Database Communication
Application layer access to the DBMS is typically accomplished through the Open Database Connectivity (ODBC) API (Garcia-Molina, Ullman & Widom, 2009; Steiert, 2007; Groff, Weinberg & Oppel, 2010). ODBC was developed by Microsoft to provide a uniform standardized access mechanism independent of specific vendor DBMS implementations (Groff, Weinberg & Oppel, 2010). ODBC has evolved to support the SQL call level interface (CLI) and has become an American National Standards Institute (ANSI) and International Standards Organization (ISO) standard (Groff, Weinberg & Oppel, 2010). Today, virtually all DBMS that support SQL now provide ANSI/ISO compliant SQL/CLI support (Groff, Weinberg & Oppel, 2010).
The application layer communicates SQL commands to the database layer by embedding CLI or SQL commands in its host programming language (Garcia-Molina, Ullman & Widom, 2009). CLI functionality allows the embedded CLI statements to access library functions to connect to and access DBMS supported SQL statements (Garcia-Molina, Ullman & Widom, 2009). Note that embedded SQL statements achieve equivalent functionality however they differ from CLI as the embedded SQL statements are sent to a preprocessor that transforms the generic embedded SQL into SQL statements supported by the DBMS (Garcia-Molina, Ullman & Widom, 2009). To illustrate this communication model from CLI perspective, the basic application database access consists of the following sequence of steps: (a) the process allocates data structures in its memory buffers for its communication with the database, (b) the process connects to a specific database server, (c) the process builds SQL statements in its memory buffers and submits the SQL statements, (d) upon completion of database processing, the process requests the database commit the transaction permanently saving the transaction, and (e) the process disconnects from the database and releases its data structures (Groff, Weinberg & Oppel, 2010, p. 550).
Application Database Impedance Mismatches
Application programs must accommodate the inherent impedance mismatch that exists between procedural and relational processing. Recall that DBMS query results are in the form of relations that are unordered sets of tuples. Storing a single tuple is straightforward as the attributes can be stored in shared application language variables. Accessing and storing multiple tuples in shared variables requires a mechanism to govern the information transfer. SQL provides cursors to manage relation tuple access and cursors functionality is similar to the operating system’s use of file descriptors (Groff, Weinberg & Oppel, 2010). Cursors incur considerable overhead and are further restricted to sequential access therefore additional processing is left to the application program (Groff, Weinberg & Oppel, 2010).
Application Database Connections
A database server must manage application database connectivity and concurrency. Creating database connections incurs a high computational overhead and this environment is complicated by the fact that connections are shared by many short-lived processes (Garcia-Molina, Ullman & Widom, 2009). ODBC provides several enhancements that improve the accessibility and the efficiency of connections. To mitigate the substantial overhead of creating connections, ODBC connections may be pooled allowing them to remain active and reallocated efficiently (Garcia-Molina, Ullman & Widom, 2009; Groff, Weinberg & Oppel, 2010). ODBC supports connection browsing that simplifies the connection process and concomitantly renders application processes more database independent (Groff, Weinberg & Oppel, 2010). Upon terminating the connection, the application process must leave the return the connection to the state in which it was found (Garcia-Molina, Ullman & Widom, 2009).
As introduced above, a database server typically maintains multiple session-oriented connections that are shared among application processes for efficiency (Garcia-Molina, Ullman & Widom, 2009; Groff, Weinberg & Oppel, 2010). In accord with the database’s architecture and authorization mechanisms, each session is associated with a catalog and a schema within catalog (Garcia-Molina, Ullman & Widom, 2009). Citing the presence of multiple connections, the database server must also ensure transaction atomicity and serializability. This may be managed at the cursor level by declaring the cursor to be sensitive or insensitive to changes (Garcia-Molina, Ullman & Widom, 2009).
Summary
As previously cited, today’s Internet driven business climate may be characterized as highly competitive, volatile, and transitory. A three-tiered architecture is the natural evolution of C/S computing to accommodate today’s Web accessible database driven Internet based marketplace (Bielski, 1999; Garcia-Molina, Ullman & Widom, 2009Steiert, 2007). As previously cited, a software architecture provides a blueprint that guides not only how the components are constructed but also when in the process they are designed, implemented, maintained and replaced (Brown et al. 2003). With this basis, a multi-tiered architecture provides the necessary framework for organizations remain agile and compete globally. Implicit in this multi-tiered architecture is the need to implement and remain in accord with open standards (e.g. W3C compliance, ODBC, etc.). In summary, successful IS implementation in accord with an organization’s mission and goals requires a thorough understanding of today’s SQL environment and the flow of SQL transactions though out the architecture’s layers.
References
Arsanjani, A., Ghosh, S., Allam, A., Abdollah, T., Ganapathy, S., & Holley, K. (2008). SOMA: A method for developing service-oriented solutions. IBM Systems Journal, 47(3), 377-396.
Beekman, G., & Beekman, B. (2009). Tomorrow’s technology and you (9th ed.). Saddle River, NJ: Prentice Hall.
Bielski, L. (1999). Ready for multi-tier, distributed computing? American Bankers Association. ABA Banking Journal, 91(1), 53-55.
Brown, K., Craig , G., Hester , G., Stinehour, R., Pitt, W. D., Weitzel, M., Amsden, J., Jakab, P. M., & Berg, D. (2003). Enterprise java programming with IBM webSphere. Boston MA: Pearson Education.
Erl, T. (2005) Service-oriented architecture: Concepts, technology, and design. Upper Saddle River, NJ: Pearson Education.
Flanagan, D. (2002). JavaScript: The definitive guide (4th ed.). Sebastopol, CA: O’Reilly.
Friedman, T. L. (2005). The world is flat: A brief history of the 21st century. NY: Farrar, Straus and Giroux.
Garcia-Molina, H., Ullman, J.D., & Widom, J. (2009). Database systems: The complete book, (2nd ed.). Upper Saddle River, NJ: Pearson Prentice Hall.
Groff, J. R., Weinberg, P. N., & Oppel, A. J. (2010). SQL: The complete reference (3rd ed.). New York: McGraw-Hill.
Kamoun, F. (2007). The convergence of business process management and service oriented architecture. Retrieved June 9, 2008 from the Association of Computing Machinery: http://www.acm.org/ubiquity/views/v8i24_bmpsoa.html
Kistijantoro, A. I., Morgan, G., Shrivastava, S. K., & Little, M. C. (2008). Enhancing an application server to support available components. IEEE Transactions on Software Engineering, 34(4), 531-545.
Laudon, K. C. & Laudon, J. P. (2004). Management information systems (8th ed.). Upper Saddle River, NY: Pearson Publishing.
Nguyen, T. N., (2005). Scalable e-business integration. Journal of American Academy of Business, Cambridge, 6(1), 135-142.
Pearcy, D. H., Parker, D. B., & Giunipero, L. C. (2008). Using electronic procurement to facilitate supply chain integration: An exploratory study of US-based firms. American Journal of Business, 23(1), 23-35.
Pearlson, K. E., & Saunders, C. S. (2006). Managing and using information systems (3rd ed.). Hoboken, NY: Wiley Publishing.
Porter, M., (1985). Competitive advantage: Creating and sustaining superior performance. New York: The Free Press
Robbins, S. P., & Judge, T. A. (2007). Organizational Behavior. Upper Saddle River, NJ: Prentice Hall.
Satinger, J. W., Jackson, R. B., & Burd, S. D., (2002). Systems analysis and design (2nd ed.) Boston, MA: Course Technology.
Steiert, H. (2007). Towards a component-based n-Tier C/S architecture. Kaiserslautern, Germany: Department of Computer Science, Database and Information Systems Group,University of Kaiserslautern. Retrieved January 1, 2009 from http://www.dr-gail.org/upload/p137-steiert.pdf
Van Duyne, D. K., Landay, J. A., & Hong, J. I. (2007). The design of sites: Patterns for creating winning web sites (2nd ed.). Upper Saddle River, NJ USA: Prentice Hall.
Welling, L., & Thomson, L. (2009). PHP and MySQL Web development (4th ed.). Upper Saddle River, NJ: Addison Wesley.