The SQL Standard describes the concepts on which SQL is based in terms of Objects, such as Tables. Each SQL Object is defined in terms of the characteristics (e.g., its name) that describe it -- the Standard calls this the Object's descriptor. Some Objects are dependent on other Objects, e.g., a Column is dependent on the Table it belongs to. If an Object is dropped (i.e., destroyed), then every Object dependent on it is also dropped.

Cluster

An SQL Cluster is the group of Catalogs available to an SQL-session at any point in time; that is, it contains all the SQL-data you may access through a given SQL-server. Clusters are created and dropped using implementation-defined methods. The Objects that belong to a Cluster are known as Cluster Objects; that is, they depend on some Cluster. Every Cluster Object has a name that must be unique (among Objects of its name class) within the Cluster it belongs to. The Cluster Object name classes are:

  • <AuthorizationID>s
  • Catalogs

<AuthorizationID>

An SQL <AuthorizationID> is a character string which identifies a user and the set of Privileges belonging to that user. (A user is either an actual person or an application program that has access to SQL-data.) An SQL Role is a set of zero or more role authorizations. A role authorization allows a given <AuthorizationID> to use every Privilege granted to that Role. <AuthorizationID>s are dependent on some Cluster; they are created, dropped and mapped to real users using implementation-defined methods. Roles are dependent on some schema and are created and dropped with the CREATE ROLE and DROP ROLE statements.

Privilege

An SQL Privilege authorizes a particular <AuthorizationID> to execute a given operation -- either DELETE, EXECUTE, INSERT, REFERENCES, SELECT, TRIGGER, UNDER, UPDATE, or USAGE on a given Schema Object. It may also allow the grantee to pass the Privilege on to others. Privileges, dependent on some <AuthorizationID>, are created and assigned to <AuthorizationID>s with the GRANT statement and are dropped and removed with the REVOKE statement.

Catalog

An SQL Catalog is a named group of Schemas, one of which must be an Ur-Schema named INFORMATION_SCHEMA. (The INFORMATION_SCHEMA Schema is a set of Views and Domains that contain the descriptions of all the SQL-data belonging to that Catalog.) Catalogs are dependent on some Cluster and are created and dropped using implementation-defined methods.

Schema

An SQL Schema is a named group of SQL-data that is owned by a particular <AuthorizationID>. Schemas are dependent on some Catalog and are created, altered, and dropped using the SQL-Schema statements. The Objects that may belong to a Schema are known as Schema Objects; that is, they depend on some Schema. Every Schema Object has a name that must be unique (among Objects of its name class) within the Schema it belongs to. The Schema Object name classes are:

  • Base tables and Views
  • Domains and UDTs
  • Constraints and Assertions
  • Character sets
  • Collations
  • Translations
  • Triggers
  • SQL-server Modules
  • SQL-invoked routines

Table

An SQL Table is a named set of rows -- an ordered row of one or more <Column name>s together with zero or more unordered rows of data values. Tables store data about a specific entity; each row of the Table describes a single occurrence of that entity. The SQL Standard defines three types of Tables: Base tables, Views, and derived tables. Tables are dependent on some Schema or some Module. Base tables are created, altered, and dropped with the CREATE TABLE, ALTER TABLE, and DROP TABLE statements, Views are created and dropped with the CREATE VIEW and DROP VIEW statements, and derived tables are created when you execute a query.

Column

An SQL Column is a named component of a Table -- a set of similar data values that describe the same attribute of an entity. A Column's values all belong to the same <data type>, or to the same Domain, and may vary over time. A Column value is the smallest unit of data that can be selected from, or updated for, a Table. Columns are dependent on some Table and are created, altered, and dropped with Column definition clauses in the CREATE TABLE and ALTER TABLE statements.

Domain and UDT

An SQL Domain and an SQL UDT, or user-defined type, are both named <data type>s that identify a set of valid data values. Their characteristics are defined by users and their purpose is to constrain the values that can be stored as SQL-data. Domains are dependent on some Schema and are created, altered, and dropped with the CREATE DOMAIN, ALTER DOMAIN and DROP DOMAIN statements. UDTs are dependent on some Schema or some Module and are created. altered, and dropped with the CREATE TYPE, ALTER TYPE, and DROP TYPE statements.

