In this section, we will provide practical exercises to help you understand and apply the concepts of normalization. Each exercise will include a problem statement, followed by a step-by-step solution. This will reinforce your understanding of the normalization process and help you identify and correct anomalies in database design.

Consider the following table structure for a Student database:

1. Identify the anomalies present in the table.
2. Determine the highest normal form (NF) the table currently satisfies.
3. Normalize the table to the highest normal form.

Step 1: Identify Anomalies

• Insertion Anomaly: If a new course is introduced without any students enrolled, we cannot insert the course details without a StudentID.
• Update Anomaly: If the instructor's email changes, we need to update it in multiple rows.
• Deletion Anomaly: If a student drops all courses, we lose the course and instructor information.

Step 2: Determine the Highest Normal Form

• The table is in 1NF because it has no repeating groups.
• It is not in 2NF because there are partial dependencies (e.g., CourseName depends only on CourseID).
• It is not in 3NF because there are transitive dependencies (e.g., InstructorEmail depends on InstructorName).

Step 3: Normalize the Table

First Normal Form (1NF)

• The table is already in 1NF.

Second Normal Form (2NF)

• Remove partial dependencies by creating separate tables for Courses and Instructors.

Student Table | StudentID | StudentName | |-----------|-------------| | 1 | Alice | | 2 | Bob | | 3 | Charlie |

Course Table | CourseID | CourseName | InstructorName | |----------|------------|----------------| | C101 | Math | Dr. Smith | | C102 | Physics | Dr. Jones | | C103 | Chemistry | Dr. Brown |

Enrollment Table | StudentID | CourseID | |-----------|----------| | 1 | C101 | | 2 | C102 | | 1 | C103 | | 3 | C101 |

Third Normal Form (3NF)

• Remove transitive dependencies by creating a separate table for Instructors.

Instructor Table | InstructorName | InstructorEmail | |----------------|-----------------| | Dr. Smith | [email protected] | | Dr. Jones | [email protected] | | Dr. Brown | [email protected] |

Updated Course Table | CourseID | CourseName | InstructorName | |----------|------------|----------------| | C101 | Math | Dr. Smith | | C102 | Physics | Dr. Jones | | C103 | Chemistry | Dr. Brown |

By breaking down the original table into multiple tables, we have eliminated the anomalies and achieved 3NF.

Consider the following Order table:

1. Identify the anomalies present in the table.
2. Determine the highest normal form (NF) the table currently satisfies.
3. Normalize the table to the highest normal form.

Step 1: Identify Anomalies

• Insertion Anomaly: Cannot insert a new product without an order.
• Update Anomaly: Changing customer information requires updating multiple rows.
• Deletion Anomaly: Deleting an order may remove customer or product information.

Step 2: Determine the Highest Normal Form

• The table is in 1NF because it has no repeating groups.
• It is not in 2NF because there are partial dependencies (e.g., CustomerName depends only on CustomerID).
• It is not in 3NF because there are transitive dependencies (e.g., TotalPrice depends on Quantity and UnitPrice).

Step 3: Normalize the Table

First Normal Form (1NF)

• The table is already in 1NF.

Second Normal Form (2NF)

• Remove partial dependencies by creating separate tables for Customers and Products.

Customer Table | CustomerID | CustomerName | |------------|--------------| | 101 | John Doe | | 102 | Jane Smith |

Product Table | ProductID | ProductName | UnitPrice | |-----------|-------------|-----------| | P001 | Laptop | 1000 | | P002 | Mouse | 20 | | P003 | Keyboard | 50 |

Order Table | OrderID | CustomerID | ProductID | Quantity | TotalPrice | |---------|------------|-----------|----------|------------| | 1 | 101 | P001 | 2 | 2000 | | 2 | 102 | P002 | 5 | 100 | | 3 | 101 | P003 | 1 | 50 |

Third Normal Form (3NF)

• Remove transitive dependencies by eliminating TotalPrice as it can be calculated.

Updated Order Table | OrderID | CustomerID | ProductID | Quantity | |---------|------------|-----------|----------| | 1 | 101 | P001 | 2 | | 2 | 102 | P002 | 5 | | 3 | 101 | P003 | 1 |

By decomposing the original table into multiple tables, we have eliminated the anomalies and achieved 3NF.

In this section, we practiced normalizing tables to eliminate anomalies and achieve higher normal forms. We identified insertion, update, and deletion anomalies and resolved them by decomposing tables into smaller, related tables. This process ensures data integrity and reduces redundancy in the database.