Controlled Redundancy

1
Controlled Redundancy
2
Database Design Methodology

• Create and check ER model
• Map ER model to tables
• Translate logical database design for target DBMS
• Choose file organizations and indexes
• Design user views
• Design security mechanisms
• Consider the introduction of controlled
  redundancy
• Monitor and tune the operational system

3
Objectives

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

4
Denormalization

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

5
Controlled Redundancy

• Determine whether introducing redundancy in a
  controlled manner by relaxing the normalization
  rules will improve system performance.
• Normalization results in a logical database
  design that is structurally consistent and has
  minimal redundancy.
• However, sometimes a normalized database design
  does not provide maximum processing efficiency.
• May be necessary to accept loss of some of the
  benefits of a fully normalized design in favor of
  performance.
• Also consider that denormalization
• makes implementation more complex
• often sacrifices flexibility
• may speed up retrievals but it slows down updates.

6
Controlled Redundancy (cont.)

• Consider denormalization in following situations,
  specifically to speed up frequent or critical
  transactions
• Step 7.1 Combining 11 relationships
• Step 7.2 Duplicating nonkey columns in 1
  relationships to reduce joins
• Step 7.3 Duplicating FK columns in 1
  relationships to reduce joins
• Step 7.4 Duplicating columns in relationships
  to reduce joins
• Step 7.5 Introducing repeating groups
• Step 7.6 Creating extract tables
• Step 7.7 Partitioning tables.

7
Combining 11 relationships
8
Duplicating nonkey columns in 1 relationships
to reduce joins
SELECT vfr., v.dailyRental FROM VideoForRental
vfr, Video v WHERE vfr.catalogNo v.catalogNo
AND branchNo B001
SELECT vfr. FROM VideoForRental vfr WHERE
branchNo B001
9
Duplicating Lookup Table Column
10
Duplicating FK columns in 1 relationship to
reduce joins
SELECT ra. FROM RentalAggrement ra,
VideoForRental vfr WHERE ra.videoNo vfr.videoNo
AND vfr.branchNo B001

• SELECT
• FROM RentalAggrement
• WHERE branchNo B001
• This only works because the new relationship
  between Branch and RentalAggrement is 1.

11
Cannot Duplicate FK columns in relationship
Video
1..1
Is
1..
VideoForRent
Branch
IsAllocated
1..1
1..

• List the video titles in stock at a branch.
• SELECT v.title
• FROM Video v, VideoForRent vfr
• WHERE v.catalogNo vfr.catalogNo AND
  vfr.branchNo B001
• Cannot add the branchNo column to the Video
  table.
• But we could consider duplicating the title
  column of the Video table in the VideoForRent
  table, although the increased storage may be more
  significant.

12
Duplicating columns in relationships to
reduce joins

• Lists the video titles and roles that each actor
  has starred in.
• SELECT v.title, a., r.
• FROM Video v, Role r, Actor a
• WHERE v.catalogNo r.catalogNo AND r.actorNo
  a.actorNo

SELECT a., r. FROM Role r, Actor a WHERE
r.actorNo a.actorNo
13
Introducing repeating groups

• Branch(branchNo, street, city, state, zipCode,
  mgrStaffNo)
• Telephone(telNo, branchNo)
• Duplicating TelNo columns in Branch
• Branch(branchNo, street, city, state, zipCode,
  telNo1, telNo2, telNo3, mgrStaffNo)

14
Creating extract tables

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

15
Partitioning tables

• Rather than combining tables, could decompose a
  table into a smaller number of partitions.
• Horizontal partition distribute records across a
  number of (smaller) tables.
• Vertical partition distribute columns across a
  number of (smaller) tables. PK duplicated to
  allow reconstruction.
• Partitions useful for applications that store and
  analyze large amounts of data.

16
Partitioning tables (cont.)
17
Partitioning tables (cont.)

• Advantages
• Improved load balancing
• Improved performance
• Increased availability
• Improved recovery
• Security.
• Disadvantages
• Complexity
• Reduced performance
• Duplication.