An SQL environment is an integrated data processing environment in which heterogeneous products, all supporting some aspect of the FIPS SQL standard (FIPS PUB 127), are able to communicate with one another and provide shared access to data and data operations and methods under appropriate security, integrity, and access control mechanisms. Some components in an SQL environment will be full-function SQL implementations that conform to an entire level of FIPS SQL and support all of its required clauses for schema definition, data manipulation, transaction management, integrity constraints, access control, and schema information. Other components in an SQL environment may be specialized data repositories, legacy databases, or graphical user interfaces and report writers, all of which support selected portions of the SQL standard and thereby provide a degree of integration between themselves and other products in the same SQL environment. The intent is to provide a high level of control over a diverse collection of legacy or specialized data resources. An SQL environment allows an organization to obtain many of the advantages of SQL without requiring a large, complex, and error-prone conversion effort; instead, the organization can evolve, in a controlled manner, to a new integrated environment.

This FIPS PUB is the beginning of a continuing effort to define appropriate conformance profiles that can be used by both vendors and users to specify exact requirements for how various products fit into an SQL environment. The emphasis in this first specification is to specify general purpose, SQL external repository interface (SQL/ERI) profiles for non-SQL data repositories. These profiles specify how a subset of the SQL standard can be used to provide limited SQL access to legacy databases, or to support SQL gateways to specialized data managers such as Geographic Information Systems (GIS), full-text document management systems, or object database management systems. All of the profiles specified herein are for server-side products, that is, products that control persistent data and provide an interface for user access to that data. Subsequent versions of this FIPS PUB may specify SQL environment profiles for client-side products, that is, products that access data and then present that data in graphical or report-writer style to an end user, or process the data in some other way on behalf of the end user.

1.1 Database Language SQL

Database Language SQL is enjoying success as an effective International Standard for customers and implementors of full-function, SQL-compliant database management systems that support the relational data model. Many vendors have implemented an entire level of the SQL'92 standard [8] and offer products that conform to all of its clauses for schema definition, data manipulation, transaction management, integrity constraints, access control, and schema information. Other vendors have implemented selected portions of the SQL standard, most often read-only data retrieval or very restricted data manipulation, in order to provide SQL access to legacy databases or to support SQL gateways to specialized data managers.

The first SQL standard, in 1986, provided basic language constructs for defining and manipulating tables of data; a revision in 1989 added language extensions for referential integrity and generalized integrity constraints; and the most recent revision in 1992 provides new facilities for schema manipulation and data administration, as well as substantial enhancements for data definition and data manipulation. A companion standard for Remote Database Access (RDA) [9], completed in 1993, provides the basic services and protocols for SQL interoperability in a distributed, wide area client/server environment. A companion standard for an SQL Call Level Interface (SQL/CLI) [10], registered as a draft international standard (DIS) in October 1994, provides a language binding appropriate for third-party software developers in a local client/server environment. An extension to SQL for definition and invocation of persistent stored procedures and for SQL flow of control statements, named Persistent SQL Modules (SQL/PSM) [11] and registered as a draft standard in March 1994, permits user definition of procedural program blocks that can then be optimized at multiple SQL servers and invoked as needed, thereby reducing both processing time and communications volume. Features of the SQL'92 standard are discussed in References [1], [2], and [15]. Proposed features of the next SQL revision, often called SQL3, are discussed in [4], [5], and [23].

SQL is particularly appropriate for the definition and management of data that is structured into repeated occurrences having common data structure definitions. SQL provides a high-level query and update language for set-at-a-time retrieval and update operations, as well as required database management functions for schema and view definition, integrity constraints, schema manipulation, and access control. SQL provides a data manipulation language that is mathematically sound and based on a first-order predicate calculus. SQL is self-describing in the sense that all schema information is queryable through a set of catalog tables called the Information Schema.

SQL is becoming the language of choice for many user productivity tools -- such tools communicate with a human user through a graphical user interface and then formulate SQL queries to communicate with underlying persistent data repositories. Formal language profiles for partial SQL conformance are necessary because the user productivity tools and the underlying data managers may be purchased at different times from different vendors and are unlikely to even know of one another's existence. A recognized profile specification will allow limited portability and interoperability, even in otherwise non-homogeneous environments.

1.2 SQL environment

