Database Assignment (Fall 2024) Building Accounting Information System using MS Access (100 marks) 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 within the revenue cycle. As the company’s Accounting Manager, you are asked to design and 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 , store analyze and report its business activities rules are as follow: (i) o with can Required: Assume
- 2. but inventory item, it can appear in many sales orders invoice and for each sales invoice, partial settlement by customer is all accounts’ beginning balances are zero all transactions happen in January and February 2024 Part I: Design SPE’s database using REA it using will Using Microsoft Access, create the appropriate entities (or tables) based on the data elements given in the appendix. the key for each entity. Fill in those entities with appropriate data. Decide on the 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 as stipulated by the REA diagram and draw the diagram in the relationship view of MS Access. Note that some of the 1 Choose relationship ne order can be fulfilled by several sales or sales, (iii) o participate in many money receipts, and for each receipt, it can only be participated one sale can only be associated ne sales representative / customer develop this participate in one or more order s or sales allowed is by settles one sales , (v) each c participated business one order, (ii) each sales order or sale can include one or more inventory items and for each only one sales representative / customer, (iv) e events and each order or sale event ach money receipt from a customer not ashier / customer can by one cashier / customer, (vi) each receipt bank account can take many money receipts. be deposited into one single bank account and the diagram and draw Microsoft Access primary (20 marks) pattern
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. Part II: Build the database using ACCESS Create a database file using your session weekday and student IDs as filename. guidelines in coming up with the data of the tables. Required number of Create at least 5 customers Create at least 7 inventory items Create at least 8 sales orders, with 5 in Jan. and 3 in Feb. 2024 Create at least 10 sales, with 6 in Jan. and 4 in Feb. 2024 (30 marks) Use the following entries (records) Create at least two employees: one salesman, one cashier. Use your full name(s) as employee name(s). If two students work together, then one student acts as salesman and the other student acts as cashier. Create at least 8 money receipts, with 2 in Jan. and 6 in Feb. 2024. There should be at least one sales invoice that has not been settled in your database. Create at least 1 bank account Point to note when you create your data: The data created 代 写Building Accounting Information System using MS Access should be consistent with the type of relationship. That is, if the relationship is many-to-many (one-to-many), you must come up with table(s) and data that illustrate the many-to-many (one-to-many) relationship. Hints: 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. 2
- Generate i 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) ii the total January revenue using the query in (i). (5 marks) iii 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.)
- Which inventory item is most popular in terms of total sales quantity for the two months combined? Rank the quantity sold in descending order. Your output should display inventory item#, inventory item description and sum of quantity sold. (No date field is necessary as you are including all sales data in both January and February) (7 marks)
- Assume a sales commission rate of 5%, generate a report that shows the commission income by salesman for January. The commission income is based on the total sales made by a salesman multiplied by the commission rate. Your report should show employee #, employee name and commission. (Hint: use one of the tables and query 1(i)). (8 marks)
- Which customers have not settled their sales invoices? Generate a report that shows the following fields: sales invoice #, sales invoice date, customer #, customer name, employee #, employee name, and receipt #. (10 marks) (50 marks) Part III: Query your database 3
Appendix 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. some of the data elements may appear in more than one table. Note that Field Customer # Customer Name Customer Address Customer Telephone Credit Limit Employee # Employee Name Employee Title Commission Rate Base Salary Date Hired Date of Birth Sales Order # Sales Order Date Quantity Ordered Sales Invoice # Sales Invoice Date Inventory Item # Inventory Item Description Unit Price Unit Cost Quantity sold Receipt # Receipt Date Receipt amount Bank Account # Bank Account Type Bank Account Balance Note: Date Type Text or Number Text Text Text Currency or Number Text or Number Text Text Number Currency or Number Time/Date Time/Date Text or Number Time/Date Number Text or Number Time/Date Text Text Currency or Number Currency or Number Number Text Time/Date Currency or Number Number Text Currency or Number
- Credit Limit means the dollar limit that SPE gives to a customer. information purposes. Students can leave it as blank as the query does not use this field.
- Example of bank account type is saving account or checking account. You could have zero in the bank account balance as the query does not use this field. -End-
WX：codinghelp
