PROJECT REQUIEMENT:

In project $2,$ you designed a database based on the Excel sheet of a small local bookstore. Now, your client provided you an updated Excel $($see the attachment and snapshot in Figure $1)$ below and asked you to provide three reports: $(1)$ Total Sales in each month, $(2)$ Total number of orders for each book $($ascending ordered by ISBN number$),$ and $(3)$ Top Three Customers' Information in sales by pulling out data from the MySQL database $($see detailed description in next page$).$

$\backslash$begin$\{$tabular$\}\{|$c$|$c$|$c$|$c$|$c$|$c$|$c$|$c$|\}$

$\backslash$hline Date & Order & Name & Street & City & ISBN & $\backslash$multicolumn$\{2\}\{|$l$|\}\{$Amount $(5)\}\backslash \backslash$

$\backslash$hline $4/10/23$ & $1$ & Judy Smith & $25$ State Strect & Logan, WV & $0-672-26197-7$ & S & $19\mathrm{.}20\backslash \backslash$

$\backslash$hline $4/10/23$ & $1$ & Judy Smith & $25$ State Street & Logan, WV & $0-712-33725-4$ & S & $23\mathrm{.}50\backslash \backslash$

$\backslash$hline $4/13/23$ & $2$ & John Doe & $1$ Main Street & Nitro, WV & $0-435-90021-2$ & S & $14\mathrm{.}49\backslash \backslash$

$\backslash$hline $5/1/23$ & $3$ & Judy Smith & $25$ State Street & Logan, WV & $0-378-43590-3$ & s & $13\mathrm{.}99\backslash \backslash$

$\backslash$hline $5/7/23$ & $4$ & Noah Robinson & $3$ River Blvd & Barboursville, WV & $0-712-33725-4$ & S & $23\mathrm{.}50\backslash \backslash$

$\backslash$hline $5/7/23$ & $5$ & Olivia Garcia & $1$ Sherwood Road & Charleston, WV & $0-672-26197-7$ & S & $192\mathrm{.}00\backslash \backslash$

$\backslash$hline $5/7/23$ & $5$ & Olivia Garcia & $1$ Sherwood Road & Charleston, WV & $0-712-33725-4$ & S & $23\mathrm{.}50\backslash \backslash$

$\backslash$hline $5/12/23$ & $6$ & Judy Smith & $25$ State Street & Logan, WV & $0-378-43590-3$ & s & $13\mathrm{.}99\backslash \backslash$

$\backslash$hline $5/13/23$ & $7$ & John Doe & $1$ Main Street & Nitro, WV & $0-378-43590-3$ & S & $13\mathrm{.}99\backslash \backslash$

$\backslash$hline $5/15/23$ & $8$ & James Thompson & $10$ Ridgeway Road & Huntington, WV & $0-435-90021-2$ & S & $28\mathrm{.}98\backslash \backslash$

$\backslash$hline $5/17/23$ & $9$ & Amelia Anderson & $11$th St$.12$ Ave & Huntngton, WV & $0-378-43590-3$ & S & $13\mathrm{.}99\backslash \backslash$

$\backslash$hline $6/3/23$ & $10$ & Noah Robinson & $3$ River Blvd & Barboursville, WV & $0-712-33725-4$ & S & $23\mathrm{.}50\backslash \backslash$

$\backslash$hline $6/21/23$ & $11$ & Mary Brown & $3248$ Oakwood Road & Charleston, WV & $0-672-26197-7$ & S & $38\mathrm{.}40\backslash \backslash$

$\backslash$hline $6/21/23$ & $11$ & Mary Brown & $3248$ Oakwood Road & Charleston, WV & $0-712-33725-4$ & S & $23\mathrm{.}50\backslash \backslash$

$\backslash$hline $6/27/23$ & $12$ & Mary Brown & $3248$ Oakwood Road & Charleston, WV & $0-435-90021-2$ & S & $14\mathrm{.}49\backslash \backslash$

$\backslash$hline

$\backslash$end$\{$tabular$\}$

Figure $1$: Snapshot of Sales activities of Small Local Book store

After careful review of Excel and an interview with the client, you found that an order can include multiple books and the order numbers in Excel are not unique. You also found out that each book $($with an unique ISBN number$)$ has its unit price $($after tax$)$ as below with no discount for the bulk order:

$\backslash$begin$\{$tabular$\}\{$ll$\}$

ISBN & Unit Price $\backslash \backslash$

$\backslash (0-672-26197-7\backslash )$ & $\backslash (\backslash$$ $19\mathrm{.}20\backslash )\backslash \backslash$

$\backslash (0-712-33725-4\backslash )$ & $\backslash (\backslash$$ $23\mathrm{.}50\backslash )\backslash \backslash$

$\backslash (0-435-90021-2\backslash )$ & $\backslash (\backslash$$ $14\mathrm{.}49\backslash )\backslash \backslash$

$\backslash (0-378-43590-3\backslash )$ & $\backslash (\backslash$$ $13\mathrm{.}99\backslash )$

$\backslash$end$\{$tabular$\}$ Requirements and Steps:

$(1)$ revise your database design if needed. $($ex$.$ database should support the client to purchase multiple books in one order$)$ and develop updated databased design diagram using MySQL Workbench $($see attached instruction: CS $410\_$ER Diagram using MySQL Workbench.pdf$).$

$(2)$ implement your database in MySQL using MySQL Workbench following your design

$(3)$ modify the data in Excel and import "proper" data into different tables

$(4)$ create SQL queries to generate three reports as depicted below:

$-$ Total Sales in each month

$-$ Total number of orders for each book $($ascending ordered by ISBN number$)$

$-$ Top Three Customers' Information in Sales

Feel free to change the look and data format in the report as you wish. However, please include title of the report. Upload your final database design $($ER diagram with multiple tables$)$ with code.