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#StatusCityP#PnameColorQty.
S110ParisP1BulbPink300
S110ParisP2TubesYellow100
S220LondonP3FanGreen500
S330ItalyP4CoolerBlack200
S330ItalyP1BulbPink450
S440IndiaP5LampWhite400
S510ParisP2TubesYellow250

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.

FD-diagram-in-2NF-normal-form (1)
FD For Relation Sale

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

FD-diagram-in-2NF-normal-form (2)
FD Diagram In 2NF

These relation can be given in following ways.

(a) Relation S

S#Stat usCity
S110Paris
S220London
S330Italy
S440India
S510Paris

(b) Relation P

P#PnameColor
P1BulbPink
P2TubesYellow
P3FanGreen
P4CoolerBlack
P5LampWhite

(c) Relation SP

S#P#Qty
S1P1300
S1P2100
S2P3500
S3P4200
S3P1450
S4P5400
S5P2250

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!

Leave a Reply

Your email address will not be published. Required fields are marked *