Implementing an rea model in a relational database suggested answers to discussion questions



Download 125.61 Kb.
Page4/5
Date03.04.2021
Size125.61 Kb.
1   2   3   4   5

Primary key


Other attributes (foreign keys in italics)

Vendor

Vendor#

Name, address, phone, contact

Employees

Employee#

Name, date hired, salary, date of birth, position

Order Inventory

Purchase Order#

Date, vendor#, employee#

Receive Inventory

Receiving Report#

Date, vendor#, employee#, check#, purchase order#

Disburse Cash

Check#

Date, amount, vendor#, employee#, GLaccount#

Inventory

Item#

Description, beginning quantity on hand, reorder quantity, reorder point

Cash

GLaccount#

Account name, beginning balance

Sales

Invoice#

Date, employee#, customer#, remittance#

Receive Cash

Remittance#

Date, amount, employee#, customer#, GLaccount#

Customers

Customer#

Name, address, beginning balance

Order Inventory-Inventory

Purchase Order#, Item#

Quantity, unit cost

Sales-Inventory

Invoice#, Item#

Quantity, unit price

Receive Inventory-Inventory

Receiving Report#, Item#

Quantity, condition


18.8 Explain how to calculate the total amount of Accounts Payable:
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.

18.9 Refer to Figure 18.4 and Table 18-1 to write the query logic needed to answer the following questions. (Optional: If requested by your instructor, write your queries in SQL or a Query-By-Example graphical interface.) Some answers may require more than one query—try to write the most efficient queries possible.


  1. Accounts payable for all suppliers in Arizona

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) and the Supplier table to determine which orders have been received this fiscal period. The supplier table is needed in order to restrict the result to only those suppliers located in Arizona.


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, restricting the sum operation to only those suppliers located in Arizona.
Step 4: Query the Disburse Cash and Supplier tables to calculate the total amount paid to suppliers located in Arizona this fiscal period by summing the amount column for every row in the Disburse Cash table for which the supplier number equals the primary key of a row in the Supplier table that has a value of Arizona in its address column.
Step 5: Total Accounts Payable to Arizona Suppliers = Answer to Query 2 + Answer to Query 3 – Answer to Query 4.

b. Total amount of sales to a customer named Smith
Step 1: Identify all sales to Smith by writing a query that joins the Sales and Customer tables, where the value of the customer# in the Sales table equals the primary key in the row of the Customer table where the name = Smith.
Step 2: Sum the product of quantity sold times unit price in the M:N Sales-Inventory table for only those rows with sales invoice numbers identified in step 1.
c. Total wage expense
Pay rates are likely to differ across employees. Therefore, calculate total wage expense by summing the wage expense for each employ, as follows:
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.
d. Total wages payable
Total wages payable equals wages earned but not yet paid. Thus, use the same procedure as used to calculate total wage expense, except restrict step 1 to only those rows in the Time Worked table for which the paycheck# column is null.

e. Net increase (decrease) in quantity-on-hand for a particular inventory item
Step 1: Write a query to sum the quantity received in the M:N relationship table linking the Receive Inventory event and the Inventory table for only those rows with a particular value in the item# column.
Step 2: Write a query to sum the quantity sold in the M:N Sales-Inventory relationship table for only those rows with the same item# as used in step 1.
Step 3: Net change in quantity-on-hand for that item = Query 1 – Query 2.
f. The proportion of sales made to walk-in customers (i.e., no order)
Step 1: Write a query to calculate total sales by summing the product of quantity sold times unit price in the Sales-Inventory M:N relationship table.
Step 2: Write a query to identify all sales to walk-in customers by listing all sales invoices in the Sales table for which the order number column is null.
Step 3: Repeat step 1, but restrict the calculation to only those rows in the Sales-Inventory table for which the sales invoice number appeared in the step 2 query.
Step 4: Divide query 3 by query 1.
g. The salesperson who made the largest amount of sales in October
Step 1: Write a query to calculate total sales by summing the product of quantity sold times unit price in the Sales-Inventory M:N relationship table.

Step 2: Write a query to identify all sales to walk-in customers by listing all sales invoices in the Sales table for which the order number column is null.

Step 3: Repeat step 1, but restrict the calculation to only those rows in the Sales-Inventory table for which the sales invoice number appeared in the step 2 query.

Step 4: Divide query 3 by query 1.



h. The salesperson who made the most sales in October
Step 1: Write a query to identify all rows in the Sales table that occurred in October.

Step 2: Write a query that counts the number of rows in the response to query 1, grouped by employee number.

Step 3: Write a query that identifies the employee number in query 2 that has the maximum value in the count column.

Step 4: Write a query that joins the result of query 3 with the employee table to display both the employee number and name.



i. The most popular item, in terms of total units sold
Step 1: Write a query against the M:N Sales-Inventory relationship table that sums the quantity sold column, grouped by product number.

Step 2: Write a query against the table resulting from query 1, that identifies the item number with the maximum value in the total quantity sold column.



Step 3: Write a query that joins the result of query 2 with the inventory table to display both the item# and its description.

18.10 Refer to Problem 17.10 and develop a set of tables to implement the REA diagram you developed for Stan’s Southern Barbeque Supply Store. Identify the primary and foreign keys for each table, and don’t forget to address any M:N relationships.


Table



Share with your friends:
1   2   3   4   5




The database is protected by copyright ©essaydocs.org 2020
send message

    Main page