Databases Reference
In-Depth Information
3.
Define extended tables with extra columns, when appropriate, to reduce the
number of joins required for dominant queries.
4.
Evaluate total cost for storage, query, and update for the database schema, with
and without the extended table, and determine which configuration minimizes
total cost.
5.
Consider also the data integrity due to denormalization. If an extended table
appears to have lower storage and processing (query and update) costs and
insignificant data integrity problems, then use that schema for physical design
in addition to the original candidate table schema. Otherwise use only the orig-
inal schema. Also, try very hard to use database management system (DBMS)
features to keep the redundant data in sync with the base data.
15.4
Example of Denormalization
The following example illustrates how to proceed through the database life cycle, in a
practical way, for a simple relational database. We will see how denormalization extends
a logical design methodology to attain significant improvements in performance, given
that the available access methods are known.
15.4.1 Requirements Specification
The management of a large retail store would like a database to keep track of sales activ-
ities. The requirements for this database lead to the following six entities and their
unique identifiers:
Entity Entity ID ID length Cardinality
(average, in bytes)
Customer custNum 6 80,000
Job jobTitle 24 80
Order orderNum 9 200,000
Salesperson salesName 20 150
Department deptNum 2 10
Item
itemNum
6 5,000
The following assertions describe the data relationships:
•
Each customer has one job title, but different customers may have the same
job title. (Note: Consider this a special database where customer job titles are
important.)
