You need to create three tables $($in Ms Excel$)$ each corresponding to the names on the attached Ms Excel file tabs $($Orders$,$ Products, and Suppliers$).$

You can choose appropriate primary keys for each table and make sure to include them in the other tables as foreign keys to create

the relational database. $[$For instance, for the Orders, you can create an Order ID as the primary key which will be a foreign key in the

Products tables. The products table can have the Product ID as the primary key which will be replicated in the Supplier tables as a foreign key.

Similar concept can be applied to the Supplier tables with Supplier ID corresponding to each specific supplier as the primary key.

Once the tables are created, you can populate them with the information in the Ms Excel worksheet.

Thereafter, you can use the query wizard in Ms Access to create the queries.Project $30$: Inventory Data

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, on

iToys.com employees visually

check 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

according inventory and orders in an Excel spreadsheet data file, AYK$30\_$Data.xlsx$.$

Develop an ERD Diagram before you begin to create the database. You will need to use the

information provided here as well as the data given in the Excel workbook.

Create the database using a personal DBMS package $($preferably Microsoft Access$)$ that will track

items $($i$.$e$.,$ products$),$ orders, order details, categories, suppliers, and shipping methods.

In addition to what is already mentioned, the database needs to track the inventory levels for

each product, according to a reorder level and lead time.

At this time, Dan does not need to store information about the customer, he simply needs you to

focus on the inventory structure.

Develop a query that will display the products that need to be ordered from their supplier. To

complete this, you will want to compare a reorder level with how many units are in stock.

Develop several reports that display:

a$.$ Each product ordered by its supplier. The report should include the product name,

quantity on hand, and reorder level.

b$.$ Each supplier ordered by shipping method.

c$.$ Each product that requires more than five days lead time. $($Hint: you will want to create a

query for this first.$)$

d$.$ Each product ordered by category.

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

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

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

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

products.

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

product.

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

more than one supplier.