2NF – Second Normal Form/ How do you know if a relationship is 2nf?/ What is 2nf example?

A relation is in 2NF(What is 2nf example) or second normal form 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.

With the help of this blog post(What is 2nf example), we are going to share What is 2nf example? What is 2nf in the database? What is 2nf and 3nf? How do you know if a relationship is 2nf? 2NF example, What is 3NF, 1NF, 2NF, 3NF, 2NF rules, 2NF check,2NF vs 3NF.

Steps for transformation the relation from 1NF to 2NF(Second normal form):

  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 subsets of the above set of primary keys that we have identified in step1.

3. Designate each of these subsets at the primary key of relation that contains 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 the supplier with a constraint that CITY determines status.
City – Name of the city in which the 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 as 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 the second normal form, the 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 is 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:

The status of a city in relation to S appears repeatedly.

For example: in relation to S the status of the 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 in inconsistency in a database or in relation.

Check it out some amazing blog post related to Normalization:

What is Normalization and why is it needed?

What is 1NF in DBMS?

What is 3NF in DBMS?

What is BCNF in DBMS?

Conclusion:

The summary of this blog post(What is 2nf example) 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.

We have gone through What is 2nf example? What is 2nf in the database? What are 2nf and 3nf? How do you know if a relationship is 2nf? 2NF example, What is 3NF, 1NF, 2NF, 3NF, 2NF rules, 2NF check,2NF vs 3NF|What is 2nf example|

Hope! you would have enjoyed this blog post(What is 2nf example). Please feel free to ask us if you have any queries related to this blog. You can write to us at [email protected]. Have a great time!

Anurag

I am a blogger by passion, a software engineer by profession, a singer by consideration and rest of things that I do is for my destination.