(Continued from previous part...)

RDBMS FUNDAMENTALS

IV. SIMPLIFYING DATA :

Need for Simplifying Data :

Normalization : Normalization is a formal process of developing data structures in a manner that eliminates redundancy and promotes integrity. You need to simplify structure of data in relations for easy storage and retrieval. The process of simplifying relations is called normalization. The new relations that are obtained after normalization are called normalized relations.

Normalization has three well defined steps :
The relations that you get at the end of the first step are said to be in 1NF.
The relations that you get at the end of the second step are said to be in 2NF.
The relations that you get at the end of the third step are said to be in 3NF.

Simplifying Data to 1NF (Eliminate Repeating Groups) : A repeating group is a set of columns that store similar info that repeats in the same table. To simplify data to 1NF, you ensure that all attributes values in a relation have atomic values. If there are attributes in a relation with non-atomic values, move these attributes to a new relation and choose an appropriate primary key for it. E.g. SupItem Table Item field having atomic.

Simplifying Data to 2NF (Eliminate Redundant Data) :
Redundant data is data that is expressed multiple times unnecessarily, or depends only on part of a multi-valued key. Functionally Dependent Attributes : Functionally Dependent Attributes are those that belong to a single entity or relationship and depend on its unique identifier. To simplify data to 2NF, you ensure that all nonkey attributes in a relation are functionally dependent on the whole key and not part of the key.

Conversion from 1NF to 2NF : To convert a relation in 1NF to 2NF, move all nonkey attributes that are not wholly dependent on the primary key, to a new relation. Then, choose an appropriate primary key for the new relation. E.g. Separating Sup. table and Item table.

Simplifying Data to 3NF (Eliminate Columns not Dependent on the Key) :
Columns in each table should be a group of columns in which the data in each column contributes to the description of each row in the table.

Transitively Dependent Attributes : Transitively Dependent Attributes in a relation are those that are dependent on a nonkey attribute and not the primary key. To simplify data to 3NF, you ensure that there are no attributes in a relation that are transitively dependent on other attributes.

Conversion from 2NF to 3NF : To convert a relation in 2NF to 3NF, move all transitively dependent attributes to a new relation. Then, choose an appropriate primary key for the new relation. E.g. Status is dependent on City in Sup. table, so move those two to separate table.
Simplifying Data to 4NF (Isolate Independent Multiple Relationships) :

(Continued on next part...)