| |
| |
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 | |