ISO Working Draft

Framework for SQL

�Part 1






August 1994




















(ISO-ANSI Working Draft) SQL Part 1: SQL/Framework-
August 29, 1994
[Compiled using EVERYTHING option]







































Digital Equipment Corporation
Maynard, Massachusetts














Contents Page


Foreword.........................................................vii

Introduction.....................................................ix

1 Scope ........................................................ 1

2 Normative references ......................................... 3

3 Definitions, notations, and conventions ...................... 7

3.1 Definitions ................................................ 7

3.2 Notations .................................................. 7

3.3 Conventions ................................................ 7

3.4 Object identifier for Database Language SQL ................ 7

4 Concepts ..................................................... 9

4.1 Introduction ............................................... 9

4.1.1SQL-environments ........................................... 9

4.1.2Catalogs ...................................................10

4.1.3s ................................11

4.1.4SQL-data ...................................................11

4.1.5Modules and s .....................................11

4.1.5Data types, domains, and assertions ........................11

4.1.5Data instances .............................................13

4.1.5Tables .....................................................14

4.1.6SQL-statements .............................................15

Index











ii (ISO-ANSI working draft) Framework for SQL Modules (SQL/Framework)







DBL:RIO-003 and X3H2-94-328





Foreword




___________________________________________________________________
ISO Only-caused by ANSI changes not yet considered by ISO
___________________________________________________________________

ISO (the International Organization for Standardization) and
IEC (the International Electrotechnical Commission) form the
specialized system for worldwide standardization. National bodies
that are members of ISO or IEC participate in the development of
International Standards through technical committees established
by the respective organization to deal with particular fields of
technical activity. ISO and IEC technical committees collaborate
in fields of mutual interest. Other international organizations,
governmental and non-governmental, in liaison with ISO and IEC,
also take part in the work.

In the field of information technology, ISO and IEC have
established a joint technical committee, ISO/IEC JTC 1. Draft
International Standards adopted by the joint technical committee
are circulated to national bodies for voting. Publication as an
International Standard requires approval by at least 75% of the
national bodies casting a vote.

International Standard ISO/IEC 9075, Part 1, was prepared by Joint
Technical Committee ISO/IEC JTC 1, Information technology.

ISO/IEC 9075 consists of the following parts, under the general
title Information technology - Database languages - SQL:

- Part 1: Framework (SQL/Framework)

- Part 2: Foundation and General Purpose Facilities (SQL
/Foundation)

- Part 3: Call Level Interface (SQL/CLI)

- Part 4: Persistent SQL Modules (SQL/PSM)

- Part 5: Host Language Bindings (SQL/Bindings)

___________________________________________________________________
ANSI Only-caused by ISO changes not yet considered by ANSI
___________________________________________________________________

___________________________________________________________________



Foreword vii







DBL:RIO-003 and X3H2-94-328





Introduction



The organization of this Part of this
American
International
Standard is as follows:

1) Clause 1, "Scope", specifies the scope of this part of this
American
International
Standard.

2) Clause 2, "Normative references", identifies additional
standards that, through reference in this part of this
American
International
Standard, constitute provisions of this part of this
American
International
Standard.

3) Clause 3, "Definitions, notations, and conventions", defines the
notations and conventions used in this part of this
American
International
Standard.

4) Clause 4, "Concepts", presents concepts related to the Parts of
the SQL standard.

In the text of this
American
International
Standard, Clauses begin a new odd-numbered page. Any resulting
blank space is not significant.















Introduction ix







DBL:RIO-003 and X3H2-94-328








Information Technology - Database Languages - SQL
Part 1 - Framework for SQL (SQL/Framework)



1 Scope











































Scope 1







DBL:RIO-003 and X3H2-94-328

























































2 (ISO-ANSI working draft) Framework for SQL Modules (SQL/Framework)







DBL:RIO-003 and X3H2-94-328



2 Normative references


The following standards contain provisions that, through reference
in this text, constitute provisions of this Part of this
American
National
Standard. At the time of publication, the editions indicated
were valid. All standards are subject to revision, and parties
to agreements based on this
American
National
Standard are encouraged to investigate the possibility of applying
the most recent editions of the standards listed below. Members
of IEC and ISO maintain registers of currently valid International
Standards.


