Normalization

Normalization is a method for organizing data elements in a database into tables.

 

Normalization Avoids

• Duplication of Data  – avoids same data being listed multiple times on the same table.
• Insert Anomaly  –the data should not be inserted without inserting another entityDelete
• Update Anomaly – tables cannot be updated without change of information in other places.

 

Normalization is normally a three stage process; first normal form, second normal form and third normal form.

Before Normalization

1.      Have the list of all fields you want to put in a database. Think of this as one big table.

2.      Computed fields are not included in the fields above.

3.      Any other additional attribute can be added to the field.

Before Normalization – Example

See Sales Order from below:

Government

 

 

 

 

FinancialFirms

 

 Customer Table

 

In this example the fields in the table will be as follows;

The table would be described as follows:

Customers (Customer_id, payment_method_code, customer_name, date_of_birth)

Government(Account_id, Account_name, date, transaction_id, transaction_type)

FinancialFirms (TransactionID, customerID, Price, item_sold_name, customer_name)

 

You can treat this as a base table.

Normalization:  First Normal Form

• Separate all the repeating groups to a new table.
• Repeating Groups represents fields that may be repeating in nature.
• Then create a new table containing these repeating groups
• Give the repeating groups a primary key. I. e Item number

First Normal Form Example

The new table is as follows:

Customers (Customer_id, payment_method_code, customer_name, date_of_birth)

Government(Account_id, Account_name, date, transaction_id, transaction_type)

FinancialFirms (TransactionID, customerID, Price, item_sold_name, customer_name)

 

Remaining fields will be as follows;

project(num, name)

employee(num, name, job_class, hourly_rate)

The tables would be as follows

Project Table

 

 

 

The two tables represent a foirst normal form of the tables.

Normalization:  Second Normal Form

• Remove data partial dependencies:
• Functional Dependency this is a type of dependency where the value of an attribute in a table is determined by another field in the table.
• Partial Dependency; an attribute is functionally dependent on only part of the primary key (primary key must be a composite key).
• Tables created at this step will usually contain descriptions of resources.Create separate table with the functionally dependent data and the part of the key on which it depends. 

When the tables are put in the second normal form, duplication of data is avoided and from the above, customer_name is almost in every table. When this is taken care of, the data would be as follows;

 

Customers (Customer_id, payment_method_code, customer_name, date_of_birth)

Government (Account_id, Account_name, date, transaction_id, transaction_type)

FinancialFirms (TransactionID, customerID, Price, item_sold_name)

 

Note that there is no customer name in the financialFirms but there is customer ID therefore reducing multiplicity of dublicate data.

 

Third Normal Form Example

The new tables would be:

project(proj_num, Proj_name)

employee(Emp_num, Emp_name, Job_class, chrg_hour,)

job(proj_num, Emp_num, hours)

 

 

The final Table would be as follows;

 

Completed Tables in Third Normal Form

Customers (Customer_id, customer_name, date_of_birth)

GovernmentTransaction (Date, transaction_id, payment_method_code, transaction_type)

FinancialFirms (TransactionID, customerID, Price, item_sold_name)

Account (AccountCode, name)