Release 8.0
A58241-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8 Application Developer's Guide Release 8.0 A58241-01 |
|
This chapter briefly outlines the steps involved in designing and implementing an Oracle database application. More detailed information needed to perform these tasks is provided later in this Guide. Although the specific tasks vary depending upon the type and complexity of the application being developed, in general the responsibilities of the application developer include the following:
This book is not meant to serve as a textbook on database or application design. If you are not already familiar with these areas, you should consult a text for guidance. Where appropriate, you are directed to other sections of this document for additional information.
The first step in designing a usable application is determining what problem you are trying to solve. It is important that you do not focus entirely on the data, but rather on how the data is being used. In designing your application you should try to answer the following questions:
You should involve the end-user as much as possible early in the design phase. This helps eliminate problems that can stem from misunderstandings about the purpose of the application. After you gain a better understanding of the tasks that the end-users of the application are trying to perform, you can then determine the data that is necessary to complete these tasks. In this step, you need to look at each task and decide:
It is important that your audience has a clear understanding of your proposed solution. It is also important that your application be designed to accommodate the changing needs of your audience.
At this point, you are ready to begin designing your data model. This model will allow you to determine how your data can be most efficiently stored and used. The Entity-Relationship model is often used to map a real-world system to a relational database management system.
The Entity-Relationship model categorizes all elements of a system as either an entity (a person, place, or thing) or a relationship between entities. Both constructs are represented by the same structure, a table. For example, in an order entry system, parts are entities, as are orders. Both part and order information is represented in tables. The relationship of which parts are requested by which order is also represented by a third table. The application of the Entity-Relationship model requires the following steps:
When modeling a system with the Entity-Relationship model, you will often include a step called normalization. Textbooks on database design will tell you how to achieve Third Normal Form. Each table must have exactly one primary key and, in third normal form, all of the data in a table is dependent solely upon the table's primary key. You might find it necessary to violate normal form on occasion to achieve a desired performance level.
Proper application of the Entity-Relationship model results in well designed tables. The benefits of a set of well designed tables include the following:
Oracle Corporation's products for database design can help improve, automate, and document designs. The Oracle database design products are Designer/2000 and Object Database Designer.
Designer/2000 is a business and application modeling toolset which generates both servers and applications from graphical models. Designer/2000 release 2.0 supports all the scalability features of Oracle8 such as partitioned tables, LOB datatypes, index-organized tables, and deferred constraint checking as well as the object features such as user-defined views, object tables, and referenced and embedded object types.
Object Database Designer supports all aspects of the design and creation of an Object-Relational Database Management System (ORDBMS). Type modeling forms the core of an object-oriented development. Object Database Designer implements type modeling using UML and then uses the type model to drive generation of Oracle8 database designs and C++ classes with transparent persistence-thus supporting both the database designer and the application developer.
See the Designer/2000 and Object Database Designer manuals for additional information about these products.
After determining the overall structure of the tables in your database, you must next design the structure of these tables. This process involves selecting the proper datatype for each column and assigning each column a meaningful name. You can find information about selecting the appropriate Oracle datatype in Chapter 5, "Selecting a Datatype" of this Guide.
If you are creating an application that runs on a distributed database, you must also determine where to locate this data and any links that are necessary to access the data across the network.
After completing your database design, you are ready to begin designing the application itself. This, too, is an iterative process, and might also cause you to rethink your database design. As much as possible, you should involve your audience in these design decisions. You should make your application available to the end-users as early as possible in order for them to provide you with the feedback needed to fine tune your design.
There are many tools available, from Oracle Corporation as well as other vendors, to aid in the development and implementation of your application. Your first task is to evaluate the available tools and select those that are most appropriate.
You must next determine how to implement your requirements using the features available in Oracle, as well as any other tools and utilities that you selected in the previous step. The features and tools that you choose to use to implement your application can significantly affect the performance of your application. The more effort you put into designing an efficient application, the less time you will have to spend tuning the application once it is complete.
Several of the more useful features available to Oracle application developers are listed below. Each of these topics is discussed in detail later in this book.
Integrity constraints allow you to define certain requirements for the data that can be included in a table, and to ensure that these requirements are met regardless of how the data is entered. These constraints are included as part of the table definition, and require no programming to be enforced.
Commonly used procedures can be written once in PL/SQL and stored in the database for repeated use by applications. This ensures consistent behavior among applications, and can reduce your development and testing time.
Related procedures can be grouped into packages, which have a package specification separate from the package body. The package body can be altered and recompiled without affecting the package specification. This allows you to make changes to the package body that are not visible to end-users, and that do not require objects referencing the specification to be recompiled.
Complex business rules that cannot be enforced using declarative integrity constraints can be enforced using triggers. Triggers, which are similar to PL/SQL anonymous blocks, are automatically executed when a triggering statement is issued, regardless of the user or application.
Database triggers can have such diverse uses as performing value-based auditing, maintaining derived data values, and enforcing complex security or integrity rules. By moving this code from your application into database triggers, you can ensure that all applications behave in a uniform manner.
The cost-based optimization method uses statistics about tables, along with information about the available indexes, to select an execution plan for SQL statements. This allows even inexperienced users to submit complex queries without having to worry about performance.
As an application designer, there may be times when you have knowledge of the data in your table that is not available to the optimizer, and that allows you to select a better execution path. In these cases, you can provide hints to the optimizer to allow it to select the proper execution path. See Oracle8 Tuning for more information.
Shared SQL allows multiple users to share a single runtime copy of procedures and SQL statements, significantly reducing memory requirements. If two identical SQL statements are issued, the shared SQL area used to process the first instance of the statement is reused for the processing of the subsequent instances of the same statement.
You should coordinate with your database administrator (DBA), as well as other application developers, to establish guidelines to ensure that statements and blocks that perform similar tasks can use the same shared SQL areas as often as possible. See Oracle8 Tuning for additional information.
Oracle supports both single and multi-byte character encoding schemes. Because language-dependent data is stored separately from the code, you can easily add new languages and language-specific features (such as date formats) without altering your application code. Refer to Oracle8 Reference for more information on national language support.
By default, Oracle provides row-level locking, allowing multiple users to access different rows of the same table without lock contention. Although this greatly reduces the chances of deadlocks occurring, you should still take care in designing your application to ensure that deadlocks do not occur.
Online transaction processing applications-that is, applications with multiple users concurrently modifying different rows of the same table-benefit the most from row-level locking. You should design your application with this feature in mind.
Oracle locks are also available to you for use within your applications. These locks are provided as part of the DBMS_LOCK package, which is described in Chapter 3, "Processing SQL Statements".
Profiles can be used to enforce per-query and per-session limits on resource use. When designing your applications, you might want to consider if any users have been denied access to the system due to limited resources. Profiles make it possible to allow these infrequent users limited access to the database. If you choose to allow access to these users, you must consider their requirements when formulating your design. Profiles are generally controlled by the database administrator. Consult your database administrator to determine if access can be granted to additional users and to identify this audience.
You can use sequence numbers to automatically generate unique keys for your data, and to coordinate keys across multiple rows or tables. The sequence number generator eliminates the serialization caused by programmatically generating unique numbers by locking the most recently used value and then incrementing it. Sequence numbers can also be read from a sequence number cache, instead of disk, further increasing their speed.
Oracle is designed to conform to industry standards. If your applications must conform to industry standards, you should consult Oracle8 SQL Reference for a detailed explanation of Oracle's conformance to SQL standards.
If you are developing applications that use the Oracle Call Interface (OCI), you should be aware that the OCI offers calls that provide:
See Oracle Call Interface Programmer's Guide for more information.
All operations performed on the information in an Oracle database are executed using SQL statements. After you have completed the design of your application, you need to begin designing the SQL statements that you will use to implement this design. You should have a thorough understanding of SQL before you begin to write your application. A general description of how SQL statements are executed is provided in Chapter 3 of this manual.
You can significantly improve the performance of your application by tuning the SQL statements it uses. Tuning SQL statements is explained in detail in the Oracle8 Tuning manual.
Your application design is not complete until you have determined the security requirements for the application. As part of your application design, you identified what tasks each user or group of users needed to perform. Now you must determine what privileges are required to perform these tasks. It is important to the security of the database that these users have no more access than is necessary to complete their tasks.
By having your application enable the appropriate roles when a user runs the application, you can ensure that the user can only access the database as you originally planned. Because roles are typically granted to users by the database administrator, you should coordinate with your database administrator to ensure that each user is granted access to the roles required by your application for a designated task.
There are two important areas to think about when tuning your database application:
Information on tuning your SQL statements, including how to use the cost-based optimization method, is included in the Oracle8 Tuning manual. Tuning your application design ideally occurs before you begin to implement your application. Before beginning your design, you should carefully read about each of the features described in this document and consider which features best suit your requirements. Some design decisions that you should consider are outlined below.
|
See Also:
"Using Integrity Constraints" on page 9-2 for a discussion of when to use integrity constraints. |
You should also work with your database administrator to determine how the database can be tuned to accommodate your application. More detailed information on tuning your application, as well as information on database tuning, is included in Oracle8 Tuning.
If you are upgrading an existing application, or writing a new application to run on an existing database, you must follow many of the same procedures described earlier in this section. You must identify and understand the needs of your audience and design your application to accommodate them.
You must also work closely with the database administrator to determine:
