Normalization

1
Normalization

• Jay Krishnan R
• Infosys Technologies Ltd.

2
Introduction

• Basically used as a tool to aid Requirements
  Analysis
• Bottoms-Up Approach involves building
  structures like tables starting from identifying
  columns and constraints involved in a table
• Based on Mathematical Theory
• Refinement process that can eliminate the
  inconsistencies and ambiguities that might
  feature in an E-R Model
• Definition
• Step by step process of refinement
• Large complex data structures broken down into
  smaller tables where in it is easier to represent
  relationships
• Avoids anomalies with regard to INSERT, UPDATE
  and DELETE to a large extent
• Necessitates that all non-key attributes in a
  table have full-functional, non-partial,
  non-transitive dependencies with the key
  attribute
• Extent of normalization depends on the nature of
  application

3
Why Normalize?

• Removal of unwanted redundancy
• Removal of anomalies with regard to INSERT,
  UPDATE and DELETE operations
• Representation of a business enterprise data
  model in the most optimal manner
• Best method to convert from an Object based
  Logical Data Model to a Physical Relational Data
  Model
• Designer can consider data representation,
  solutions for anomalies, performance etc. with
  equal importance

4
Process of Refinement

• Anomalies given equal importance as design
  principles
• Performance also be taken into consideration
  before freezing the design
• All transactions that can act on this database
  should complete without any bottlenecks in terms
  of implementation or performance
• Normal Forms
• First Normal Form 1NF
• Second Normal Form 2NF
• Third Normal Form 3NF
• Boyce Codd Normal Form - BCNF

5
Some definitions

• Determinant
• X is a determinant of Y if X uniquely identifies
  Y
• Employee Number determines an Employee
• Represented as Empno ? Empname
• Determinants may not always be the key attribute
  in a table
• Functional Dependency
• Hours Worked by an employee is determined by a
  combination of Employee Number and Project Code
• Empno,ProjectCode ? HrsWorked
• Job Title is determined by Job Code
• JobCode ? JobTitle

6
Some definitions

• Full Functional Dependency
• Hours Worked by an employee is determined FULLY
  by ONLY a combination of Employee Number and
  Project Code
• Empno,ProjectCode ? HrsWorked
• Neither Employee Number nor Project Code can
  individually determine Hours Worked by an
  employee in a project
• However, other attributes like Empname,
  ProjectTitle, ClientName etc are dependent on
  either EmpCode or ProjectCode
• Partial Key Dependency
• Dependencies when a non-key attribute partially
  depends on a Primary Key
• Client Name partially depends on
  Empno,ProjectCode. It depends ONLY on
  ProjectCode
• Transitive Dependency
• A non-key attribute depends on another non-key
  attribute which in turn depends on the key
  attribute
• In a table with columns, Empno,Empname,JobCode,Job
  Title and the data being identified by Empno,
  JobTitle depends on Jocode and Jobcode depends on
  Empno. Hence, JobTitle transitively (indirectly)
  depends on Empno.

7
A Scenario

• Consider the following data structure
• Employees are assigned certain Job Codes
  depending on their nature of work and
  specialization
• Employees are assigned to different projects
• What are the anomalies with this design?
• INSERT?
• UPDATE?
• DELETE?
• SELECT?