Understanding Data Definition Language (DDL)

Data definition language (DDL) is the set of SQL statements (ALTER, CREATE, DROP, GRANT) that let you create, alter, or destroy (drop) the objects that make up a relational database. To put it another way, you use DDL to define the structure and security of a database. SQL-89 (the first ANSI/ISO standard written for SQL) defines data manipulation language (DML) and DDL as two distinct and relatively unrelated languages. Moreover, DML statements, which allow you to update the data in the database, must be available for use while users are accessing the database, for obvious reasons. SQL-89 does not require that the DBMS accept DDL statements during its normal operation. Thus, the standard allows a static database structure similar to that of the hierarchical model (see Tip 3, "Understanding the Hierarchical Database Model") and the network model (see Tip 4, "Understanding the Network Database Model").

The most basic (and powerful) DDL statement is the CREATE statement. Using CREATE, you build the database schema (which you learned about in Tip 12, "Understanding Schemas"). For example, to build a database with two schemas as shown in Figure 18.1, you could use the following SQL statements:

CREATE SCHEMA AUTHORIZATION KONRAD
 CREATE TABLE EMPLOYEES
   (ID           CHAR(3),
    NAME         VARCHAR(35),
    ADDRESS      VARCHAR(45),
    PHONE_NUMBER CHAR(11),
    DEPARTMENT   CHAR(10),
    SALARY       MONEY,
    HOURLY_RATE  MONEY)
 CREATE CUSTOMERS
   (NAME         VARCHAR(35),
    ADDRESS      VARCHAR(45),
    PHONE_NUMBER CHAR(11),
    FOOD_PLAN    CHAR(2))
 CREATE TABLE        APPT_SCHEDULE
   (APPT_DATE        DATE,
    APPT_TIME        INTEGER,
    APPT_DISPOSITION CHAR(4),
    APPT_SALESMAN_ID CHAR(3))
GRANT SELECT, UPDATE
 ON EMPLOYEES
 TO HR_DEPARTMENT
GRANT ALL PRIVILEGES
 ON CUSTOMERS
 TO MARKETING_REPS,  OFFICE_CLERKS
GRANT SELECT
 ON APPT SCHEDULE
 TO PUBLIC
GRANT SELECT, INSERT
 ON APPT SCHEDULE
 TO MARKETING REPS
CREATE SCHEMA AUTHORIZATION KAREN
 CREATE TABLE EMPLOYEES
   (ID            CHARM,
    NAME          VARCHAR(35),
    ADDRESS       VARCHAR(45),
    PHONE_NUMBER  CHAR(11),
    EMPLOYEE_TYPE CHAR (2),
    SALARY        MONEY,
    HOURLY_RATE,  MONEY)
 GRANT SELECT, UPDATE
   ON EMPLOYEES
   TO HR DEPARTMENT
 CREATE PATIENTS
   (ID            INTEGER
    SPONSOR_SSAN  CHAR(11),
    NAME          VARCHAR(35),
    ADDRESS       VARCHAR(45),
    PHONE_NUMBER  CHAR(11),
    AILMENT_CODES VARCHAR(120))
 GRANT SELECT, UPDATE
   ON PATIENTS
   TO DOCTORS, NURSES, CLERKS
 CREATE TABLE APPT_SCHEDULE
   (APPT_DATE          DATE,
    APPT_TIME          INTEGER,
    REASON_CODES       VARCHAR(120),
    DOCTOR_ID          CHAR(3),
    NURSE_ID           CHAR(3),
    REFERRAL_DOCTOR_ID CHAR(15))
 GRANT SELECT, UPDATE
   ON APPT_SCHEDULE
   TO PUBLIC

Note

The CREATE SCHEMA statement uses AUTHORIZATION in place of USER in naming the schema (such as CREATE SCHEMA AUTHORIZATION KONRAD, in the example). Not only does the SQL-89 specification refer to users as "authorization IDs," but the term authorization ID is more applicable on those occasions when you are creating a schema for a department vs. an individual. Moreover, even when creating schemas for individual authorization ID "works," the user ID (or username) is the ID that is authorized to the rights of ownership over the objects in the schema

