Physical Database Design - High-Performance Web Databases: Design, Development, and Deployment - page 335

• Replacement. There comes a time when the DBMS is not able to sup-

port new requirements of the enterprise in which it is used. New tech-
nology may enable new DBMSs to better serve the enterprise. When
the benefits of the new DBMS outweigh the costs of converting the old
database to the new DBMS, the old DBMS is replaced and the database
lifecycle begins again.

INITIAL PHYSICAL DATABASE DESIGN METHODOLOGY

Creating the initial physical database design is an important stage in the
database lifecycle. The DBA determines the physical data structures for
representing data so that the DBMS efficiently processes queries and trans-
actions in the workload. The basic methodology for creating the initial
physical database design consists of four phases:

1. For each table in the relational schema, determine the primary
search key, determine the appropriate file structure, and choose the
primary access method for accessing that file.
2. For frequently joined tables, determine how to support the join
operation.
3. For each file, identify additional search keys and determine the
appropriate physical structures for supporting them.
4. For each file, identify multi-attribute search keys and determine the
appropriate physical structures for supporting them.

To illustrate each phase of the initial physical database design process,
a simple relational database — which consists of two tables, Employee and
Department — will be used. Exhibit 2 illustrates the relational schema for
these two tables. The Employee table has four columns with the EmpId col-

Exhibit 21-2. Example of a relational schema.