REA Data medeling Project

Oklahoma State University Spears School of Business ACCT 3603 – Accounting Information Systems Spring 2021 Comprehensive REA Data Modeling Project – Part 1 This assignment is a comprehensive project that introduces you to database design, modeling, creation, and querying. This project uses a particular type of Entity-Relationship diagraming technique called REA data modeling, which is discussed in Chapters 17 & 18. This project also relies on the Microsoft Access software for the database creation and querying. The Access software is discussed extensively in Chapter 4. IMPORTANT NOTES: If you have a PC computer, you can download the Access software for free from OSU. If you have a Mac computer, Access software is not really available, but you can log into the Spears labs remotely at desktop.okstate.edu. If you log in remotely, please remember that everything will delete once you log out of the system. Save your file to a cloud service like OneDrive, Dropbox, etc. before logging out. Instructions for both the free OSU software and remote log-in to Spears labs are provided within the syllabus. For this part of project, you can work in a group or individually. If you choose to work in a group, you may select your group members, but there can be no more than three individuals in a group. Once completed, you must upload the items noted below by the project deadline. If you have questions about this project, please post them to the corresponding Discussion Board in Canvas. For this assignment, neither my TA nor I will answer individual email questions. Questions must be posted to the Discussion Board, and one of us will respond there for everyone’s benefit.

Submission of projects from prior semesters will result in an Academic Integrity violation. I will check the file properties for every submission, and I will not accept an assignment where the property information (particularly, the file creation date) has been deleted.

To complete Part 1 of this project, you need to perform the following steps: • Read through the narrative on Jumping Jitters Coffee, Inc. and the subsequent Assignment instructions

and additional details • Identify the Resources, Events, and Agents in the narrative and the relationships described for each

entity • Create a REA data model based on the narrative, the list of attributes provided, and the cardinalities

described within the narrative • Use the data model to create a corresponding Access database, including relationships The following items must be uploaded to the corresponding Assignment folder in Canvas by the project deadline:

1) Your REA data model, which must be completed using Microsoft Word 2) Your Microsoft Access database 3) Your Relationship Report, which will be within your Microsoft Access database.

JUMPING JITTERS COFFEE, Inc. (JJC) is a coffee bean distribution company located in the Ozark mountain region of Arkansas. JJC imports exotic varieties of coffee beans from around the world, roasts the coffee beans at its Arkansas location, then distributes the coffee beans to high- end retailers and restaurants across the United States through its regionally-assigned salespeople. The following paragraphs provide further detail regarding a portion of JJC’s revenue cycle procedures.

JJC maintains an inventory of roasted coffee beans in large containers. Each container has an assigned number and is stored in a temperature- and moisture-controlled warehouse. As common to this type of business, these containers are reused many times for different batches and types of coffee. However, because JJC prides itself on the quality of its coffee beans, each batch of beans is individually labeled with a batch ID, specific vendor ID, vendor location, and roast date of the beans. The coffee bean containers are very large and allow JJC to fill many customer orders before having to prepare a new batch. When JJC receives an order from one of its customers, the amount and type of coffee beans ordered by the customer are removed from the container and packaged in a burlap sack. Word of JJC’s coffee quality has spread within the business, so oftentimes, customers order more than one type of coffee at a time.

To sell its coffee, JJC maintains a staff of dedicated salespeople. Each salesperson is assigned a specific region within the U.S. and is responsible for maintaining all customer relations within their assigned areas, as well as identifying new customers within their area. When a new customer is identified by the salesperson, that customer’s data is entered into JJC’s system, even though a sales transaction with that customer may not have taken place yet. There is no overlap of customer sales regions by salesperson, and each salesperson receives an annual salary plus commission for all sales activity within their region. To stimulate sales activity, each salesperson is allowed to offer their customers up to a pre-approved maximum price discount amount, but the price discount reduces their totals sales activity, which impacts the amount of commission they receive.

Each of JJC’s customers is assigned a unique customer ID, and when the salesperson enters the customer into the system, they also enter additional pertinent information, such as name, type of business, address, etc. Once the customer data is entered by the salesperson, JJC’s credit manager reviews each customer and enters a credit limit amount for that customer. Because JJC operates based on credit sales transactions, cash is not received at the point of sale, and an invoice is issued to the customer at a later date. However, JJC’s customers are required to pay the full amount of the invoice in one payment within 30 days after the invoice date. During the month, it is not uncommon for JJC’s customers to have multiple orders and invoices issued, but when that happens, customers pay for the multiple invoices with one check. When the customer payments are received by JJC’s cashier, the payments are batched and deposited into JJC’s operating account.

JJC Assignment:

Using the narrative above and the list of attributes below, complete an Entity-Relationship (ER) Diagram of JJC’s revenue cycle using the REA data modeling approach. No additional attributes other than the ones identified below are necessary. In the diagram, include all classes and attributes of the economic resources, events and agents described, as well as all identified associations and cardinalities. For each class, be sure to identify the primary key. Sales agents and cashiers can be combined into one agent class identified as “Employee”.

Once the REA data model is complete, use it to prepare a database within Access. Based on the model, create the necessary tables. Label each table based on the name of the class you identified. Within each table, include the attributes identified by your REA model. Also, identify in each table which attribute represents the primary key.

Within your Access database, you also need to establish any necessary associations (i.e. relationships) between the tables and attributes.

For this assignment, you do not need to populate the tables with data, nor do you need to identify cardinalities in Access. Cardinalities only need to be identified in the REA data model.

Invoice Number Employee ID Employee Name Remittance Advice # Customer Name Bank Account # Batch ID Vendor ID Vendor Location Invoice Date Invoice Amount Remittance Payment Date Bank Account Type Customer State Customer Business Type Customer Address Remittance Payment Amount Employee Position Employee Assigned Region Employee Commission Rate Customer City Inventory Roast Date Inventory Quantity Remaining Employee Discount Allowed Sale Price Discount Applied Inventory Quantity Sold Unit Cost per Sack Customer Credit Limit Bank Account Balance Employee Annual Base Salary List Price per Sack Customer ID Container #