1) As a team, design a database with two tables, one for the vendors from which a company purchases products that it resells and the other for the product the company is reselling (include price and quantity on hand attributes). These should form a 1:M relationship, which means selecting a product for which there is a single vendor. Examples: - Publisher and Book - Electronics manufacturer and Electronics Retail Stores - Movie producer and DVD seller Show me your design before creating your physical tables. 2) In the lab, specify your database in Access. Be sure to: a) Select appropriate types and sizes for the fields b) Designate a primary key for each table c) After you create both tables, specify the relationship between the two tables in Access. Be sure to do this before entering data. 3) Enter data into your two tables as follows: a) First, enter data into your vendor table (the one side of the 1:M relationship). Include at least two rows. b) Second, enter data into your product table (the many side of the 1:M relationship). Include at least 5 rows and at least some rows for each vendor. c) Enter a product row that has a vendor that does not exist in the vendor table, and show me the error you get. 4) Use SQL to write the following queries to list. Name your queries QueryA, QueryB, etc. Reminder on getting to the SQL window in Access: Use the Create tab, select Query Design. Close the window that opens with the list of tables. In the upper left comer you can select the SQL view. After typing your SQL query (and ending it with a semi-colon), click the exclamation point in the upper left corner to run your query. After viewing your output, use the view drop-down in the upper left comer to go back to the SQL view. a) List each vendor with all the products they supply to the company b) List some information from the vendor table about each vendor with a count of the products the vendor supplies to the company c) List some information from the vendor table with the average, min, and max prices of the products they supply to the company d) List the total value of the inventory held by the company e) List the total value of the inventory broken down by each vendor 5) Close your database and submit it via the appropriate upload link in the Class 03 Module (under Class 03 Exercise). In your file name for the database, include your group number