Many applications require a logically integrated database of diverse data (e.g. documents, graphics, spatial data, alphanumeric records, complex objects, images, voice, video) stored in geographically separated data banks under the management and control of heterogeneous data management systems. An over-riding requirement is that these various data managers be able to communicate with one another and provide shared access to data and data operations and methods under appropriate security, integrity, and access control mechanisms. Much of this source data may be stored in simple file systems, legacy data management systems, or very specialized data repositories that satisfy only a small percentage of these data management requirements. The objective of an SQL environment is to logically integrate these diverse data repositories "as if" they were under the control of a single SQL data manager. User presentation tools, such as graphical user interfaces or report writers, can then use this SQL interface to collect data from various sources, merge it together under ad hoc join conditions, and present it to the user in a pleasing graphical format.

A properly functioning SQL environment will use the SQL language to describe this data using standardized facilities, integrate it into a single federated collection, enforce any integrity or access control constraints, and make it available as a logical whole to sophisticated user productivity or presentation tools. These client-side tools can then use the full power and flexibility of SQL for data retrieval and manipulation. The underlying data managers may implement non-relational data models and thus may have difficulty supporting SQL requirements for nested subqueries, multi-table joins, derived columns in a select list, referential integrity, or other relational model features. On the other hand, they may offer advanced features of other data models that are rarely supported by relational implementations. With emerging features in the SQL language for user-defined abstract data types (ADTs), stored procedures, encapsulation, polymorphism, and other object management facilities, these diverse data repositories can be described as specialized SQL repositories and accessed using already standardized SQL binding alternatives identified in Section 6. With this approach, SQL may prove to be as successful as an integrator of heterogeneous data repositories as it has been as a language interface to the relational data model. The SQL language can meet these integration objectives if non-SQL implementations provide a "simple" SQL interface to their data and services, and if full-function SQL implementations use that simple interface to provide full-function services to end user tools. This specification defines standard profiles for such "simple" SQL interfaces, thereby making it easier to specify and support the desired integration.

An SQL environment depends upon the data integration architecture presented in Section 2. A simplification of this architecture is given in the figure below. Components in the architecture consist of Application Processors, Full-Funtion SQL Processors, and Non-SQL Processors. The Application Processors represent client-side products that desire the ability to use the full power and flexibility of the SQL language when accessing data from a database. The Non-SQL Processors are server-side products representing data managers that control much of the data that is to be integrated and made available to the Application Processors. The Full-Function SQL Processors serve a dual role, both as server-side data managers and as "integrators" that make it possible for Application Processors to access data managed by the Non-SQL Processors in a standard manner. The interface between an Application Processor and an SQL Processor is a full-function SQL interface, whereas the interface between an SQL Processor and a Non-SQL Processor is one of the more limited SQL/ERI interfaces described in Section 3. All interfaces use one of the binding styles identified in Section 6. It is the integrator's role to provide access to all data as if it were managed by a full-function SQL processor.

Section 4 describes existing conformance levels of the SQL language and then defines a new, minimal SQL language level that can be used to define conformance alternatives for SQL/ERI Servers. Section 5 identifies various higher level SQL features and data types that an SQL/ERI Server may support. In this way, an SQL/ERI Server can present the features of a different data model to an SQL application by describing them as SQL abstract data types, methods, procedures, or other callable routines. Section 7 specifies two major SQL/ERI Server profiles -- a read-only profile for static data repositories, and a read-write profile that allows SQL Update, Insert, and Delete statements. The read-write profile also provides an option that allows creation of SQL tables and views. Section 8 identifies a number of application areas for which SQL/ERI Server profiles may be applicable. Section 9 describes how the NIST SQL Test Suite serves as the basis for conformance testing of SQL/ERI Servers and Section 10 identifies some procurement considerations for users that intend to use this FIPS PUB for SQL Evironments to aide in the specification of procurement requirements.

The SQL/ERI profiles specified herein may be used by customers and vendors of non-SQL processors to validate claims of conformance for partial support of the SQL language. If these SQL/ERI Server profiles prove to be helpful for integrating non-SQL data repositories into SQL environments, then later versions of this FIPS PUB may specify profiles for SQL/ERI Clients as enhancements to full-function SQL implementations and profiles for other client-side products in an SQL environment. This specification may also be used as a starting point for defining International Standardized Profiles (ISPs) [14] for SQL language access to non-SQL data repositories.