PHYSICAL DATABASE DESIGN - Fundamentals of Database Management Systems - page 200

Inputs to Physical Database Design
The Tables Produced by the
Logical Database Design Process
Business Environment Requirements
Data Characteristics
Application Characteristics
Operational Requirements: Data
Security, Backup, and Recovery
Physical Database Design Techniques
Adding External Features

Reorganizing Stored Data
Splitting a Table into Multiple Tables
Changing Attributes in a Table
Adding Attributes to a Table
Combining Tables
Adding New Tables
Example: Good Reading Book Stores
Example: World Music Association
Example: Lucky Rent-A-Car
Summary

INTRODUCTION

Database performance can be adversely affected by a wide variety of factors,
as shown in Figure 8.1. Some factors are a result of application requirements and
often the most obvious culprit is the need for joins. Joins are an elegant solution
to the need for data integration, but they can be unacceptably slow in many cases.
Also, the need to calculate and retrieve the same totals of numeric data over and
over again can cause performance problems. Another type of factor is very large
volumes of data. Data is the lifeblood of an information system, but when there is a
lot of it, care must be taken to store and retrieve it efficiently to maintain acceptable
performance. Certain factors involving the structure of the data, such as the amount
of direct access provided and the presence of clumsy, multi-attribute primary keys,
can certainly affect performance. If related data in different tables that must be
retrieved together is physically dispersed on the disk , retrieval performance will be
slower than if the data is stored physically close together on the disk. Finally, the
business environment often presents significant performance challenges. We want
data to be shared and to be widely used for the benefit of the business. However, a
very large number of access operations to the same data can cause a bottleneck that

Factors Affecting Application and Database Performance
• Application Factors
■ Need for Joins
■ Need to Calculate Totals
• Data Factors
■ Large Data Volumes
• Database Structure Factors
■ Lack of Direct Access
■ Clumsy Primary Keys
• Data Storage Factors
■ Related Data Dispersed on Disk
• Business Environment Factors
■ FIGURE 8.1

Factors affecting application
and database performance

Too Many Data Access Operations

Overly Liberal Data Access

■