# Database Normalization Assignment

## 1. Introduction to Normalization

Normalization is a systematic process of organizing data in a database to reduce redundancy and

improve data integrity. The primary goals are to:

- Eliminate redundant data.

- Ensure logical data dependencies.

- Minimize insertion, update, and deletion anomalies.

## 2. First Normal Form (1NF)

### Definition

A relation is in **1NF** if:

- All attributes contain atomic (indivisible) values.

- There are no repeating groups.

### Example: Unnormalized Table

**Table: STUDENT (Unnormalized)**

| Course_Code | Course_Name | Teacher_Name | RollNo | Name | System_Used |

Hourly_Rate | Total_Hrs |

|-------------|--------------|--------------|---------------|---------------|-------------|-------------|-----------|

| C1 | Visual Basic | ABC | 100,101,102,103 | A1,A2,A3,A4 | P-I,P-II,...| 20,30,... | 7,3,6,1

|

### Conversion to 1NF

**Table: STUDENT (1NF)**

| Course_Code | Course_Name | Teacher_Name | RollNo | Name | System_Used | Hourly_Rate |

Total_Hrs |
|-------------|--------------|--------------|--------|------|-------------|-------------|-----------|

| C1 | Visual Basic | ABC | 100 | A1 | PentiumI | 20 |7 |

| C1 | Visual Basic | ABC | 101 | A2 | PentiumII | 30 |3 |

| C1 | Visual Basic | ABC | 102 | A3 | Celeron | 10 |6 |

| C1 | Visual Basic | ABC | 103 | A4 | PentiumIV | 40 |1 |

**Primary Key:** (Course_Code, RollNo)

### Anomalies in 1NF

- **Insertion Anomaly:** Cannot add a course without a student, or a student without a course.

- **Update Anomaly:** Changing a teacher's name requires updating multiple rows.

- **Deletion Anomaly:** Deleting a student may also delete course information.

## 3. Second Normal Form (2NF)

### Definition

A relation is in **2NF** if:

- It is in 1NF.

- Every non-key attribute is **fully functionally dependent** on the primary key.

### Problem in 1NF Table

In the 1NF STUDENT table:

- `(Course_Code, RollNo) → Total_Hrs` ✓

- But `RollNo → Name, System_Used, Hourly_Rate` ✗ (partial dependency)

### Decomposition into 2NF

**Table: COURSE**

| Course_Code | Course_Name | Teacher_Name |

|-------------|--------------|--------------|
| C1 | Visual Basic | ABC |

| C2 | Oracle&Dev | DEF |

**Table: STUDENT_SYSTEM_CHARGE**

| RollNo | Name | System_Used | Hourly_Rate |

|--------|------|-------------|-------------|

| 100 | A1 | PentiumI | 20 |

| 101 | A2 | PentiumII | 30 |

**Table: HOURS_ASSIGNED**

| Course_Code | RollNo | Total_Hrs |

|-------------|--------|-----------|

| C1 | 100 | 7 |

| C1 | 101 | 3 |

### Anomalies Removed in 2NF

- **Insertion:** Can add a student without a course.

- **Update:** Change teacher name in one row.

- **Deletion:** Delete student without losing course info.

## 4. Third Normal Form (3NF)

### Definition

A relation is in **3NF** if:

- It is in 2NF.

- No **transitive dependency** exists (non-key attribute depends on another non-key attribute).

### Problem in 2NF Table

In STUDENT_SYSTEM_CHARGE:

- `RollNo → System_Used`
- `System_Used → Hourly_Rate` (transitive dependency)

### Decomposition into 3NF

**Table: STUDENT_SYSTEM**

| RollNo | Name | System_Used |

|--------|------|-------------|

| 100 | A1 | PentiumI |

| 101 | A2 | PentiumII |

**Table: CHARGES**

| System_Used | Hourly_Rate |

|-------------|-------------|

| PentiumI | 20 |

| PentiumII | 30 |

### Anomalies Removed in 3NF

- **Insertion:** Can add a system rate without a student.

- **Update:** Change rate in one row.

- **Deletion:** Delete last student using a system without losing rate info.

## 5. Boyce-Codd Normal Form (BCNF)

### Definition

A relation is in **BCNF** if:

- Every determinant is a candidate key.

