Hello. I would like help with this assignment? Will you review it and let me know if you can help?

CASE 18-1 Practical Database Design Hands-on practice in database design is important. Use a relational DBMS to implement the integrated REA data model presented in this chapter, or one of the integrated data models from the homework problems, or a model provided by your instructor. Then, perform the following tasks: 1. Write a query to calculate total accounts receivable. 2. Write a query to calculate accounts receivable for a specific customer. 3. Create a sales invoice form that references the appropriate tables and inputs data about attributes into the proper tables. 4. Write queries to calculate as many financial statement items as possible from the data model you implement. 5. Design appropriate input controls to ensure the validity of data entered in the form created in step 3. 18.1. Refer to Problems 17.1 and 17.2 for information about the revenue and expenditure cycle activities for Joe's ice-cream shop in order to draw an integrated REA diagram of both cycles. PROBLEMS 17.1. Joe's is a small ice-cream shop located near the local university's baseball field. Joe's serves walk-in customers only. The shop carries 26 flavors of ice cream. Customers can buy cones, sundaes, or shakes. When a customer pays for an individual purchase, a sales transaction usually includes just one item. When a customer pays for a family or group purchase, however, a single sales transaction includes many different items. All sales must be paid for at the time the ice cream is served. Joe's maintains several banking accounts but deposits all sales receipts into its main checking account. REQUIRED Draw an REA diagram, complete with cardinalities, for Joe's revenue cycle. 17.2. Joe, the owner of the ice-cream shop, purchases ice cream from two vendors. Over the years, he has developed good relationships with both vendors so that they allow Joe to pay them biweekly for all purchases made during the preceding two-week period. Joe calls in icecream orders on Mondays and Thursdays. The orders are delivered the next day. Joe buys ice-cream toppings from one of several local stores and pays for each such purchase at the time of sale with a check from the company's main checking account. REQUIRED Draw an REA diagram, complete with cardinalities, for Joe's expenditure cycle. PROBLEMS 17.1. Joe's is a small ice-cream shop located near the local university's baseball field. Joe's serves walk-in customers only. The shop carries 26 flavors of ice cream. Customers can buy cones, sundaes, or shakes. When a customer pays for an individual purchase, a sales transaction usually includes just one item. When a customer pays for a family or group purchase, however, a single sales transaction includes many different items. All sales must be paid for at the time the ice cream is served. Joe's maintains several banking accounts but deposits all sales receipts into its main checking account. REQUIRED Draw an REA diagram, complete with cardinalities, for Joe's revenue cycle. 17.2. Joe, the owner of the ice-cream shop, purchases ice cream from two vendors. Over the years, he has developed good relationships with both vendors so that they allow Joe to pay them biweekly for all purchases made during the preceding two-week period. Joe calls in icecream orders on Mondays and Thursdays. The orders are delivered the next day. Joe buys ice-cream toppings from one of several local stores and pays for each such purchase at the time of sale with a check from the company's main checking account. REQUIRED Draw an REA diagram, complete with cardinalities, for Joe's expenditure cycle. CASE 18-1 Practical Database Design Hands-on practice in database design is important. Use a relational DBMS to implement the integrated REA data model presented in this chapter, or one of the integrated data models from the homework problems, or a model provided by your instructor. Then, perform the following tasks: 1. Write a query to calculate total accounts receivable. 2. Write a query to calculate accounts receivable for a specific customer. 3. Create a sales invoice form that references the appropriate tables and inputs data about attributes into the proper tables. 4. Write queries to calculate as many financial statement items as possible from the data model you implement. 5. Design appropriate input controls to ensure the validity of data entered in the form created in step 3. 18.1. Refer to Problems 17.1 and 17.2 for information about the revenue and expenditure cycle activities for Joe's ice-cream shop in order to draw an integrated REA diagram of both cycles. PROBLEMS 17.1. Joe's is a small ice-cream shop located near the local university's baseball field. Joe's serves walk-in customers only. The shop carries 26 flavors of ice cream. Customers can buy cones, sundaes, or shakes. When a customer pays for an individual purchase, a sales transaction usually includes just one item. When a customer pays for a family or group purchase, however, a single sales transaction includes many different items. All sales must be paid for at the time the ice cream is served. Joe's maintains several banking accounts but deposits all sales receipts into its main checking account. REQUIRED Draw an REA diagram, complete with cardinalities, for Joe's revenue cycle. REA Diagram, complete with cardinalities, for Joe's revenue cycle 17.2. Joe, the owner of the ice-cream shop, purchases ice cream from two vendors. Over the years, he has developed good relationships with both vendors so that they allow Joe to pay them biweekly for all purchases made during the preceding two-week period. Joe calls in icecream orders on Mondays and Thursdays. The orders are delivered the next day. Joe buys ice-cream toppings from one of several local stores and pays for each such purchase at the time of sale with a check from the company's main checking account. REQUIRED REA diagram, complete with cardinalities, for Joe's expenditure cycle. CASE 18-1 Practical Database Design Hands-on practice in database design is important. Use a relational DBMS to implement the integrated REA data model presented in this chapter, or one of the integrated data models from the homework problems, or a model provided by your instructor. Then, perform the following tasks: 1. Write a query to calculate total accounts receivable. 2. Write a query to calculate accounts receivable for a specific customer. 3. Create a sales invoice form that references the appropriate tables and inputs data about attributes into the proper tables. 4. Write queries to calculate as many financial statement items as possible from the data model you implement. 5. Design appropriate input controls to ensure the validity of data entered in the form created in step 3. 18.1. Refer to Problems 17.1 and 17.2 for information about the revenue and expenditure cycle activities for Joe's ice-cream shop in order to draw an integrated REA diagram of both cycles. 1. Write a query to calculate total accounts receivable. Total Accounts Payable can be calculated in five steps, as follows: Step 1: Normally, only orders actually received are considered purchases for purposes of calculating accounts payable. Therefore, begin with a query of the Receive Inventory table (or M:N relationship table linking the Order Inventory and Receive Inventory events) to determine which orders have been received this fiscal period. Step 2: Query the Order Inventory - Inventory table to determine the total amount purchased this fiscal period by summing the product of quantity ordered by its unit cost for those purchase orders for which there is a corresponding receipt of inventory (from step 1). Step 3: Retrieve the total beginning balance of Accounts Payable by querying the Suppliers table and summing the beginning balance column. Step 4: Query the Disburse Cash table to calculate the total amount paid to suppliers this fiscal period by summing the amount column for every row in which the supplier number is not null. It is important to exclude rows where the supplier number is null, as those represent other types of payments (e.g., payroll). Step 5: Total Accounts Payable = Answer to Query 2 + Answer to Query 3 - Answer to Query 4. 2. Write a query to calculate accounts receivable customer. for a specific Calculate beginning Accounts Receivable by summing the beginning balance attribute in the Customer table for the customer of interest. a. Select only those rows in the Sales table that represent sales to the customer of interest (i.e., those rows in the Sales table which have a value in the Customer# foreign key column equal to the Customer# of the particular customer of interest). b. Calculate total new Sales this fiscal period by summing the product of quantity sold times unit price from the Sales - Inventory M:N relationship table for only those rows which have an invoice number in the set of invoice numbers from query b. c. Calculate total cash received from customers by summing amount received column in the Receive Cash table for only those rows which have a value in the Customer# foreign key column equal to the Customer# of the particular customer of interest. d. Total Accounts Receivable = Query A + Query C - Query D. 3. Create a sales invoice form that references the appropriate tables and inputs data about attributes into the proper tables. The syntax for doing this will vary depending upon the DBMS used. Solutions should be tested to ensure that the form adds new rows to the following tables: Sales Sales-Inventory It will also need to reference the Customer and Inventory tables to identify relevant information (shipping and billing addresses, item description, etc.). 4. Write queries to calculate as many financial statement items as possible from the data model you implement. Income Statement items derivable from Figure 18.1: Only Sales and Wage Expense can be definitively calculated; cost of goods sold requires making assumptions about inventory cost/valuation method (FIFO, LIFO, weighted average, specific identification). Sales Sum the product of quantity sold times unit price for all rows in the M:N Sales-Inventory relationship table Wage expense Step 1: Query the Time Worked table to sum the total hours worked, grouped by employee number. Step 2: Query the table produced in step 1 and the Employee table to calculate the total wages earned by each employee (by multiplying total hours worked times that employee's pay rate). Step 3: Sum the total amounts in the table produced in step 2. Balance Sheet items derivable from Figure 18.1: Only Cash, Accounts Receivable, Accounts Payable, and Wages Payable can be definitively calculated. Inventory valuation requires an assumption about inventory method (FIFO, LIFO, weighted average, specific identification). Cash Step 1: Calculate the beginning balance of cash by summing the amount column in the Cash table. Step 2: Calculate total cash receipts by summing the amount column in the Receive Cash table. Step 3: Calculate total cash disbursements by summing the amount column in the Disburse Cash table. Step 4: Ending Cash Balance = Answer to Query 1 + Answer to Query 2 - Answer to Query 3. Accounts Receivable a. Calculate beginning Accounts Receivable by summing the beginning balance attribute in the Customer table. b. Calculate total new Sales this fiscal period by summing the product of quantity sold times unit price from the Sales - Inventory M:N relationship table. c. Calculate total cash received from customers by summing amount received column in the Receive Cash table. d. Total Accounts Receivable = Query A + Query B - Query C Accounts Payable Step 1: Normally, only orders actually received are considered purchases for purposes of calculating accounts payable. Therefore, begin with a query of the Receive Inventory table (or M:N relationship table linking the Order Inventory and Receive Inventory events) to determine which orders have been received this fiscal period. Step 2: Query the Order Inventory - Inventory table to determine the total amount purchased this fiscal period by summing the product of quantity ordered by its unit cost for those purchase orders for which there is a corresponding receipt of inventory (from step 1). Step 3: Retrieve the total beginning balance of Accounts Payable by querying the Suppliers table and summing the beginning balance column. Step 4: Query the Disburse Cash table to calculate the total amount paid to suppliers this fiscal period by summing the amount column for every row in which the supplier number is not null. It is important to exclude rows where the supplier number is null, as those represent other types of payments (e.g., payroll). Step 5: Total Accounts Payable = Answer to Query 2 + Answer to Query 3 - Answer to Query 4. Wages Payable Step 1: Query the Time Worked table to sum the total hours worked, grouped by employee number, restricted to only those rows for which the check number column is null. Step 2: Query the table produced in step 1 and the Employee table to calculate the total wages earned by each employee (by multiplying total hours worked times that employee's pay rate). Step 3: Sum the total amounts column in the table produced in step 2. 5. Design appropriate input controls to ensure the validity of data entered in the form created in step 3. The syntax required will vary depending upon the DBMS used. Students should be encouraged to review material from chapter 10 to identify appropriate input controls. Solutions should include the following: 1. 2. 3. 4. Auto-number the sales invoice to prevent creating duplicate or null primary keys. Validity checks on item numbers. Sign check on quantity sold and price fields. Completeness checks on customer information (billing and shipping address) - this information should ideally be automatically populated upon entering the customer number. 5. Completeness checks on inventory information (description, list price, etc.) - this information should ideally be automatically populated upon entering the item number. 6. Validity check on sales date (check against the current system date). CASE 18-1 Practical Database Design Hands-on practice in database design is important. Use a relational DBMS to implement the integrated REA data model presented in this chapter, or one of the integrated data models from the homework problems, or a model provided by your instructor. Then, perform the following tasks: 1. Write a query to calculate total accounts receivable. 2. Write a query to calculate accounts receivable for a specific customer. 3. Create a sales invoice form that references the appropriate tables and inputs data about attributes into the proper tables. 4. Write queries to calculate as many financial statement items as possible from the data model you implement. 5. Design appropriate input controls to ensure the validity of data entered in the form created in step 3. 18.1. Refer to Problems 17.1 and 17.2 for information about the revenue and expenditure cycle activities for Joe's ice-cream shop in order to draw an integrated REA diagram of both cycles. 1. Write a query to calculate total accounts receivable. Total Accounts Payable can be calculated in five steps, as follows: Step 1: Normally, only orders actually received are considered purchases for purposes of calculating accounts payable. Therefore, begin with a query of the Receive Inventory table (or M:N relationship table linking the Order Inventory and Receive Inventory events) to determine which orders have been received this fiscal period. Step 2: Query the Order Inventory - Inventory table to determine the total amount purchased this fiscal period by summing the product of quantity ordered by its unit cost for those purchase orders for which there is a corresponding receipt of inventory (from step 1). Step 3: Retrieve the total beginning balance of Accounts Payable by querying the Suppliers table and summing the beginning balance column. Step 4: Query the Disburse Cash table to calculate the total amount paid to suppliers this fiscal period by summing the amount column for every row in which the supplier number is not null. It is important to exclude rows where the supplier number is null, as those represent other types of payments (e.g., payroll). Step 5: Total Accounts Payable = Answer to Query 2 + Answer to Query 3 - Answer to Query 4. 2. Write a query to calculate accounts receivable customer. for a specific Calculate beginning Accounts Receivable by summing the beginning balance attribute in the Customer table for the customer of interest. a. Select only those rows in the Sales table that represent sales to the customer of interest (i.e., those rows in the Sales table which have a value in the Customer# foreign key column equal to the Customer# of the particular customer of interest). b. Calculate total new Sales this fiscal period by summing the product of quantity sold times unit price from the Sales - Inventory M:N relationship table for only those rows which have an invoice number in the set of invoice numbers from query b. c. Calculate total cash received from customers by summing amount received column in the Receive Cash table for only those rows which have a value in the Customer# foreign key column equal to the Customer# of the particular customer of interest. d. Total Accounts Receivable = Query A + Query C - Query D. 3. Create a sales invoice form that references the appropriate tables and inputs data about attributes into the proper tables. The syntax for doing this will vary depending upon the DBMS used. Solutions should be tested to ensure that the form adds new rows to the following tables: Sales Sales-Inventory It will also need to reference the Customer and Inventory tables to identify relevant information (shipping and billing addresses, item description, etc.). 4. Write queries to calculate as many financial statement items as possible from the data model you implement. Income Statement items derivable from Figure 18.1: Only Sales and Wage Expense can be definitively calculated; cost of goods sold requires making assumptions about inventory cost/valuation method (FIFO, LIFO, weighted average, specific identification). Sales Sum the product of quantity sold times unit price for all rows in the M:N Sales-Inventory relationship table Wage expense Step 1: Query the Time Worked table to sum the total hours worked, grouped by employee number. Step 2: Query the table produced in step 1 and the Employee table to calculate the total wages earned by each employee (by multiplying total hours worked times that employee's pay rate). Step 3: Sum the total amounts in the table produced in step 2. Balance Sheet items derivable from Figure 18.1: Only Cash, Accounts Receivable, Accounts Payable, and Wages Payable can be definitively calculated. Inventory valuation requires an assumption about inventory method (FIFO, LIFO, weighted average, specific identification). Cash Step 1: Calculate the beginning balance of cash by summing the amount column in the Cash table. Step 2: Calculate total cash receipts by summing the amount column in the Receive Cash table. Step 3: Calculate total cash disbursements by summing the amount column in the Disburse Cash table. Step 4: Ending Cash Balance = Answer to Query 1 + Answer to Query 2 - Answer to Query 3. Accounts Receivable a. Calculate beginning Accounts Receivable by summing the beginning balance attribute in the Customer table. b. Calculate total new Sales this fiscal period by summing the product of quantity sold times unit price from the Sales - Inventory M:N relationship table. c. Calculate total cash received from customers by summing amount received column in the Receive Cash table. d. Total Accounts Receivable = Query A + Query B - Query C Accounts Payable Step 1: Normally, only orders actually received are considered purchases for purposes of calculating accounts payable. Therefore, begin with a query of the Receive Inventory table (or M:N relationship table linking the Order Inventory and Receive Inventory events) to determine which orders have been received this fiscal period. Step 2: Query the Order Inventory - Inventory table to determine the total amount purchased this fiscal period by summing the product of quantity ordered by its unit cost for those purchase orders for which there is a corresponding receipt of inventory (from step 1). Step 3: Retrieve the total beginning balance of Accounts Payable by querying the Suppliers table and summing the beginning balance column. Step 4: Query the Disburse Cash table to calculate the total amount paid to suppliers this fiscal period by summing the amount column for every row in which the supplier number is not null. It is important to exclude rows where the supplier number is null, as those represent other types of payments (e.g., payroll). Step 5: Total Accounts Payable = Answer to Query 2 + Answer to Query 3 - Answer to Query 4. Wages Payable Step 1: Query the Time Worked table to sum the total hours worked, grouped by employee number, restricted to only those rows for which the check number column is null. Step 2: Query the table produced in step 1 and the Employee table to calculate the total wages earned by each employee (by multiplying total hours worked times that employee's pay rate). Step 3: Sum the total amounts column in the table produced in step 2. 5. Design appropriate input controls to ensure the validity of data entered in the form created in step 3. The syntax required will vary depending upon the DBMS used. Students should be encouraged to review material from chapter 10 to identify appropriate input controls. Solutions should include the following: 1. 2. 3. 4. Auto-number the sales invoice to prevent creating duplicate or null primary keys. Validity checks on item numbers. Sign check on quantity sold and price fields. Completeness checks on customer information (billing and shipping address) - this information should ideally be automatically populated upon entering the customer number. 5. Completeness checks on inventory information (description, list price, etc.) - this information should ideally be automatically populated upon entering the item number. 6. Validity check on sales date (check against the current system date)