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

8.2 P HYSICAL D ATABASE D ESIGN I NPUTS

YOUR

TURN

C onsider a university information sys-
tems environment or another information systems envi-
ronment of your choice. Think about a set of 5-10
applications that constitute the main applications in this
environment.

What would the volumes be of the database tables
needed to support these applications? How volatile
would you expect the data to be? What concerns
would you have about the security and privacy of the
data?

Q UESTION :
For each of these 5-10 applications, specify the response
time requirements and the throughput requirements.

DBMS Characteristics All relational database management systems are certainly
similar in that they support the basic, even classic at this point, relational model.
However, relational DBMSs may differ in certain details, such as the exact nature
of their indexes, attribute data type options, SQL query features, etc., that must be
known and taken into account during physical database design.

Hardware Characteristics Certain hardware characteristics, such as processor speeds
and disk data transfer rates, while not directly parts of the physical database design
process, are associated with it. Simply put, the faster the hardware, the more tolerant
the system can be of a physical design that avoids relatively severe changes in the
logical design.

PHYSICAL DATABASE DESIGN TECHNIQUES

Figure 8.20 lists several physical database design categories and techniques within
each. The order of the categories is significant. Depending on how we modify
the logical design to try to make performance improvements, we may wind up
introducing new complications or even reintroducing data redundancy. Also, as
noted in Figure 8.20, the first three categories do not change the logical design
while the last four categories do. So, the order of the categories is roughly from
least to most disruptive of the original logical design. And, in this spirit, the only
techniques that introduce data redundancy (storing derived data , denormalization ,
duplicating tables, and adding subset tables ) appear at the latter part of the list.

Adding External Features
This first category of physical design changes, adding external features, doesn't
change the logical design at all! Instead, it involves adding features to the logical
design, specifically indexes and views. While certain tradeoffs have to be kept
in mind when adding these external features, there is no introduction of data
redundancy.