Normalization

Introduction
In this exercise we are looking at the optimisation of data structure. The example system we are going to use as a model is a database to keep track of employees of an organisation working on different projects.
Objectives
By the end of the exercise you should be able to:
Show understanding of why we normalize data
Give formal definitions of 1NF, 2NF & 3NF
Apply the process of normalization to your own work
Normalization 3
Three problems become apparent with our current model:
Tables in a RDBMS use a simple grid structure
Each project has a set of employees so we can’t even use this format to enter data into a table. How would you construct a query to find the employees working on each project?
All tables in an RDBMS need a key
Each record in a RDBMS must have a unique identity. Which field should be the primary key?
Data entry should be kept to a minimum
Our main problem is that each project contains repeating groups, which lead to redundancy and inconsistency.
Normalization 5
Addressing our three problems:
Tables in a RDBMS use a simple grid structure
We can find members of each project using a simple SQL or QBE search on either Project Number or Project Name
All tables in an RDBMS need a key
We CAN uniquely identify each record. Although no primary key exists we can use two or more fields to create a composite key.
Data entry should be kept to a minimum
Our main problem that each project contains repeating groups still remains. To create a RDBMS we have to eliminate these groups or sets.
Normalization 6
Did you notice that Madagascar was misspelled in the 3rd record! Imagine trying to spot this error in thousands of records. By using this structure (flat filing) we create:
Redundant data
Duplicate copies of data – we would have to key in Madagascar travel site 3 times. Not only do we waste storage space we risk creating;
Inconsistent data
The more often we have to key in data the more likely we are to make mistakes. (see IT01 notes on the importance of accurate data).
Normalization 11
Looking at the project note the reduction in:
Redundant data
The text “Madagascar travel site” is stored once only, not for each occurrence of an employee working on the project.
Inconsistent data
Because we only store the project name once we are less likely to enter “Madagascat”
The page link is made through the key, Project No. Obviously there is no way to remove this duplication without losing the relation altogether, but it is far more efficient storing a short number repeatedly, than a large chunk of text.
Normalization 13
The solution, as before, is to remove this excess data to another table. We do this by:
Looking for Transitive Relationships
Relationships where a non-key attribute is dependent on another non-key attribute. Hourly rate should depend on rate category BUT rate category is not a key
Removing Transitive Relationships
As before we remove the redundant data and place it in a separate table. In this case we create a new table tblRates and add the fields rate category and hourly rate. We then delete hourly rate from the employees table.
Normalization 15
Again, we have cut down on redundancy and it is now impossible to assume Rate category A is associated with anything but £90.
Our model is now in its most efficient format with:
Minimum REDUNDANCY
Minimum INCONSISTENCY