Total Page Preview: 000000000641
What is 2NF in SQL in English and Hindi
2NF in SQL in English
2NF stands for Second Normal Form, which is a property of a relational database table. A table is in 2NF if it is in 1NF and every non-key attribute is fully dependent on the primary key.
-
In the 2NF, relational must be in 1NF.
-
In the second normal form, all non-key attributes are fully functional dependent on the primary key
To explain this in more detail, consider the following example of a table named "orders":
In this table, the primary key is "Order Number" and the non-key attributes are "Product Name", "Quantity", "Supplier Name", and "Supplier Address".
However, we can see that "Supplier Name" and "Supplier Address" are dependent only on the supplier, not on the order. In other words, they are not fully dependent on the primary key. This violates the condition for 2NF.
To bring this table into 2NF, we need to split it into two separate tables: "orders" and "suppliers".
In this revised schema, the "orders" table now contains only information directly related to the orders, and "Supplier Name" and "Supplier Address" have been removed to a separate table called "suppliers". This means that the table is now in 2NF, as every non-key attribute is fully dependent on the primary key.
2NF in SQL in Hindi
"Second Normal Form" (2NF) ek relational database management system (RDBMS) ka concept hai, jiske dwara tables ko design kiya jata hai. 2NF ko maintain karne ke liye table ko 1NF hona chahiye, saath hi saath tables ke columns ke beech mai functional dependencies ko maintain karna chahiye. Functional dependency ka matlab hai ki koi ek column dusre column ki value ko uniquely determine karta hai.
2NF ke example ke liye, hum ek "students_courses" table consider karte hai jisme student_id, course_id, course_name, course_description, aur course_instructor columns hai:
students_courses table:
Yahan par hum dekh sakte hai ki "course_name", "course_description", aur "course_instructor" columns student_id aur course_id ke saath redundantly repeat ho rahe hai. Yeh 2NF ko violate karta hai.
Is table ko 2NF mai convert karne ke liye, hume "courses" table ko create karna chahiye jisme course_id, course_name, course_description, aur course_instructor columns honge. Uske baad hum students_courses table ko split karenge taki ek student ek course ko multiple baar attend kare:
courses table:
students_courses table in 2NF:
Is tarah se, humne "students_courses" table ko 2NF mai convert kar diya hai, jisse ab yeh redundancies nahi rakhta hai. Yeh ab ek normalized table hai jisme course details ek alag table mai hai aur students un courses ko attend karne ke liye reference kar rahe hai.
Thank You
About Author
Brijesh Kumar
Database Developer
I have more then 6 years Experience in Microsoft Technologies - SQL Server Database, ETL
Azure Cloud - Azure SQL Database, CosmosDB, Azure Data Factory, PowerBI, Web Job, Azure Function, Azure Storage, Web Apps, Powershall
and Database Migration On-Premise to Azure Cloud.
LinkedIn : https://www.linkedin.com