# 2NF – Second Normal Form

A relation is in 2NF if all the non-prime attributes are fully functionally dependent on the primary key. The non-prime attribute is those which are not a primary key. A database is said in 2NF if every relation of it is in 2NF.

### Steps for transformation the relation from 1NF to 2NF:

1. Identify the set of attributes that makes up the primary key:

Here you need find out all the attribute which we can make a primary key.

2. Create all possible subset of above set of primary key that we have identified in step1.

3. Designate each of these subsets at the primary key of a relation that contain those attributes which are dependent on these primary keys.

for example, we take a relation of marketing department named sale with the attributes as shown in a table below.

Table: Relation Sale

 S# Status City P# Pname Color Qty. S1 10 Paris P1 Bulb Pink 300 S1 10 Paris P2 Tubes Yellow 100 S2 20 London P3 Fan Green 500 S3 30 Italy P4 Cooler Black 200 S3 30 Italy P1 Bulb Pink 450 S4 40 India P5 Lamp White 400 S5 10 Paris P2 Tubes Yellow 250

#### The explanation of table attributes are as follows:

S# – Supplier number
Status – this denotes the status of supplier with a constraint that CITY determines status.
City – Name of the city in which supplier lives.
P# – Product number.
Pname – Name of the Product.
Color – Color of the product.
Qty – Denotes quantity of a product sold by a supplier.

This relation is already in 1NF and faces all the anomalies that 1NF faces that is an insertion, deletion, and updating.

After 1NF, for simplicity, we draw the functional dependency diagram of relation as shown in the below diagram.

### To convert this relation in 2NF, the steps are as follows:

• Attributes that make primary key are S# and P# that is {S#, P#}.
• The subset of an above set is {}, {S#}, {P#}, {S#, P#}.
• We designate these subsets as the primary key of the relation with attributes that are dependent on theses primary key.

So, we have one relation S with Primary key S# and attributes status and city. another relation P with primary key P# and attributes Pname and color.

Another relation SP with primary key S# and P# with attribute Qty.

In second normal form, above relation can be given as below

#### These relation can be given in following ways.

(a) Relation S

 S# Stat us City S1 10 Paris S2 20 London S3 30 Italy S4 40 India S5 10 Paris

(b) Relation P

 P# Pname Color P1 Bulb Pink P2 Tubes Yellow P3 Fan Green P4 Cooler Black P5 Lamp White

(c) Relation SP

 S# P# Qty S1 P1 300 S1 P2 100 S2 P3 500 S3 P4 200 S3 P1 450 S4 P5 400 S5 P2 250

### The advantage of 2NF is that the problem that we were facing in 1NF is solved in 2NF. The explanation of the same is given below.

Insert:

We can insert the information that supplier S lives in city Paris, even though S1 does not currently supply any part. by simply inserting an appropriate tuple into relation S.

Delete:

We can delete the connection S1 and P2 by deleting a tuple from relation SP, without losing the information that S1 is located in Paris, as we have stored this information in relation S.

Update:

the city for a given supplier written once, it’s not repeated many times as shown in relation S. The primary key of this relation is S#. For one value of S#, there is only one value of the city, which was repeated many times in 1NF.

So, If want to update the city of a particular supplier, updation is required only once. Even though, 2NF faces some anomalies for removal of which we go for 3NF. These anomalies are given below.

### Anomalies or Problems with 2NF:

Insert:

we can not insert the fact that a particular city has a particular status. e.g – We can not state that a supplier in Italy must have a status 30 until we have some supplier located in that city.

Delete:

If we delete the whole S tuple for a particular city, we delete not only the information for the supplier concerned but also the information that the city has that particular status.

For example: if we delete the second tuple from relation S we lose the information that city London has status 20. At the same time, information that, supplier S2 is concerned with city London.

Update:

Status of a city in relation S appears repeatedly.

For example: in relation S the status of city Paris appears repeatedly. For updating the status of city Paris, first, we have to find all the tuples that have city Paris then we have to update them.

If we miss any tuple, it will result inconsistency in database or in relation.

## Conclusion:

The summary of this blog post is that when we convert a relation from 1NF to 2NF then all the non-prime attribute attributes are functionally dependent on a primary key or prime attribute. You can say that the first normal form is the introduction of the primary key in the relation.

Hope! you would have enjoyed this blog post. Please feel free to ask us if you have any query related to this blog. You can write to us at a5theorys@gmail.com. Have a great time!