SQL Series...7

The Components of SQL

SQL is a special-purpose language designed for the creation and mainte-nance of data in relational databases. Although the vendors of relational

database management systems have their own SQL implementations, an ISO/ANSI standard (revised in 2003) defines and controls what SQL is. All implementations differ from the standard to varying degrees. Close adher-ence to the standard is the key to running a database (and its associated applications) on more than one platform.

Although SQL isn’t a general-purpose programming language, it contains some impressive tools. Three languages-within-a-language offer everything you need to create, modify, maintain, and provide security for a relational database:

The Data Definition Language (DDL): The part of SQL that you use to create (completely define) a database, modify its structure, and destroy it when you no longer need it.

The Data Manipulation Language (DML): Performs database mainte-nance. Using this powerful tool, you can specify what you want to do with the data in your database — enter it, change it, or extract it.

The Data Control Language (DCL): Protects your database from becom-ing corrupted. Used correctly, the DCL provides security for your data-base; the amount of protection depends on the implementation. If your implementation doesn’t provide sufficient protection, you must add that protection to your application program.

Data Definition Language

The Data Definition Language (DDL) is the part of SQL you use to create, change, or destroy the basic elements of a relational database. Basic ele-ments include tables, views, schemas, catalogs, clusters, and possibly other things as well. In this section, I discuss the containment hierarchy that relates these elements to each other and look at the commands that operate on these elements.

In previously, mention tables and schemas, noting that a schema is an overall structure that includes tables within it. Tables and schemas are two elements of a relational database’s containment hierarchy. You can break down the con-tainment hierarchy as follows:

Tables contain columns and rows. Schemas contain tables and views. Catalogs contain schemas.

The database itself contains catalogs. Sometimes the database is referred to as a cluster.

Creating tables

A database table is a two-dimensional array made up of rows and columns. You can create a table by using the SQL CREATE TABLE command. Within the command, you specify the name and data type of each column.

After you create a table, you can start loading it with data. (Loading data is a DML, not a DDL, function.) If requirements change, you can change a table’s structure by using the ALTER TABLE command. If a table outlives its useful-ness or becomes obsolete, you can eliminate it with the DROP command. The various forms of the CREATE and ALTER commands, together with the DROP command, make up SQL’s DDL.

Say that you’re a database designer and you don’t want your database tables to turn to guacamole as you make updates over time. You decide to structure your database tables according to the best-normalized form to ensure main-tenance of data integrity. Normalization, an extensive field of study in its own right, is a way of structuring database tables so that updates don’t introduce anomalies. Each table you create contains columns that correspond to attri-butes that are tightly linked to each other.

You may, for example, create a CUSTOMER table with the attributes CUSTOMER.

CustomerID, CUSTOMER.FirstName, CUSTOMER.LastName, CUSTOMER.Street,

CUSTOMER.City, CUSTOMER.State, CUSTOMER.Zipcode, and CUSTOMER.Phone. All of these attributes are more closely related to the customer entity than to any other entity in a database that may contain many tables. These attributes contain all the relatively permanent customer information that your organiza-tion keeps on file.

Most database management systems provide a graphical tool for creating database tables. You can also create such tables by using an SQL command. The following example demonstrates a command that creates your CUSTOMER table:

CREATE TABLE CUSTOMER (
CustomerID			INTEGER	NOT NULL,
FirstName			CHARACTER (15),
LastName			CHARACTER (20)	NOT NULL,
Street		CHARACTER (25),
City	CHARACTER (20),
State		CHARACTER (2),
Zipcode		INTEGER,
Phone		CHARACTER (13) ) ;

For each column, you specify its name (for example, CustomerID), its data type (for example, INTEGER), and possibly one or more constraints (for example, NOT NULL).

Figure 3-1 shows a portion of the CUSTOMER table with some sample data.

If the SQL implementation you use doesn’t fully implement SQL:2003, the syntax you need to use may differ from the syntax that I give in this book. Read your DBMS’s user documentation for specific information.

Say that you need to create a database for your organization. Excited by the prospect of building a useful, valuable, and totally righteous structure of great importance to your company’s future, you sit down at your computer and start entering SQL CREATE commands. Right?

Well, no. Not quite. In fact, that’s a prescription for disaster. Many database development projects go awry from the start as excitement and enthusiasm overtake careful planning. Even if you have a clear idea of how to structure your database, write everything down on paper before touching your key-board. Keep in mind the following procedures when planning your database:

