উত্তরঃ
What is Normalization?
Normalization is a systematic process of organizing the columns and tables of a relational database to minimize data redundancy and improve data integrity. The primary goals of normalization are to eliminate redundant data, ensure data dependencies make logical sense (i.e., only store related data in a table), and reduce data anomalies (insertion, update, and deletion anomalies) that can occur when data is not organized properly. It helps in maintaining a cleaner, more efficient, and robust database.
How 1NF and 2NF work in a database?
First Normal Form (1NF)
A relational table is said to be in First Normal Form (1NF) if and only if all attribute domains contain only atomic (indivisible) values. This means:
- Each column in a table must contain only a single value for each row. There should be no multi-valued attributes (e.g., a single cell containing multiple phone numbers or multiple skill sets).
- Each row in the table must be unique. This is typically ensured by having a primary key.
- There are no repeating groups of columns (e.g., `phone1`, `phone2`, `phone3` columns for a single entity).
How it works: To convert an unnormalized table to 1NF, you identify multi-valued attributes or repeating groups and separate them into distinct rows or separate tables. If a column has multiple values, you typically create new rows for each value, duplicating the other non-multi-valued data, or create a new table linked by a foreign key.
Example:
Consider a table `Employees` before 1NF:
| EmpID |
Name |
Skills |
| 101 |
Alice |
Database, Java |
| 102 |
Bob |
Python |
After applying 1NF (splitting multi-valued 'Skills'):
| EmpID |
Name |
Skill |
| 101 |
Alice |
Database |
| 101 |
Alice |
Java |
| 102 |
Bob |
Python |
Second Normal Form (2NF)
A relational table is said to be in Second Normal Form (2NF) if and only if it is already in 1NF and every non-key attribute is fully functionally dependent on the primary key. This specifically addresses tables with composite primary keys. If a non-key attribute depends only on *part* of a composite primary key, it violates 2NF.
How it works: To convert a 1NF table to 2NF, you identify partial dependencies. For any non-key attribute that is dependent on only a subset of the composite primary key, you remove that attribute and the part of the primary key it depends on to a new table. The original table then retains the full composite key, and the new table's primary key (which was part of the original composite key) becomes a foreign key in the original table.
Example:
Consider a table `Order_Details` in 1NF but violating 2NF (assuming `(OrderID, ProductID)` is the composite primary key):
| OrderID |
ProductID |
Quantity |
ProductName |
ProductPrice |
| 1 |
A001 |
2 |
Laptop |
1200 |
| 1 |
B002 |
1 |
Mouse |
25 |
Here, `ProductName` and `ProductPrice` depend only on `ProductID`, not on the full composite key `(OrderID, ProductID)`. This is a partial dependency.
To convert to 2NF, we split it into two tables:
`Orders` table:
| OrderID |
ProductID |
Quantity |
| 1 |
A001 |
2 |
| 1 |
B002 |
1 |
`Products` table:
| ProductID |
ProductName |
ProductPrice |
| A001 |
Laptop |
1200 |
| B002 |
Mouse |
25 |
Now, `OrderID` and `ProductID` form the composite primary key in the `Orders` table, and `Quantity` fully depends on both. In the `Products` table, `ProductID` is the primary key, and `ProductName` and `ProductPrice` fully depend on `ProductID`. This design is in 2NF.