___________________________________________________________________
ANSI Only-SQL3
___________________________________________________________________


- ANSI X3.9-1978, American National Standard Programming Language
FORTRAN.

- ANSI X3.23-1985, American National Standard for Information
Systems-Programming Language-COBOL.

- ANSI X3.53-1976, American National Standard Programming Language
PL/I.

- ANSI X3.135-1992, American National Standard for Information
Systems - Database Language - SQL.

- ANSI X3.159-1989, American National Standard for Information
Systems-Programming Language-C.

- ANSI X3.198-1991, American National Standard for Information
Systems-Programming Language-Fortran.

Note: ANSI X3.198-1991 introduces no incompatibilities with
ANSI X3.9-1978 that affect the binding between Fortran and SQL;
therefore, wherever "Fortran" is specified in this
American
International
Standard, ANSI X3.198-1991 is implicit.

- ANSI/MDC X11.1-1990, American National Standard for Information
Systems-Programming Language-MUMPS.

- ANSI/IEEE 770/X3.97-1983, American National Standard for
Information Systems-Programming Language-Pascal.

Normative references 3







DBL:RIO-003 and X3H2-94-328



- ANSI/IEEE 770/X3.160-1989, American National Standard for
Information Systems-Programming Language-Extended Pascal.

- ANSI/MIL-STD-1815A-1983, American National Standard for
Information Systems-Reference Manual for the Ada� Programming
Language.
________________________________________________________________
ISO Only-SQL3
________________________________________________________________

- ISO/IEC 1539:1991, Information technology - Programming
languages - Fortran.

- ISO 1989:1985, Programming languages - COBOL.

- ISO 6160:1979, Programming languages - PL/I.

- ISO 7185:1990, Information technology - Programming languages -
Pascal.

- ISO 8652:1987, Programming languages - Ada.

- ISO/IEC 9075-2:199x, Information Technology - Database Languages
- SQL Foundation.

- ISO/IEC 9075-3:199x, Information Technology - Database Languages
- SQL Call-Level Interface.

- ISO/IEC 9075-4:199x, Information Technology - Database Languages
- SQL Persistent Stored Modules.

- ISO/IEC 9075-5:199x, Information Technology - Database Languages
- SQL Host Language Bindings.

- ISO/IEC 9899:1990, Information technology - Programming
languages - C.

- ISO/IEC 10206:1991, Information technology - Programming
languages - Extended Pascal.

- ISO/IEC 11756:1992, Information technology-Programming
languages-MUMPS.
________________________________________________________________
ISO Only-SQL2
________________________________________________________________

- ISO/IEC 1539:1991, Information technology - Programming
languages - Fortran.

- ISO 1989:1985, Programming languages - COBOL.

- ISO 6160:1979, Programming languages - PL/I.


4 (ISO-ANSI working draft) Framework for SQL Modules (SQL/Framework)







DBL:RIO-003 and X3H2-94-328



- ISO 7185:1990, Information technology - Programming languages -
Pascal.

- ISO 8652:1987, Programming languages - Ada.

- ISO/IEC 9075:1992, Information Technology - Database Languages -
SQL.

- ISO/IEC 9899:1990, Information technology - Programming
languages - C.

- ISO/IEC 10206:1991, Information technology - Programming
languages - Extended Pascal.

- ISO/IEC 11756:1992, Information technology-Programming
languages-MUMPS.
________________________________________________________________





































Normative references 5







DBL:RIO-003 and X3H2-94-328

























































6 (ISO-ANSI working draft) Framework for SQL Modules (SQL/Framework)







DBL:RIO-003 and X3H2-94-328






3 Definitions, notations, and conventions



3.1 Definitions

For the purposes of this
American
International
Standard, the following definitions apply.

This Part of this
American
International
Standard defines the following terms:





3.2 Notations




3.3 Conventions




3.4 Object identifier for Database Language SQL


Note: It is possible that the object identifier for SQL may have to
be adjusted to account for the new structure of SQL3.
















Definitions, notations, and conventions 7







DBL:RIO-003 and X3H2-94-328

























































8 (ISO-ANSI working draft) Framework for SQL Modules (SQL/Framework)







