Physical Database Design Using Oracle

Donald K. Burleson

• Publisher: CRC Press
• Year: 2004
• ISBN:
  9781135497156
• Edition: 1
• Topic: Computer Science
• Subtopic: Databases
• Index: Computer Science

The evolution of Oracle has led to a revolution in design practices. For Oracle 10g, database physical structures have become more complex than ever before and database designers face multiple ways to implement their logical models. IS students studying database design and administration need to be able to implement management systems in a way that

1
INTRODUCTION TO ORACLE PHYSICAL DESIGN

PREFACE

Over the past 30 years, we’ve seen the evolution of a wide variety of systems analysis and design methodologies. We’ve seen the methodologies of Grady Booch, Ed Yourdon, Chris Gane and Trish Sarson, as well as the emergence of standard systems development methodologies such as joint application development and Unified Modeling Language (UML).

Regardless of the methodology, at some point in the systems implementation, the database designer must be able to convert a logical data modeling for data into physical data structures. From a database point of view, it is incidental whether you’re dealing with a commercial database management system (DBMS), such as MySQL^® or Oracle, or whether you’re writing your own DBMS in a language such as C or C⁺⁺. The point is that we must be able to take the logical data models and convert them into physical implementations that will minimize disk input/output (I/O) and provide the fastest possible throughput.

We need to be able to implement the DBMS in such fashion that performance will be fast while preserving the logical data structures. This book is dedicated to the premise that the database designer should be able to take logical data models and convert them into a series of data structures that allow for fast and easy, logical access to the data.

RELATIONAL DATABASES AND PHYSICAL DESIGN

Relational databases made the following improvements over hierarchical and network databases:

• Simplicity — the concept of tables with rows and columns is extremely simple and easy to understand. End users have a simple data model. Complex network diagrams used with the hierarchical and network databases are not used with a relational database.
• Data independence — data independence is the ability to modify data structures (in this case, tables) without affecting existing programs. Much of this is because tables are not hard-linked to one another. Columns can be added to tables, tables can be added to the database, and new data relationships can be added with little or no restructuring of the tables. A relational database provides a much higher degree of data independence than do hierarchical and network databases.
• Declarative data access — the Structured Query Language (SQL) users specify what data they want, then the embedded SQL (a procedural language) determines how to get the data. In relational database access, the user tells the system the conditions for the retrieval of data. The system then gets the data that meets the selection conditions in the SQL statements. The database navigation is hidden from the end user or programmer, unlike a Conference on Data Systems Languages (CODASYL) Data Manipulation Language (DML), where the programmer had to know the details of the access path.

The most important point about SQL is that it provided programmers and end users with a simple, easy way to add, change, and extract data from a relational database. Any two tables could be joined together on the fly at runtime using their primary or foreign keys. There are no pointers or hard links from one table to another.

SYSTEMS DEVELOPMENT AND PHYSICAL DESIGN

To understand all of the steps that occur during the physical database design, let’s take a quick look at the overall phases of a database project:

1. Feasibility study — a feasibility study is a cost-benefit analysis for a proposed system, quantifying all tangible costs and benefits for the warehouse, as well as describing intangible costs and benefits. Essentially, the goal of this study is to provide a go/no-go decision about whether to proceed with the data warehouse project. Activities involve an analysis of technological and economical feasibility with a focus on understanding all of the costs and benefits that will accrue from the data warehouse.
2. Systems analysis — systems analysis is a logical description of the data sources for the warehouse, data extraction analysis, data cleansing analysis, and data loading analysis. Unlike a traditional system, the warehouse analysis is heavily data-centric and not concerned with defining the system interfaces.
3. Logical design — the systems design phase is the physical implementation of the logical data model that was developed in the systems analysis phase. This includes the design of the warehouse, specifications for data extraction tools, data loading processes, and warehouse access methods. In this phase, a working prototype should be created for the end user.
4. Physical design— the system design phase is also where the logical documentation is transformed into a physical structure. For database design, this involves the creation of the entity/relation (E/R) model and the determination of appropriate data storage techniques and index usage. This phase is where a thorough understanding of Oracle database architecture will pay off.
5. Implementation— the implementation phase is the phase in which the warehouse is constructed and the software is written and tested.

Figure 1.1The System Development Life Cycle

As shown in Figure 1.1, the implementation phase normally consumes as much effort as all of the other steps combined. Regardless of the reasons, it remains true that the implementation phase is by far the most time-consuming phase in the creation of any system.

If a development team has done a good job of analyzing, designing, and coding a new system, you might suspect that the programming team would disband immediately after coding is completed. But, this is seldom the case. The cost curve continues to grow after a system has been delivered: this can be attributed to the dynamic nature of systems requirements. Almost by definition, most long-term development efforts will deliver an obsolete system to their end users. The end users often lament, “You gave me the system that I needed two years ago when you began the project! Many requirements have changed, even while you were creating the system.” This is a common complaint and it’s not surprising to see that the programming staff immediately begins addressing the maintenance requests that have been stacking up while they were initially creating the system. A traditional computer system will continually become more and more expensive to maintain, until the cumulative costs exceed the benefits of the system. A goal of a savvy systems manager is to foresee this dilemma and to start rewriting the system so that a new system is ready to replace the aging system when the costs become too cumbersome.

