Agenda

• Normalization – Definition
• Implementation of Normalization Process
• A Practical Example on
  • First Normal Form
  • Second Normal Form
  • Third Normal Foem

Normalization

• Normalization is performed to reduce or eliminate Insertion, Deletion or Update anomalies.
• However, a completely normalized database may not be the most efficient or effective implementation.
• “Denormalization” is sometimes used to improve efficiency.
• Normalization splits database information across multiple tables.
•  To retrieve complete information from a normalized database, the JOIN operation must be used.
• JOIN tends to be expensive in terms of processing time, and very large joins are very expensive.

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.

• 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 project

The Scenario Why Normalization? 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. We could place the data into a table called: tblProjects_Employees 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. Did you notice that “Website” was misspelled in the 3^rd 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 Alliance Softech Pvt Ltd Website 3 times. Not only do we waste storage space  we risk creating Inconsistent data; Inconsistent data •          The more often we have to key in data the more likely we are to make mistakes.   Normalization Process The solution is simply to take out the duplication. We do this by: Identifying a key In this case we can use the project no and employee no to uniquely identify each row

• We create new tables

Clearly we can’t take the data out and leave it out of our database. We put it into a new table consisting of the field      that has the partial dependency and the field it is dependent on. Looking at our example we will need to create two       new tables: We now have 3 tables: Looking at the project, note the reduction in: Redundant data The text “Alliance Softech Pvt Ltd Website” 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 “Webite” The 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. Our model has improved but is still far from perfect. There is still room for inconsistency. Again, we have stored redundant data: the hourly rate- rate category relationship is being stored in its entirety i.e.  We have to key in both the rate category AND the hourly rate. 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. We now have 4 tables: Again, we have cut down on redundancy and it is now impossible to assume Rate category A is associated with anything but $ 250. Our model is now in its most efficient format with:

• Minimum REDUNDANCY
• Minimum INCONSISTENCY

What we have formally done is NORMALIZE the database: At the beginning we had a data structure:

• Project No
• Project Name
• Employee No (1n)
• Employee name (1n)
• Rate Category (1n)
• Hourly Rate (1n)

(1n indicates there are many occurrences of the field – it is a repeating group). To begin the normalization process we start by moving from zero normal form to 1st normal form.

1st Normal Form

The definition of 1st normal form;

• There are no repeating groups
• All the key attributes are defined
• All attributes are dependent on the primary key

So far, we have no keys, and there are repeating groups. So we remove the repeating groups and define the keys and are left with: Employee Project table

• Project number – part of key
• Project name
• Employee number – part of key
• Employee name
• Rate category
• Hourly rate

This table is in first normal form (1NF)

2nd Normal Form

A table is in 2nd normal form if:

• It’s already in first normal form
• It includes no partial dependencies (where an attribute is dependent on only part of the key)

We look through the fields:

• Project name is dependent only on project number
• Employee name, rate category and hourly rate are dependent only on employee number.
•  

So we remove them, and place these fields in a separate table, with the key being that part of the original key they are dependent on. We are left with the following three tables: Employee Project table

• Project number – part of key
• Employee number – part of key

Employee table

• Employee number - primary key
• Employee name
• Rate category
• Hourly rate

Project table

• Project number - primary key
• Project name

The tables are now in 2nd normal form (2NF). Are they in 3rd normal form?

3rd Normal Form

A table is in 3rd normal form if:

• It’s already in second normal form
• It includes no transitive dependencies (where a non-key attribute is dependent on another non-key attribute)

We can narrow our search down to the Employee table, which is the only one with more than one non-key attribute. Employee name is not dependent on either Rate category or Hourly rate, the same applies to Rate category, but Hourly rate is dependent on Rate category. So, as before, we remove it, placing it in it's own table, with the attribute it was dependent on as key. Employee project table

• Project number – part of key
• Employee number – part of key

Employee table

• Employee number - primary key
• Employee name
• Rate Category

Rate table

• Rate category - primary key
• Hourly rate

Project Table

• Project number - primary key
• Project name

These tables are all now in 3rd normal form, and ready to be implemented.

Complete Video Lecture