Book Hub Database Model

The Project

This is a MySQL database model called Book Hub, which is intended for use by the librarian of the same establishment. Overall, the database supports E-Commerce and Data and Transaction storage for The Book Hub. It was made as a capstone project to showcase my understanding and knowledge base of SQL. The range of this database model includes: i) Facilitating user data for the library. (username, password,card_id, etc.) ii) Data on books. (genre,author,release_date,publisher,collection etc.) iii) Book reviews via users. iv) Maintaining an inventory of the library and performing a purchase_book operation. v) Specifying books up for sale from the inventory and performing a sell_book operation to users. vi) Maintaining a detailed record of all purchasing and selling transactions with timestamps. vii) Views to quickly get purchased or sold operations and notice revenue generated per transaction. viii) Indexes on chosen columns to improve querying speed. Difficulties were encountered along the way, with triggers and procedures co-existing and leading to recursive errors. Some limitations of the current iteration of the model also include: i) The current scheme assumes only one author per book, which will lead to representation issues in transactions. One solution can be to entirely remove author_id From transactions and related databases or rework the model to sustain many-to-many relations. ii) In this iteration, a numerical review aggregate is not possible as only the content of the review is present. More features that could have been implemented include, * Languages in which books are and translators for them. * Book Editions. * A rental feature alongside the selling feature with a due date. * Another limitation can be that if the Book Hub Library expands, then the new branches at various locations will need to be taken into account as well. * The genre's SET can be expanded upon as well.

View this project on YouTube