HomeOracle Page 7 - Developing and Implementing Applications
Standard Deliverables - Oracle
This article, the first of three parts, focuses on the design and creation of applications that use the database. It is excerpted from chapter five of the book Oracle Database 10g DBA Handbook, written by Kevin Loney and Bob Bryla (McGraw-Hill/Osborne, 2005; ISBN: 0072231459).
How do you know if an application is ready to be migrated to a production environment? The application development methodology must clearly define, both in format and in level of detail, the required deliverables for each stage of the life cycle. These should include specifications for each of the following items:
Entity relationship diagram
Physical database diagram
Tuning goals for queries and transaction processing
Query execution plans
Acceptance test procedures
In the following sections, you will see descriptions of each of these items.
Entity Relationship Diagram
The entity relationship (E-R) diagram illustrates the relationships that have been identified among the entities that make up the application. E-R diagrams are critical for providing an understanding of the goals of the system. They also help to identify interface points with other applications and to ensure consistency in definitions across the enterprise.
Physical Database Diagram
A physical database diagram shows the physical tables generated from the entities and the columns generated from the defined attributes in the logical model. A physical database diagramming tool is usually capable of generating the DDL necessary to create the application’s objects.
You can use the physical database diagram to identify tables that are most likely to be involved in transactions. You should also be able to identify which tables are commonly used together during a data entry or query operation. You can use this information to effectively plan the distribution of these tables (and their indexes) across the available physical devices to reduce the amount of I/O contention encountered.
In data warehousing applications, the physical database diagram should show the aggregations and materialized views accessed by user queries. Although they contain derived data, they are critical components of the data access path and must be documented.
The space requirements deliverable should show the initial space requirements for each database table and index. The recommendations for the proper size of tables, clusters, and indexes are shown in the “Sizing Database Objects” section, later in this chapter.