Total Page Preview: 000000001056
What is 3NF in SQL in English and Hindi
3NF in SQL in English
3NF stands for Third Normal Form, which is a property of a relational database table. A table is in 3NF if it is in 2NF and every non-key attribute is transitively dependent on the primary key.
-
A relation will be in 3NF if it is in 2NF and not contain any transitive partial dependency.
-
3NF is used to reduce the data duplication. It is also used to achieve the data integrity.
-
If there is no transitive dependency for non-prime attributes, then the relation must be in third normal form.
To explain this in more detail, let's use the following example of a table named "sales":
In this table, the primary key is "Order ID" and the non-key attributes are "Product Name", "Product Category", "Supplier Name", "Supplier City", "Customer ID", "Customer Name", "Customer City", "Quantity", and "Price".
However, we can see that "Supplier City" and "Customer City" are transitively dependent on the primary key through the supplier and customer names, respectively. In other words, they are indirectly dependent on the primary key. This violates the condition for 3NF.
To bring this table into 3NF, we need to split it into three separate tables: "orders", "suppliers", and "customers".
In this revised schema, the "orders" table now contains only information directly related to the orders, and "Supplier Name", "Supplier City", "Customer Name", and "Customer City" have been removed to separate tables called "suppliers" and "customers". This means that the table is now in 3NF, as every non-key attribute is transitively dependent on the primary key.
3NF in SQL in Hindi
3NF (Third Normal Form) ek database normalization level hai jo tab hota hai jab ek relation (table) mein teeno Normal Forms (1NF, 2NF, 3NF) satisfy hoti hai.
3NF ke liye teeno rules hote hai:
-
Sabhi non-key attributes fully dependent honi chahiye primary key par.
-
Sabhi non-key attributes ke beech koi transitive dependency nahi honi chahiye.
-
Sabhi non-key attributes unique honi chahiye.
Isko samjhne ke liye ek example dekhte hai:
Maan lijiye ki humara ek table hai "Order_Details" jisme hum order ki details store karte hai:
Ab dekhte hai ki hum kaise 3NF achieve kar sakte hai:
-
Sabhi non-key attributes fully dependent honi chahiye primary key par.
Hum dekh sakte hai ki "Product_Name", "Product_Category", "Manufacturer", "Manufacturer_Country" aur "Unit_Price" sabhi "Order_ID" par fully dependent hai. Isliye humara table 1NF mein already hai.
-
Sabhi non-key attributes ke beech koi transitive dependency nahi honi chahiye.
Hum dekh sakte hai ki "Manufacturer" aur "Manufacturer_Country" ke beech ek transitive dependency hai. Kyunki "Manufacturer_Country" directly "Order_ID" se nahi depend karta, balki indirectly "Manufacturer" se depend karta hai. Isliye humara table 2NF mein already hai.
Ab hum "Manufacturer_Country" ko "Manufacturer" se alag table mein move karenge. Yeh humara 3NF achieve karne ka step hoga.
Ab humara "Order_Details" table fully 3NF mein 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