Week 5 Assignement

Assignment: Normalization to 3NF

Scenario Overview:

The university library tracks which students borrow which books. Each book may have multiple authors, and each author has a nationality. The system initially stored this information in a single table, but the library realized that this approach led to several data problems. Your task is to help the library by identifying and fixing these problems using the normalization process (1NF, 2NF, and 3NF). Please use screenshot attached.

In the initial table:

• Some columns contain multiple values in a single cell, such as the Authors and AuthorNationality columns. For example:
• Dr. Smith (American) and Dr. Brown (British) are both authors of the book “Database Systems”.
• When reading the Authors and AuthorNationality columns, interpret the values in order: Dr. Smith is American, and Dr. Brown is British.
• The library has decided to use a composite key consisting of StudentID and BookID in their database because a student can borrow multiple books, and each book can be borrowed by multiple students. Together, these two fields uniquely identify each borrowing event.

Step-by-Step Questions and Tasks:

Step 1: Identify 1NF Violation

Question 1: Does the initial table satisfy 1NF (First Normal Form)? Why or why not?

• Hint: Look for columns with multiple values stored in a single cell.

Task 1: Fix the 1NF violation.

Step 2: Identify 2NF Violation

Question 2: Does the new table (after fixing 1NF) satisfy 2NF (Second Normal Form)? Why or why not?

• Hint: In 2NF, every non-key attribute must depend on the entire composite key.

Task 2: Fix the 2NF violation.

Step 3: Identify 3NF Violation

Question 3: Does the new table (after fixing 2NF) satisfy 3NF (Third Normal Form)? Why or why not?

• Hint: In 3NF, there must be no transitive dependencies. Does any non-key column depend on another non-key column?

Task 3: Fix the 3NF violation.

Final Table Format after Normalization:

After completing the normalization process, the database will consist of four tables:

1. Students Table Contains information about each student.
2. Authors Table Contains information about each author and their nationality.
3. Books Table Contains information about each book and its author(s).
4. Borrowing Table Tracks which student borrowed which book and for how long.

Final Questions:

1. Why was it important to fix the 1NF violation?
2. How did you fix the partial dependencies issue that caused the table to violate 2NF?
3. What was the transitive dependency that violated 3NF, and how did you fix it?