If you were working in an environment using a static DDL, you would submit the CREATE SCHEMA statements to the database builder program, which would create the tables and set up the security scheme. The database structure would then be "frozen." Users could log in (and application programs could attach) to the database and send DML commands to work with the data in the database, but no tables could be removed or added.

In support of the static nature of the database structure, the SQL-89 specification did not include DROP TABLE and ALTER TABLE statements in the DDL definition. If you needed to change the structure of the database (by adding or removing a table, for example), you would have to get everyone to log out of the database and stop all DBMS access and processing. Then you would unload the data, submit a revised schema to the builder application, and then reload the data.

Although the SQL-89 standard permits a static database structure, no SQL database ever used this approach. Even the earliest releases of the IBM SQL products included the DROP TABLE and ALTER TABLE statements. Full compliance with SQL-92 eliminates the static database structure in that the current (as of this writing) SQL standard includes both DROP and ALTER statements—which require that users be able to remove or modify the structure of tables on the fly (that is, during the normal operation of the DBMS).

Although only the CREATE SCHEMA statement was shown in detail in this tip, other tips in this book will show how to use each of the DDL statements to:

• CREATE/DROP/ALTER ASSERTION. Limits the values that can be assigned to a column based on single or multiple table column relationships. DDL assertion statements are discussed in Tip 199, "Using the CREATE ASSERTION Statement to Create Multi-table Constraints."

• CREATE/DROP/ALTER DOMAIN. A named set of valid values for a column. DDL domain statements are discussed in Tip 170, "Using the CREATE DOMAIN Statement to Create Domains."

• CREATE/DROP INDEX. Structures that speed up database access by making it easier for SQL query statements to find the set of rows with columns that meet the search criteria. DDL index statements are discussed in Tip 161, "Using the CREATE INDEX Statement to Create an Index," and Tip 163, "Using the MS-SQL Server Enterprise Manager to Create an Index."

• CREATE/DROP SCHEMA. a set of related tables, views, domains, constraints, and security structure. Discussed in Tip 506, "Using the CREATE SCHEMA Statement to Create Tables and Grant Access to Those Tables."

• CREATE/DROP/ALTER TABLE. Rows of related columns (of data). DDL table statements are discussed in Tip 46, "Using the CREATE TABLE Statement to Create Tables"; Tip 56, "Understanding the ALTER TABLE Statement"; Tip 57, "Using the ALTER TABLE Statement to Add a Column to a Table"; Tip 60, "Using the ALTER TABLE Statement to Change Primary and Foreign Keys"; and Tip 63, "Using the DROP TABLE Statement to Remove a Table from the Database."

• CREATE/DROP/ALTER VIEW. Virtual tables that display columns of data from rows in one or more base tables. DDL view statements are discussed in Tip 64, "Using the DROP VIEW Statement to Remove a View"; Tip 206, "Using a View to Display Columns in One or More Tables or Views"; and Tip 460, "Using the ALTER VIEW Statement to Modify a View."

• GRANT. Gives specific SQL statement access on individual database objects to a user or group of users. The GRANT statement is discussed in Tip 145, "Using the GRANT Statement WITH GRANT OPTION to Allow Users to Give Database Object Access to Other Users."

The important thing to know now is that DDL consists of the statements that let you create, alter, and destroy objects (tables, views, indexes, domains, constraints) in your database. DDL also has the GRANT statement that you can use to set up database security by granting users or groups DDL and DML statement access on a statement-by-statement and object-by-object basis.

Note

The GRANT statement is part of the DDL and the data control language (DCL). When used in a CREATE SCHEMA statement, GRANT acts as a DDL statement. When used to give users (or groups) additional privileges outside a schema definition, GRANT is a DCL statement. As such, you will find GRANT and its opposite (REVOKE) in Tip 20, "Understanding Data Control Language (DCL)," which describes the SQL DCL statements.