Day 59: Understanding Normalization in SQL | Class 11, Chapter 6 - BSEB Class 11-12 Computer Science: 100-Day Bilingual Guide with MCQs, Solutions and Questions

Day 59 – Normalization in SQL

Class 11 Computer Science – Chapter 6

What is Normalization in SQL? (SQL में Normalization क्या है?)

Normalization in SQL is the process of organizing data in a database to reduce redundancy and improve data integrity. It divides a table into smaller tables and establishes relationships between them.
SQL में Normalization डेटाबेस में डेटा को संगठित करने की प्रक्रिया है ताकि पुनरावृत्ति को कम किया जा सके और डेटा की अखंडता में सुधार हो। यह एक टेबल को छोटे टेबल्स में विभाजित करता है और उनके बीच संबंध स्थापित करता है।

Why is Normalization Important? (Normalization क्यों महत्वपूर्ण है?)

• Minimizes redundancy and prevents anomalies.
  पुनरावृत्ति को कम करता है और विसंगतियों को रोकता है।
• Improves data integrity and consistency.
  डेटा की अखंडता और स्थिरता में सुधार करता है।
• Makes database queries faster and more efficient.
  डेटाबेस क्वेरीज़ को तेज़ और अधिक कुशल बनाता है।

Types of Normal Forms (Normalization के प्रकार)

Normalization involves applying a series of rules called normal forms. The most commonly used forms are:

1. First Normal Form (1NF) (प्रथम सामान्य रूप)

A table is in 1NF if all its columns contain atomic values (no repeating groups or arrays).
एक टेबल 1NF में होता है यदि इसके सभी कॉलम में केवल परमाणु मान होते हैं (कोई रिपीटिंग ग्रुप या एरे नहीं होते)।

Example:

Before 1NF:
| StudentID | Name     | Subjects       |
|-----------|----------|----------------|
| 1         | Alice    | Math, Science  |
| 2         | Bob      | English, History|

After 1NF:
| StudentID | Name     | Subject  |
|-----------|----------|----------|
| 1         | Alice    | Math     |
| 1         | Alice    | Science  |
| 2         | Bob      | English  |
| 2         | Bob      | History  |

2. Second Normal Form (2NF) (द्वितीय सामान्य रूप)

A table is in 2NF if it is in 1NF and all non-key attributes are fully dependent on the primary key.
एक टेबल 2NF में होता है यदि यह 1NF में है और सभी गैर-कुंजी विशेषताएँ प्राथमिक कुंजी पर पूरी तरह निर्भर हैं।

Example:

Before 2NF:
| OrderID | Product  | SupplierID | SupplierName |
|---------|----------|------------|--------------|
| 1       | Pen      | 101        | ABC Supplies |
| 2       | Pencil   | 102        | XYZ Supplies |

After 2NF:
Table 1: Orders
| OrderID | Product  | SupplierID |
|---------|----------|------------|
| 1       | Pen      | 101        |
| 2       | Pencil   | 102        |

Table 2: Suppliers
| SupplierID | SupplierName |
|------------|--------------|
| 101        | ABC Supplies |
| 102        | XYZ Supplies |

3. Third Normal Form (3NF) (तृतीय सामान्य रूप)

A table is in 3NF if it is in 2NF and all attributes are only dependent on the primary key (no transitive dependency).
एक टेबल 3NF में होता है यदि यह 2NF में है और सभी विशेषताएँ केवल प्राथमिक कुंजी पर निर्भर हैं (कोई ट्रांजिटिव निर्भरता नहीं है)।

Example:

Before 3NF:
| EmployeeID | Name     | Department | Manager |
|------------|----------|------------|---------|
| 1          | Alice    | HR         | John    |
| 2          | Bob      | IT         | Sarah   |

After 3NF:
Table 1: Employees
| EmployeeID | Name     | Department |
|------------|----------|------------|
| 1          | Alice    | HR         |
| 2          | Bob      | IT         |

Table 2: Departments
| Department | Manager |
|------------|---------|
| HR         | John    |
| IT         | Sarah   |

Advantages of Normalization (Normalization के लाभ)

• Eliminates data redundancy.
  डेटा पुनरावृत्ति को समाप्त करता है।
• Improves database performance.
  डेटाबेस प्रदर्शन में सुधार करता है।
• Ensures data consistency and integrity.
  डेटा की स्थिरता और अखंडता सुनिश्चित करता है।

Practice Questions

Multiple Choice Questions (MCQs)

1. Which normal form eliminates repeating groups?
   (a) 1NF | (b) 2NF | (c) 3NF | (d) BCNF
2. Which normal form removes partial dependency?
   (a) 1NF | (b) 2NF | (c) 3NF | (d) 4NF
3. What is transitive dependency?
   (a) A column dependent on the primary key
   (b) A column dependent on another non-key column
   (c) A column with unique values
   (d) None
4. Which normal form removes transitive dependency?
   (a) 1NF | (b) 2NF | (c) 3NF | (d) BCNF
5. Which of these is not a goal of normalization?
   (a) Reduce redundancy | (b) Improve performance | (c) Increase anomalies | (d) Ensure integrity
6. What is a composite key?
   (a) A key with one attribute
   (b) A key with multiple attributes
   (c) A key with duplicate values
   (d) None
7. What is the primary key?
   (a) A unique identifier for table rows
   (b) A repeating group
   (c) A duplicate column
   (d) None
8. What is the purpose of 3NF?
   (a) To eliminate partial dependency
   (b) To eliminate transitive dependency
   (c) To allow duplicate data
   (d) None
9. What is BCNF?
   (a) Boyce-Codd Normal Form
   (b) Binary Conditional Normal Form
   (c) Balanced Coded Normal Form
   (d) None
10. What happens if a table is not normalized?
    (a) Data redundancy
    (b) Performance issues
    (c) Anomalies
    (d) All of the above

Answers to MCQs:

1: (a), 2: (b), 3: (b), 4: (c), 5: (c), 6: (b), 7: (a), 8: (b), 9: (a), 10: (d)

Short Answer Questions

1. What is the purpose of normalization in SQL?
   Answer: To reduce redundancy and improve data integrity.
2. What are the key differences between 1NF and 2NF?
   Answer: 1NF eliminates repeating groups; 2NF eliminates partial dependency.
3. Explain transitive dependency with an example.
   Answer: Transitive dependency occurs when a non-key column depends on another non-key column.
4. Write the syntax for creating a primary key.
   Answer:

   CREATE TABLE table_name (
       column1 datatype PRIMARY KEY,
       column2 datatype
   );
           

5. List two advantages of 3NF.
   Answer:
   • Removes transitive dependency.
   • Ensures better data integrity.

Long Answer Questions

1. Explain the different types of normal forms with examples.
2. Discuss the benefits of normalization in relational databases.
3. Describe anomalies in an unnormalized table and how normalization resolves them.