In Microsoft access, please make the following changes to a database: Make the following modifications to the Animalstarterdb

Part $1$:

$1.$ Create the following tables $($key fields are underlined$).$

a$.$ Purchase$($Order Number, Product ID$,$ quantity, unit price$)$

b$.$ Product$($Product ID$,$ ProductName, category, quantity in stock, supplier price, Supplier ID$)$

Notes:

$$ Product ID in Product, should be of the type "autonumber".

$$ For other fields, pick an appropriate data type $($number$,$ currency, text, date, etc.$)$

$$ You will need to use validation rules to assure valid values.

$$ Valid values for the product category should be "food", "medications", "accessory", "live animal", and "miscellaneous".

$$ Be sure to use the same data type and sizes for fields holding common data.

$2.$ Forms $-$ You can use the form wizard to create the forms All data should be entered through forms. Create forms for each of the tables. Use the forms wizard to create a form to allow data entry for each of the tables in the database.

a$.$ The product form should have a drop down combo box that is preloaded with valid values for the category. $("$food$",$ "medications", "accessory", "live animal", and "miscellaneous". $)$

b$.$ The customer form should have a drop down combo box preloaded with valid values for customer type. $($Kennel$,$Breeder$,$Individual$)$

c$.$ All forms should be of type "columnar" except for the Purchase form which should be "tabular".

Part $2$:

$3.$ Use the forms to add $10$ records to the customer table, $10$ to the product table, and $5$ to the supplier table.

$4.$ Create all relationships and enforce referential integrity

Customer to Order using CustomerID

Purchase to Product using ProductID

Purchase to Order using OrderID

Product to Supplier using SupplierID

$5.$ Modify forms$-$ your forms should allow users to easily enter data into the database

a$.$ Modify your purchase form to add:

i$.$ a calculated field that shows the totals for that item $($unit price$*$quantity$),$

ii$.$ a calculated field to the form footer that totals all items bought.

b$.$ Modify: $**$see order form example below

i$.$ your Order form created in part one to use drop$-$down combo boxes for the selection of Customers from the Customer table,

ii$.$ your Purchase form to allow selection of Products concatenated with product price from the Product table $($using a combo box$)$

c$.$ your Product form to allow selection of Suppliers from the supplier table $($using a combo box$).$

d$.$ Add the Purchase form as a "sub$-$form" to your Order Form using Order ID to link the forms. $**$See below for how the form should look.

e$.$ Add two purchases for a customer

f$.$ To the bottom of the Order form, add $2$ buttons:

i$.$ one button will close the form

ii$.$ one button will create a new record.

g$.$ Enter additional orders and purchases using the Order form$($Make sure you have $5$ Orders with purchases.

h$.$

Order Form Example:

$6.$ Queries

a$.$ Create queries to answer the following questions. In order to test these queries you will have to enter enough data into your tables to make them produce a list which is not empty. Check the results of your queries.

i$.$ C$-1$ Give a list of customers from a given city who have purchased items in the "Food" category.

ii$.$ C$-2$ Give a list of customers who have purchased products supplied by Purina.

iii. C$-3$ Create a query based on Customers, Purchase, Products, Orders, and Suppliers that gives a list of customer names, customer types, order numbers, product categories, product names, quantity purchased, unit price, supplier price, and supplier names.

iv$.$ C$-4$ Create a crosstab query based on C$-3$ that uses product name as the row heading, uses customer type as the column heading, sums total quantity purchased by product and customer types. Add enough data to your tables so that you have customers of different types buying several different products. This is so that you can see that the crosstab query works.

$7.$ Create and add a Queries form

$8.$ Create a report that displays Suppliers and the Products they supply. The report should include Product name, quantity, and Unit price.

Part $3$:

$9.$ Add a Navigation form to your application.

A navigation form makes it easy for users to switch between the various forms and reports in your database. Navigation forms are a great addition to any desktop database. Every Access database application should have a navigation form.

B$.)$ Security

$$ Set a password for the database $.$ Password should be set to dwolf.

$$ Have the menus and toolbar not show when MS ACCESS is running However, once you do this you will find that you can$$t edit your Navigation Form. So how do you get your menus and toolbar back? Hold down the shift key while opening your database.

$$ Have your Navigation Form automatically loaded when your database is opened $.$