Identify all tables.

Define the columns that each table must contain.

Give each table a primary key that you can guarantee is unique. (I dis-cuss primary keys in Chapters 4 and 5.)

Make sure that every table in the database has at least one column in common with one other table in the database. These shared columns serve as logical links that enable you to relate information in one table to the corresponding information in another table.

Put each table in third normal form (3NF) or better to ensure the preven-tion of insertion, deletion, and update anomalies. (I discuss database normalization in Chapter 5.)

After you complete the design on paper and verify that it is sound, you’re ready to transfer the design to the computer by using SQL CREATE commands.

A room with a view

At times, you want to retrieve specific information from the CUSTOMER table. You don’t want to look at everything — only specific columns and rows. What you need is a view.

A view is a virtual table. In most implementations, a view has no independent physical existence. The view’s definition exists only in the database’s meta-data, but the data comes from the table or tables from which you derive the view. The view’s data is not physically duplicated somewhere else in online disk storage. Some views consist of specific columns and rows of a single table. Others, known as multitable views, draw from two or more tables.

Single-table view

Sometimes when you have a question, the data that gives you the answer resides in a single table in your database. If the information you want exists in a single table, you can create a single-table view of the data. For example, say that you want to look at the names and telephone numbers of all cus-tomers who live in the state of New Hampshire. You can create a view from the CUSTOMER table that contains only the data you want. The following SQL command creates this view:

CREATE VIEW NH_CUST AS

SELECT CUSTOMER.FirstName,

CUSTOMER.LastName,

CUSTOMER.Phone

FROM CUSTOMER

WHERE CUSTOMER.State = ‘NH’ ;

Figure shows how you derive the view from the CUSTOMER table.

This code is correct, but a little on the wordy side. You can accomplish the same thing with less typing if your SQL implementation assumes that all table references are the same as the ones in the FROM clause. If your system makes that reasonable default assumption, you can reduce the command to the following lines:

CREATE VIEW NH_CUST AS

SELECT FirstName, LastName, Phone

FROM CUSTOMER

WHERE STATE = ‘NH’;

Although the second version is easier to write and read, it’s more vulnerable to disruption from ALTER TABLE commands. Such disruption isn’t a problem for this simple case, which has no JOIN, but views with JOINs are more robust when they use fully qualified names.

Creating a multitable view

Typically, you need to pull data from two or more tables to answer your ques-tion. For example, say that you work for a sporting goods store, and you want to send a promotional mailing to all the customers who have bought ski equipment since the store opened last year. You need information from the CUSTOMER table, the PRODUCT table, the INVOICE table, and the INVOICE_ LINE table. You can create a multitable view that shows the data you need. After you create the view, you can use that same view again and again. Each time you use the view, it reflects any changes that occurred in the underlying tables since you last used the view.

		CUSTOMER Table					NH_CUST View
		Customer ID
You derive								FirstName
		FirstName
the
		LastName						LastName
NH_CUST		Street
view		City
from the		State
CUSTOMER		Zipcode						Phone
table.		Phone
						WHERE State = ‘NH’

The sporting goods store database contains four tables: CUSTOMER, PRODUCT, INVOICE, and INVOICE_LINE. The tables are structured as shown in Table 

Table	Column	Data Type	Constraint
CUSTOMER	CustomerID	INTEGER	NOT NULL
	FirstName	CHARACTER (15)
	LastName	CHARACTER (20)	NOT NULL
	Street	CHARACTER (25)
	City	CHARACTER (20)
	State	CHARACTER (2)
	Zipcode	INTEGER
	Phone	CHARACTER (13)
PRODUCT	ProductID	INTEGER	NOT NULL
	Name	CHARACTER (25)
	Description	CHARACTER (30)
	Category	CHARACTER (15)
	VendorID	INTEGER
	VendorName	CHARACTER (30)
INVOICE	InvoiceNumber	INTEGER	NOT NULL
	CustomerID	INTEGER
	InvoiceDate	DATE
	TotalSale	NUMERIC (9,2)
	TotalRemitted	NUMERIC (9,2)
	FormOfPayment	CHARACTER (10)
INVOICE_LINE	LineNumber	INTEGER	NOT NULL
	InvoiceNumber	INTEGER
	ProductID	INTEGER
	Quantity	INTEGER
	SalePrice	NUMERIC (9,2)

