CSIT$154$

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.

Submission instructions:

Download this assignment and write your answers under each question. Save as pdf$.$ Upload into brightspace.

To represent each table, you can write the answers horizontally. For example:

Tablename, columnName $($pk$),$ columnName, columnName, columnName$($fk$),$ etc.