DBL:RIO-003 and X3H2-94-328






4 Concepts



4.1 Introduction

4.1.1 SQL-environments

The SQL database language defined by this
American
International
Standard is processed within an SQL-environment (Subclause 4.28,
"SQL-environment", in Part 2 of this
American
International
Standard) by an SQL-implementation that supports some level of
conformance (Subclause 4.44, "Leveling", in Part 2 of this
American
International
Standard). An SQL Flagger (Subclause 4.45, "SQL Flagger", in Part 2
of this
American
International
Standard) may be provided to effect a static check of SQL language
relative to a given level of conformance.

An SQL-agent (Subclause 4.38, "SQL-agents", in Part 2 of this
American
International
Standard) is an implementation-dependent entity that causes the
execution of SQL-statements (Subclause 4.36, "SQL-statements", in
Part 2 of this
American
International
Standard). An SQL-agent is bound to a single SQL-client (Subclause
4.42, "Client-server operation", in Part 2 of this
American
International
Standard). An SQL-connection (Subclause 4.40, "SQL-connections", in
Part 2 of this
American
International
Standard) is an association between an SQL-client and an SQL-
server. An SQL-session (Subclause 4.41, "SQL-sessions", in Part
2 of this
American
International
Standard) spans the execution of consecutive SQL-statements invoked
by a single user from a single SQL-agent.


Concepts 9







DBL:RIO-003 and X3H2-94-328
4.1 Introduction


An SQL-transaction (Subclause 4.39, "SQL-transactions", in Part 2
of this
American
International
Standard) is a sequence of executions of SQL-statements that is
atomic with respect to recovery.

Besides the SQL-implementation, the SQL-environment may contain
catalogs, s, s, and SQL-data.
These concepts will now be introduced in order.

4.1.2 Catalogs

Catalogs (Subclause 4.25, "Catalogs", in Part 2 of this
American
International
Standard) are named collections of SQL-schemas (Subclause 4.24,
"SQL-schemas", in Part 2 of this
American
International
Standard). An SQL-environment contains zero or more catalogs. A
cluster of catalogs (Subclause 4.26, "Clusters of catalogs", in
Part 2 of this
American
International
Standard) is a collection of catalogs, and exactly one cluster
is associated with an SQL-session and defines the totality of the
SQL-data available to that session.

A catalog contains one or more SQL-schemas, always including the
Information Schema (Subclause 4.43, "Information Schema", in Part 2
of this
American
International
Standard), which defines a representation of the information in
all the descriptors in the schemas of that catalog. An SQL-schema
(referred to hereafter as simply a "schema") contains its name,
its owner's , the name of its default
character set, and a descriptor of each of its components. The
persistent objects described by the descriptors are said to be
owned by or to have been created by the
of the schema. A persistent object may be a privilege, a routine, a
character set, a collation, a translation, an assertion, a trigger,
a module, a domain, an abstract data type, a type template, a null
class, a role a distinct type, a constraint, an operator, a base
table, or a view. These concepts will be described below.

Note: "" is defined in Part 4 of this
American
International
Standard.



10 (ISO-ANSI working draft) Framework for SQL Modules (SQL/Framework)







DBL:RIO-003 and X3H2-94-328
4.1 Introduction


4.1.3 s

Users are represented in an SQL-environment by identifier>s, where the mapping of s
to operating system users is implementation-dependent. Privileges
and roles (Subclause 4.37, "Privileges and roles", in Part 2 of
this
American
International
Standard) may be granted to (and revoked from) identifier>s or other roles, in order to authorize users to
perform certain classes of actions. Roles serve to group together
privileges that can be granted and revoked as a group instead of
individually.

An is specified for each schema and
as well as for each SQL-session.

4.1.4 SQL-data

SQL-data (Subclause 4.27, "SQL-data", in Part 2 of this
American
International
Standard) is any data described by schemas that is under the
control of an SQL-implementation in an SQL-environment.


4.1.5 Modules and s

Modules (Subclause 4.29, "Modules", in Part 2 of this
American
International
Standard) are objects that may be persistent or may be bounded by
the duration of an SQL-session. A module consists of one or more
s (see Subclause 4.6, "Routines", in Part 4 of this
American
International
Standard), together with other constituents. During the execution
of its s, a module is associated with one or more
compilation-units (segments of executable code).

