data程序代做、代写python/Java程序语言

日期：2024-09-28 09:31

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

1. 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 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

1. 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.)

2. 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)

3. 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)

4. 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

1. 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.

2. 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-

This field is only for

4