D esign a clean and normalized database that handles books for a library application. You need to consider the following rules before you design the database: •All books are mapped to categories. For example, the book Distributed Operating System belongs to: ◦Computer science ◦Operating systems ◦Distributed systems •The following parameters associated with the books are available with the library: ◦Author ◦Quantity ◦Year of purchase ◦Year of publication ◦Publisher •A member can have one of the following memberships: ◦Life members (Platinum) ◦Yearly members (Gold) ◦Monthly members ◦Members with fixed durations ◦Visitors •Depending upon the membership, every member can borrow some books. Using the library project discussed earlier, respond to the following: •Analyze and identify some of the logical problems that might exist in the library system, Answer: How to calculate charges if user returns the book late How to determine the book condition/depreciation Provision of scanning the barcode instead of entering the data manually Generating alerts if due date is over sending emails to all users for new arrivals and delay notifications •Submit the queries required to create the tables in the database. Use proper SQL notation and make sure all tables are normalized (follow third normal form (3NF) schema).

Designing a clean and normalized database for a library application involves considering various rules and parameters. In this case, we need to ensure that all books are mapped to categories and have associated parameters such as author, quantity, year of purchase, year of publication, and publisher. Additionally, we must also consider the different types of memberships available to library members, each with different borrowing privileges.

Before delving into the query to create the tables in the database, let’s analyze and identify some potential logical problems that might exist within the library system:

1. Calculating charges for late book returns: One possible problem is determining how to calculate charges for users who return books late. This involves considering factors such as the length of the delay, the membership type, and the specific policy set by the library for late returns.

2. Determining book condition/depreciation: Another challenge is how to determine and track the condition and depreciation of the books in the library. This information can be crucial for making decisions regarding book maintenance, replacement, or removal from circulation.

3. Provision of scanning barcodes: It might also be beneficial to incorporate the capability to scan book barcodes instead of manually entering the data. This can improve accuracy and efficiency in managing the library’s inventory.

4. Generating alerts for overdue books: The system should be able to generate alerts for books that are overdue, notifying both the member and the library staff. This feature can help ensure timely return of borrowed books and minimize late fees.

5. Sending email notifications for new arrivals and delays: The system should have the capability to send email notifications to all library users regarding new book arrivals and potential delays in obtaining requested books. Timely and informative communication can enhance the user experience and engagement with the library.

Now, let’s proceed with the design by submitting the queries required to create the tables in the database. We will follow the third normal form (3NF) schema to ensure proper normalization. Normalization is a process that minimizes data redundancy and improves the efficiency and integrity of the database.

The necessary tables for the library database would include:

1. BookCategories: This table would store all the unique categories to which books are mapped. It would have a primary key column, CategoryID, and a CategoryName column.

2. Books: This table would store information about individual books. It would have a primary key column, BookID, and foreign key columns to reference the BookCategories table. Additionally, it would have columns for the book’s author, quantity, year of purchase, year of publication, and publisher.

3. Memberships: This table would store information about the different types of library memberships. It would have a primary key column, MembershipID, and a MembershipType column.

4. Members: This table would store information about library members. It would have a primary key column, MemberID, as well as columns for the member’s name, address, contact details, and a foreign key column referencing the Memberships table.

5. Borrowings: This table would store information about book borrowings by members. It would have columns for BorrowingID, MemberID (foreign key reference to Members table), BookID (foreign key reference to Books table), and the borrowing and due dates.

By following the principles of normalization and designing the tables based on their relationships, we can ensure an efficient and effective database for a library application.

Need your ASSIGNMENT done? Use our paper writing service to score better and meet your deadline.

Click Here to Make an Order Click Here to Hire a Writer