Normalization is the process of minimizing redundancy from a relation or set of relations. Redundancy in relation may cause insertion, deletion, and update anomalies. Normal forms are used to eliminate or reduce redundancy in database tables. In practical applications, the most frequently used normal forms 1NF, 2NF, and 3NF.

• First Normal Form (1NF): If a relation contains composite or multi-valued attribute, it violates first normal form or a relation is in first normal form if it does not contain any composite or multi-valued attribute. A relation is in first normal form if every attribute in that relation is singled valued attribute.
• Second Normal Form (2NF): To be in second normal form, a relation must be in first normal form and relation must not contain any partial dependency, i.e. every non-key field is functionally dependent on the entire primary key.
• Third Normal Form (3NF): A relation is in third normal form, if it is in 2NF, and every non-key field is functionally dependent on only the primary key, no transitive dependency.

Now, let’s start with an unnormalized table as below. Each record has a unique ORDER_ID.

ORDERS

ORDER_ ID	DATE	CUST_ ID	CUST_ NAME	STATE	ITEM _NUM	ITEM_ DESCRIP	QUANTITY	PRICE
1001	7/7	A004	George	VA	6531 7890 7956	Tape Table Pens	5 1 1	100.00 400.00 2.00
1002	7/8	A999	Ben	PA	3456	Stove	1	300.00
1003	7/9	A201	Tom	PA	4234 7956	Paper Pens	15 10	234.00 20.00

Activity 1: Provide three reasons why normalization is needed.

Normalize a Table to 1NF

First Normal Form prohibits repeating groups, such as, ITEM_NUM, ITEM_DESCRIP, and PRICE. To convert the table to First Normal Form:

1. Remove the repeating groups from the base table.
2. Create a new table with the Primary Key of the base table and the repeating group.

Assuming that a situation might arise where an order could have multiple customers (i.e., multiple people receiving the items), the attributes of {ORDER_ID, CUST_ID} taken together are the primary key for the relation.

Activity 2: Convert the unnormalized table the 1NF tables. List the relational schemas.

Normalize a Table to 2NF

Second Normal Form removes non-key columns that are not dependent upon the table’s entire primary key. The steps to accomplish this are:

1. Determine which non-key columns are not dependent upon the table’s entire primary key.
2. Remove those columns from the base table.
3. Create a second table with those columns and the column(s) from the Primary Key that they are dependent upon.

To be in Second Normal Form we remove partial key dependencies, or, in other words, each column must be dependent upon the entire primary key. Also, any table with a single column primary key is automatically in Second Normal Form.

Activity 3: Find all the functional dependencies on the first normal form tables and convert the tables to the Second Normal Form.

Normalize a Table to 3NF

Third Normal Form removes transitive dependencies, or, in other words, any columns that are dependent upon another non-key column. The steps to accomplish this are:

1. Determine which columns are dependent upon another non-key column.
2. Remove those columns from the base table.
3. Create a second table with those columns and the non-key column that they are dependent upon.
 

Answer rating: 100% (QA)

Activity 1 Reasons why normalization is needed redundancy will be reduced to avoid inconsistency to ... View the full answer