CONTENTS

  Title Page

  Copyright Page

  Preface

  Technical Changes and New Features

  1      Designing a Relational Database

  1.1     Understanding Relational Concepts and Terminology

  1.2     Choosing a Design Method

  1.3     Understanding Logical and Physical Database Design
    1.3.1      Logical Design Concepts
    1.3.2      Physical Design Concepts
    1.3.3      Rdb/VMS On-Disk Structures
    1.3.4      Storage Methods
    1.3.5      Retrieval Methods

  1.4     Introducing the Sample Databases

  2      Making a Logical Database Design

  2.1     Analyzing Requirements

  2.2     Translating Requirements into Data Items

  2.3     Mapping Relationships Among Columns and Tables

  2.4     Normalizing Tables

  2.5     Analyzing Transactions
    2.5.1      Tracing Transaction Paths Through the Logical Model
    2.5.2      Prototype Transactions in SQL

  2.6     Archiving Information

  2.7     Developing a Volume Table

  3      Defining a Database

  3.1     Overview of Database Definition

  3.2     Summary of Database Elements That SQL Defines

  3.3     Options for Executing Statements That Define a Database

  3.4     Creating the Database
    3.4.1      Using the Dictionary When You Define a Database
    3.4.2      Specifying the Characteristics of the Database
      3.4.2.1      Choosing Single-File or Multifile Storage
      3.4.2.2      Specifying Storage Areas for Multifile Databases
      3.4.2.3      Choosing Among Snapshot File Options
      3.4.2.4      Specifying Default Allocation for Disk Space and Memory
      3.4.2.5      Specifying an Alias
      3.4.2.6      Setting Database Key (Dbkey) Scope
    3.4.3      Looking for More Detailed Information About Database Definition

  3.5     Creating Domains
    3.5.1      Creating Domains Based on Data Dictionary Fields
    3.5.2      Specifying Characteristics of Domains
      3.5.2.1      Naming Domains
      3.5.2.2      Specifying Data Types
      3.5.2.3      Specifying Default Values for Domains
      3.5.2.4      Specifying Collating Sequences
      3.5.2.5      Specifying SQL and DATATRIEVE Formatting Clauses

  3.6     Creating Tables
    3.6.1      Creating Tables Based on Data Dictionary Definitions
    3.6.2      Specifying Elements of Tables
      3.6.2.1      Specifying the Data Type of Columns
      3.6.2.2      Specifying the COMPUTED BY Clause
      3.6.2.3      Specifying Default Values for Columns
      3.6.2.4      Creating Constraints

  3.7     Referential Integrity Through Constraints and Triggers
    3.7.1      Using Constraints to Enforce Referential Integrity
    3.7.2      Using Triggers to Enforce Referential Integrity

  3.8     Creating Indexes
    3.8.1      Deciding Between an Index and a Constraint to Enforce Unique Column Values
    3.8.2      Deciding When Indexes Are Beneficial
    3.8.3      Using Compressed Indexes

  3.9     Creating Views
    3.9.1      Creating the CURRENT_JOB View
    3.9.2      Creating the CURRENT_SALARY View
    3.9.3      Creating the CURRENT_INFO View
      3.9.3.1      Creating Views to Calculate Dates

  3.10    Verifying the Definition Phase

  3.11    Using Snapshot Files

  4      Implementing a Multifile Database

  4.1     Deciding on a Storage Design for Your Multifile Database

  4.2     Assigning Tables and Indexes to Storage Areas

  4.3     Choosing Uniform or Mixed Page Format
    4.3.1      Advantages of Uniform Page Format
    4.3.2      Advantages of Mixed Page Format

  4.4     Choosing Read/Write, Read-Only, or Write-Once Storage Areas

  4.5     Specifying Storage Options in a Multifile Database
    4.5.1      Achieving Optimal Performance for Different Kinds of Queries and Update Operations
    4.5.2      Achieving Optimal Performance for Exact Match Retrieval
      4.5.2.1      Setting Database and Storage Area Parameters When Using Hashed Indexes
      4.5.2.2      Understanding the Page Overhead and Record Types
      4.5.2.3      Calculating the Size of Fixed and Variable Page Overhead
      4.5.2.4      Calculating the Size of Hashed Index Structures
      4.5.2.5      Calculating the Size of Hashed Indexes for the MF_PERSONNEL Database
      4.5.2.6      Calculating the Size of Data Rows
      4.5.2.7      Calculating the Page Size
      4.5.2.8      Calculating the File Allocation Size
    4.5.3      Achieving Optimal Performance for Range Retrieval
    4.5.4      Achieving Optimal Performance for Join Operations or Update of Related Rows
    4.5.5      Achieving Optimal Performance for the LIST OF VARBYTE Data Type
      4.5.5.1      Storing List Data in Isolation
      4.5.5.2      Storing List Data on WORM Devices

  5      Implementing a Multischema Database

  5.1     Understanding Multischema Databases

  5.2     Creating Multischema Databases

  5.3     Creating Catalogs

  5.4     Creating Schemas

  5.5     Naming Elements
    5.5.1      Using Qualified Names
    5.5.2      Using Internal and External Names

  5.6     Using Aliases

  5.7     Creating Schema Elements
    5.7.1      Creating Domains in the PERSONNEL Schema
    5.7.2      Creating Tables in Multischema Databases
    5.7.3      Creating Views in Multischema Databases
    5.7.4      Creating Triggers in Multischema Databases

  6      Loading Data

  6.1     Data Loading Strategies

  6.2     Using the PLACE Statement
    6.2.1      Using the PLACE Statement to Get the Dbkey for Each Row to Be Stored
    6.2.2      Sorting the Dbkeys in Ascending Order
    6.2.3      Using the Dbkeys to Read the Rows in Sorted Order and Store Them in the Database

  6.3     Modifying the Database to Load Data
    6.3.1      Adjusting Database-Wide Parameters
    6.3.2      Adjusting Storage Area Parameters
    6.3.3      Modifying Tables
    6.3.4      Modifying Indexes
    6.3.5      Modifying Storage Maps

  6.4     Placement and Clustering Strategies Using Hashed Indexes
    6.4.1      Separate Areas, No Placement Clause
    6.4.2      Separate Areas, with Placement Clause
    6.4.3      Same Area, with Placement Clause (One I/O Operation)
    6.4.4      Clustering:  Add Child Rows, Separate Storage Area, with Placement Clause
    6.4.5      Shadowing:  Child and Parent in Separate Areas, with Placement Clause
    6.4.6      Clustering:  Child and Parent Rows and Hashed Index All in the Same Area, with Placement Clause

  6.5     Troubleshooting Data Load Operations

  6.6     Loading Data from a Record Management Services (RMS) File Using SQL Programs
    6.6.1      Using the SQL Module Language and BASIC to Load Data
    6.6.2      Using the SQL Module Language, COBOL, and Data Dictionary Definitions to Load Data
    6.6.3      Using SQL Precompiled C Programs to Load Data

  6.7     Loading and Unloading Data Using the RMU/LOAD and RMU/UNLOAD Commands
    6.7.1      Understanding the Format of the RMS Record Definition File
    6.7.2      Loading Data into a Database Table from an RMS File
    6.7.3      Restructuring Databases Using RMU/LOAD and RMU/UNLOAD
    6.7.4      Loading and Unloading Data from Rdb/VMS Databases
    6.7.5      Loading Data from One Database to Another
    6.7.6      Improving Performance While Using the RMU/LOAD Command

  6.8     Changing Database Definitions Following a Data Load Operation

  6.9     Using DATATRIEVE to Load Data

  6.10    Importing an Rdb/ELN Database into an Rdb/VMS Database

  7      Maintaining and Restructuring a Database

  7.1     Summary of SQL Options for Restructuring a Database

  7.2     Enabling After-Image Journaling

  7.3     Changing Data Definitions While Users Are Attached to the Database

  7.4     Altering and Deleting Domains

  7.5     Altering and Deleting Tables
    7.5.1      Deleting Tables
    7.5.2      Altering or Deleting Tables That Contain Views or Indexes
    7.5.3      Altering Columns in a Table
    7.5.4      Altering the Name of a Table or the Name or Position of a Column
    7.5.5      Altering Column Data Types
    7.5.6      Altering and Deleting Constraints
    7.5.7      Altering and Deleting References Between Tables
    7.5.8      Altering and Deleting Tables in Multischema Databases

  7.6     Deleting Views

  7.7     Deleting Indexes

  7.8     Deleting Schemas in Multischema Databases

  7.9     Deleting Catalogs in Multischema Databases

  7.10    Deleting Databases, Database Files, and Dictionary Definitions

  7.11    Moving Databases and Database Files

  7.12    Duplicating Metadata

  7.13    Adjusting Database Storage and Memory Parameters
    7.13.1     Adjusting Storage Area Parameters to Cluster Rows
    7.13.2     Adjusting RDB$SYSTEM Storage Areas
    7.13.3     Changing Read/Write Storage Areas to Read-Only Storage Areas
    7.13.4     Moving Read/Write Data to Write-Once Storage Areas

  7.14    Opening Databases

  7.15    Restructuring Databases Without Using the Data Dictionary

  8      Defining Database Protection

  8.1     Understanding Access Privilege Sets (APSs)

  8.2     Differences Between ANSI/ISO- and ACL-Style Privileges

  8.3     Attaching to the Database

  8.4     Access Control Lists (ACLs)
    8.4.1      Creating Access Control List Entries
    8.4.2      Entering GRANT and REVOKE Statements
    8.4.3      Building Access Control Lists
    8.4.4      Putting the Access Control List in Order

  8.5     Using a Command Procedure to Define Database Protection

  8.6     Defining Protection for Tables

  8.7     Defining Protection for Views

  8.8     Defining Protection for Columns

  8.9     Privileges and Security Controls Required for Database Operations

  8.10    Restricting the Creation of Databases

  8.11    Verifying Protection for a Database

  8.12    Privileges with Override Capability

  8.13    VMS Security Considerations

  8.14    Securing Shareable Rdb/VMS Definitions in VAX CDD/Plus

  9      Using VAX Rdb/VMS with VAX CDD/Plus

  9.1     Overview of CDD/Plus
    9.1.1      Data Dictionary Naming Conventions
    9.1.2      Criteria for Using the Data Dictionary with Rdb/VMS Databases

  9.2     Creating New Dictionary Definitions

  9.3     Modifying Dictionary Definitions and Database Files
    9.3.1      Modifying Dictionary Definitions Using CDO
    9.3.2      Updating the Dictionary Using the Database Files
      9.3.2.1      Modifying Dictionary Definitions Using SQL
      9.3.2.2      Creating Dictionary Definitions Using SQL
    9.3.3      Updating the Database File Using the Dictionary Definitions

  9.4     Deleting Definitions Using SQL and CDO

  9.5     Changing the Database File Name Using CDD/Plus

  9.6     Deciding Whether or Not to Require CDD/Plus

  A   Definitions for Sample Personnel Databases

  EXAMPLES

  2-1        Modeling a Read-Only Transaction

  3-1        Creating the Database

  3-2        Creating Domains Using the FROM Path-Name Clause

  3-3        Creating Domains

  3-4        Specifying Default Values

  3-5        Defining Fields and Records with CDD/Plus

  3-6        Creating a Table Using the FROM Path-Name Clause

  3-7        Creating Tables

  3-8        Creating a Table with Columns Based on Domains

  3-9        Specifying Default Values for Columns

  3-10       Creating Column Constraints

  3-11       Creating Table Constraints Based on Other Tables

  3-12       Displaying Table Constraints

  3-13       Creating a Trigger to Delete All Information About an Employee

  3-14       Creating a Trigger to Prevent Deleting a Row

  3-15       Creating a Sorted Index

  3-16       Creating the CURRENT_JOB View

  3-17       Creating the CURRENT_SALARY View

  3-18       Creating the CURRENT_INFO View

  3-19       Creating a View That Contains Records for Those Employees with 15 or More Years of Service

  4-1        Defining Logical Names for the Multifile Sample Database

  4-2        An SQL Procedure That Creates the Multifile SAMPLE_DB Sample Database

  4-3        Assigning a Table and an Index to a Storage Area

  4-4        Creating Write-Once Storage Areas on WORM Optical Devices

  4-5        Creating Storage Maps for Write-Once Storage Areas

  4-6        Partitioning the JOB_HISTORY Table

  4-7        Partitioning a Hashed Index

  4-8        Creating a Hashed Index

  4-9        Optimizing Performance for Range Retrieval Queries

  4-10       Clustering Related Rows from the EMPLOYEES and JOB_HISTORY Tables

  5-1        Creating a Multischema Database

  5-2        Creating a Catalog

  5-3        Creating a Schema

  5-4        Creating a Schema with Subordinate Elements

  5-5        Displaying Internal and External Element Names

  5-6        Specifying External Names

  5-7        Using an Alias

  5-8        Creating Domains in Multischema Databases

  5-9        Creating Tables That Refer to Objects in Other Schemas

  5-10       Creating Views That Refer to Tables in Other Schemas

  5-11       Creating Triggers That Refer to Objects in Other Schemas

  6-1        Using the PLACE Statement in RDO

  6-2        Using the PLACE Statement for Reading Data from an Rdb/VMS Database Table

  6-3        Placing Rows and the Hashed Index in Separate Storage Areas and Not Using the Placement Clause

  6-4        Placing Rows and the Hashed Index in Separate Storage Areas and Using the Placement Clause

  6-5        Placing Data Rows and the Hashed Index in the Same Storage Area and Using the Placement Clause

  6-6        Placing Parent and Child Rows in the Same Storage Area and the Hashed Indexes in a Separate Storage Area, Using the

  6-7        Placing Parent Rows and Hashed Indexes in One Storage Area and Child Rows in a Separate Storage Area, Using the Pla

  6-8        Placing Parent and Child Rows and Hashed Indexes All in the Same Storage Areas and Using the Placement Clause

  6-9        A BASIC Program That Calls an SQL Module to Load Data

  6-10       An SQL Module for Loading Data

  6-11       A COBOL Program That Calls an SQL Module to Load Data

  6-12       Loading Data Using an SQL Module

  6-13       Loading Data Using an SQL Precompiled C Program

  6-14       Unloading the COLLEGES Table Using the RMU/UNLOAD Command

  6-15       Displaying the Contents of the COLLEGES.RRD File

  6-16       Displaying the Contents of the COLLEGES.UNL Data File

  6-17       Loading Three More Rows into the COLLEGES Table Using the RMU/LOAD Command

  6-18       Displaying the Contents of the COLLEGES Table

  6-19       Displaying the Contents of the ALL_COLLEGES.RRD File, Showing the Modified Record Definition

  6-20       Loading the ALL_COLLEGES Table with Data Rows Using the RMU/LOAD Command

  6-21       Restructuring a Table Using RMU/LOAD and RMU/UNLOAD

  6-22       Creating a Command Procedure to Unload Data

  6-23       Displaying the Contents of the Command Procedure to Unload the Database

  6-24       Unloading Data Using RMU/UNLOAD

  6-25       Creating a Command Procedure to Load Data

  6-26       Creating a View to Help in Loading Data

  6-27       Editing a Command Procedure to Load Data

  6-28       Loading Data Using RMU/LOAD

  6-29       Creating the Command File

  6-30       Defining a Database from DATATRIEVE Record Definitions

  6-31       Loading the Data Using DATATRIEVE

  6-32       DATATRIEVE Domain with Repeating Groups

  6-33       Defining the EMPLOYEE_DEPENDENTS Table

  6-34       Using DATATRIEVE to Transfer Data

  7-1        Enabling After-Image Journaling

  7-2        Modifying Domain Definitions

  7-3        Altering Tables

  7-4        Deleting Tables

  7-5        Altering Tables That Contain Views and Indexes

  7-6        Altering Columns in a Table

  7-7        Altering the Default Value of an Existing Column

  7-8        Adding Columns with Default Values to Tables

  7-9        Adding Columns Without Propagating Default Values to Previously Stored Rows

  7-10       Changing Data Types in a Table

  7-11       Altering and Deleting Constraints

  7-12       Using ALTER DATABASE, ALTER INDEX, and ALTER STORAGE MAP Statements

  7-13       Using EXPORT and IMPORT Statements

  7-14       Changing the Read/Write Status of the DEPARTMENTS Storage Area to Read-Only Access

  7-15       Moving List Data from a Read/Write Storage Area to a Write-Once Storage Area

  7-16       Moving List Data from a Write-Once Storage Area to a Read/Write Storage Area

  7-17       Adding New List Data to a Write-Once Storage Area

  8-1        Defining Default Protection

  8-2        Attaching to the Database

  8-3        Attaching to a Database in a Command Procedure

  8-4        Denying Privileges to a Group of Users

  8-5        Issuing SHOW PROTECTION Statements

  8-6        Sample Command Procedure to Create ACLs

  8-7        Sample Command Procedure to Modify Default ACLs

  8-8        Creating a View in Order to Restrict Access to the Table

  8-9        Restricting Access with View Definitions

  8-10       Column Protection

  8-11       Verifying ACLs

  8-12       Issuing the SHOW PRIVILEGES Statement

  9-1        Defining Shareable Fields in CDO

  9-2        Checking Field Definitions

  9-3        Defining CDO Records

  9-4        Using CDO Definitions to Create a Database in SQL

  9-5        Modifying Dictionary Definitions Using the INTEGRATE Statement with ALTER DICTIONARY Clause

  9-6        Storing Existing Database System File Definitions in the Dictionary Using the INTEGRATE Statement with CREATE PATHN

  9-7        Updating the Database File to Match the Dictionary Definitions

  9-8        Using CDD/Plus to Change the Database File Name

  A-1        Definitions for the Single-File PERSONNEL Database

  A-2        Definitions for the Multifile MF_PERSONNEL Database

  A-3        Definitions for the Multischema CORPORATE_DATA Database

  FIGURES

  1-1        The EMPLOYEES Table

  1-2        All Tables in One File (Single-File Database)

  1-3        Separate Files of a Multifile Database

  2-1        An Entity-Relationship (E-R) Map

  2-2        Transaction Paths for the MF_PERSONNEL Sample Database

  2-3        Consolidated Transaction Map

  3-1        The Logical Model of the Sample Databases

  3-2        Snapshot Transaction Time Line

  5-1        Multischema Database with Multiple Catalogs and Schemas

  8-1        The Relationship Between Generic-Style Privileges and ACL- and ANSI/ISO-Style Privileges

  8-2        Privileges to Access Rdb/VMS Databases

  9-1        Centralized Design with VAX CDD/Plus

  9-2        Shareable Fields in VAX CDD/Plus

  TABLES

  1-1        The Entity:  EMPLOYEES Table, Representing Data at the Logical Level

  2-1        The DEPARTMENTS Table

  2-2        The JOB_HISTORY Table

  2-3        The One-to-One and One-to-Many Relationships That Exist in the Sample Personnel Database

  2-4        A One-to-Many Relationship Example

  2-5        A One-to-One Relationship Example

  2-6        A Many-to-Many Relationship Example

  2-7        A One-to-One Reflexive Relationship Example

  2-8        Volume Table for the MF_PERSONNEL Database

  3-1        Storage Design Expertise as Required by Database Size and Complexity

  4-1        Calculating the Fixed and Variable Overhead for a Page

  4-2        Calculating the Size of Hashed Indexes

  4-3        Calculating the Size of Hashed Indexes for the MF_PERSONNEL Database

  4-4        The EMPLOYEES Row Columns and Column Sizes

  4-5        The JOB_HISTORY Row Columns and Column Sizes

  4-6        Calculating the Fixed Overhead for a Page

  4-7        Calculating the Data Row Overhead for a Page

  4-8        Calculating the Page Size

  4-9        Calculating the File Allocation Size

  4-10       Calculating the File Allocation Size to Store 100 Data Pages

  4-11       Calculating the SPAM Pages and Adding These Pages to the Estimated File Allocation Size

  7-1        Requirements for Making Metadata Updates

  7-2        Requirements for Making Metadata Updates to Database-Wide Parameters

  7-3        Adjusting Storage and Memory Use Parameters

  8-1        Privileges Required for Database Operations

  8-2        Security Controls Required to Use RMU Functions

  8-3        Privilege Override Capability

  9-1        Summary of CDO Tracking Commands