SAND CDBMS SQL Reference Guide
SAND CDBMS SQL Database Objects and Language Elements

Schemas

A schema is a logical name-space within a database, to which database objects may belong: each table, view, and domain in the database is identified with a particular schema, which is specified (or implied) when the object is created. Each schema in a database must have a unique name; however, the database objects contained within different schemas may have the same names, since each can be identified uniquely by specifying the schema to which it belongs.

Creating a Schema

The CREATE SCHEMA statement adds a schema to a database. The user executing this statement becomes the owner of the schema, and has full privileges for creating, altering, manipulating, and dropping objects within it. The CREATE SCHEMA statement has an optional AUTHORIZATION clause, used to specify an existing database user as the schema owner. A CREATE AUTHORIZATION statement may also add a schema to the database if it specifies a default schema that does not exist; the default schema is created with the new authorization as the owner. (Note that only a user with DBA privileges may issue CREATE AUTHORIZATION statements.)

Besides user-created schemas, each SAND database also contains two special schemas by default: the SYSTEM schema, which holds system-created tables dedicated to storing information about the structure of the database itself; and the PUBLIC schema, which contains system-created views based on selected tables in the SYSTEM schema.

The SYSTEM Schema

The SYSTEM schema contains the SAND CDBMS system tables, which store information about all objects in a database, as well as information relating to internal system management. The information stored in the system tables is continuously updated by SAND CDBMS to reflect changes in the database state. Only users with DBA privileges may access the system tables. Refer to Appendix C for more information about the system tables.

The PUBLIC Schema

The PUBLIC schema contains system views which any user may query to retrieve information about database objects they own or upon which they have privileges. The system views provide information about the following database objects:

• Authorizations
  
• Schemas
  
• Tables
  
• Columns
  
• Domains
  
• Constraints

Refer to Appendix C for more information about the PUBLIC views.

If no default schema is specified in a CREATE AUTHORIZATION statement, the initial schema is set to PUBLIC when the user connects to the database. Since the user DBA and the PUBLIC user have no default schema defined when the database is created, they initially start in the PUBLIC schema when connecting to the database.

Default Schema

When a user establishes a connection with a database, the current schema for that session is initially set to the user's default schema. This default is defined by the user DBA (or a user with DBA privileges) by means of a DEFAULT SCHEMA clause included in the CREATE AUTHORIZATION statement that creates the user authorization. A user’s default schema can be set, as well as changed, through an ALTER AUTHORIZATION...SET DEFAULT SCHEMA statement.

If the default schema did not exist prior to the execution of the CREATE AUTHORIZATION statement, the schema is created with the new user as owner. If the default schema already existed, with another authorization as owner, the schema is established as the initial schema for the new user, but no actual privileges on the schema or its contents are conferred. In this case, if the new user requires the ability to add, drop, or manipulate objects in the schema, these privileges will have to be granted to the user separately by the DBA or another user with the appropriate privileges.

If no default schema is specified in a CREATE AUTHORIZATION statement, the initial schema is set to PUBLIC when the user connects to the database. Since the DBA and PUBLIC users have no default schema defined when the database is created, they initially start in the PUBLIC schema when connecting to the database.

Using Schemas

Schemas are much like operating system directories, in that a database user is considered always to be situated in a current (working) schema. Any reference to tables, views, and domains in an SQL statement is implicitly interpreted in the context of the current schema, unless the object names are qualified with the name of another schema. Tables, views, and domains in the current schema can be accessed without explicitly specifying the schema name.

The following statement, for example, drops table t1 from the current schema; this is an example of an unqualified table reference:

The following statement drops table t2 from schema s2. The table name is qualified, and so the statement drops table t2 from schema s2 regardless of the current schema:

The present working schema can be changed using the SET SCHEMA statement.

The DBA, and users with DBA privileges, can access information about all schemas in the database by querying the system table SYSTEM.SCHEMAS. Users without DBA privileges may query the system view PUBLIC.SCHEMAS for information about the schemas. Refer to Appendix C: SAND CDBMS System Tables/Views for more information about system tables and views.