Defining How Data is Stored and Retrieved

• Tables

• Views

• Indexes

• Constraints

• Procedures

• Rules

Creating a Table

When you define a table, use the CREATE TABLE statement to accomplish the following tasks:

The following example contains numbers that refer to the list of tasks shown above:

            1              ---2---
            |             |       |
   CREATE PUBLIC TABLE PurchDB.Parts
 
       (PartNumber   CHAR(16)      NOT NULL, ---
        PartName     VARCHAR(30),              | --3
        SalesPrice   DECIMAL (10,2))         ---
       IN WarehFS
            |
            4

You can also specify native language characteristics and integrity constraints at both the table and the column level.

   CREATE TABLE PurchDB.Parts
                (Column 1 char(20),
                Column 2 DEFAULT NULL)

Specifying a DBEFileSet

The table rows are stored in the DBEFiles previously associated with the DBEFileSet named in the IN clause of the CREATE TABLE statement. If you do not specify a DBEFileSet, rows for the table are stored in the SYSTEM DBEFileSet. For best performance, explicitly specify a DBEFileSet other than the SYSTEM DBEFileSet.

Specifying Native Language Tables and Columns

Use the LANG = TableLanguageName option in the CREATE TABLE statement to specify a language other than the DBEnvironment's language. You can only specify n-computer or the current native language of the DBEnvironment.

   CREATE TABLE NewTable
                LANG = "n-computer"
                (Column1 char(20),
                Column2 char(10))

You must use double quotes around the name "n-computer" because it contains a hyphen. Normally, native language names do not require quotes. For more information on naming rules, refer to the "Names" chapter.

Use the LANG = ColumnLanguageName option in the column definitions of the CREATE TABLE statement to specify a column with a language different from that of the table as a whole. For example:

   CREATE TABLE NewTable
                (Column1 char(20) LANG = "n-computer",
                Column2 char(20))

Sorting and pattern matching follow the rules of the column language. In order to maintain ASCII performance as much as possible, n-computer column sorting and matching are done in ASCII.

By default, the language of a new table is the language of the DBEnvironment, and the language of a new column is the language of the table it belongs to.

Creating a View

A view is a table derived by placing a "window" over one or more tables to let users or programs see only certain data. Views are useful for limiting data visibility; they are also useful for pulling together data from various tables for easier use. The tables from which data for the view is derived are called base tables.

You define a view with the CREATE VIEW statement. The following are components of a view definition:

The following example contains numbers that refer to the view components listed above:

                  1
                  |
   CREATE VIEW HiPrice
               (PartNum, Price)             --2
     AS SELECT PartNumber, SalesPrice   ---
          FROM PurchDB.Parts               |--3
         WHERE SalesPrice > 1000        ---

View names are governed by the same rules as table names.

The columns in a view can have the same names as the columns in the table(s) they are based on, or they can have different names. You only need to include column names in a view definition if you are using multiple base tables which have duplicate column names or if you want to rename the columns. You enclose the names in parentheses, but omit data types, which depend on the types of the columns in the base tables.

The derivation of the view is a SELECT statement. In the previous example, the view is derived from the PurchDB.Parts table. Each row in the view contains a part number and a price; only rows for parts costing more than $1000 can be accessed through this view.

Unlike a table definition, a view definition does not require that you specify where to store rows. A view is a SELECT statement stored in the system catalog, not a physical copy of the data; ALLBASE/SQL extracts data from physical tables at the time you use the view. Views can be used for both retrieving and modifying data. Refer to "Updatability of Queries" in Chapter 3 “SQL Queries” for restrictions governing the use of a view to change data in a base table.

The WITH CHECK OPTION for views is described in Chapter 4 “Constraints, Procedures, and Rules”

Creating Indexes

You can create an index on one or more columns in a query. An index can provide quick access to the data in your tables. For information on indexes, refer to section "Understanding Data Access Paths" later in this section.

Specifying Integrity Constraints

Using integrity constraints helps to ensure that a database contains only valid data. Integrity constraints provide a way to check data within the database system rather than by coding elaborate validation checks within application programs. An integrity constraint is either a unique constraint, a referential constraint, or a check constraint. These constraints are described in Chapter 4 “Constraints, Procedures, and Rules”

Creating Procedures

You can define procedures to enforce relationships among database tables or to automate nearly any operation in the DBEnvironment. The following example shows creating a procedure to perform deletions from the SupplyPrice table in the sample DBEnvironment PartsDBE:

   CREATE PROCEDURE PurchDB.DelSupply(Part CHAR(16) NOT NULL) AS
   BEGIN
      DELETE FROM PurchDB.SupplyPrice
      WHERE PartNumber = :Part;
   END

The procedure definition includes a parameter declaration. The parameter Part accepts a value into the procedure at run time. You execute the procedure with a statement like the following example:

   EXECUTE PROCEDURE PurchDB.DelSupply ('1123-P-01')

The effect of the procedure is to delete all rows in the SupplyPrice table whose part number is 1123-P-01. For detailed information about creating and using procedures, refer to Chapter 4 “Constraints, Procedures, and Rules”

Creating Rules

Once a table is defined, you can create a rule that will execute a procedure whenever a specific firing condition is met. For example, you can define a rule that will execute a procedure to delete rows from the SupplyPrice table whenever a specific part is dropped from the Parts table in the sample DBEnvironment PartsDBE:

   CREATE RULE PurchDB.RemovePart AFTER DELETE FROM PurchDB.Parts
   EXECUTE PROCEDURE PurchDB.DelSupply (PartNumber)

Once the rule exists, you activate it by performing a DELETE:

   DELETE FROM PurchDB.Parts
   WHERE PartNumber = '1123-P-01'

For detailed information about creating and using rules, refer to Chapter 4 “Constraints, Procedures, and Rules”