Download Database chapter 05 full lecture notes and more Lecture notes Database Programming in PDF only on Docsity!
CHAPTER 5:CHAPTER 5:
PHYSICAL DATABASE DESIGN AND PHYSICAL DATABASE DESIGN AND
PERFORMANCE PERFORMANCE
Ā© 2013 Pearson Education, Inc. Publishing as Prentice Hall 1
Modern Database Management
th
Edition
Jeffrey A. Hoffer, V. Ramesh, Heikki Topi
Chapter 5 Ā© 2013 Pearson Education, Inc. Publishing as Prentice HallĀ© 2013 Pearson Education, Inc. Publishing as Prentice Hall
OBJECTIVES OBJECTIVES
Define termsDefine terms
Describe the physical database designDescribe the physical database design
process process
Choose storage formats for attributesChoose storage formats for attributes
Select appropriate file organizationsSelect appropriate file organizations
Describe three types of file organizationDescribe three types of file organization
Describe indexes and their appropriate useDescribe indexes and their appropriate use
Translate a database model into efficientTranslate a database model into efficient
structures structures
Know when and how to use denormalizationKnow when and how to use denormalization
Chapter 5 Ā© 2013 Pearson Education, Inc. Publishing as Prentice HallĀ© 2013 Pearson Education, Inc. Publishing as Prentice Hall
PHYSICAL DESIGN PROCESS PHYSICAL DESIGN PROCESS
ļ¬Normalized relations
ļ¬Volume estimates
ļ¬Attribute definitions
ļ¬Response time
expectations
ļ¬Data security needs
ļ¬Backup/recovery needs
ļ¬Integrity expectations
ļ¬DBMS technology used
Inputs
ļ¬Attribute data types
ļ¬Physical record
descriptions (doesnāt always
match logical design)
ļ¬File organizations
ļ¬Indexes and database
architectures
ļ¬Query optimization
Leads to
Decisions
Chapter 5 Ā© 2013 Pearson Education, Inc. Publishing as Prentice HallĀ© 2013 Pearson Education, Inc. Publishing as Prentice Hall
PHYSICAL DESIGN FOR PHYSICAL DESIGN FOR
REGULATORY REGULATORY
COMPLIANCE COMPLIANCE
Sarbanes- Oxley Act (SOX) ā protectSarbanes- Oxley Act (SOX) ā protect
investors by improving accuracy and investors by improving accuracy and
reliability reliability
Committee of Sponsoring OrganizationsCommittee of Sponsoring Organizations
(COSO) of the Treadway Commission (COSO) of the Treadway Commission
IT Infrastructure Library (ITIL)IT Infrastructure Library (ITIL)
Control Objectives for Information andControl Objectives for Information and
Related Technology (COBIT) Related Technology (COBIT)
Regulations and standards that impact physical design decisions
Figure 5-1 Composite usage map (Pine Valley Furniture Company) (cont.) Data volumes
Chapter 5 Ā© 2013 Pearson Education, Inc. Publishing as Prentice HallĀ© 2013 Pearson Education, Inc. Publishing as Prentice Hall
8 Figure 5-1 Composite usage map (Pine Valley Furniture Company) (cont.) Access Frequencies (per hour)
Chapter 5 Ā© 2013 Pearson Education, Inc. Publishing as Prentice HallĀ© 2013 Pearson Education, Inc. Publishing as Prentice Hall
Figure 5-1 Composite usage map (Pine Valley Furniture Company) (cont.) Usage analysis: 7500 suppliers accessed per hour ļØ 4000 quotations accessed from these 7500 supplier accesses ļØ 4000 purchased parts accessed from these 4000 quotation accesses
Chapter 5 Ā© 2013 Pearson Education, Inc. Publishing as Prentice HallĀ© 2013 Pearson Education, Inc. Publishing as Prentice Hall
Chapter 5 Ā© 2013 Pearson Education, Inc. Publishing as Prentice HallĀ© 2013 Pearson Education, Inc. Publishing as Prentice Hall
DESIGNING FIELDS DESIGNING FIELDS
Field: smallest unit of applicationField: smallest unit of application
data recognized by system data recognized by system
software software
Field designField design
Choosing data typeChoosing data type
Coding, compression, encryptionCoding, compression, encryption
Controlling data integrityControlling data integrity
Figure 5-2 Example of a code look-up table (Pine Valley Furniture Company)
Code saves space, but
costs an additional lookup
to obtain actual value
Chapter 5 Ā© 2013 Pearson Education, Inc. Publishing as Prentice HallĀ© 2013 Pearson Education, Inc. Publishing as Prentice Hall
Chapter 5 Ā© 2013 Pearson Education, Inc. Publishing as Prentice HallĀ© 2013 Pearson Education, Inc. Publishing as Prentice Hall
FIELD DATA INTEGRITY FIELD DATA INTEGRITY
Default valueāassumed value if noDefault valueāassumed value if no
explicit value explicit value
Range controlāallowable value limitationsRange controlāallowable value limitations
(constraints or validation rules) (constraints or validation rules)
Null value controlāallowing or prohibitingNull value controlāallowing or prohibiting
empty fields empty fields
Referential integrityārange control (andReferential integrityārange control (and
null value allowances) for foreign-key to null value allowances) for foreign-key to
primary-key match-ups primary-key match-ups
Sarbanes-Oxley Act (SOX) legislates importance of financial data integrit
Chapter 5 Ā© 2013 Pearson Education, Inc. Publishing as Prentice HallĀ© 2013 Pearson Education, Inc. Publishing as Prentice Hall
DENORMALIZATION DENORMALIZATION
ļ TransformingTransforming normalizednormalized relations intorelations into non-non- normalized normalized physical record specificationsphysical record specifications ļ (^) Benefits:Benefits:
ļ Can improve performance (speed) by reducing number ofCan improve performance (speed) by reducing number of
table lookups (i.e. table lookups (i.e. reduce number of necessary join queriesreduce number of necessary join queries ))
ļ (^) Costs (due to data duplication)Costs (due to data duplication)
ļ Wasted storage spaceWasted storage space
ļ Data integrity/consistency threatsData integrity/consistency threats
ļ (^) Common denormalization opportunitiesCommon denormalization opportunities
ļ One-to-one relationship (Fig. 5-3)One-to-one relationship (Fig. 5-3)
ļ Many-to-many relationship with non-key attributesMany-to-many relationship with non-key attributes
(associative entity) (Fig. 5-4) (associative entity) (Fig. 5-4)
ļ Reference data (1:N relationship where 1-side has data notReference data (1:N relationship where 1-side has data not
used in any other relationship) (Fig. 5-5) used in any other relationship) (Fig. 5-5)
Figure 5-3 A possible denormalization situation: two entities with one-
to-one relationship
Chapter 5 Ā© 2013 Pearson Education, Inc. Publishing as Prentice HallĀ© 2013 Pearson Education, Inc. Publishing as Prentice Hall
Figure 5-
A possible
denormalization
situation:
reference data
Extra table access required Data duplication
Chapter 5 Ā© 2013 Pearson Education, Inc. Publishing as Prentice HallĀ© 2013 Pearson Education, Inc. Publishing as Prentice Hall
Chapter 5 Ā© 2013 Pearson Education, Inc. Publishing as Prentice HallĀ© 2013 Pearson Education, Inc. Publishing as Prentice Hall
DENORMALIZE WITH DENORMALIZE WITH
CAUTION CAUTION
Denormalization canDenormalization can
ļ
Increase chance of errors and inconsistenciesIncrease chance of errors and inconsistencies
ļ
Reintroduce anomaliesReintroduce anomalies
ļ
Force reprogramming when business rulesForce reprogramming when business rules
change change
Perhaps other methods could be used toPerhaps other methods could be used to
improve performance of joins improve performance of joins
ļ
Organization of tables in the database (fileOrganization of tables in the database (file
organization and clustering) organization and clustering)
ļ
Proper query design and optimizationProper query design and optimization