Constraint and Assertion

An SQL Constraint and an SQL Assertion are both named rules that identify sets of valid data values. They constrain the allowable data values for Columns, Domains, and Tables and are defined with two checking characteristics: a deferral mode (either DEFERRABLE or NOT DEFERRABLE) and a constraint check time (either DEFERRED or IMMEDIATE). Constraints are dependent on some Table or some Domain and are created and dropped with Constraint clauses in the CREATE TABLE, ALTER TABLE, CREATE DOMAIN and ALTER DOMAIN statements. Assertions are dependent on some Schema and are created and dropped with the CREATE ASSERTION and DROP ASSERTION statements.

Character Set

An SQL Character set is a named group of characters (the character repertoire) combined with that repertoire's Form-of-use, or coding scheme -- the (usually one-to-one) mapping scheme between each character in the repertoire and a set of internal codes (usually 8-bit values) that give the characters' order in the repertoire and define how the characters are encoded as numbers. Every Character set must contain a space character that is equivalent to the Unicode character U+0020. Character sets are dependent on some Schema and are created and dropped with the CREATE CHARACTER SET and DROP CHARACTER SET statements. Every Character set has a default Collation.

Collation

An SQL Collation is a named set of rules that describes a collating sequence. Each Collation is defined for exactly one Character set and is used to determine the results of comparisons between character strings based on that Character set. All Character sets have a default Collation. Additional Collations may also be created for any Character set, so for any character string comparison, there are one or more Collations that may be invoked by the COLLATE function. Collations are dependent on some Schema and are created and dropped with the CREATE COLLATION and DROP COLLATION statements.

Translation

An SQL Translation is a named set of rules that maps characters from a source Character set to characters in a target Character set for conversion purposes. For any pair of Character sets, there are zero or more Translations that may be invoked by the TRANSLATE function. Translations are dependent on some Schema and are created and dropped with the CREATE TRANSLATION and DROP TRANSLATION statements.

Trigger

An SQL Trigger is a named rule that is associated with a single Base table. Each Trigger defines a trigger event specifying which action -- either INSERT, DELETE, or UPDATE -- on the Table will cause the triggered actions, a trigger action time specifying whether the triggered action is to be taken before or after the trigger event, and one or more triggered actions (the action to take when the Trigger is fired, or invoked). Triggers are dependent on some Schema and are created and dropped with the CREATE TRIGGER and DROP TRIGGER statements.

Module

An SQL Module is an optionally-named group of SQL statements that is treated as a unit of an application program. Such programs use SQL statements to carry out database operations instead of routines written in the host language. There are three kinds of SQL Modules: (a) an SQL-client Module contains SQL procedures that are invoked by a host language and is defined with the MODULE statement, (b) an SQL-session Module contains only SQL statements prepared in that SQL-session and is usually an implicit Module (that is, its presence isn't obvious to the user), and (c) an SQL-server Module -- the SQL/PSM type -- is dependent on some Schema, contains only SQL-invoked routines and is created, altered, and dropped with the CREATE MODULE, ALTER MODULE, and DROP MODULE statements.

SQL-invoked Routine

An SQL-invoked routine is a function or a procedure that can be invoked from SQL. An SQL-invoked function is invoked by a routine invocation in some value expression, while an SQL-invoked procedure is a procedure invoked with the CALL statement. SQL- invoked routines are dependent either directly on some Schema or on some Module and are created and dropped with the CREATE PROCEDURE, DECLARE PROCEDURE, CREATE FUNCTION, CREATE METHOD, DROP SPECIFIC ROUTINE, DROP SPECIFIC FUNCTION, and DROP SPECIFIC PROCEDURE statements.

Note:

Portions of the text in this entry are Copyright © 1999 by Ocelot Computer Services Incorporated. Used by permission.

Comments

Comments loading...