### Example: Manufacturer Table

**Table: MANUFACTURER**
| Id_No | Name | Item_No | Quantity |

|-------|----------------|---------|----------|

| M101 | Electronics USA | H3772 | 1000 |

| M101 | Electronics USA | J08732 | 700 |

**Functional Dependencies:**

- `(Id_No, Item_No) → Quantity`

- `(Name, Item_No) → Quantity`

- `Id_No → Name`

- `Name → Id_No`

**Problem:** `Id_No` and `Name` are determinants but not candidate keys.

### Decomposition into BCNF

**Table: ID_NAME**

| Id_No | Name |

|-------|----------------|

| M101 | Electronics USA|

**Table: ID_QTY**

| Id_No | Item_No | Quantity |

|-------|---------|----------|

| M101 | H3772 | 1000 |

| M101 | J08732 | 700 |

## 6. Detailed Examples of Anomalies

### Insertion Anomalies

**Example 1: 1NF Student-Course Table**

- **Problem:** Cannot insert new course "C5 - Python" without at least one student.

- **Reason:** Primary key (Course_Code, RollNo) requires both values.

- **Solution after 2NF:** Separate COURSE table allows adding courses independently.

**Example 2: 2NF Student-System Table**

- **Problem:** Cannot insert system rate "Laptop - $25" without a student using it.

- **Solution after 3NF:** Separate CHARGES table allows adding system rates independently.

### Update Anomalies

**Example: 1NF Student-Course Table**

- **Problem:** Changing teacher "ABC" to "XYZ" requires updating all rows with Course_Code = C1.

- **Risk:** Missing one update causes data inconsistency.

- **Solution after 2NF:** Update only one record in COURSE table.

**Example: 2NF Student-System Table**

- **Problem:** Changing PentiumI rate from $20 to $25 requires updating all students using it.

- **Solution after 3NF:** Update only one record in CHARGES table.

### Deletion Anomalies

**Example: 1NF Student-Course Table**

- **Problem:** Deleting student RollNo 109 also deletes course "Java" information.

- **Solution after 2NF:** Separate COURSE table preserves course information.

**Example: 2NF Student-System Table**

- **Problem:** Deleting last student using "Cyrix" system loses the rate information.

- **Solution after 3NF:** Separate CHARGES table preserves rate information.

## 7. Higher Normal Forms

### Fourth Normal Form (4NF)

- Removes **multi-valued dependencies (MVD)**.

- **Example:** COURSE_STUDENT_BOOK with MVD: `Course →→ Student_Name` and `Course →→

Text_Book`.

- **Decomposition:**

- COURSE_STUDENT(Course, Student_Name)

- COURSE_BOOK(Course, Text_Book)

### Fifth Normal Form (5NF)

- Deals with **join dependencies**.

- A relation is in 5NF if it cannot be decomposed further without loss of information.

- Mostly theoretical; used when a table must be split into three or more projections.

## 8. Normalization Steps Summary

| Step | Normal Form | Action | Result |

|------|-------------|--------|---------|

| 1 | 1NF | Remove repeating groups | Atomic values |

| 2 | 2NF | Remove partial dependencies | Full functional dependency |

| 3 | 3NF | Remove transitive dependencies | No non-key dependencies |

| 4 | BCNF | Every determinant is candidate key | Stronger 3NF |

| 5 | 4NF | Remove multi-valued dependencies | Independent facts |

| 6 | 5NF | Remove join dependencies | Irreducible decomposition |

## 9. Conclusion

Normalization is a critical database design technique that ensures:

- **Data consistency** through elimination of redundancies.

- **Efficient storage** by minimizing duplicate data.

- **Ease of maintenance** through logical organization.

- **Data integrity** by preventing anomalies.

## 10. Exercises

1. Normalize the following table to 3NF:

**SALES**(OrderID, CustomerID, CustomerName, ProductID, ProductName, Quantity, Price)

2. Identify anomalies in the given table and suggest normalization steps:

**EMPLOYEE**(EmpID, EmpName, DeptID, DeptName, ProjectID, ProjectName, HoursWorked)

3. Explain why a table might be in 3NF but not in BCNF, with an example.

---

*Assignment based on "Simplified Approach to DBMS" - Normalization Chapter*