SPE Limited sells different kinds of smartphones that it purchases from different manufacturers. Its customers purchase their desired products via filling in an online order form in the company website. Peter Watsons started the company 6 years ago and the company revenue has grown four times since its incorporation. Currently, SPE uses MS Excel to record the revenue cycle business activities. However, storing and analyzing the data in MS Excel has numerous limitations. SPE would like to use MS ACCESS to record, store analyze and report its business activities within the revenue cycle. As the company’s Accounting Manager, you are asked to design and develop this database for SPE. The following paragraph describes the revenue cycle activities and business rules of SPE.

A customer of SPE places an online order via the website. The order indicates the products that the customer wants to purchase. SPE sends a sales invoice to the customer together with the delivery of the products. When the customer settles the sales invoices, he/she can mail the cheques to SPE or transfer the money to SPE’s bank account through electronic fund transfer. The business rules are as follow: (i) one order can be fulfilled by several sales but one sale can only be associated with one order, (ii) each sales order or sale can include one or more inventory items and for each inventory item, it can appear in many sales orders or sales, (iii) one sales representative / customer can participate in one or more orders or sales events and each order or sale event is participated by only one sales representative / customer, (iv) each money receipt from a customer settles one sales invoice and for each sales invoice, partial settlement by customer is not allowed, (v) each cashier / customer can participate in many money receipts, and for each receipt, it can only be participatedby one cashier / customer, (vi) each receipt will be deposited into one single bank account and the

Required:

2. all transactions happen in January and February 2024

Using Microsoft Access, create the appropriate entities (or tables) based on the data elements given in the appendix. Choose the primary key for each entity. Fill in those entities with appropriate data.

Decide on the relationship type between entities based on the business rules described above and the business rules normally adopted by commercial organizations. Link up those entities by joining the primary and foreign key together. You should follow the pattern as stipulated by the REA diagram and draw the diagram in the relationship view of MS Access. Note that some of the relationship types between entities may be unspecified and you need to think critically and make your own judgement based on the materials that you learn in class.

Point to note when you create your data:

If a sales invoice includes many inventory items and you want to show the quantity sold for each item, it is not feasible to include the “Quantity Sold” field in the sales table because each sale is represented by only one row in the sales table. If you include inventory # as one of the fields in the sales table, you can only sell one inventory # in each sales invoice given there is only one cell for input.

Leave the Receipt # as blank in the Sales table if customer has not made any payment to settle an invoice. You can retrieve these sales by using the criteria: is null, within the receipt # field.

the January 2024 sales report; in your query output, you should display the following information: sales invoice date, sales invoice number, sales invoice amount, customer #, employee # and receipt #. (10 marks)

the total January revenue using the query in (i). (5 marks)

the January 2024 gross margin by inventory item; in your query output you should display the following information: inventory item#, inventory item description, sum of quantity sold, unit price, unit cost, total revenue, total cost of goods sold and gross margin amount and gross margin percentage. (10 marks)

(Hints for iii: If you use two queries, the first query takes out those sales in January. The second query utilizes the first query to calculate total revenue, total cost of goods sold and gross margin. In the second query, do not include any date field.)

4. Which customers have not settled their sales invoices? Generate a report that shows thefollowing fields: sales invoice #, sales  invoice date, customer #, customer name, employee #, employee name, and receipt #. (10 marks)

The following data elements and types are given to you. You may leave some of the data elements nil entries as they are not relevant in generating the queries in part III. Example of those data elements are Address, Telephone, Credit Limit, Date Hired, Date of Birth, etc. Note that some of the data elements may appear in more than one table.