Modules may be explicitly created and dropped.

� 1 Subclause moved to Part 4

4.1.5.1 Data types, domains, and assertions

Each datum occurring as part of SQL-data has a data type (Subclause
4.2, "Character strings", through Subclause 4.15, "Type conversions
and mixing of data types", in Part 2 of this
American
International
Standard), and its values may be further constrained by integrity

Concepts 11







DBL:RIO-003 and X3H2-94-328
4.1 Introduction


constraints (Subclause 4.21, "Integrity constraints", in Part 2 of
this
American
International
Standard) including assertions (Subclause 4.21.4, "Assertions", in
Part 2 of this
American
International
Standard). A domain (Subclause 4.17, "Domains", in Part 2 of this
American
International
Standard) is a shorthand for a data type accompanied by other
properties such as integrity constraints and a default value. Every
domain, column in a base table, SQL variable, and SQL-supplied
parameter also has a null class, and its value may be a null value
of that class (Subclause 4.18, "Nulls", in Part 2 of this
American
International
Standard).

The data type system of SQL may be summarized as follows:

- There is a set of predefined types such as character string,
numeric, and datetime (Subclause 4.2, "Character strings",
through Subclause 4.9, "Datetimes and intervals", in Part 2
of this
American
International
Standard).

- There is a predefined type generator for row types (Subclause
4.12, "Row types", in Part 2 of this
American
International
Standard).

- There is a set of predefined type generators for object
identifier types (Subclause 4.10, "Object identifier", in Part 2
of this
American
International
Standard) and for the collection types multiset, set, and list
(Subclause 4.13, "Collection types", in Part 2 of this
American
International
Standard).

- New types may be constructed by defining abstract data types
(ADTs) or distinct types in terms of existing types-either
predefined types or type generators, or previously defined ADTs,
distinct types, or templates (Subclause 4.11, "Abstract data
types", in Part 2 of this
American

12 (ISO-ANSI working draft) Framework for SQL Modules (SQL/Framework)







DBL:RIO-003 and X3H2-94-328
4.1 Introduction


International
Standard).

- Type templates (user-defined type generators) define potential
families of ADTs, from which individual ADTs are generated when
values are specified for their parameters (Subclause 4.11.6,
"Type templates", in Part 2 of this
American
International
Standard).

- Tables with rows and columns may be defined, where each
column of a table has one of the above types as its data type
(Subclause 4.1.5.3, "Tables", in this Part of this
American
International
Standard and Subclause 4.20, "Tables", in Part 2 of this
American
International
Standard).

The discussion of the character string data type (Subclause 4.2,
"Character strings", in Part 2 of this
American
International
Standard) covers the descriptors of the character set, collation,
and translation objects.

