$244-$Online Bookstore Management System

Consider an online bookstore that sells a variety of books to customers. The

bookstore requires a database system to manage its inventory, customer orders,

shipping information, and book details. The data requirements for the system are

outlined below:

Book Inventory Management:

Each book in the inventory is identified by a unique ISBN, title, author,

genre, publication date, price, and quantity available.

The system tracks the stock levels of each book and alerts when the stock is

low.

It maintains information on book categories and subcategories for easy

classification.

Customer Order Processing:

Customers can place orders for books by providing their details such as

name, shipping address, contact number, and email.

The system records each customer's order history, including the books

purchased, order date, and total amount.

It generates invoices for each order and tracks the payment status.

Shipping and Delivery:

The system manages shipping information, including the shipping address,

delivery status, and tracking number.

It calculates shipping costs based on the customer's location and the weight

of the books in the order.

It provides estimated delivery dates and updates customers on the order

status.

Book Details and Reviews:

The system stores detailed information about each book, including a brief

description, cover image, ratings, and reviews.

Customers can rate and review books they have purchased, which helps in

recommending books to other customers.

It tracks popular books, best sellers, and new arrivals for marketing and

promotion purposes.

Customer Accounts and Preferences:

Customers can create accounts on the platform to manage their orders,

wish lists, and preferences.

The system stores customer preferences such as favorite genres, authors,

and recommendations based on past purchases.

It allows customers to update their account information, password, and

communication preferences.

By implementing a robust database system for the online bookstore, the

management can efficiently handle book inventory, customer orders, and enhance

the overall shopping experience for book enthusiasts.

Online Bookstore Management System

a$)$ Fill the table below with all the Entities based on the given requirements:

b$)$ Fill the table below with all the relationship type based on the given

requirements.

c$)$ Draw the ER Diagram

d$)$ Tables before the normalization

e$)$ Tables after the normalization

f$)$ Create the normalized tables and populate them with at least $10$ rows.

g$)$ Write the necessary SQL statements to create the tables.

h$)$ Insert at least five rows into each table.

i$)$ Write SQL queries to find the following:

List the first and last names of customers who have purchased more than

three books.

List all books' ids and titles published since $\frac{1}{1}\frac{?}{2024}.$

List all customers who have rated books $($First and last name, customer

id$).$

Find all customers who have purchased books in the "Fiction" genre.

List all customers who have books in their wish list.

Find all customers who have purchased books and reviewed them.