ONGOING MAINTENANCE AND GROWTH - Database Design and Development: An Essential Guide for IT Professionals - page 538

• Placing of data and indexes across multiple disks, as in a RAID configuration,
helps speed up concurrent transactions.
• In environments with a high volume of transactions, B-tree indexes themselves
may be the bottlenecks. Change indexing techniques in such cases.
Tuning the Schema
Sometimes, you will find that the design of the conceptual and the physical schema
does not meet the objectives of the evolving workload. In that case, the schema itself
must be examined. You may consider changing the file organizations and allocation
of database records to physical files. Changing the conceptual or logical schema
requires substantial effort and time.
Here are a few options to be considered while examining the logical schema for
the purpose of revisions:
• Decide to settle for third normal form (3NF) relations instead of further nor-
malization into Boyce-Codd normal form (BCNF). The more you normalize a
data structure, the more decomposed the relations become. Data access to
retrieve a piece of information will have to extend to extra relations. For all
practical purposes, 3NF proves to be adequate.
• Data retrieval patterns may indicate that some parts of a table are more fre-
quently accessed than others. Splitting the table appropriately speeds up pro-
cessing in such cases. Opt for horizontal or vertical partitioning.
• Denormalize in certain situations. For example, if the DEPARTMENT
table and the EMPLOYEE table are in a one-to-many relationship and
the DepartmentName attribute is required in most queries, it is worthwhile
to denormalize the EMPLOYEE table. Repeat the DepartmentName
attribute in the EMPLOYEE table. Such controlled redundancies improve
efficiency.
CHAPTER SUMMARY
• The database development life cycle does not terminate as soon as you deploy
the database system. Ongoing maintenance just gets started and continues as
long as the database is in operation.
• The project team members, especially the DBA, must provide for growth and
keep the database system tuned for top performance.
• Routine maintenance includes the following: regular backups and preparation
for recovery from possible failures, administration of security, storage space
management, concurrency control, and problem resolution.
• In large organizations, security maintenance is a major ongoing activity.
• The DBA and other responsible professionals need to monitor the database
environment continually for growth planning and tuning the database for
performance.