Informix Guide to Designing Databases and Data Warehouses / Edition 1
by Informix Software, Inc., Informix SoftwareISBN-10: 0130161675
ISBN-13: 9780130161673
Pub. Date: 11/19/1999
Publisher: Pearson Education
- The authoritative database design guide for Informix Dynamic Server.2000 and Informix Enterprise Decision Server!
- Detailed data models illustrating each key approach to database design
- Proven, step-by-step techniques for building your Informix-based data warehouse
- Using SQL to implement and manage enterprise databases
Overview
- The authoritative database design guide for Informix Dynamic Server.2000 and Informix Enterprise Decision Server!
- Detailed data models illustrating each key approach to database design
- Proven, step-by-step techniques for building your Informix-based data warehouse
- Using SQL to implement and manage enterprise databases
The Informix insider's guide to database and data warehouse design!
Written by a team of insiders, this is the most complete, authoritative guide to database and data warehouse design, implementation, and administration with Informix Dynamic Server.2000 and Informix Enterprise Decision Server. You'll start by walking through the construction of data models that illustrate each key approach to database design. Compare ANSI and non-ANSI compliant databases, learn the fundamentals of entity-relationship modeling, and choose appropriate data types. Next, you'll learn how to use the Informix implementation of SQL to implement and manage your databases.
The Informix Guide to Designing Databases and Data Warehouses covers all this, and more:
- Implementing relational data models with the CREATE DATABASE and CREATE TABLE statements, synonyms, synonym chains, and command scripts
- Applying fragmentation strategies for maximizing database and data warehouse performance
- Implementing dimensional data models-including solutions to common problems
- Applying indexing techniques for data warehousing environments
- Granting and limiting access to data through privileges, views, and stored procedures
Whether you're a manager, developer, or DBA, the InformixGuide to Designing Databases and Data Warehouses is your authoritative single source for guidance on high-performance database design with Informix Dynamic Server.2000 and Informix Enterprise Decision Server.
0-13-016167-5
Product Details
- ISBN-13:
- 9780130161673
- Publisher:
- Pearson Education
- Publication date:
- 11/19/1999
- Series:
- Informix Press Series
- Pages:
- 350
- Product dimensions:
- 6.91(w) x 9.10(h) x 0.93(d)
Table of Contents
| Introduction | ||
| In This Introduction | 3 | |
| About This Manual | 3 | |
| Types of Users | 4 | |
| Software Dependencies | 4 | |
| Assumptions About Your Locale | 5 | |
| Demonstration Databases | 5 | |
| New Features | 6 | |
| New Features in Version 8.3 | 6 | |
| New Features in Version 9.2 | 6 | |
| Documentation Conventions | 8 | |
| Typographical Conventions | 8 | |
| Icon Conventions | 9 | |
| Sample-Code Conventions | 12 | |
| Additional Documentation | 13 | |
| On-Line Manuals | 13 | |
| Printed Manuals | 13 | |
| On-Line Help | 14 | |
| Error Message Documentation | 14 | |
| Documentation Notes, Release Notes, Machine Notes | 15 | |
| Related Reading | 16 | |
| Compliance with Industry Standards | 16 | |
| Informix Welcomes Your Comments | 16 | |
| Section I | Basics of Database Design and Implementation | |
| Chapter 1 | Planning a Database | |
| In This Chapter | 3 | |
| Choosing a Data Model for Your Database | 3 | |
| Using ANSI-Compliant Databases | 5 | |
| Designating a Database as ANSI Compliant | 6 | |
| Determining If an Existing Database Is ANSI Compliant | 6 | |
| Differences Between ANSI-Compliant and Non-ANSI-Compliant Databases | 7 | |
| Using a Customized Language Environment for Your Database | 12 | |
| Chapter 2 | Building a Relational Data Model | |
| In This Chapter | 3 | |
| Why Build a Data Model | 3 | |
| Overview of the Entity-Relationship Data Model | 4 | |
| Identifying and Defining Principal Data Objects | 5 | |
| Discovering Entities | 5 | |
| Defining the Relationships | 9 | |
| Identifying Attributes | 17 | |
| Diagramming Data Objects | 20 | |
| Reading E-R Diagrams | 21 | |
| Telephone-Directory Example | 22 | |
| Translating E-R Data Objects into Relational Constructs | 23 | |
| Defining Tables, Rows, and Columns | 24 | |
| Determining Keys for Tables | 26 | |
| Resolving Relationships | 30 | |
| Resolving m:n Relationships | 30 | |
| Resolving Other Special Relationships | 31 | |
| Normalizing a Data Model | 32 | |
| First Normal Form | 33 | |
| Second Normal Form | 35 | |
| Third Normal Form | 35 | |
| Summary of Normalization Rules | 36 | |
| Chapter 3 | Choosing Data Types | |
| In This Chapter | 3 | |
| Defining the Domains | 3 | |
| Data Types | 4 | |
| Null Values | 26 | |
| Default Values | 27 | |
| Check Constraints | 27 | |
| Chapter 4 | Implementing a Relational Data Model | |
| In This Chapter | 3 | |
| Creating the Database | 3 | |
| Using CREATE DATABASE | 4 | |
| Using CREATE TABLE | 6 | |
| Creating a Fragmented Table | 9 | |
| Using CREATE INDEX | 9 | |
| Using Synonyms with Table Names | 15 | |
| Using Synonym Chains | 16 | |
| Using Command Scripts | 17 | |
| Populating the Tables | 19 | |
| Section II | Managing Databases | |
| Chapter 5 | Table Fragmentation Strategies | |
| In This Chapter | 3 | |
| What Is Fragmentation? | 3 | |
| Enhanced Fragmentation for Enterprise Decision Server | 6 | |
| Why Use Fragmentation? | 6 | |
| Whose Responsibility Is Fragmentation? | 8 | |
| Fragmentation and Logging | 8 | |
| Distribution Schemes for Table Fragmentation | 9 | |
| Expression-Based Distribution Scheme | 10 | |
| Round-Robin Distribution Scheme | 12 | |
| Range Distribution Scheme | 12 | |
| System-Defined Hash Distribution Scheme | 13 | |
| Hybrid Distribution Scheme | 14 | |
| Eliminating Fragments from a Search | 18 | |
| Creating a Fragmented Table | 23 | |
| Creating a New Fragmented Table | 23 | |
| Rowids in a Fragmented Table | 25 | |
| Creating a Fragmented Table from Nonfragmented Tables | 25 | |
| Fragmenting Smart Large Objects | 27 | |
| Modifying Fragmentation Strategies | 28 | |
| Using the INIT Clause to Reinitialize a Fragmentation Scheme | 29 | |
| Using the INIT Clause to Change From Hash to Hybrid Fragmentation | 30 | |
| Using the MODIFY Clause to Modify an Existing Fragmentation Strategy | 31 | |
| Using ATTACH and DETACH Clauses to Modify an Existing Fragmentation Strategy | 32 | |
| Using the ADD Clause to Add a Fragment | 34 | |
| Using the DROP Clause to Drop a Fragment | 34 | |
| Fragmenting Temporary Tables | 35 | |
| Fragmenting Temporary Tables with Enterprise Decision Server | 35 | |
| Fragmentation of Table Indexes | 37 | |
| Attached Indexes | 37 | |
| Detached Indexes | 38 | |
| Rowids | 39 | |
| Accessing Data Stored in Fragmented Tables | 40 | |
| Using Primary Keys Instead of Rowids | 41 | |
| Creating a Rowid Column in a Fragmented Table | 41 | |
| Granting and Revoking Privileges from Fragments | 42 | |
| Chapter 6 | Granting and Limiting Access to Your Database | |
| In This Chapter | 3 | |
| Controlling Access to Databases | 4 | |
| Granting Privileges | 5 | |
| Database-Level Privileges | 6 | |
| Ownership Rights | 8 | |
| Table-Level Privilege | 8 | |
| Column-Level Privileges | 12 | |
| Type-Level Privileges | 14 | |
| Routine-Level Privileges | 15 | |
| Language Privileges | 16 | |
| Automating Privileges | 17 | |
| Using SPL Routines to Control Access to Data | 21 | |
| Restricting Data Reads | 21 | |
| Restricting Changes to Data | 22 | |
| Monitoring Changes to Data | 23 | |
| Restricting Object Creation | 24 | |
| Using Views | 25 | |
| Creating Views | 26 | |
| Modifying with a View | 31 | |
| Privileges and Views | 35 | |
| Privileges When Creating a View | 35 | |
| Privileges When Using a View | 36 | |
| Section III | Object-Relational Databases | |
| Chapter 7 | Creating and Using Extended Data Types in Dynamic Server | |
| In This Chapter | 3 | |
| User-Defined Data Types | 4 | |
| Opaque Data Types | 4 | |
| Distinct Data Types | 4 | |
| Smart Large Objects | 5 | |
| BLOB Data Type | 5 | |
| CLOB Data type | 5 | |
| Using Smart Large Objects | 7 | |
| Copying Smart Large Objects | 8 | |
| Complex Data Types | 9 | |
| Collection Data Types | 10 | |
| Named Row Types | 17 | |
| Unnamed Row Types | 25 | |
| Chapter 8 | Understanding Type and Table Inheritance in Dynamic Server | |
| In This Chapter | 3 | |
| What Is Inheritance? | 3 | |
| Type Inheritance | 4 | |
| Defining a Type Hierarchy | 4 | |
| Overloading Routines for Types in a Type Hierarchy | 8 | |
| Inheritance and Type Substitutability | 9 | |
| Dropping Named Row Types from a Type Hierarchy | 10 | |
| Table Inheritance | 11 | |
| The Relationship Between Type and Table Hierarchies | 12 | |
| Defining a Table Hierarchy | 14 | |
| Inheritance of Table Behavior in a Table Hierarchy | 15 | |
| Modifying Table Behavior in a Table Hierarchy | 17 | |
| SERIAL Types in a Table Hierarchy | 19 | |
| Adding a New Table to a Table Hierarchy | 20 | |
| Dropping a Table in a Table Hierarchy | 21 | |
| Altering the Structure of a Table in a Table Hierarchy | 22 | |
| Querying Tables in a Table Hierarchy | 22 | |
| Creating a View on a Table in a Table Hierarchy | 23 | |
| Chapter 9 | Creating and Using User-Defined Casts In Dynamic Server | |
| In This Chapter | 3 | |
| What Is a Cast? | 3 | |
| Creating User-Defined Casts | 5 | |
| Invoking Casts | 6 | |
| Restrictions on User-Defined Casts | 6 | |
| Casting Row Types | 7 | |
| Casting Between Named and Unnamed Row Types | 8 | |
| Casting Between Unnamed Row Types | 9 | |
| Casting Between Named Row Types | 10 | |
| Row-Type Conversions that Require Explicit Casts on Fields | 10 | |
| Casting Individual Fields of a Row Type | 12 | |
| Casting Collection Data Types | 13 | |
| Restrictions on Collection-Type Conversions | 14 | |
| Converting Between Collections with Different Element Types | 14 | |
| Converting Relational Data to a MULTISET Collection | 16 | |
| Casting Distinct Data Types | 16 | |
| Using Explicit Casts with Distinct Types | 16 | |
| Casting Between a Distinct Type and Its Source Type | 17 | |
| Casting to Smart Large Objects | 20 | |
| Creating Cast Functions for User-Defined Casts | 20 | |
| An Example of Casting Between Named Row Types | 21 | |
| An Example of Casting Between Distinct Data Types | 22 | |
| Multilevel Casting | 24 | |
| Section IV | Dimensional Databases | |
| Chapter 10 | Building a Dimensional Data Model | |
| In This Chapter | 3 | |
| Overview of Data Warehousing | 4 | |
| Why Build a Dimensional Database? | 5 | |
| What is Dimensional Data? | 7 | |
| Concepts of Dimensional Data Modeling | 10 | |
| The Fact Table | 12 | |
| Dimensions of the Data Model | 13 | |
| Building a Dimensional Data Model | 16 | |
| Choosing a Business Process | 17 | |
| Summary of a Business Process | 17 | |
| Determining the Granularity of the Fact Table | 19 | |
| Identifying the Dimensions and Hierarchies | 21 | |
| Choosing the Measures for the Fact Table | 23 | |
| Resisting Normalization | 26 | |
| Choosing the Attributes for the Dimension Tables | 27 | |
| Handling Common Dimensional Data-Modeling Problems | 29 | |
| Minimizing the Number of Attributes in a Dimension Table | 29 | |
| Handling Dimensions That Occasionally Change | 31 | |
| Using the Snowflake Schema | 33 | |
| Chapter 11 | Implementing a Dimensional Database | |
| In This Chapter | 3 | |
| Implementing the sales_demo Dimensional Database | 3 | |
| Using CREATE DATABASE | 4 | |
| Using CREATE TABLE for the Dimension and Fact Tables | 4 | |
| Mapping Data from Data Sources to the Database | 7 | |
| Loading Data into the Dimensional Database | 9 | |
| Creating the sales_demo Database | 12 | |
| Testing the Dimensional Database | 12 | |
| Logging and Nonlogging Tables in Enterprise Decision Server | 13 | |
| Choosing Table Types | 14 | |
| Switching Between Table Types | 18 | |
| Indexes for Data-Warehousing Environments | 18 | |
| Using GK Indexes in a Data-Warehousing Environment | 20 | |
| Index |
Customer Reviews
Average Review: