Chapter 5Lecture 2

1
Chapter 5Lecture 2

• Normalization

2
Normal Forms Review

• Write down the 3 rules of normalization.

3
Improving the Design (Before and After
Normalization

• Table structures are cleaned up to eliminate
  dependencies during normalization
• Normalization cannot be relied on to make good
  designsmust start with an ERD
• The process of normalization is valuable because
  it is an additional check against unwanted
  redundancies.

4
Improving the Design (continued)

• The following items need to be checked
• Correct PK assignment
• Naming conventions for all attributes
• Attribute atomicity
• Adding attributes needed for querying
• Adding relationships necessary to do joins
• Creating derived attributes

5
Normalization and Database Design

• Normalization should be part of design process
• entities must meet Third normal form before table
  structures are created
• Once entities are normalized, structural changes
  to database should be few

6
Normalization and Database Design (continued)

• ER diagram
• Provides the big picture, or macro view, of an
  organizations data requirements and operations
• Created through an iterative process
• Identifying relevant entities, their attributes
  and their relationship
• Use results to identify additional entities and
  attributes

7
Normalization and Database Design (continued)

• Normalization procedures
• Focus on the characteristics of specific entities
• A micro view of the entities within the ER
  diagram
• Difficult to separate normalization process from
  ER modeling process

8
Denormalization

• Creation of normalized relations is important
  database design goal
• As entities are normalized, number of database
  tables expands
• Processing requirements should also be a design
  goal

9
Denormalization (continued)

• Joining larger number of tables takes additional
  disk input/output (I/O) operations and processing
  logic
• Reduces system speed
• Conflicts among design efficiency, information
  requirements, and processing speed are often
  resolved through compromises that may include
  denormalization

10
Denormalization (continued)

• Unnormalized tables in a production database tend
  to have these defects
• Data updates are less efficient because programs
  that read and update tables must deal with larger
  tables
• Indexing is much more cumbersome
• Unnormalized tables yield no simple strategies
  for creating virtual tables known as views

11
Denormalization (continued)

• Use denormalization cautiously
• Understand whyunder some circumstancesunnormaliz
  ed tables are a better choice

12
Assignment

• Do 15-17, pp. 216-217.