Notice that some of the columns in Table contain the constraint NOT NULL. These columns are either the primary keys of their respective tables or columns that you decide must contain a value. A table’s primary key must uniquely identify each row. To do that, the primary key must contain a non-null value in every row. 

The tables relate to each other through the columns that they have in common.

The following list describes these relationships (as shown in Figure):

The CUSTOMER table bears a one-to-many relationship to the INVOICE table. One customer can make multiple purchases, generating multiple invoices. Each invoice, however, deals with one and only one customer.

The INVOICE table bears a one-to-many relationship to the INVOICE_LINE table. An invoice may have multiple lines, but each line appears on one and only one invoice.

The PRODUCT table also bears a one-to-many relationship to the INVOICE_LINE table. A product may appear on more than one line on one or more invoices. Each line, however, deals with one, and only one, product.

The CUSTOMER table links to the INVOICE table by the common CustomerID column. The INVOICE table links to the INVOICE_LINE table by the common InvoiceNumber column. The PRODUCT table links to the INVOICE_LINE table by the common ProductID column. These links are what makes this database a relational database.

To access the information about customers who bought ski equipment, you need FirstName, LastName, Street, City, State, and Zipcode from the CUSTOMER table; Category from the PRODUCT table; InvoiceNumber from the INVOICE table; and LineNumber from the INVOICE_LINE table. You can create the view you want in stages by using the following commands:

CREATE VIEW SKI_CUST1 AS

SELECT FirstName,

LastName,

Street,

City,

State,

Zipcode,

InvoiceNumber

FROM CUSTOMER JOIN INVOICE

USING (CustomerID) ;

CREATE VIEW SKI_CUST2 AS

SELECT FirstName,

LastName,

Street,

City,

State,

Zipcode,

ProductID

FROM SKI_CUST1 JOIN INVOICE_LINE

USING (InvoiceNumber) ;

CREATE VIEW SKI_CUST3 AS

SELECT FirstName,

LastName,

Street,

City,

State,

Zipcode,

Category

FROM SKI_CUST2 JOIN PRODUCT

USING (ProductID) ;

CREATE VIEW SKI_CUST AS

SELECT DISTINCT FirstName,

LastName,

Street,

City,

State,

Zipcode

FROM SKI_CUST3

WHERE CATEGORY = ‘Ski’ ;

These CREATE VIEW statements combine data from multiple tables by using the JOIN operator. Figure diagrams the process.

CUSTOMER Table

SKI_CUST1 View

SKI_CUST2 View

SKI_CUST3 View

SKI_CUST View

Customer ID

FirstName

FirstName

FirstName

FirstName

FirstName

LastName

LastName

LastName

LastName

LastName

Street

Street

Street

Street

Street

City

City

City

City

City

State

State

State

State

State

Zipcode

Zipcode

Zipcode

Zipcode

Zipcode

Phone

InvoiceNumber

ProductID

Category

INVOICE Table

INVOICE_LINE Table

PRODUCT Table

Creating a

InvoiceNumber

LineNumber

ProductID

multitable

CustomerID

InvoiceNumber

Name

view by

Date

ProductID

Description

using

TotalSale

Quantity

Category

TotalRemitted

SalePrice

VendorID

JOINs.

FormOfPayment

VendorName

Here’s a rundown of the four CREATE VIEW statements:

The first statement combines columns from the CUSTOMER table with a column of the INVOICE table to create the SKI_CUST1 view.

The second statement combines SKI_CUST1 with a column from the INVOICE_LINE table to create the SKI_CUST2 view.

The third statement combines SKI_CUST2 with a column from the PRODUCT table to create the SKI_CUST3 view.

The fourth statement filters out all rows that don’t have a category of Ski. The result is a view (SKI_CUST) that contains the names and addresses of all customers who bought at least one product in the Ski category.

The DISTINCT keyword in the fourth CREATE VIEW’s SELECT clause ensures that you have only one entry for each customer, even if some customers made multiple purchases of ski items. 

Collecting tables into schemas

A table consists of rows and columns and usually deals with a specific type of entity, such as customers, products, or invoices. Useful work generally requires information about several (or many) related entities. Organizationally, you collect the tables that you associate with these entities according to a logical schema. A logical schema is the organizational structure of a collection of related tables.

