This is all the information I am given, please help!

Accounting Information Systems Database Fundamentals Exercise (using Access) - General Instructions Version 8 This exercise is intended to help you know how a database is formed and structured. You will build this database from scratch using Access, define data elements (fields), establish relations, and query the database to generate meaningful summarized information. Although Access has been packaged into or superseded by more advanced database software, it is more than adequate to demonstrate and learn the database concepts required for this course. The Islanders Supreme Company is an electronics wholesale company that opened on 1/1/YY. In order to support the Company's operations, you are required to help set up a database to store data and transactions and eventually run queries to answer management inquiries. Requirement - Part 1 There are different types of tables forming a database; mainly 'master' tables and 'transaction tables. After reviewing the master files and the business processes described by the data on next pages, design and build the database in Access (when you save and name the file in Access, use your first and last name, a hyphen then the version number (VH) assigned to you based on the last digit of your Alf). Only include the tables and fields that are given AND used in the accounting cycle pertinent to your accounting cycle (one table given may end up not being used, so that table can be left out). Thirty (30) points will be taken off if certain fields which are not given in any transaction or activity are added to certain tables to go around database rules and design requirements (tables relationships). Do not forget to link the tables using the relationships window (it is required to organize tables where Resources are on left, Events or transactions are in the center, and Agents are on the right; see REA Model of Chapter 17). Enforce referential integrity when linking tables (15 points off if no referential integrity is enforced). Requirement - Part 2 When the tables and relationships are completed (nine tables including intersection table). construct the seven queries described at end of the data/transactions given on next pages (include query number AND title). There will be a penalty of up to 10 points for each query not included or incorrect query. Format of queries are expected to be in a reasonable report table format": points will be taken off for deviation from specified attribute/field and sort order of fields and rows). Assignment due date specified in the syllabus and on Blackboard. There will be a late penalty of 10 points for each weekday or part of weekday that assignment is late (regardless if class day or not) unless waived by professor. Submit your Access file on Blackboard [upload Access file AND do not forget to click SUBMIT]. VWS-Page 1 The following information pertains to the Islanders Supreme Company. [User need to recognize the type of these first five tables; to Access these are just mere tables] Description Price Cost 100 90 100 VCR 101 Stereo 300 270 102 DVD 150 125 103 CD 75 55 104 TV 1000 Vendor Name 50 Acme 51 Ace 13052 Cougar 63 Cat 54 Hom Employee # Name 10000 Smith 10001 King 10002 Nguyen 10003 Martinez 10004 Potter 10005 Homer 10006 Al 10007 Jones 10008 Ma 10009 Sanchez 10010 Singh 10011 Yang 10012 Shanahan Account # Bank Name 100 FirstFederal 101 SecondFederal Customer # Name 50 Hurtz 51 Johnson 52 Spur 53 Rosha 54 Astro Position Sales Buyer Warehouse Cashier Buyer Clerk Cashier Sales Sales Sales Warehouse Clerk Sales 840 VIS-Page 2 Use the following transactions to identify resources, events and agents. Determine which entities are parties to each transaction and what can be inferred about cardinalities given the transactions. I. Transactions (Substitute current year for YY) 1/3/YY Smith generated sales order number 10 for 3 CDs, sold to Rosha. 1/7/YY Jones generated sales order number 11 for 6 Stereos, sold to Johnson. Shanahan generated sales order number 12 for 5 Stereos, sold to Johnson. 1/9/YY Shanahan generated sales order number 13 for 7 VCRs, sold to Hurtz. Nguyen generated shipping form number 303 for 3 CDs related to sales order 10. 1/10/YY Homer generated invoice number 100 for $225 relating to customer po number 63421 and shipping form number 303. 1/11/YY Jones generated sales order number 14 for 3 TVs, sold to Astro. 1/12/YY Singh generated shipping form number 304 for 3 Stereos related to sales order 12. Martinez generated remittance number 100 on account number 100 for $225 relating to invoice number 100. 1/13/YY Homer generated invoice number 101 for $900 relating to customer po number 7001 and shipping form number 304. 1 Vas-Page 3 1/14/YY Smith generated sales order number 15 for 7 Stereos, sold to Johnson. Singh generated shipping form number 305 for 6 Stereos related to sales order 11. 1/15/YY Homer generated invoice number 102 for $1,800 relating to customer po number 608 and shipping form number 305. 1/16/YY Shanahan generated sales order number 16 for 5 DVDs, sold to Spur. Martinez generated remittance number 101 on account number 100 for $1,800 relating to invoice number 102. 1/18/YY Nguyen generated shipping form number 306 for 3 TVs related to sales order 14. 1/19/YY Singh generated shipping form number 307 for 7 VCRs related to sales order 13. Homer generated invoice number 103 for $3,000 relating to customer po number 700 and shipping form number 306. Ali generated remittance number 102 on account number 100 for $3,000 relating to invoice number 103. 1/20/YY Smith generated sales order number 17 for 3 DVDs, sold to Spur. Yang generated invoice number 104 for $700 relating to customer po number 806 and shipping form number 307. 1/21/YY Ali generated remittance number 103 on account number 100 for $700 relating to invoice number 104. VB-Page 4 1/22/YY Jones generated sales order number 18 for 10 VCRs, sold to Hurtz. Nguyen generated shipping form number 308 for 2 Stereos related to sales order 12. 1/23/YY Shanahan generated sales order number 19 for 2 TV, sold to Astro. Homer generated invoice number 105 for $600 relating to customer po number 3010 and shipping form number 308. 1/25/YY Smith generated sales order number 20 for 11 VCRs, sold to Hurtz Nguyen generated shipping form number 309 for 7 Stereos related to sales order 15. 1/26/YY Singh generated shipping form number 310 for 2 DVD related to sales order 17. Yang generated invoice number 106 for $2,100 relating to customer po number 4000 and shipping form number 309. 1/27/YY Homer generated invoice number 107 for $300 relating to customer po number 427 and shipping form number 310. Martinez generated remittance number 104 on account number 100 for $2,700 relating to invoice number 105 ($600) and invoice number 106 ($2,100). 1/28/YY Nguyen generated shipping form number 311 for 5 DVDs related to sales order 16. Martinez generated remittance number 105 on account number 100 for $150 relating to a partial payment for invoice number 107. 1/29/YY Singh generated shipping form number 312 for 10 VCRs related to sales order 18. Vas-Page 5 1/30/YY Singh generated shipping form number 313 for 2 TV related to sales order 19. r po number 6012 and Yang generated invoice number 108 for $2,000 relating to customer p shipping form number 313. Ali generated remittance number 106 on account number 100 for $150 relating to a partial payment for invoice number 107. II. Queries 1) Produce a query showing a cost list. Include the item number, item description and item cost. Sort the cost list by item description. 2) Produce a query showing the total number of units ordered for each item. Include the item description and total units ordered. Sort the report by item description. 3) Produce a query showing the total dollar amount of payments received from each customer. Include the customer name and the total payments. Sort the report by customer name. 4) Produce a query showing the average dollar amount per invoice for each employee generating invoices. Include the employee name and the average amount for invoices generated. Sort the report by employee name. 5) Produce a query showing the total number of invoices processed by Homer. Include the employee name and the number of invoices handled by Homer. 6) Produce a query showing the gross margin in dollars, per unit for each item. Include the item description and the gross margin. Sort by gross margin from largest to smallest. 7) Produce a query showing the total number of units shipped on or after 1/20/YY for each item. Include item description and total units shipped. Sort by item description. VIE-Page Accounting Information Systems Database Fundamentals Exercise (using Access) - General Instructions Version 8 This exercise is intended to help you know how a database is formed and structured. You will build this database from scratch using Access, define data elements (fields), establish relations, and query the database to generate meaningful summarized information. Although Access has been packaged into or superseded by more advanced database software, it is more than adequate to demonstrate and learn the database concepts required for this course. The Islanders Supreme Company is an electronics wholesale company that opened on 1/1/YY. In order to support the Company's operations, you are required to help set up a database to store data and transactions and eventually run queries to answer management inquiries. Requirement - Part 1 There are different types of tables forming a database; mainly 'master' tables and 'transaction tables. After reviewing the master files and the business processes described by the data on next pages, design and build the database in Access (when you save and name the file in Access, use your first and last name, a hyphen then the version number (VH) assigned to you based on the last digit of your Alf). Only include the tables and fields that are given AND used in the accounting cycle pertinent to your accounting cycle (one table given may end up not being used, so that table can be left out). Thirty (30) points will be taken off if certain fields which are not given in any transaction or activity are added to certain tables to go around database rules and design requirements (tables relationships). Do not forget to link the tables using the relationships window (it is required to organize tables where Resources are on left, Events or transactions are in the center, and Agents are on the right; see REA Model of Chapter 17). Enforce referential integrity when linking tables (15 points off if no referential integrity is enforced). Requirement - Part 2 When the tables and relationships are completed (nine tables including intersection table). construct the seven queries described at end of the data/transactions given on next pages (include query number AND title). There will be a penalty of up to 10 points for each query not included or incorrect query. Format of queries are expected to be in a reasonable report table format": points will be taken off for deviation from specified attribute/field and sort order of fields and rows). Assignment due date specified in the syllabus and on Blackboard. There will be a late penalty of 10 points for each weekday or part of weekday that assignment is late (regardless if class day or not) unless waived by professor. Submit your Access file on Blackboard [upload Access file AND do not forget to click SUBMIT]. VWS-Page 1 The following information pertains to the Islanders Supreme Company. [User need to recognize the type of these first five tables; to Access these are just mere tables] Description Price Cost 100 90 100 VCR 101 Stereo 300 270 102 DVD 150 125 103 CD 75 55 104 TV 1000 Vendor Name 50 Acme 51 Ace 13052 Cougar 63 Cat 54 Hom Employee # Name 10000 Smith 10001 King 10002 Nguyen 10003 Martinez 10004 Potter 10005 Homer 10006 Al 10007 Jones 10008 Ma 10009 Sanchez 10010 Singh 10011 Yang 10012 Shanahan Account # Bank Name 100 FirstFederal 101 SecondFederal Customer # Name 50 Hurtz 51 Johnson 52 Spur 53 Rosha 54 Astro Position Sales Buyer Warehouse Cashier Buyer Clerk Cashier Sales Sales Sales Warehouse Clerk Sales 840 VIS-Page 2 Use the following transactions to identify resources, events and agents. Determine which entities are parties to each transaction and what can be inferred about cardinalities given the transactions. I. Transactions (Substitute current year for YY) 1/3/YY Smith generated sales order number 10 for 3 CDs, sold to Rosha. 1/7/YY Jones generated sales order number 11 for 6 Stereos, sold to Johnson. Shanahan generated sales order number 12 for 5 Stereos, sold to Johnson. 1/9/YY Shanahan generated sales order number 13 for 7 VCRs, sold to Hurtz. Nguyen generated shipping form number 303 for 3 CDs related to sales order 10. 1/10/YY Homer generated invoice number 100 for $225 relating to customer po number 63421 and shipping form number 303. 1/11/YY Jones generated sales order number 14 for 3 TVs, sold to Astro. 1/12/YY Singh generated shipping form number 304 for 3 Stereos related to sales order 12. Martinez generated remittance number 100 on account number 100 for $225 relating to invoice number 100. 1/13/YY Homer generated invoice number 101 for $900 relating to customer po number 7001 and shipping form number 304. 1 Vas-Page 3 1/14/YY Smith generated sales order number 15 for 7 Stereos, sold to Johnson. Singh generated shipping form number 305 for 6 Stereos related to sales order 11. 1/15/YY Homer generated invoice number 102 for $1,800 relating to customer po number 608 and shipping form number 305. 1/16/YY Shanahan generated sales order number 16 for 5 DVDs, sold to Spur. Martinez generated remittance number 101 on account number 100 for $1,800 relating to invoice number 102. 1/18/YY Nguyen generated shipping form number 306 for 3 TVs related to sales order 14. 1/19/YY Singh generated shipping form number 307 for 7 VCRs related to sales order 13. Homer generated invoice number 103 for $3,000 relating to customer po number 700 and shipping form number 306. Ali generated remittance number 102 on account number 100 for $3,000 relating to invoice number 103. 1/20/YY Smith generated sales order number 17 for 3 DVDs, sold to Spur. Yang generated invoice number 104 for $700 relating to customer po number 806 and shipping form number 307. 1/21/YY Ali generated remittance number 103 on account number 100 for $700 relating to invoice number 104. VB-Page 4 1/22/YY Jones generated sales order number 18 for 10 VCRs, sold to Hurtz. Nguyen generated shipping form number 308 for 2 Stereos related to sales order 12. 1/23/YY Shanahan generated sales order number 19 for 2 TV, sold to Astro. Homer generated invoice number 105 for $600 relating to customer po number 3010 and shipping form number 308. 1/25/YY Smith generated sales order number 20 for 11 VCRs, sold to Hurtz Nguyen generated shipping form number 309 for 7 Stereos related to sales order 15. 1/26/YY Singh generated shipping form number 310 for 2 DVD related to sales order 17. Yang generated invoice number 106 for $2,100 relating to customer po number 4000 and shipping form number 309. 1/27/YY Homer generated invoice number 107 for $300 relating to customer po number 427 and shipping form number 310. Martinez generated remittance number 104 on account number 100 for $2,700 relating to invoice number 105 ($600) and invoice number 106 ($2,100). 1/28/YY Nguyen generated shipping form number 311 for 5 DVDs related to sales order 16. Martinez generated remittance number 105 on account number 100 for $150 relating to a partial payment for invoice number 107. 1/29/YY Singh generated shipping form number 312 for 10 VCRs related to sales order 18. Vas-Page 5 1/30/YY Singh generated shipping form number 313 for 2 TV related to sales order 19. r po number 6012 and Yang generated invoice number 108 for $2,000 relating to customer p shipping form number 313. Ali generated remittance number 106 on account number 100 for $150 relating to a partial payment for invoice number 107. II. Queries 1) Produce a query showing a cost list. Include the item number, item description and item cost. Sort the cost list by item description. 2) Produce a query showing the total number of units ordered for each item. Include the item description and total units ordered. Sort the report by item description. 3) Produce a query showing the total dollar amount of payments received from each customer. Include the customer name and the total payments. Sort the report by customer name. 4) Produce a query showing the average dollar amount per invoice for each employee generating invoices. Include the employee name and the average amount for invoices generated. Sort the report by employee name. 5) Produce a query showing the total number of invoices processed by Homer. Include the employee name and the number of invoices handled by Homer. 6) Produce a query showing the gross margin in dollars, per unit for each item. Include the item description and the gross margin. Sort by gross margin from largest to smallest. 7) Produce a query showing the total number of units shipped on or after 1/20/YY for each item. Include item description and total units shipped. Sort by item description. VIE-Page