Introduction

In relational databases the term normalization refers to a reversible step-by-step process in which a given set of relations is replaced by successive collections of relations that have a progressively simpler and more regular structure. Each step, referred to as a normal form, defines a set of criteria (the normal form test) that needs to be met by the different tables of the database. As the normalization progresses to higher forms, the individual collection of relationis becomes progressively more restricted on the type of functionaal dependencies that they can satisfy and the data anomalies that they can experience.

The objectives of the normalization process are:

to make it feasible to represent any relation in the database

to obtain powerful relational retrieval algorithms based on a collection of primitive relational operators

to free relations from undesirable insertion, update and deletion anomalies

to reduce the need for restructuring the relations as new data types are introduced

The first two objectives apply specifically to the first normal form (1NF); the last two apply to all normal forms.

The entire normalization process is based upon the analysis of relations, their schemes, their primary keys and their functional dependencies. Initially E. F. Codd proposed three normal forms that he called first, second and third normal form. These are usually abbreviated as 1NF, 2NF, 3NF.

First Normal Form

When designing a database, it's always wise to populate the tables with sample data. Sometimes, during the design process, you will notice that a table has entries with more than one value. Notice in the table below that the first row has multiple values for employee name, ID, department, hourly rate and total hours. So we can see that each project (first row is project ID 100) is worked on by multiple employees.

proj id	proj name	proj mgr id	emp id	emp name	emp dept	emp hrly rate	total hrs
100	E-Commerce	789487453	123423479 980808980 234809000 542298973	Heydary Jones Alexander Johnson	MIS Techsupport Techsupport Techdoc	65 45 35 30	10 6 6 12
110	Distance Ed	820972445	432329700 689231199 712093093	Mantle Richardson Howard	MIS TechSupport Techdoc	50 35 30	5 12 8
120	Cyber	980212343	834920043 380802233 553208932 1234234709	Lopez Harrison Olivier Heydary	Engineering Techsupport Techdoc MIS	80 35 30 65	4 11 12 07
130	Nitts	550227043	340783453	Shaw	MIS	65	07

Table entries that have more than one value are called multivalue entries. Tables containing multivalue entries are unnormalized.

To be able to represent this table as a relation and to implemen it in a RDBMS, we must normalize the table. In other words, we need to put the table in first normal form.

First normal form (1NF)a relation is said to be in first normal form inf and only if every entry of the relation (intersection of a row (or tuple) and column, has at most one value

The objective of normalizing a table is to remove the repeating groups and ensure all entries have at most a single value. A table can be flattened to achieve normalization. To flatten a table, simply copy the non-repeating attributes and create one row for each repeating value. The first few rows of a flattened version of our table are shown below:

proj id	proj name	proj mgr id	emp id	emp name	emp dept	emp hrly rate	total hrs
100	E-Commerce	789487453	123423479	Heydary	MIS	65	10
100	E-Commerce	789487453	980808980	Jones	Techsupport	45	6
100	E-Commerce	789487453	234809000	Alexander	Techsupport	35	6
100	E-Commerce	789487453	542298973	Johnson	Techdoc	30	12

We have copied information from non-repeating attributes in the first row of the original table, so that each row/column intersection contains only one value. This now meets the rule for first normal form.

Student Exercise 2

Let's answer some questions related to flattening the table.

Browse to your notepad++ window with yourlastnameLab_Module9.txt open

Enter the data from the 2nd row of the original table (project id 110) as it should be after the table is flattened. You don't have to create a table, just enter each row of information on a seperate line and provide adequate spacing between columns

Would you put a table like this in your database? Why or why not?

Can you help with excerise two? thanks