Creating a Database

Creating a Database
The process of creating a database differs significantly from product to product. In
multi-user systems, the authority to create a database is usually reserved for the DBA.
In a single-user system, a default database may be established when the system is installed
and configured and others can be created by the user as and when required. The ISO standard
does not specify how databases are created, and each dialect generally has a different
approach.
According to the ISO standard, relations and other database objects exist in an environment.
Among other things, each environment consists of one or more catalogs, and each
catalog consists of a set of schemas. A schema is a named collection of database objects
that are in some way related to one another (all the objects in the database are described
in one schema or another). The objects in a schema can be tables, views, domains, assertions,
collations, translations, and character sets. All the objects in a schema have the same
owner and share a number of defaults.
The standard leaves the mechanism for creating and destroying catalogs as
implementation-defined, but provides mechanisms for creating and destroying schemas.
The schema definition statement has the following (simplified) form:
CREATE SCHEMA [Name | AUTHORIZATION CreatorIdentifier]
Therefore, if the creator of a schema SqlTests is Smith, the SQL statement is:
CREATE SCHEMA SqlTests AUTHORIZATION Smith;
The ISO standard also indicates that it should be possible to specify within this statement
the range of facilities available to the users of the schema, but the details of how these
privileges are specified are implementation-dependent.
A schema can be destroyed using the DROP SCHEMA statement, which has the
following form:
DROP SCHEMA Name [RESTRICT | CASCADE]
If RESTRICT is specified, which is the default if neither qualifier is specified, the
schema must be empty or the operation fails. If CASCADE is specified, the operation
cascades to drop all objects associated with the schema in the order defined above. If any
of these drop operations fail, the DROP SCHEMA fails. The total effect of a DROP
SCHEMA with CASCADE can be very extensive and should be carried out only
with extreme caution. The CREATE and DROP SCHEMA statements are not yet widely
implemented.