# 2NF – What is a 2NF example?

A relation is in 2NF(What is a 2NF example) or second normal form if all the non-prime attributes are fully functionally dependent on the primary key. The non-prime attributes are those that 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 the 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.

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

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

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

2. Create all possible subsets of the above set of primary keys that we have identified in step 1.

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

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

Table: Relation Sale

#### The explanation of table attributes is 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 as 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 into 2NF, the steps are as follows:

• Attributes that make the 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 these primary keys.

So, we have one relation S with Primary key S# and attributes status and city. and 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 below

(a) Relation S

(b) Relation P

(c) Relation SP

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

Insert:

We can insert the information that supplier S lives in the city of 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, I want to update the city of a particular supplier, the update 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 the city of London has status 20. At the same time, information that, supplier S2 is concerned with the city of London.

Update:

The status of a city about S appears repeatedly.

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

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

Check out some amazing blog posts 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?

You can also go through a few more amazing blog links related to DBMS below:

## 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 the 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 a5theorys@gmail.com. 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.