Process-Oriented Design

4.4. Design the Physical Database

Physical database design takes place concurrently with factoring and decomposition. A person with special skills, usually a database administrator (DBA), actually does physical database design. In companies without job specialization, a project team member acts as the DBA to design the physical database. Physical database design is a nontrivial task that may take several weeks or even months.

Rules for Designing the Physical Database 

The general physical database design activities are summarized below. Keep in mind that many other activities may be involved in designing a physical database that relate to a specific implementation environment. 

1. Define user views based on transaction types and data accessed for each transaction.

2. Identify access method if choices exist. 

3. Map user views to access method and storage technology to optimize disk space and to minimize access time. 

4. Build prototype and test, revising as indicated. 

5. Develop database for application testing. 

6. Document physical database design and distribute user view information to all project team members. 

7. Work with conversion team to build production databases. 

Designing user views means to analyze the transactions or inputs of each process to define which database items are required. In general, the data items processed together should be stored together. These logical design activities constrain the physical design and help the person mapping to hardware and software.

In selecting the access method, the physical data designer seeks to optimize matching available access methods to access requirements. Access method choices usually are data sequenced (i.e., indexed), entry sequenced (i.e., direct), inverted lists, or some type b-tree processing. Each DBMS and operating system has its own access method(s) from which selection is made. The details of these access methods are beyond the scope of this text. 

User views are mapped to the access method and a specific media. Media mapping seeks to optimize access time for individual items and sets of items. It also seeks to minimize wasted space while providing for growth of the database. Since media have become one of the major expenses in the computing environment, there may be political issues involved with physical database design. At this point, a database walk-through reviews all database design before a prototype is built.

The DBA documents and trains team members in data access requirements. The DBA, working from the application specification, maps data requirements to user views to processes. Each process, then, has specific data items assigned. Every team member must know exactly what data items to access and how to access them. If a module or program accesses the wrong data item, an inconsistent database might result. Also, minimal data coupling requires that each process access only data that it requires. Incorrect use of access methods can lead to process bottlenecks or an inconsistent database. To assure that programs are using the data correctly, the DBA may participate in walk-throughs to monitor data access. 

The DBA works with the test team to load the data needed for testing. The DBA also works with the conversion team to load the initial production database. These activities may be trivial or may require hiring of temporary clerks to input information to the database. The DBA and the two teams work together to verify the correctness of the data, to provide program test database access to the rest of the development team, and to provide easily accessed backup when the test database is compromised. After the test database is loaded, the backup and recovery procedures, transaction logic procedures, and other database integrity procedures are all finalized and tested.

To summarize, a person who intimately knows the technical production data environment acts as a DBA, mapping the database to a physical environment and building both test and production databases. The DBA provides training and guidance to the other team members for data access, and participates in data related walk-throughs.

ABC Video Example Physical Database Design 

In order to do the physical database design, a DBMS must be selected. We will design as if some SOL engine were being used. SOL's physical design is closely tied to the logical design so the design activity becomes less DBMS software sensitive. In addition, SOL data definition is the same in both mainframe and micro environments so the design activity does not need to be hardware platform sensitive. The amount of storage space (i.e., number of tracks or cylinders) will vary, of course, since disks on PCs do not yet hold as much information as mainframe disks.

Beginning with the logical design from Table 7 -7, we define the relations and data items that are required to develop user views. Remember from database class, that the logical database design can map directly to the physical database. The relations defining the actual database mayor may not be accessed by users. For security reasons, user views may be used to control access to data and only the DBA would even know the real relation names. 

To define user views, we examine each process and identify the data requirements. List the requirements by process (see Table 8-5). Match similar data requirements across processes to identify shared user views. The problem is to balance the number of views against the number of processes. Ideally a handful of user views are defined; a heuristic for large applications is about 20 user views. Beyond that, more DBAs are required and database maintenance becomes difficult. In a large application, keeping the number of user views manageable may be difficult and require several design and walk-through iterations.

For ABC rental processing, we need a user view for each major data store: Customer, Video Inventory, and Open Rentals. We also need user views for the minor files: Video History, Customer History, and End Of Day Totals. If data coupling and memory usage are not an issue, using a SQL database, we can create one user view for each of Customer, Video, and Open Rental, and create one joined user view using the common fields to link them together. The individual views are used for processes that do not need all of the data together; the joined view can be used for query processing and for processes that need all of the data. The resulting data definitions for customer, video, open rentals, and the related user views are shown in Table 8-6. We also need separate user views for the history files and EOD totals. They are included in the table.

At this point, with SQL software, we are ready to prototype the database. If either access method selection or storage mapping is an issue, a prototype should be built. Otherwise, the next step is to map user views to access methods and storage media. This activity depends on the implementation environment and is beyond this text. The database may be walked through again at this point to verify processing requirements for the database. The database is then prototyped and documented. The information needed for each program is included in program specifications. Team members are usually given an overview of the database environment either as part of the last walk-through or as a separate training session. When the prototype appears complete and workable, test and production databases are developed.