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

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 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

This relation can be given in the 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. 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:

Composite key: Composite key vs Super key…
What is the null value Problem in DBMS…
What is the super key in DBMS…
What is a candidate key in DBMS…
What is Key in DBMS…
BCNF – Boyce Codd Normal Form…
3NF – Third normal form…
2NF – Second Normal Form…
What is1NF(First Normal Form)…
What is normalization with an example…
What do you mean by distributed DBMS and what are its types…
What is DBMS ARCHITECTURE…
DBMS vs RDBMS: What is the main difference between DBMS and RDBMS…
What are the functions of DBA in DBMS…
Integrity constraints 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 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!