Design 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,
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).