a$)$ Identify the entity types, attributes, keys.$$

b$)$ Identify the relationship and cardinalities. $$

c$)$ Draw the ERD.$$

d$)$ Schemas before Normalization.$$

e$)$ Schemas after Normalization.$$

f$)$ Createtables.$$

g$)$ Populate your tables with at least $5$ rows.$$

h$)$ Execute the requested sample queries.

$$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:

$1.$ 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.

$2.$ 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.

$3.$ 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.

$4.$ 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.

$5.$ 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.