Methodology Monitoring and Tuning the Operational System

1
Chapter 17

• Methodology Monitoring and Tuning the
  Operational System
• Transparencies

2
Chapter 17 - Objectives

• Meaning of denormalization.
• When to denormalize to improve performance.
• Importance of monitoring and tuning the
  operational system.

3
Step 8 Consider the Introduction of Controlled
Redundancy

• To determine whether introducing redundancy in a
  controlled manner by relaxing the normalization
  rules will improve the performance of the system.
  

4
Step 8 Consider the Introduction of Controlled
Redundancy

• Result of normalization is a logical database
  design that is structurally consistent and has
  minimal redundancy.
• However, sometimes a normalized database design
  does not provide maximum processing efficiency.
• It may be necessary to accept the loss of some of
  the benefits of a fully normalized design in
  favor of performance.

5
Step 8 Consider the Introduction of Controlled
Redundancy

• Also consider that denormalization
• makes implementation more complex
• often sacrifices flexibility
• may speed up retrievals but it slows down
  updates.

6
Step 8 Consider the Introduction of Controlled
Redundancy

• Denormalization refers to a refinement to
  relational schema such that the degree of
  normalization for a modified relation is less
  than the degree of at least one of the original
  relations.
• Also use term more loosely to refer to situations
  where two relations are combined into one new
  relation, which is still normalized but contains
  more nulls than original relations.

7
Step 8 Consider the Introduction of Controlled
Redundancy

• Consider denormalization in following situations,
  specifically to speed up frequent or critical
  transactions
• Step 8.1 Combining 11 relationships
• Step 8.2 Duplicating nonkey attributes in 1
  relationships to reduce joins
• Step 8.3 Duplicating foreign key attributes in
  1 relationships to reduce joins

8
Step 8 Consider the Introduction of Controlled
Redundancy

• Step 8.5 Introducing repeating groups
• Step 8.6 Merging lookup tables with base
  relations
• Step 8.7 Creating extract tables.

9
Sample Global Relation Diagram
10
Sample Relations
11
Step 8.1 Combining 11 Relationships
12
Step 8.2 Duplicating Nonkey Attributes in 1
Relationships to Reduce Joins
13
Step 8.3 Duplicating Foreign Key Attributes in
1 Relationship to Reduce Joins
14
Step 8.4 Duplicating Attributes in
Relationships to Reduce Joins
15
Step 8.5 Introducing Repeating Groups
16
Step 8.6 Merging Lookup Tables with Base
Relations
17
Step 8.7 Creating Extract Tables

• Reports can access derived data and perform
  multi-relation joins on same set of base
  relations. However, data the report is based on
  may be relatively static or may not have to be
  current.
• Possible to create a single, highly denormalized
  extract table based on the relations required by
  the reports, and allow the users to access
  extract table directly instead of the base
  relations.

18
Step 9 Monitor and Tune the Operational System

• To monitor operational system and improve
  performance of system to correct inappropriate
  design decisions or reflect changing
  requirements.
• DreamHome wish to hold pictures of properties,
  and comments that describe main features of
  property.