The ANSI/ISO SQL1 standard made a very strong distinction between the SQL Data Manipulation Language and Data Definition Language, defining them effectively as two separate languages. The standard did not require that the DDL statements be accepted by the DBMS during its normal operation. One of the advantages of this separation of the DML and DDL was that the standard permitted a static database structure like that used by older hierarchical and network DBMS products, as shown in Figure 13-12.
The database structure specified by the SQL1 standard was fairly straightforward. Collections of tables were defined in a database schema, associated with a specific user. In Figure 13-12, the simple database has two schemas. One schema is associated with (the common terminology is owned by) a user named Joe, and the other is owned by Mary. Joe’s schema contains two tables, named PEOPLE and PLACES. Mary’s schema also contains two tables, named THINGS and PLACES. Although the database contains two tables named PLACES, it’s possible to tell them apart because they have different owners.
The SQL2 standard significantly extended the SQL1 notion of database definition and database schemas. As previously noted, the SQL2 standard requires that data definition statements be executable by an interactive SQL user or by a SQL program. With this capability, changes to the database structure can be made at any time, not just when the database is created. In addition, the SQL1 concepts of schemas and users (officially called authorization-ids in the standard) is significantly expanded. Figure 13-13 shows the high-level database structure specified by the SQL2 standard.
The highest-level database structure described by the SQL2 standard is the SQL- environment. This is a conceptual collection of the database entities associated with a DBMS implementation that conforms to the SQL2 standard. The standard doesn’t specify how a SQL-environment is created; that depends on the particular DBMS implementation. The standard defines these components of a SQL-environment:
- DBMS software that conforms to the SQL2 standard.
- Named users (called authorization-ids in the standard) who have the privileges to perform specific actions on the data and structures within the database.
- Program modules that are used to access the database. The SQL2 standard specifies the actual execution of SQL statements in terms of a module language, which in practice is not used by most major commercial SQL products. No matter how the SQL programs are actually created, however, the standard says that, conceptually, the SQL-environment includes the program’s database access code.
- Catalogs that describe the structure of the database. SQL1-style database schemas are contained within these catalogs.
- Database data, which is managed by the DBMS software, accessed by the users through the programs, and whose structure is described in the catalogs. Although the standard conceptually describes the data as outside of the catalog structure, it’s common to think of data as being contained in a table that is in a schema, which is in a catalog.
1. SQL2 Catalogs
Within a SQL-environment, the database structure is defined by one or more named catalogs. The word “catalog” in this case is used in the same way that it has historically been used on mainframe systems—to describe a collection of objects (usually files). On minicomputer and personal computer systems, the concept is roughly analogous to a directory. In the case of a SQL2 database, the catalog is a collection of named database schemas. The catalog also contains a set of system tables (confusingly, often called the system catalog) that describe the structure of the database. The catalog is thus a selfdescribing entity within the database. This characteristic of SQL2 catalogs (which is provided by all major SQL products) is described in detail in Chapter 16.
The SQL2 standard describes the role of the catalog and specifies that a SQL- environment may contain one or more (actually zero or more) catalogs, each of which must have a distinct name. It explicitly says that the mechanism for creating and destroying catalogs is implementation-defined. The standard also says that the extent to which a DBMS allows access across catalogs is implementation defined. Specifically, whether a single SQL statement can access data from multiple catalogs, whether a single SQL transaction can span multiple catalogs, or even whether a single user session with the DBMS can cross catalog boundaries are all implementation-defined characteristics.
The standard says that when a user or program first establishes contact with a SQL-environment, one of its catalogs is identified as the default catalog for the session. (Again, the way in which this catalog is selected is implementation-defined.) During the course of a session, the default catalog can be changed with the SET CATALOG statement.
2. SQL2 Schemas
The SQL2 schema is the key high-level container for objects in a SQL2 database structure. A schema is a named entity within the database and includes the definitions for the following:
- Tables. Along with their associated structures (columns, primary and foreign keys, table constraints, and so on), tables remain the basic building blocks of a database in a SQL2 schema.
- Views. These are virtual tables, derived from the actual tables defined in the schema, as described in Chapter 14.
- Domains. Function like extended data types for defining columns within the tables of the schema, as described in Chapter 11.
- Assertions. These database integrity constraints restrict the data relationships across tables within the schema, as described earlier in the section “Assertions.”
- Assertions. Database privileges control the capabilities that are given to various users to access and update data in the database and to modify the database structure. The SQL security scheme created by these privileges is described in Chapter 14.
- Character sets. Databases support international languages and manage the representation of non-Roman characters in those languages (for example, the diacritical accent marks used by many European languages or the 2-byte representations of the word-symbols used in many Asian languages) through character sets defined by the schema.
- Collations. Work hand-in-hand with character sets, defining the sorting sequence for a character set.
- Translations. Control how text data is converted from one character set to another and how comparisons are made of text data from different character sets.
A schema is created with the CREATE SCHEMA statement, shown in Figure 13-14. Here is a simple SQL2 schema definition for the simple two-table schema for the user JOE shown in Figure 13-12:
CREATE SCHEMA JSCHEMA AUTHORIZATION JOE
CREATE TABLE PEOPLE
(NAME VARCHAR(30),
AGE INTEGER)
CREATE TABLE PLACES
(CITY VARCHAR(30),
STATE VARCHAR(30))
GRANT ALL PRIVILEGES
ON PEOPLE
TO PUBLIC
GRANT SELECT
ON PLACES
TO MARY
The schema defines the two tables and gives certain other users permission to access them. It doesn’t define any additional structures, such as views or assertions. Note that the CREATE TABLE statements within the CREATE SCHEMA statement are legitimate SQL statements in their own right. If you type them into an interactive SQL program, the DBMS will create the specified tables in the current default schema for your interactive SQL session, according to the standard.
Note that in SQL2, the schema structure is related to, but independent of, the user-id structure. A given user can be the owner of several different named schemas. For backward compatibility with the SQL1 standard, however, the SQL2 standard allows you to create a schema with:
- Both a schema name and a user-id (as in the last example).
- Only a schema name. In this case, the user who executes the CREATE SCHEMA statement automatically becomes the owner of the schema.
- Only a user-id. In this case, the schema name becomes the user-id. This conforms to the SQL1 standard, and to the practice of many commercial DBMS products where there was conceptually one schema per user.
A SQL2 schema that is no longer needed can be dropped using the DROP SCHEMA statement, shown in Figure 13-15. The statement requires that you specify one of the drop rules previously described for dropping columns—either CASCADE or RESTRICT. If you specify CASCADE, then all of the structures within the schema definition (tables, views, assertions, and so on) are automatically dropped. If you specify RESTRICT, the statement will not succeed if any of these structures are remaining within the schema. Effectively, the RESTRICT rule forces you to first drop the individual tables, views, and other structures within the schema before dropping the schema itself. This is a protection against accidentally dropping a schema that contains data or database definitions of value. No ALTER SCHEMA table is specified by the SQL2 standard. Instead, you can individually alter the definitions of the structures within a schema, using statements like ALTER TABLE.
At any time while a user or program is accessing a SQL2 database, one of its schemas is identified as the default schema. Any DDL statements that you execute to create, drop, or alter schema structures implicitly apply to this schema. In addition, all tables named in SQL2 data manipulation statements are assumed to be tables defined within this default schema. The schema name implicitly qualifies the names of all tables used in the SQL statements. As noted in Chapter 5, you can use a qualified table name to refer to tables from other schemas. According to the SQL2 standard, the name used to qualify the table name is the schema name. For example, if the sample database were created as part of a schema named SALES, the qualified table name for the OFFICES table would be:
SALES.OFFICES
If a SQL2 schema is created with just a user-id as the schema name, then the table qualification scheme becomes exactly the simple one described in Chapter 5. The schema name is the user name, and the qualified table name specifies this name before the dot.
The SQL2 CREATE SCHEMA statement has one other nonobvious advantage. You may recall from the earlier discussion of the CREATE TABLE statement that you could not easily create a referential cycle (two or more tables that refer to one another using foreign key/primary key relationships). Instead, one of the tables had to be created first without its foreign key definition, and then the foreign key definition had to be added (with the ALTER TABLE statement) after the other table(s) had been created. The CREATE SCHEMA statement avoids this problem, since the DBMS does not check the referential integrity constraints specified by the schema until all of the tables it defines have been created. In practice, the CREATE SCHEMA statement is generally used to create a new set of interrelated tables for the first time. Subsequently, individual tables are added, dropped, or modified using the CREATE/DROP/ALTER TABLE capabilities.
Many of the major DBMS brands have moved to adopt some form of the CREATE SCHEMA statement, although there are significant variations across the brands. Oracle’s CREATE SCHEMA statement allows you to create tables, views, and privileges, but not the other SQL2 structures, and it requires that the schema name and the user name be one and the same. Informix Universal Server follows a similar pattern, requiring a user-id as the schema name and extending the objects within the schema to include indexes, triggers, and synonyms. Sybase provides similar capabilities. In each case, the offered capabilities conform to the SQL2 Entry level implementation requirements.
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.