coderz.py

What is 2NF?

The second step in Normalization is 2NF. A table must meet two requirements to be in the second normal form:

• The relation must be in the 1NF.
• In the second normal form, all non-key attributes are entirely functionally dependent on the primary key

Let’s see, what we mean by dependency.

What are Database Dependencies, and Why Do They Matter?

A constraint that outlines the connection between attributes is a database dependency. This occurs when data from one database table uniquely determines data from another stored in a different database table. Since database dependencies serve as the fundamental building blocks for database normalization, it is crucial to understand what they are.

The process of effectively organizing data in a database is called normalization. Database normalization aims to achieve two main goals. First, redundant data must be removed, and second, data dependencies must be made logically. By doing this, you can ensure that the data is stored logically and decrease the space a database uses.

What is Partial Dependency?

A table’s attribute may be functionally dependent on only a portion of a composite primary key rather than the entire key, which is known as partial dependency. It happens when a non-key attribute is dependent only on a portion of the columns for the primary key.

Note: If the proper subset of the candidate key determines non-prime attribute, it is called partial dependency.

For example, let’s consider a table of customer orders:

Order_ID	Customer_ID	Customer_Name	Order_Date	Order_Total
1A01	10	Ranbeer Singh	12-03-22	2300
1A02	81	Niharika Yadav	01-01-23	499
1B21	10	Ranbeer Singh	13-02-22	1599

In this table, the Order_ID and Customer_ID combination serves as the primary key. But the Customer_Name attribute only needs the Customer ID; it doesn’t need the entire primary key. This is an example of partial dependency.

We must normalize the table to make sure that every non-key attribute depends on the entire primary key to eliminate partial dependency. To achieve this, we could divide the table into two tables:

Order_ID	Customer_ID	Order_Date	Order_Total
1A01	10	12-03-22	2300
1A02	81	01-01-23	499

Customer_ID	Customer_Name
10	Ranbeer Singh
81	Niharika Yadav

The Customer Name attribute now only depends on the Customer ID and does not depend on any other elements of the primary key. By doing so, the table will be in Second Normal Form (2NF).

Note: also read about Normalization of Database

Follow Me

Please follow me to read my latest post on programming and technology if you like my post.

https://www.instagram.com/coderz.py/

https://www.facebook.com/coderz.py