CONTENTS Title Page Copyright Page Send Us Your Comments 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 DEC Rdb 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 3.3 Options for Executing Statements That Define a Database 3.4 Using the Repository When You Define a Database 3.5 Creating the Database and Specifying Its Characteristics 3.5.1 Specifying a Database with Subordinate Elements 3.5.2 Creating Databases Using Multiple Character Sets 3.5.3 Specifying an Alias 3.5.4 Reserving Slots for After-Image Journal Files 3.5.5 Choosing Single-File or Multifile Storage 3.5.6 Reserving Slots for Storage Areas 3.5.7 Specifying Storage Areas for Multifile Databases 3.5.8 Compressing System Indexes 3.5.9 Choosing Among Snapshot File Options 3.5.10 Specifying Default Allocation for Disk Space and Memory 3.5.11 Setting Database Key (Dbkey) Scope 3.5.12 Looking for More Detailed Information About Database Definition 3.6 Naming Database Elements 3.7 Using Data Types 3.8 Specifying the Length of Characters in Octets or Characters 3.9 Creating Domains 3.9.1 Creating Domains Based on Repository Fields 3.9.2 Specifying Characteristics of Domains 3.9.2.1 Naming Domains 3.9.2.2 Specifying Data Types for Domains 3.9.2.3 Specifying Character Sets for Domains 3.9.2.4 Specifying Default Values for Domains 3.9.2.5 Specifying Collating Sequences 3.9.2.6 Specifying SQL and DATATRIEVE Formatting Clauses 3.9.2.7 Specifying Domain Constraints 3.10 Creating Tables 3.10.1 Creating Tables Based on Repository Definitions 3.10.2 Specifying Elements of Tables 3.10.2.1 Specifying the Data Type of Columns 3.10.2.2 Assigning Character Sets to Columns 3.10.2.3 Specifying the COMPUTED BY Clause 3.10.2.4 Specifying Default Values for Columns 3.10.2.5 Creating Constraints 3.11 Referential Integrity Through Constraints and Triggers 3.11.1 Using Constraints to Enforce Referential Integrity 3.11.2 Using Triggers to Enforce Referential Integrity 3.12 Creating Triggers to Invoke External Functions 3.13 Creating Indexes 3.13.1 Creating Sorted Indexes 3.13.2 Creating Hashed Indexes 3.13.3 Deciding Between an Index and a Constraint to Enforce Unique Column Values 3.13.4 Deciding When Indexes Are Beneficial 3.13.5 Creating Indexes Concurrently 3.13.6 Creating Compressed Indexes 3.13.6.1 Creating Run-Length Compressed Indexes 3.13.6.2 Creating a SIZE IS Segment-Truncated Index 3.13.6.3 Creating a Mapping Values Compressed Index 3.14 Creating Views 3.14.1 Creating the CURRENT_JOB View 3.14.2 Creating the CURRENT_SALARY View 3.14.3 Creating the CURRENT_INFO View 3.14.3.1 Creating Views to Calculate Dates 3.15 Verifying the Definition Phase 4 Implementing a Multifile Database 4.1 Deciding on a Storage Design for Your Multifile Database 4.2 Understanding General Storage Options for a Multifile Database 4.3 Assigning Tables and Indexes to Storage Areas 4.4 Choosing Uniform or Mixed Page Format 4.4.1 Advantages of Uniform Page Format 4.4.2 Advantages of Mixed Page Format 4.5 Choosing Read/Write, Read-Only, or Write-Once Storage Areas 4.6 Achieving Optimal Performance for Different Kinds of Queries and Update Operations 4.6.1 Achieving Optimal Performance for Exact Match Retrieval 4.6.2 Achieving Optimal Performance for Range Retrieval 4.6.3 Achieving Optimal Performance for Join Operations or Update of Related Rows 4.6.4 Achieving Optimal Performance for the LIST OF VARBYTE Data Type 4.6.4.1 Storing List Data in Isolation 4.6.4.2 Storing List Data Randomly or Sequentially 4.6.4.3 Storing List Data on WORM Devices 4.7 Setting Sorted Index Characteristics for Performance 4.8 Setting Database and Storage Area Parameters When Using Hashed Indexes 4.8.1 Understanding the Page Overhead and Record Types 4.8.2 Calculating the Size of Fixed and Variable Page Overhead 4.8.3 Calculating the Size of Hashed Index Structures 4.8.4 Calculating the Size of Hashed Indexes for the MF_PERSONNEL Database 4.8.5 Calculating the Size of Data Rows 4.8.6 Calculating the Page Size 4.8.7 Calculating the File Allocation Size 4.9 Placement and Clustering Strategies Using Hashed Indexes 4.9.1 Separate Areas, No Placement Clause 4.9.2 Separate Areas, with Placement Clause 4.9.3 Same Area, with Placement Clause (One I/O Operation) 4.9.4 Clustering: Add Child Rows, Separate Storage Area, with Placement Clause 4.9.5 Shadowing: Child and Parent in Separate Areas, with Placement Clause 4.9.6 Clustering: Child and Parent Rows and Hashed Index All in the Same Area, with Placement Clause 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 Stored Names and SQL 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 Improving Performance When Loading Data 6.2 Using the PLACEMENT ONLY RETURNING DBKEY Clause 6.2.1 Using the INSERT Statement to Get the Dbkey for Each Row to Be Stored 6.2.2 Sorting the Dbkeys in Ascending Order 6.2.3 Reading the Rows in Sorted Order and Storing 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 Troubleshooting Data Load Operations 6.5 Loading Data from a Record Management Services (RMS) File Using SQL Programs 6.5.1 Using the SQL Module Language and BASIC to Load Data 6.5.2 Using the SQL Module Language, COBOL, and Repository Definitions to Load Data 6.5.3 Using SQL Precompiled C Programs to Load Data 6.6 Loading and Unloading Data Using the RMU/LOAD and RMU/UNLOAD Commands 6.6.1 Understanding the Format of the RMS Record Definition File 6.6.2 Loading Data into a Database Table from an RMS File 6.6.3 Restructuring Databases Using RMU/LOAD and RMU/UNLOAD 6.6.4 Loading and Unloading Data from DEC Rdb Databases 6.6.5 Loading Data from One Database to Another 6.6.6 Improving Performance While Using the RMU/LOAD Command 6.7 Changing Database Definitions Following a Data Load Operation 6.8 Using DATATRIEVE to Load Data 7 Modifying Databases and Storage Areas 7.1 Modifying Databases and Storage Areas - A Summary 7.2 Modifying Data Definitions While Users Are Attached to the Database 7.3 Modifying Database Characteristics 7.3.1 Modifying the OPEN IS Option of the Database 7.3.2 Enabling After-Image Journaling 7.3.3 Adding After-Image Journal Files 7.3.4 Modifying Allocation Characteristics for After-Image Journal Files 7.3.5 Modifying the JOURNAL FAST COMMIT Options 7.3.6 Modifying Extent Values of the Database 7.3.7 Modifying the Maximum Number of Users 7.3.8 Modifying the Maximum Number of VMScluster Nodes 7.3.9 Modifying Database Lock Characteristics 7.3.10 Selecting Locking Levels for Storage Areas 7.3.11 Enabling or Disabling Global Buffers 7.3.12 Modifying the Number of Local Database Buffers 7.3.13 Modifying the Number of Database Recovery Buffers 7.3.14 Controlling Snapshot Files 7.3.15 Using Deferred Snapshot Files 7.3.16 Modifying Extent Characteristics for the Snapshot Files 7.4 Modifying the Requirement for Using the Repository 7.5 Modifying Storage Areas and Storage Area Parameters 7.5.1 Adding New Storage Areas for Multifile Databases 7.5.2 Adjusting Storage Area Parameters to Cluster Rows 7.5.3 Adjusting RDB$SYSTEM Storage Areas 7.5.4 Moving Storage Areas 7.5.5 Moving Read/Write Data to Write-Once Storage Areas 7.5.6 Moving Data from a Write-Once Storage Area 7.5.7 Adding List Data to Write-Once Storage Areas 7.5.8 Modifying Read/Write Storage Areas to Read-Only Storage Areas 7.5.9 Deleting Storage Areas 7.6 Modifying Indexes 7.6.1 Modifying Sorted Indexes 7.6.2 Modifying Hashed Indexes 7.6.3 Disabling Indexes 7.7 Deleting Indexes 7.8 Modifying Storage Maps 7.9 Deleting Storage Maps 7.10 Reorganizing Databases 7.10.1 Reorganizing a Single-File Database into a Multifile Database 7.10.2 Reorganizing a Database for Special Use 7.10.3 Creating a Copy of the Database 7.10.4 Creating a Copy of an Empty Database 7.11 Moving Databases and Database Files 7.12 Archiving a Version-Independent Copy of Data 7.13 Deleting Databases, Database Files, and Repository Definitions 7.14 Migrating the Database to Another DEC Rdb System 8 Modifying Database Elements 8.1 Altering and Deleting Domains 8.2 Altering and Deleting Tables 8.2.1 Deleting Tables 8.2.2 Altering or Deleting Tables That Contain Views or Indexes 8.2.3 Altering Columns in a Table 8.2.4 Adding, Altering, and Dropping Default Values from a Column 8.2.5 Altering the Name of a Table or the Name or Position of a Column 8.2.6 Altering Column Data Types 8.2.7 Altering and Deleting Tables in Multischema Databases 8.3 Altering and Deleting Constraints 8.4 Altering and Deleting Triggers 8.5 Deleting Views 8.6 Deleting Schemas in Multischema Databases 8.7 Deleting Catalogs in Multischema Databases 9 Defining Database Protection 9.1 Planning for Database Security 9.2 Understanding Privilege Checking for SQL Statements 9.2.1 Differences Between ANSI/ISO- and ACL-Style Privileges 9.2.2 Understanding Access Control Lists (ACLs) 9.2.3 Creating Access Control List Entries 9.2.4 Privileges Required for Data Manipulation and Data Definition Operations 9.2.5 Building Access Control Lists 9.2.6 Putting the Access Control List in Order 9.2.7 Granting and Revoking Privileges 9.2.8 Defining Protection for Databases 9.2.9 Defining Protection for Tables 9.2.10 Defining Protection for Columns 9.2.11 Restricting Access to Tables by Using Views 9.2.12 Restricting Access to a Subset of Rows 9.2.13 Using Views to Maintain Role-Oriented Access 9.2.14 Verifying Protection for a Database 9.2.15 Privileges with Override Capability 9.3 Understanding Privilege Checking for RMU Commands 9.3.1 Using RMU Privileges 9.3.2 Using RMU Privileges with Databases Created with VAX Rdb/VMS V4.1 or Earlier 9.4 Restricting the Creation of Databases 9.5 Securing Shareable DEC Rdb Definitions in the Repository 10 Using DEC Rdb with CDD/Repository 10.1 Overview of CDD/Repository 10.1.1 Repository Naming Conventions 10.1.2 Criteria for Using the Repository with DEC Rdb Databases 10.2 Deciding Whether to Require CDD/Repository 10.3 Creating New Repository Definitions 10.4 Defining Record-Level Constraints in the Repository 10.5 Modifying Repository Definitions Using CDO 10.5.1 Using the CDO DEFINE Commands 10.5.2 Using the CDO CHANGE Commands 10.6 Modifying Repository Definitions and Database Files 10.7 Updating the Repository Using the Database Files 10.7.1 Automatically Updating the Repository and the Database File Using SQL 10.7.2 SQL Generates Error on Updating the Repository 10.7.3 SQL Initially Stores Definitions in the Repository and Updates Only the Database File 10.7.4 SQL Does Not Store Initial Definitions in the Repository and Updates Only the Database File 10.8 Updating the Database File Using Repository Definitions 10.9 Using SQL to Create Repository Definitions 10.10 Using SQL to Delete Definitions 10.10.1 Deleting Associations with Database Definitions 10.10.2 Deleting Definitions 10.11 Using CDO to Delete Repository Definitions 10.12 Changing the Database File Name Using CDD/Repository A Definitions for Sample Personnel Databases