Normalization Basics

Scenario

Imagine you are tasked with designing a database for a small library. The library has the following information to manage:

$1.$ Books: Each book has a unique ISBN, a title, an author, a publication year, and a number of available copies.

$2.$ Library Members: Each library member has a unique member ID$,$ a name, an email address, and a phone number.

$3.$ Borrowing Records: Keep track of books that are borrowed by library members. Each record should include a unique transaction ID$,$ the member ID of the borrower, the ISBN of the borrowed book, the date the book was borrowed, and the due date for returning the book.

Tasks

$1.$ Create an initial unnormalized database schema that represents the given information. This schema should consist of three tables: Books, LibraryMembers, and BorrowingRecords.

$2.$ Identify any potential data redundancy, update anomalies, or other issues in your unnormalized schema. Explain why these issues could be problematic.

$3.$ Apply normalization techniques $($up to at least $3$rd Normal Form$)$ to the unnormalized schema to eliminate the identified issues.

Create a database that is free from redundancy and update anomalies.