DBMS- Second Normal Form (2NF)

The Second Normal Form (2NF) is used to establish a relation between the key (simple or composite) attributes and all the non-key attributes of the table.

It is in the second normal form that the dependency between the attributes of the relation is established.

Conditions for Second Normal Form

A table is said to be in Second Normal Form if it completely follows the following set of constraints. The constraints are:

• The relation (table) must be in First Normal Form (1NF).
• Every non-key attribute must fully depend on the key attribute (i.e., the primary key).

NOTE: If the relation has a single attribute only, then it will be in Second Normal Form automatically.

Let us have a closer look and understand the concept with the help of an example.

Example

To have a better understanding, let us consider a relation named PROD_DETAILS that keep details regarding orders of products being manufactured by a firm. The key attributes of the relation are: Prod_No, Order_No.

If we observecarefully, the table is in its first normal formalready. Now let us have a look at its key dependencies. The dependencies are as follows:

1. (Prod_No, Order_No) -> (Qty, Rate)
2. Prod_No -> Rate

Now, the attribute Rate is not fully dependent on the composite key attribute as the rate of any product cannot be dependent on the order number. So, we will conclude that the table is in First Normal Form but is not in its Second Normal Form.

Now, before converting the relation to its Second Normal Form, let us have a look at certain anomalies that will arise in this relation.

Anomalies in relations- Second Normal Form

There are three anomalies that arise here. These are:

1. Insert Anomaly
2. Delete Anomaly
3. Update Anomaly

Let us have a closer look at these.

Insert Anomaly

Insert anomaly arises when we face issues while inserting a new tuple in a relation.

For instance, in the above relation PROD_DETAILS, if we want to just insert a new product then we would face an issue as we will not be able to add the product until some order for that product is placed.

Delete Anomaly

Delete anomaly arises when we tend to delete important information in a relation while deleting a tuple in a relation.

For instance, in the above relation we cancel the order with Order_No 4755. There are two tuples which need to be deleted for this order. Here, important information regarding the rate of Prod_No 165 will also get deleted.

Hence, in future when an order for this deleted product (165) needs to be placed then, we would not have its Rate.

Update Anomaly

Update anomaly arises when we face issues while updating values of tuples in a relation.

For instance, in the above relation we have placed two orders for the product with Prod_No 104. Now, if we wish to update the rate of this product (and we have a very large database) then we will have to search for each and every occurrence of the tuples with this Prod_No.

Now, if we miss to update the rate of any one tuple also, this will lead to inconsistent information in the relation leading to Update Anomaly in the relation.

Converting relation in Second Normal Form

So, in order to convert the PROD_DETAILS relation in second normal form we can split the table into two so that the dependencies between the attributes are well defined. So, on dividing it into two distinct relations we get the following tables say ITEMS and ORDERS.

Table Name: ITEMS

Table Name: ORDERS

Here,

• The non-key attributes of the relations are independently dependent on the key attributes of the respective tables.
• Unnecessary redundancy of data is eliminated.
• And now the relation can belinked with the help of foreign key attributes.

Hence, Second Normal Form has been achieved for the above relation.

Anupreet Kalsi