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 Schema 3.4.1 Using the Dictionary When You Define a Schema 3.4.2 Specifying the Characteristics of the Schema 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 Authorization Identifier 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 Elements 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.3 Specifying the COMPUTED BY Clause 3.6.3.1 Specifying Default Values for Columns 3.6.3.2 Creating Constraints 3.7 Referential Integrity Through Constraints and Triggers 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.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 General Use of Storage Options in a Multifile Database 4.4.1 Achieving Optimal Performance for Different Kinds of Queries and Update Operations 4.4.2 Achieving Optimal Performance for Exact Match Retrieval 4.4.3 Achieving Optimal Performance for Range Retrieval 4.4.4 Achieving Optimal Performance for Join Operations or Update of Related Rows 4.4.5 Achieving Optimal Performance for the LIST OF VARBYTE Data Type 5 Maintaining and Restructuring a Database 5.1 Summary of SQL Options for Restructuring a Database 5.1.1 Altering and Deleting Domains 5.1.2 Altering and Deleting Tables 5.1.2.1 Deleting Tables 5.1.2.2 Altering or Deleting Tables That Contain Views or Indexes 5.1.2.3 Altering Columns in a Table 5.1.2.4 Altering the Name of a Table or the Name or Position of a Column 5.1.2.5 Altering Column Data Types 5.1.2.6 Altering and Deleting Constraints 5.1.2.7 Altering and Deleting References Between Tables 5.1.3 Deleting Views 5.1.4 Deleting Indexes 5.1.5 Deleting Database Schemas and Files 5.2 Duplicating Metadata 5.3 Making Storage and Memory Parameter Adjustments for a Database 5.3.1 How to Restructure a Database Without Using the Data Dictionary During Database Creation 6 Defining Database Protection 6.1 Access Privilege Sets 6.2 Differences Between ANSI/ISO- and ACL-Style Privileges 6.3 Declaring the Schema 6.4 Access Control Lists 6.4.1 Creating Access Control List Entries 6.4.2 Entering GRANT and REVOKE Statements 6.4.3 Building Access Control Lists 6.4.4 Putting the Access Control List in Order 6.5 Using a Command Procedure to Define Database Protection 6.6 Defining Protection for Tables 6.7 Defining Protection for Views 6.8 Defining Protection for Columns 6.9 Privileges and Security Controls Required for Database Operations 6.10 Restricting the Creation of Databases 6.11 Verifying Protection for a Database 6.12 Privileges with Override Capability 6.13 VMS Security Considerations 6.14 Securing Shareable Rdb/VMS Definitions in VAX CDD/Plus 7 Using VAX Rdb/VMS with VAX CDD/Plus 7.1 Overview of CDD/Plus 7.1.1 CDD/Plus Dictionary Naming Conventions 7.1.2 Criteria for Using CDD/Plus with Rdb/VMS Databases 7.2 Creating New Dictionary Definitions 7.3 Modifying Dictionary Definitions and Database Files 7.3.1 Modifying Dictionary Definitions Using CDO 7.3.2 Updating the Dictionary Using the Database Files 7.3.2.1 Modifying Dictionary Definitions Using SQL 7.3.2.2 Creating Dictionary Definitions Using SQL 7.3.3 Updating the Database File Using the Dictionary Definitions 7.4 Deleting Definitions Using SQL and CDO 7.5 Deciding Whether or Not to Require CDD/Plus A Definitions for Sample Personnel Databases EXAMPLES 1-1 Creating the Sample Multifile Database 1-2 Declaring the Sample PERSONNEL Database 1-3 Declaring the Sample MF_PERSONNEL Database 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 Triggers 3-14 Creating a Sorted Index 3-15 Creating the CURRENT_JOB View 3-16 Creating the CURRENT_SALARY View 3-17 Creating the CURRENT_INFO View 4-1 Defining Concealed 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 Partitioning the JOB_HISTORY Table 4-5 Partitioning a Hashed Index 4-6 Creating a Hashed Index 4-7 Optimizing Performance for Range Retrieval Queries 4-8 Clustering Related Rows from the EMPLOYEES and JOB_HISTORY Tables 5-1 Modifying Domain Definitions 5-2 Altering Tables 5-3 Deleting Tables 5-4 Altering Tables That Contain Views and Indexes 5-5 Altering Columns in a Table 5-6 Changing Data Types in a Table 5-7 Altering and Deleting Constraints 5-8 Using ALTER SCHEMA, ALTER INDEX, and ALTER STORAGE MAP Statements 5-9 Using EXPORT and IMPORT Statements 6-1 Default Protection 6-2 Declaring the Schema 6-3 Issuing the DECLARE SCHEMA Statement in a Command Procedure 6-4 Denying Privileges to a Group of Users 6-5 Issuing SHOW PROTECTION Statements 6-6 Sample Command Procedure to Create ACLs 6-7 Sample Command Procedure to Modify Default ACLs 6-8 Creating a View in Order to Restrict Access to the Table 6-9 Restricting Access with View Definitions 6-10 Column Protection 6-11 Verifying ACLs 6-12 Issuing the SHOW PRIVILEGES Statement 7-1 Defining Shareable Fields in CDO 7-2 Checking Field Definitions 7-3 Defining CDO Records 7-4 Using CDO Definitions to Create a Schema in SQL 7-5 Modifying Dictionary Definitions Using the INTEGRATE Statement with ALTER DICTIONARY Clause 7-6 Storing Existing Database System File Definitions in the Dictionary Using the INTEGRATE Statement with CREATE PATHN 7-7 Updating the Database File to Match the Dictionary Definitions A-1 Definitions for the Single-File Personnel Database A-2 Definitions for the Multifile Personnel 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 6-1 The Relationship Between Generic-Style Privileges and ACL- and ANSI/ISO-Style Privileges 6-2 Privileges to Access Rdb/VMS Databases 7-1 Centralized Design with VAX CDD/Plus 7-2 Shareable Fields in VAX CDD/Plus TABLES 1-1 The Entity: EMPLOYEES Table, Representing Data at the Logical Level 1-2 Files that Create Sample Databases 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 5-1 Requirements for Making Metadata Updates 5-2 Adjusting Storage and Memory Use Parameters 6-1 Privileges Required for Database Operations 6-2 Security Controls Required to Use RMU Functions 6-3 Privilege Override Capability 7-1 Summary of CDO Tracking Commands