You have been asked to create a database to keep track of the stock of a small bookstore. The business wants to record details of books, their authors and publishers, the categories of books, and book bundles they offer. You have the following details:
• Details of books that need to be stored are the ISBN, title, edition number and the price.
• Details of book authors (their names and date of birth) must be stored. Each author can write multiple books, and each book can have multiple authors.
• The bookstore only wishes to store details of authors who have written books they sell.
• The name, contact phone number and address of book publishers must be stored. Each publisher can publish multiple books.
• The bookstore wishes to store details of all major publishers, even if the store does not currently sell any of their books.
• A list of book categories/topics must be stored. Each book can have multiple categories, and each category can apply to multiple books.
• Sometimes the bookstore offers book bundles, where they sell multiple books together for a discounted price. The database must store a name, description and price for each bundle.
• The database must record which books are in which bundles. A book can be in multiple bundles, and each bundle contains multiple books.
Create a suitable physical Entity-Relationship diagram based on this scenario. Ensure that you show all attributes mentioned, as well as the cardinality of all relationships. Clearly state any assumptions.
It is recommended that you use auto-incrementing integers for primary keys, unless a suitable primary key attribute exists in the specified scenario.
Use underlining to depict primary keys, and dotted underlining to depict foreign keys. Use both types of underlining on a single attribute if necessary.