$**$PLEASE PROVIDE ALL DIAGRAMS$**$

An independent retailer of mobile entertainment and wireless phones, iToys.com has built its business on offering the widest selection, expert advice, and outstanding customer service. However, iToys. com does not use a formal, consistent inventory tracking system. Periodically, an iToys.com employee visually checks to see what items are in stock. Although iToys.com does try to keep a certain level of each top seller in stock, the lack of a formal inventory tracking system has led to overstocking some items and understocking other items. On occasion, a customer will request a hot item, and it is only then that iToys.com realizes that the item is out of stock. If an item is not available, iToys.com risks losing a customer to a competitor.

Lately, iToys.com has become concerned with its inventory management methods. The owner of iToys.com, Dan Connolly, wants to manage his inventory better. The company receives orders by mail, by telephone, or through its website. Regardless of how the orders are received, Dan needs a database to automate the inventory checking and ordering process.

Dan has provided you with a simplified version of the company$$s current system $($an Excel workbook$)$ for recording inventory and orders in an Excel spreadsheet data file, Project data.xlsx Download Project data.xlsx$.$

Excel File: $\backslash$table$[[$Order$,$Order Date,Product,Qty$],[1,\frac{1}{1}\frac{?}{2011},$Ipod Shuffle,$1],[1,\frac{1}{1}\frac{?}{2011},$Ipod Mini,$2],[2,1/1/2011,$LG C$2000,1],[3,\frac{1}{1}\frac{?}{2011},$Nokia $6061,1],[4,\frac{1}{1}\frac{?}{2011},$Ipod Shuffle,$1],[5,\frac{1}{1}\frac{?}{2011},$Ipod Mini,$2],[6,\frac{1}{1}\frac{?}{2011},$Ipod Nano,$1],[6,\frac{1}{1}\frac{?}{2011},$Nokia $6061,1],[7,\frac{1}{1}\frac{?}{2011},$Ipod Shuffle,$1],[7,\frac{1}{1}\frac{?}{2011},$Ipod Nano,$1]]\backslash$table$[[$Product Name,Product Description,Category ID$,$Unit Price,Reorder Level,Lead Time,Supplier Name,QOH $($ Quantity$-$on$-$Hand $)],[$Ipod Shuffle,$1GB,$MP$3,$$$99\mathrm{.}00,10,10,$Apple,$52],[$Ipod Mini,$4$GB$,$MP$3,$$$149\mathrm{.}00,25,10,$Apple,$20],[$Ipod Nano,$2$GB$,$MP$3,$$$199\mathrm{.}00,25,10,$Apple,$22],[$LG C$2000,$Camera phone with $3x$ digital zoom,Cell Phone$/$SmartPhone$,$$$99\mathrm{.}00,5,10,$LG$,24],[$Nokia $6061,$Media Net capable for surfing the Internet,Cell Phone$/$SmartPhone$,$$$79\mathrm{.}00,10,10,$Nokia America,$26],[$Nokia $6102,$EDGE high$-$speed data access,Cell Phone$/$SmartPhone$,$$$99\mathrm{.}00,10,5,$Nokia America,$20],[$Motorola RAZR V$3,$Black,Cell Phone$/$SmartPhone$,$$$149\mathrm{.}00,10,5,$CellularFactory,$10],[$LG CU$320,$MP$3$ Music Player with Stereo Sound,Cell Phone$/$SmartPhone$,$$$149\mathrm{.}00,10,5,$LG$,10],[$Garmin Streetpilot $2730,$Portable Car Navigation,GPS$,$$$899\mathrm{.}00,5,10,$Garmin,$2],[$Magellan Explorist,explorist $100$ North American,GPS$,$$$125\mathrm{.}00,5,5,$CellularFactory,$5]]$

Here are some business rules to assist you in completing this task:

An order must have at least one product but can contain more than one product.

A product can have one or more orders but need not have any orders.

A product must belong to one and only one category, but a category may contain many products.

A product can only be stocked by one supplier, but a supplier can provide more than one product.

A supplier will use one type of shipping method, but shipping methods can be used by more than one supplier.

Project$-$ part A

Develop an ER diagram before you begin to create the database and tables. You will need to use the information provided here as well as the data given in the Excel workbook $(15$ points$).$

Create the relational schema $($show the referential integrity constraints$)$ and use normalization to ensure the correctness of the tables$($relations$)(15$ points$).$ Figure $4-5$ on page $160$ of our first textbook is a typical example of a relational schema.