Implementing Your Design › Implementing the Design › Implementation Steps

Implementation Steps

1. Decide on naming conventions for:
   • Tables
   • Columns
   • Constraints
   • Indexes
2. Create the database.
3. Create the logical definition of your database using SQL DDL statements.
4. Copy the segment definition from the system dictionary into the application dictionary in which you will define your tables.

More Information

For more information on physical definition and creation, see the CA IDMS Database Administration Guide.

Steps 1 through 3 are described in more detail below.

You are now ready to define the tables and other logical components of your database.

Naming conventions

Database tables and columns should have short, meaningful names. Table names are up to 18 characters in length. Columns within tables can have names of up to 32 characters. Underscores are usually used between tokens within a name (for example, SKILL_LEVEL). Hyphens should be avoided since names containing hyphens must be enclosed in double quotes when used in SQL syntax.

Referential constraints are typically named by concatenating the names of the two related tables. For example, the referential constraint between the EMPLOYEE table and the DEPARTMENT table becomes DEPT_EMPLOYEE. This convention may need to be modified, however, since constraint names can be no more than 18 characters.

Indexes must also be named. Names up to 18 characters are permitted.

Creating the database

A database is represented by a segment. To create a database, you:

1. Define the segment in the system dictionary using CREATE SEGMENT, FILE, and AREA statements.
2. Include the segment definition in a DMCL and punch and link edit it to a load or core image library.
3. Allocate the operating system files defined in the segment and initialize them using the FORMAT utility statement.

Creating the logical database definition

The following examples illustrate how the logical components of your design are translated into SQL DDL.

For complete DDL syntax, see CA IDMS SQL Reference Guide.

CREATE SCHEMA statement

A schema groups one or more tables together. Typically all tables associated with a single database, or with a specific application within a single database, are defined within one schema. The statement below defines the schema, EMPSCHM.

   CREATE SCHEMA EMPSCHM;   ◄----------- Names the schema

CREATE TABLE statement

Each entity in your design is defined as an SQL table. The definition of a table includes:

• The name of the table
• A list of columns (data elements), including the data type of each, whether a default has been designated, and whether or not nulls are allowed
• An optional check constraint that limits the data that can be maintained in the database for a particular column or columns
• The name of the area in which the data for the table is to be stored

The following statement defines the table, SALARY GRADE.

  CREATE TABLE EMPSCHM.SALARY_GRADE   ◄--------- Names the table

    (SALARY_GRADE     UNSIGNED NUMERIC(2,0)      NOT NULL, ┐
     JOB_ID           UNSIGNED NUMERIC(4,0)      NOT NULL, │ Names the
     HOURLY_RATE      UNSIGNED DECIMAL(7,2)              , │ columns and
     SALARY_AMOUNT    UNSIGNED DECIMAL(10,2)             , │ assigns column
     BONUS_PERCENT    UNSIGNED DECIMAL(7,3)              , │ characteristics
     COMM_PERCENT     UNSIGNED DECIMAL(7,3)              , │
     OVERTIME_RATE    UNSIGNED NUMERIC(5,2)                ┘

  CHECK ( (HOURLY_RATE IS NOT NULL AND SALARY_AMOUNT IS NULL)
           OR (HOURLY_RATE IS NULL AND SALARY_AMOUNT IS NOT NULL) ) )

    IN SQLDEMO.EMP_DEMO_REGION;    ◄---- Names the area qualified
                                           with a segment name

Null values

SQL allows you to represent the absence of a column value in a particular row by assigning NULL to the column. This could happen because the value is not known yet (such as a credit rating when a credit check has not yet been completed for a new customer) or because it isn't applicable (such as phone number for an employee with no phone). Null values may receive special treatment in certain SQL DML statements. For example, the COUNT aggregate function doesn't include null values in a particular column when counting the number of rows based on that column.

CREATE INDEX statement

The definition of an index includes:

• The name of the index
• The name of the table and columns in the table on which the index is placed
• The area in which the index is to be stored
• The UNIQUE and/or clustering specification
• Additional physical tuning options

The statement below defines the EMP_NAME_NDX index.

CREATE EMPSCHM.INDEX EMP_NAME_NDX     ◄------- Names the index
    ON EMPSCHM.EMPLOYEE(EMP_LAST_NAME, EMP_FIRST_NAME) ◄-- Names the columns
    IN SQLDEMO.INDXAREA;   ◄--------- Names the area qualified with
                                      segment name

CREATE CONSTRAINT statement

In an SQL-defined database, relationships are the vehicle for the enforcement of referential integrity. The system automatically ensures that the foreign key columns of child rows are either null or match the primary key of an existing parent row.

Linked and unlinked relationships are implemented as constraints. The definition of a constraint includes:

• The name of the constraint
• The names of the two tables it relates
• The referenced and referencing columns
• A specification of whether the constraint is linked or unlinked
• A specification of whether child entity occurrences are to be clustered based on this relationship
• Additional tuning options

The statement below defines the EMP_EXPERTISE constraint.

   CREATE CONSTRAINT EMPSCHM.EMP_EXPERTISE  ◄--- Names the referential constraint

           EMPSCHM.EXPERTISE  (EMP_ID)  REFERENCES ┐ Names referenced and referencing
           EMPSCHM.EMPLOYEE   (EMP_ID)             ┘ tables and columns

               LINKED CLUSTERED;  ◄----- Specifies type of referential constraint

Creating views

SQL-defined views can be used to:

• Implement security because they can restrict access to a subset of the rows and columns within a table
• Provide a shorthand means of referring to complex SELECT statements

Below are some sample views that might be created for the Commonweather database:

   CREATE VIEW EMPSCHM.SS_FORMAT
          (EMP_ID, EMP_LAST_NAME, EMP_FIRST_NAME, SS1, SS2, SS3)
          AS SELECT EMP_ID, EMP_LAST_NAME, EMP_FIRST_NAME,
                    SUBSTR(SS_NUMBER, 1, 3), SUBSTR(SS_NUMBER, 4, 2),
                    SUBSTR(SS_NUMBER, 6, 4)
             FROM EMPSCHM.EMPLOYEE;
 
   CREATE VIEW EMPSCHM.EMP_HOME_INFO
          AS SELECT EMP_ID, EMP_LAST_NAME, EMP_FIRST_NAME, STREET,
                    CITY, STATE, ZIP_CODE, PHONE
             FROM EMPSCHM.EMPLOYEE;
 
   CREATE VIEW EMPSCHM.EMP_WORK_INFO
          AS SELECT EMP_ID, START_DATE, TERMINATION_DATE
             FROM EMPSCHM.EMPLOYEE;

Table and view security

If CA IDMS/DB internal security is in effect, GRANT statements must be used to allow others, besides the owner, to access the tables and views within a schema. Every schema has an owner. The initial owner of a schema is the user who created it. Ownership can be transferred to another individual using the TRANSFER OWNERSHIP statement.

For more information on these statements, see CA IDMS SQL Reference Guide.