SYSTEMS ANALYSIS AND PHYSICAL DATABASE DESIGN

Fundamentally, the purpose of any systems analysis is to logically identify the processes and the data moving between the processes and to describe the processing rules and data items. Only after these items are defined can design begin, regardless of the physical implementation of the system. To meet these goals, a data warehouse analysis should begin with the creation of a structured specification. A structured specification is a document that describes all of the data, data storage, external entities, and processes for a system. This document is then used in the design phase for the creation of the behaviors, E/R model, and class hierarchy.

The Structured Specification

Most of the system analysis methodologies provide a method for documenting logical processes, data items, and data stores. These components generally include:

• Data flow diagram (DFD) — the DFD is a set of top-down diagrams that depict all processes within a system, the data flow among the processes, and the data stores. Figure 1.2 depicts a sample DFD. The DFDs begin at a general level and become progressively more detailed. The lowest level of processing is called the functional primitive level, which has been traditionally used as the starting point for systems design.
• Data dictionary — the data dictionary is a description of all of the logical data items, including all data flows and data stores (files). The data dictionary describes how all of the data items are stored and how they have been transformed by the processes. The data dictionary’s file specifications also become the foundation for the relational tables that will comprise the Oracle warehouse.
• Process logic specifications (structured specifications) — these specifications describe all functional primitive processes. A process is defined as an operation that modifies a data flow. The tools used to describe processes include pseudocode, procedure flowcharts, decision trees, and decision tables.

Figure 1.2A Sample DFD

In a traditional systems analysis, the DFD does not stand by itself. Rather, the DFD is augmented by a data dictionary that describes all of the data flows and files and a set of process logic specifications that describes how each process transforms data flows. A process logic specification (sometimes called a minispec) can be expressed as structured English, decision trees, or any of the many other techniques used to describe how data flows are being changed.

In traditional systems analysis, data dictionary definitions for all data items are normalized or grouped into database entities, which become E/R models in the database design phase. Eventually, the E/R models become relational tables during physical design. The identification and grouping of data items constitutes the entities that will establish the basic E/R model for the database engine.

THE ROLE OF FUNCTIONAL DECOMPOSITION IN PHYSICAL DATABASE DESIGN

The principles of top-down analysis tell us to begin our DFD at a general level. The entire system is viewed as a single process and this view is called a Context Level DFD. Next, the DFD is decomposed and levels of detail are added to the model. Any process that can be identified can probably be subdivided to smaller processes; it is possible to decompose a DFD to the level where each process represents a single statement. An extreme example of functional decomposition would be showing a statement such as add 1 to counter as a separate process on the DFD. The pivotal question is: At what point should the developer stop decomposing the processes?

Theoreticians such as Gane and Sarson tell us that a DFD should be decomposed to the functional primitive level, where each process bubble performs one granular function. Under this definition, one should departition the functional specs until each behavior performs a functional primitive process. For example, in Figure 1.2 we see a high-level specification for a process named fill_order. We can now take this process and departition it into sublevels (Figure 1.3).

Figure 1.3A Sample of Functional Decomposition

A good rule of thumb for database analysis is that a DFD should be decomposed to the level where each process corresponds to a SQL operation. This allows the use of triggers within a relational database and greatly simplifies the data physical database design.

As you are probably beginning to see, the level of partitioning is critical for a successful database systems analysis. While this level of decomposing is fine for traditional systems analysis, it is better to continue to decompose the behavior for effective relational database design.

There is still a great deal of controversy about the best way to approach database analysis for database systems. Architecturally, some theoreticians state that the relational model is better suited for use in an online transaction processing (OLTP) environment and multidimensional architectures are better suited to data warehouses. To address these data storage issues, Oracle has implemented physical constructs that are specific to data warehouse and object-oriented systems:

• Oracle data warehouse physical constructs:

• —Table value partitions
• —Table range partitions
• —Table hash partitioning
• —Oracle9i^™ database multicolumn partitioning
• —Index partitioning

• Oracle object-oriented physical features:

• —Nested tables
...

Table of contents

• COVER PAGE
• TITLE PAGE
• COPYRIGHT PAGE
• DEDICATION
• PREFACE
• ABOUT THE AUTHOR
• 1 INTRODUCTION TO ORACLE PHYSICAL DESIGN
• 2 PHYSICAL ENTITY DESIGN FOR ORACLE
• 3 ORACLE HARDWARE DESIGN
• 4 ORACLE INSTANCE DESIGN
• 5 ORACLE TABLESPACE DESIGN
• 6 ORACLE TABLE DESIGN
• 7 ORACLE INDEX DESIGN