Abstract data types support the concepts of encapsulation,
subtyping, and overloading (Subclause 4.11, "Abstract data types",
in Part 2 of this
American
International
Standard, and Subclause 4.6, "Routines", in Part 4 of this
American
International
Standard.

4.1.5.2 Data instances

A datum in SQL is always an instance of a data type. Equality,
comparability, and assignability of data instances are defined
(Subclause 4.15, "Type conversions and mixing of data types", in
Part 2 of this
American
International
Standard).

Unless specifically defined otherwise, instances of ADTs have
unique object identifiers, that are themselves instances of object
identifier types (Subclause 4.10, "Object identifier", in Part 2 of
this
American
International

Concepts 13







DBL:RIO-003 and X3H2-94-328
4.1 Introduction


Standard). Testing object identifiers for equality thus serves to
test for the identity of the corresponding ADT instances.

4.1.5.3 Tables

A table (Subclause 4.20, "Tables", in Part 2 of this
American
International
Standard) is an SQL-data object that consists of a collection of
rows (Subclause 4.20, "Tables", in Part 2 of this
American
International
Standard).

A table is either:

- a base table (actually containing SQL-data), which may be
persistent or temporary;

- a derived table, which is defined by a ;

- or a viewed table, which may be permanent (called a view), or
temporary (called a temporary view). A viewed table is a named
derived table defined by a that includes a
.

Rules for determining functional dependencies and candidate keys
of tables are defined (Subclause 4.22, "Functional dependencies and
candidate keys", in Part 2 of this
American
International
Standard).

Triggers may be associated with operations on a table to cause
specified triggered actions to take place
before, instead of,
before
or after the normal action (Subclause 4.23, "Triggers", in Part 2
of this
American
International
Standard).

Cursors (Subclause 4.35, "Cursors", in Part 2 of this
American
International
Standard) may be associated with derived tables in order to make it
possible to operate on their rows sequentially.






14 (ISO-ANSI working draft) Framework for SQL Modules (SQL/Framework)







DBL:RIO-003 and X3H2-94-328
4.1 Introduction


4.1.6 SQL-statements

This introduction may now be completed by returning to the starting
point: the SQL-statements that are to be executed within the SQL-
environment.

SQL-statements may be known before an SQL-session begins, or may be
generated, prepared, and executed during the SQL-session (Subclause
4.9, "SQL dynamic statements", in Part 5 of this
American
International
Standard).

When an SQL-statement is executed, completion and exception
information is stored in a diagnostics area (Subclause 4.33,
"Diagnostics area", in Part 2 of this
American
International
Standard).

All of the SQL-statements may now be classified according to the
conceptual structure that has been outlined (Subclause 4.36.2,
"SQL-statements classified by function", in Part 2 of this
American
International
Standard):

- SQL-schema statements, which create, alter, and drop schemas and
schema objects;

- SQL-data statements, which perform set-at-a-time or row-at-a-
time select, insert, update, and delete operations on tables;

- SQL-transaction statements, which influence and terminate
transactions;

- SQL-control statements, which may be used to program an routine body>;

- SQL-connection statements, which control connections;

- SQL-session statements, which set defaults and other information
for an SQL-session;

- SQL-diagnostics statements, which get diagnostics and signal
exceptions.








Concepts 15




















Index


Index entries appearing in boldface indicate the page
where the word, phrase, or BNF nonterminal was defined;
index entries appearing in italics indicate a page where
the BNF nonterminal was used in a Format; and index
entries appearing in roman type indicate a page where
the word, phrase, or BNF nonterminal was used in a
heading, Function, Syntax Rule, Access Rule, General
Rule, Leveling Rule, Table, or other descriptive text.


- A - Information Schemao10
Adao4, 5 integrity constrainto12

- B - - L -
based ono3 level of conformanceo9

- C - - M -
Co3, 4, 5 moduleo10, 11
candidate keyo14 o10, 11
charactero10, 12, 13 multiseto12
clustero10 MUMPSo3, 4, 5

COBOLo3, 4 - N -
columno12, 13 nullo10, 12
conformanceo9 null valueo12

- D - - O -
Datao3, 4, 5, 7, 11, 13 object identifiero7, 12,
databaseo9, 11 13, 14
dateo12, 14, 15 operatoro10
dependo9, 11, 14
dependento9, 11 - P -
descriptoro10, 13 parametero12, 13
diagnostics areao15 Part 2o9, 10, 11, 12, 13,

- E - 14, 15
encapsulationo13 Part 4o10, 11, 13
environmento9, 10, 11, 15 Part 5o15
exceptiono15 Pascalo3, 4, 5
explicito11 persisto10, 11, 14
persistento10, 11, 14
- F - privilegeo10, 11
FORTRANo3 - R -
functional dependencieso14 o10, 11

- I - row typeo12
implementation-dependento
9, 11
implicito3
includeo14

Index 1







DBL:RIO-003 and X3H2-94-328


- S - SQL-transactiono10, 15
scopeo1 suppliedo1, 7, 12

sequenceo10 - T -
spanso9 temporary viewo14
SQL-agento9 transactiono10, 15
SQL-cliento9 translationo10, 13
SQL-connectiono9, 15 Typeo13
SQL-environmento9, 10, 11, type templateo10
15 - V -
SQL-implementationo9, 10, valido3
11 variableo12
SQL-servero9 viewo10, 14
SQL-sessiono9, 10, 11, 15 viewed tableo14
SQL-statemento9, 10, 15








































2 (ISO-ANSI working draft) Framework for SQL Modules (SQL/Framework)