A database also has a physical schema. The physical schema is the way the data and its associated items, such as indexes, are physically arranged on the system’s storage devices. When I mention the schema of a database, I’m referring to the logical schema, not the physical schema.

On a system where several unrelated projects may co-reside, you can assign all related tables to one schema. You can collect other groups of tables into schemas of their own.

You want to name schemas to ensure that no one accidentally mixes tables from one project with tables of another. Each project has its own associated schema, which you can distinguish from other schemas by name. Seeing cer-tain table names (such as CUSTOMER, PRODUCT, and so on) appear in multi-ple projects, however, isn’t uncommon. If any chance exists of a naming ambiguity, qualify your table name by using its schema name as well (as in SCHEMA_NAME.TABLE_NAME). If you don’t qualify a table name, SQL assigns that table to the default schema.

Ordering by catalog

For really large database systems, multiple schemas may not be sufficient. In a large distributed database environment with many users, you may even find duplication of a schema name. To prevent this situation, SQL adds another level to the containment hierarchy: the catalog. A catalog is a named collection of schemas.

You can qualify a table name by using a catalog name and a schema name. This ensures that no one confuses that table with a table of the same name in a schema with the same schema name. The catalog-qualified name appears in the following format:

CATALOG_NAME.SCHEMA_NAME.TABLE_NAME

A database’s containment hierarchy has clusters at the highest level, but rarely will a system require use of the full scope of the containment hierarchy. Going to catalogs is enough in most cases. A catalog contains schemas; a schema contains tables and views; tables and views contain columns and rows.

The catalog also contains the information schema. The information schema contains the system tables. The system tables hold the metadata associated with the other schemas. In Chapter 1, I define a database as a self-describing collection of integrated records. The metadata contained in the system tables is what makes the database self-describing.

Because you distinguish catalogs by their names, you can have multiple cata-logs in a database. Each catalog can have multiple schemas, and each schema can have multiple tables. Of course, each table can have multiple columns and rows. The hierarchical relationships are shown in Figure 

Getting familiar with DDL commands

SQL’s Data Definition Language (DDL) deals with the structure of a database, whereas the Data Manipulation Language (described later) deals with the data contained within that structure. The DDL consists of these three commands:

CREATE: You use the various forms of this command to build the essen-tial structures of the database.

ALTER: You use this command to change structures that you create.

DROP: If you apply this command to a table, it destroys not only the table’s data, but its structure as well.

In the following sections, I give you brief descriptions of the DDL commands.

In Chapters 4 and 5, I use these commands in examples.

CREATE

You can apply the SQL CREATE command to several SQL objects, including schemas, domains, tables, and views. By using the CREATE SCHEMA state-ment, you can create a schema, identify its owner, and specify a default char-acter set. An example of such a statement appears as follows:

CREATE SCHEMA SALES

AUTHORIZATION SALES_MGR

DEFAULT CHARACTER SET ASCII_FULL ;

Use the CREATE DOMAIN statement to apply constraints to column values or to specify a collation order. The constraints you apply to a domain determine

what objects the domain can and cannot contain. You can create domains after you establish a schema. The following example shows how to use this command:

CREATE DOMAIN Age AS INTEGER

CHECK (AGE > 20) ;

You create tables by using the CREATE TABLE statement, and you create views by using the CREATE VIEW statement. Earlier in this chapter, I show you examples of these two statements. When you use CREATE TABLE to create a new table, you can specify constraints on its columns at the same time. Sometimes, you may want to specify constraints that don’t specifically attach to a table but that apply to an entire schema. You can use the CREATE ASSERTION statement to specify such constraints.

You also have CREATE CHARACTER SET, CREATE COLLATION, and CREATE TRANSLATION statements, which give you the flexibility of creating new char-acter sets, collation sequences, or translation tables. (Collation sequences define the order in which you carry out comparisons or sorts. Translation tables control the conversion of character strings from one character set to another.)

ALTER

After you create a table, you’re not necessarily stuck with that exact table for-ever. As you use the table, you may discover that it’s not everything you need it to be. You can use the ALTER TABLE command to change the table by adding, changing, or deleting a column in the table. In addition to tables, you can also ALTER columns and domains.

DROP

Removing a table from a database schema is easy. Just use a DROP TABLE <tablename> command. You erase all the table’s data as well as the meta-data that defines the table in the data dictionary. It’s almost as if the table never existed.