13.10. Summary

Base table schemes may be defined using a create-table statement,whose main syntax is:

create table tablename (
    colname           data-type          [not  null]
                                                [defaultiteral | nullluser]
                                                [primary key | unique   ]
                                                [ references   tablename]
                                                [check (col-condition-on-same-row) ]
     [,...]
    [, primary key (col-list) ]
    [, unique (col-list) ]
    [, foreign key (col-list) references tablename[ (unique-col-list) ] [ .... ]]
    [, check (table-condition-on-same-row) [ ....  ]])

In addition to creating tables by definition, as just shown, tables can also be created like another table or as the result of a select query. Other SQL statements exist for tasks such as altering and dropping tables, and equivalent alter or drop statements are available for other types of database objects.

Views may be created using a create-view statement,with the syntax:

create view viewname[ (col-list) ] as
   select-query
          [with check option ]

View definitions are used to translate queries over views into equivalent queries on base tables. Some views are nonupdatable (e.g., views involving joins).

Triggers are used to respond to specific events in the context of a given table or view. When the specified event is detected, the body of code in the trigger is executed. The data leading to the event can be examined by the trigger to determine the appropriate course of action. The basic syntax for creating a trigger is:

create trigger triggername
  { before | after } { insert | delete | update [ of col-list] } on tablename
  [referencing       { old | new }[row] [as ] correlation-namel
                     { old | new }table [ as ] table-alias    [ .... ]]
  [for each { row l statement } ]
  [ when (condition) ]
  triggered-SOL-statement

Routines are bodies of code that are explicitly called,in contrast to triggers, which are inherently event driven. User-defined functions return either a scalar or a table result. The basic syntax for creating a function is:

create function functionname ( [ parameter-list ])
  returns { data-type | table col-name data-type [ .... ] }
  function-body

Stored procedures are able to carry out a greater range of activities than functions, such as updating tables, and are the standard way of encapsulating SQL code. Stored procedures are often used to “insulate” the database from user actions that could prove damaging. The basic syntax for creating a stored procedure is:

create procedure procedurename
  ([{in | out | inout } parmname parmtype [ .... ] ])
        procedure-body

A database schema may contain other types of objects. Sequencesprovide for the automatic generation of unique identifiers. Indexescan be used to improve query performance. Cursorscan be used to provide row-at-a-time processing.

Practical database systems include measures to deal with concurrency, typically based on the selective locking of resources to avoid interference between transactions. The preferred position between more isolation and more throughput can be specified at the statement or the connection level.

Database security revolves around two main concepts. Users are authenticated against specified credentials to confirm their identity. Privileges—the rights to carry out certain types of action—can be given to, or withheld from, specific individuals or groups.

Metadata—information about the database system itself—is stored in various tables for the system’s internal use. This information can also be queried, although standard practice is to discourage direct access to these tables and instead use system views.

XML is becoming increasingly important, and most database vendors have added features to store XML data, to operate on XML data, and convert backward and forward between XML and relational table structures. XPath and Xquery allow XML data to be queried in a way that corresponds roughly to the way that the select statement is used in querying SQL tables. XQuery is a rich language with many features that would require a complete book to cover, and we have only been able to give an outline of its capabilities in the space available here.

This chapter completes our basic coverage of the major feature of modern relational database systems. Some other aspects of SQL are considered elsewhere in the book in the context of the topic under consideration. More advanced SQL aspects are included in the online Appendix C, and the chapter notes provide further detail. While knowledge of the relevant SQL standards provides an excellent basis, the easiest way to learn the language is by using it, which entails discovering how your chosen SQL dialect differs from the standard. Fortunately, most commercial DBMSs provide extensive online resources to assist you in this regard.