| |
| |
| Introduction | |
| |
| |
| In This Introduction | |
| |
| |
| About This Manual | |
| |
| |
| Types of Users | |
| |
| |
| Software Dependencies | |
| |
| |
| Assumptions About Your Locale | |
| |
| |
| Demonstration Databases | |
| |
| |
| New Features | |
| |
| |
| New Features in Version 8.3 | |
| |
| |
| New Features in Version 9.2 | |
| |
| |
| Documentation Conventions | |
| |
| |
| Typographical Conventions | |
| |
| |
| Icon Conventions | |
| |
| |
| Sample-Code Conventions | |
| |
| |
| Additional Documentation | |
| |
| |
| On-Line Manuals | |
| |
| |
| Printed Manuals | |
| |
| |
| On-Line Help | |
| |
| |
| Error Message Documentation | |
| |
| |
| Documentation Notes, Release Notes, Machine Notes | |
| |
| |
| Related Reading | |
| |
| |
| Compliance with Industry Standards | |
| |
| |
| Informix Welcomes Your Comments | |
| |
| |
| |
| Basics of Database Design and Implementation | |
| |
| |
| |
| Planning a Database | |
| |
| |
| In This Chapter | |
| |
| |
| Choosing a Data Model for Your Database | |
| |
| |
| Using ANSI-Compliant Databases | |
| |
| |
| Designating a Database as ANSI Compliant | |
| |
| |
| Determining If an Existing Database Is ANSI Compliant | |
| |
| |
| Differences Between ANSI-Compliant and Non-ANSI-Compliant Databases | |
| |
| |
| Using a Customized Language Environment for Your Database | |
| |
| |
| |
| Building a Relational Data Model | |
| |
| |
| In This Chapter | |
| |
| |
| Why Build a Data Model | |
| |
| |
| Overview of the Entity-Relationship Data Model | |
| |
| |
| Identifying and Defining Principal Data Objects | |
| |
| |
| Discovering Entities | |
| |
| |
| Defining the Relationships | |
| |
| |
| Identifying Attributes | |
| |
| |
| Diagramming Data Objects | |
| |
| |
| Reading E-R Diagrams | |
| |
| |
| Telephone-Directory Example | |
| |
| |
| Translating E-R Data Objects into Relational Constructs | |
| |
| |
| Defining Tables, Rows, and Columns | |
| |
| |
| Determining Keys for Tables | |
| |
| |
| Resolving Relationships | |
| |
| |
| Resolving m:n Relationships | |
| |
| |
| Resolving Other Special Relationships | |
| |
| |
| Normalizing a Data Model | |
| |
| |
| First Normal Form | |
| |
| |
| Second Normal Form | |
| |
| |
| Third Normal Form | |
| |
| |
| Summary of Normalization Rules | |
| |
| |
| |
| Choosing Data Types | |
| |
| |
| In This Chapter | |
| |
| |
| Defining the Domains | |
| |
| |
| Data Types | |
| |
| |
| Null Values | |
| |
| |
| Default Values | |
| |
| |
| Check Constraints | |
| |
| |
| |
| Implementing a Relational Data Model | |
| |
| |
| In This Chapter | |
| |
| |
| Creating the Database | |
| |
| |
| Using CREATE DATABASE | |
| |
| |
| Using CREATE TABLE | |
| |
| |
| Creating a Fragmented Table | |
| |
| |
| Using CREATE INDEX | |
| |
| |
| Using Synonyms with Table Names | |
| |
| |
| Using Synonym Chains | |
| |
| |
| Using Command Scripts | |
| |
| |
| Populating the Tables | |
| |
| |
| |
| Managing Databases | |
| |
| |
| |
| Table Fragmentation Strategies | |
| |
| |
| In This Chapter | |
| |
| |
| What Is Fragmentation? | |
| |
| |
| Enhanced Fragmentation for Enterprise Decision Server | |
| |
| |
| Why Use Fragmentation? | |
| |
| |
| Whose Responsibility Is Fragmentation? | |
| |
| |
| Fragmentation and Logging | |
| |
| |
| Distribution Schemes for Table Fragmentation | |
| |
| |
| Expression-Based Distribution Scheme | |
| |
| |
| Round-Robin Distribution Scheme | |
| |
| |
| Range Distribution Scheme | |
| |
| |
| System-Defined Hash Distribution Scheme | |
| |
| |
| Hybrid Distribution Scheme | |
| |
| |
| Eliminating Fragments from a Search | |
| |
| |
| Creating a Fragmented Table | |
| |
| |
| Creating a New Fragmented Table | |
| |
| |
| Rowids in a Fragmented Table | |
| |
| |
| Creating a Fragmented Table from Nonfragmented Tables | |
| |
| |
| Fragmenting Smart Large Objects | |
| |
| |
| Modifying Fragmentation Strategies | |
| |
| |
| Using the INIT Clause to Reinitialize a Fragmentation Scheme | |
| |
| |
| Using the INIT Clause to Change From Hash to Hybrid Fragmentation | |
| |
| |
| Using the MODIFY Clause to Modify an Existing Fragmentation Strategy | |
| |
| |
| Using ATTACH and DETACH Clauses to Modify an Existing Fragmentation Strategy | |
| |
| |
| Using the ADD Clause to Add a Fragment | |
| |
| |
| Using the DROP Clause to Drop a Fragment | |
| |
| |
| Fragmenting Temporary Tables | |
| |
| |
| Fragmenting Temporary Tables with Enterprise Decision Server | |
| |
| |
| Fragmentation of Table Indexes | |
| |
| |
| Attached Indexes | |
| |
| |
| Detached Indexes | |
| |
| |
| Rowids | |
| |
| |
| Accessing Data Stored in Fragmented Tables | |
| |
| |
| Using Primary Keys Instead of Rowids | |
| |
| |
| Creating a Rowid Column in a Fragmented Table | |
| |
| |
| Granting and Revoking Privileges from Fragments | |
| |
| |
| |
| Granting and Limiting Access to Your Database | |
| |
| |
| In This Chapter | |
| |
| |
| Controlling Access to Databases | |
| |
| |
| Granting Privileges | |
| |
| |
| Database-Level Privileges | |
| |
| |
| Ownership Rights | |
| |
| |
| Table-Level Privilege | |
| |
| |
| Column-Level Privileges | |
| |
| |
| Type-Level Privileges | |
| |
| |
| Routine-Level Privileges | |
| |
| |
| Language Privileges | |
| |
| |
| Automating Privileges | |
| |
| |
| Using SPL Routines to Control Access to Data | |
| |
| |
| Restricting Data Reads | |
| |
| |
| Restricting Changes to Data | |
| |
| |
| Monitoring Changes to Data | |
| |
| |
| Restricting Object Creation | |
| |
| |
| Using Views | |
| |
| |
| Creating Views | |
| |
| |
| Modifying with a View | |
| |
| |
| Privileges and Views | |
| |
| |
| Privileges When Creating a View | |
| |
| |
| Privileges When Using a View | |
| |
| |
| |
| Object-Relational Databases | |
| |
| |
| |
| Creating and Using Extended Data Types in Dynamic Server | |
| |
| |
| In This Chapter | |
| |
| |
| User-Defined Data Types | |
| |
| |
| Opaque Data Types | |
| |
| |
| Distinct Data Types | |
| |
| |
| Smart Large Objects | |
| |
| |
| BLOB Data Type | |
| |
| |
| CLOB Data type | |
| |
| |
| Using Smart Large Objects | |
| |
| |
| Copying Smart Large Objects | |
| |
| |
| Complex Data Types | |
| |
| |
| Collection Data Types | |
| |
| |
| Named Row Types | |
| |
| |
| Unnamed Row Types | |
| |
| |
| |
| Understanding Type and Table Inheritance in Dynamic Server | |
| |
| |
| In This Chapter | |
| |
| |
| What Is Inheritance? | |
| |
| |
| Type Inheritance | |
| |
| |
| Defining a Type Hierarchy | |
| |
| |
| Overloading Routines for Types in a Type Hierarchy | |
| |
| |
| Inheritance and Type Substitutability | |
| |
| |
| Dropping Named Row Types from a Type Hierarchy | |
| |
| |
| Table Inheritance | |
| |
| |
| The Relationship Between Type and Table Hierarchies | |
| |
| |
| Defining a Table Hierarchy | |
| |
| |
| Inheritance of Table Behavior in a Table Hierarchy | |
| |
| |
| Modifying Table Behavior in a Table Hierarchy | |
| |
| |
| SERIAL Types in a Table Hierarchy | |
| |
| |
| Adding a New Table to a Table Hierarchy | |
| |
| |
| Dropping a Table in a Table Hierarchy | |
| |
| |
| Altering the Structure of a Table in a Table Hierarchy | |
| |
| |
| Querying Tables in a Table Hierarchy | |
| |
| |
| Creating a View on a Table in a Table Hierarchy | |
| |
| |
| |
| Creating and Using User-Defined Casts In Dynamic Server | |
| |
| |
| In This Chapter | |
| |
| |
| What Is a Cast? | |
| |
| |
| Creating User-Defined Casts | |
| |
| |
| Invoking Casts | |
| |
| |
| Restrictions on User-Defined Casts | |
| |
| |
| Casting Row Types | |
| |
| |
| Casting Between Named and Unnamed Row Types | |
| |
| |
| Casting Between Unnamed Row Types | |
| |
| |
| Casting Between Named Row Types | |
| |
| |
| Row-Type Conversions that Require Explicit Casts on Fields | |
| |
| |
| Casting Individual Fields of a Row Type | |
| |
| |
| Casting Collection Data Types | |
| |
| |
| Restrictions on Collection-Type Conversions | |
| |
| |
| Converting Between Collections with Different Element Types | |
| |
| |
| Converting Relational Data to a MULTISET Collection | |
| |
| |
| Casting Distinct Data Types | |
| |
| |
| Using Explicit Casts with Distinct Types | |
| |
| |
| Casting Between a Distinct Type and Its Source Type | |
| |
| |
| Casting to Smart Large Objects | |
| |
| |
| Creating Cast Functions for User-Defined Casts | |
| |
| |
| An Example of Casting Between Named Row Types | |
| |
| |
| An Example of Casting Between Distinct Data Types | |
| |
| |
| Multilevel Casting | |
| |
| |
| |
| Dimensional Databases | |
| |
| |
| |
| Building a Dimensional Data Model | |
| |
| |
| In This Chapter | |
| |
| |
| Overview of Data Warehousing | |
| |
| |
| Why Build a Dimensional Database? | |
| |
| |
| What is Dimensional Data? | |
| |
| |
| Concepts of Dimensional Data Modeling | |
| |
| |
| The Fact Table | |
| |
| |
| Dimensions of the Data Model | |
| |
| |
| Building a Dimensional Data Model | |
| |
| |
| Choosing a Business Process | |
| |
| |
| Summary of a Business Process | |
| |
| |
| Determining the Granularity of the Fact Table | |
| |
| |
| Identifying the Dimensions and Hierarchies | |
| |
| |
| Choosing the Measures for the Fact Table | |
| |
| |
| Resisting Normalization | |
| |
| |
| Choosing the Attributes for the Dimension Tables | |
| |
| |
| Handling Common Dimensional Data-Modeling Problems | |
| |
| |
| Minimizing the Number of Attributes in a Dimension Table | |
| |
| |
| Handling Dimensions That Occasionally Change | |
| |
| |
| Using the Snowflake Schema | |
| |
| |
| |
| Implementing a Dimensional Database | |
| |
| |
| In This Chapter | |
| |
| |
| Implementing the sales_demo Dimensional Database | |
| |
| |
| Using CREATE DATABASE | |
| |
| |
| Using CREATE TABLE for the Dimension and Fact Tables | |
| |
| |
| Mapping Data from Data Sources to the Database | |
| |
| |
| Loading Data into the Dimensional Database | |
| |
| |
| Creating the sales_demo Database | |
| |
| |
| Testing the Dimensional Database | |
| |
| |
| Logging and Nonlogging Tables in Enterprise Decision Server | |
| |
| |
| Choosing Table Types | |
| |
| |
| Switching Between Table Types | |
| |
| |
| Indexes for Data-Warehousing Environments | |
| |
| |
| Using GK Indexes in a Data-Warehousing Environment | |
| |
| |
| Index | |