SQL Bible [Electronic resources] نسخه متنی

Schemas

A
schema is a logical database object holder. SQL99 defines
a schema as a named group of related objects. Creating schemas can be useful
when objects have circular references, that is, when we
need to create two tables each with a foreign key referencing the other table.
Different implementations treat schemas in slightly different ways.

CREATE SCHEMA statement

The
CREATE
SCHEMA statement has different meanings
in SQL99, Oracle, DB2, and MS SQL Server.

SQL99

SQL99 states that the
CREATE
SCHEMA statement creates a group of
objects that somehow logically relate to each other; the group has a name that
is called schema name. Also, you can grant privileges on
these objects within the
CREATE
SCHEMA statement. The syntax is

CREATE SCHEMA
{<schema_name> | AUTHORIZATION <authorization_id> |
<schema_name> AUTHORIZATION <authorizat_id> }
<create_object_statement>,...
<grant_privilege_statement>,...

The schema creator usually owns the
objects within the schema unless otherwise specified by using a different
authorization_id.

Objects that can be created as a part
of a schema include tables, views, domains, assertions, character sets,
collations, and translations; you can grant any valid privileges within the
grant_privilege_statement
clause.

Oracle
9i

We already mentioned that in Oracle
terminology the word schema is almost identical to
user. You still can use the
CREATE
SCHEMA statement in Oracle, but the
only use for that operation would be to create multiple objects in a single
transaction.

The syntax for
CREATE
SCHEMA is

CREATE SCHEMA AUTHORIZATION
<schema=your_user_name> <create_object_statement>,...
<grant_privilege_statement>,... ;

The schema name must be the same as
your Oracle user ID, otherwise the statement will fail. The valid objects to
create include tables and views; you can grant any valid object privileges on
them to anybody. The following example creates two tables in hypothetical
schema
ACMETEST (i.e., we assume that the
statement is run by user
ACMETEST that does not exist in the
ACME test database) and gives permissions on them to user
ACME:

CREATE
SCHEMA AUTHORIZATION ACMETEST CREATE TABLE address ( addr_id_n INT CONSTRAINT
pk_addrprimary PRIMARY KEY, addr_custid_fn INT, addr_salesmanid_fn INT
CONSTRAINT fk_addr_salesman REFERENCES salesman (salesman_id_n) ON DELETE
CASCADE, addr_address_s VARCHAR2(60), addr_type_s VARCHAR2(8) CONSTRAINT
chk_addr_type CHECK (addr_type_s IN ('BILLING', 'SHIPPING')), addr_city_s
VARCHAR2(18) CONSTRAINT nn_addr_city NOT NULL, addr_state_s CHAR(2), addr_zip_s
VARCHAR2(10) NOT NULL, addr_country_s CHAR(3) ) CREATE TABLE salesman (
salesman_id_n INT CONSTRAINT pk_salesmanprim PRIMARY KEY, salesman_code_s
VARCHAR2 (2) CONSTRAINT uk_salescode UNIQUE, salesman_name_s VARCHAR2 (50) NOT
NULL, salesman_status_s CHAR (1) CONSTRAINT chk_salesstatus CHECK
(salesman_status_s in ('N', 'Y')) ) GRANT ALL ON salesman TO ACME GRANT ALL ON
address TO ACME ;

DB2 UDB 8.1

The DB2
CREATE
SCHEMA statement seems to be closer to
SQL99 standards. The syntax is

CREATE
SCHEMA {<schema_name> | AUTHORIZATION <authorization_id> |
<schema_name> AUTHORIZATION <authorization_id> }
<create_object_statement>,...
<grant_privilege_statement>,...

The valid objects to create within the
create_object_statement clause are
tables, views, and indexes. The owner of the schema is either
authorization_id or (if not specified)
the user who issued the
CREATE
SCHEMA statement:

CREATE
SCHEMA ACMETEST AUTHORIZATION ACMETEST CREATE TABLE address ( addr_id_n INT NOT
NULL CONSTRAINT pk_addrprimary PRIMARY KEY, addr_custid_fn INT,
addr_salesmanid_fn INT CONSTRAINT fk_addr_salesman REFERENCES salesman
(salesman_id_n) " ON DELETE CASCADE, addr_address_s VARCHAR(60), addr_type_s
VARCHAR(8) CONSTRAINT chk_addr_type CHECK (addr_type_s IN ('BILLING',
'SHIPPING')), addr_city_s VARCHAR(18) CONSTRAINT nn_addr_city NOT NULL,
addr_state_s CHAR(2), addr_zip_s VARCHAR(10) NOT NULL, addr_country_s CHAR(3) )
CREATE TABLE salesman ( salesman_id_n INT NOT NULL CONSTRAINT pk_salesmanprim
PRIMARY KEY, salesman_code_s VARCHAR (2) NOT NULL CONSTRAINT uk_salescode
UNIQUE, salesman_name_s VARCHAR (50) NOT NULL, salesman_status_s CHAR (1)
CONSTRAINT chk_salesstatus CHECK (salesman_status_s in ('N', 'Y')) ) GRANT ALL
ON salesman TO ACME GRANT ALL ON address TO ACME

MS SQL Server 2000

MS SQL Server provides this syntax to
create a schema:

CREATE SCHEMA AUTHORIZATION
<owner> <create_object_statement>,...
<grant_privilege_statement>,...

The owner must have a valid security
account in the database. The statement below assumes the existence of account
ACMETEST:

CREATE
SCHEMA AUTHORIZATION ACMETEST CREATE TABLE address ( addr_id_n INT CONSTRAINT
pk_addrprimary PRIMARY KEY, addr_custid_fn INT, addr_salesmanid_fn INT
CONSTRAINT fk_addr_salesman REFERENCES salesman (salesman_id_n) ON DELETE
CASCADE, addr_address_s VARCHAR(60), addr_type_s VARCHAR(8) CONSTRAINT
chk_addr_type CHECK (addr_type_s IN ('BILLING', 'SHIPPING')), addr_city_s
VARCHAR(18) CONSTRAINT nn_addr_city NOT NULL, addr_state_s CHAR(2), addr_zip_s
VARCHAR(10) NOT NULL, addr_country_s CHAR(3) ) CREATE TABLE salesman (
salesman_id_n INT CONSTRAINT pk_salesmanprim PRIMARY KEY, salesman_code_s
VARCHAR (2) not null CONSTRAINT uk_salescode UNIQUE, salesman_name_s VARCHAR
(50) NOT NULL, salesman_status_s CHAR (1) CONSTRAINT chk_salesstatus CHECK
(salesman_status_s in ('N', 'Y')) ) GRANT ALL ON salesman TO ACME GRANT ALL ON
ADDRESS TO ACME