CHAPTER 18 IMPLEMENTING AN REA MODEL IN A RELATIONAL DATABASE SUGGESTED ANSWERS TO DISCUSSION QUESTIONS 18.1 How would the process of generating a cash disbursements journal from the REA data model presented in Figure 18-4 and Table 18-1 differ from the process for creating a sales journal? The steps required to create a cash disbursements journal would be simpler than the process used to create a sales journal. A cash disbursements journal typically lists all of an organization’s payments to its various suppliers (payroll disbursements are usually recorded separately from payments to suppliers).
The information typically recorded in a cash disbursements journal includes the date, method of payment, payment identifier (e.g., check number or EFT transaction number), payee, amount, and description of the purpose. Note that all of this information can be found in the disburse cash event table.
Thus, the information necessary to produce a cash disbursements journal can be found by querying only one table. The query would be restricted to those rows for which the supplier number, a foreign key, has values (null values for the supplier number would occur because the cash disbursements were for payroll).
18.2 Why take the time to develop separate REA diagrams for each business cycle if the ultimate objective is to combine them into one integrated enterprise-wide data model? Why not just focus on the integrated model from the start? One way to think about it is divide and conquer. Modeling each business cycle individually makes it easier to be sure to identify all the relevant resources, events, and agents for that cycle. Once the resources, events, and agents have been identified, the relationships between these entities can be prepared.
It is also easier to assign cardinalities to relationships in REA diagrams for a single business cycle because the relationships directly represent the organization’s business policies.
The data modeler can also show the single cycle REA diagram to the employees who participate in those activities to be sure that everything of importance is included in the model and that the business policies are represented correctly. Working with a single cycle REA diagram makes this review process easier by excluding information not relevant to a particular employee.
Once each single cycle REA diagram is correct, the data modeler can combine them using the rules explained in chapters 17 and 18. This combination process can be done without the involvement of any employees. The integrated diagram, however, needs to be discussed with management to verify accuracy and completeness.
18.3 Building separate tables for every relationship (1:1, 1:N, and M:N) does not violate any of the rules for building a well-structured database. Why then do you think that REA data modelers recommend building separate tables only for M:N relationships and using foreign keys to implement 1:1 and 1:N relationships? To reduce the number of tables. It is more efficient to use foreign keys for relationships than to build separate tables.
This also simplifies queries because fewer tables are needed to retrieve information. Consider the task of generating a list of payments received from a particular customer. If the 1:N relationship between customers and the Receive Cash event is implemented using foreign keys, all the information needed can be found in the Receive Cash and Customer tables. The Receive Cash table would identify the date and amount of payments received from each customer; the customer table would be referenced in order to restrict the query to retrieving only those rows for which the foreign key value for customer number in the Receive Cash table matched the primary key value of a customer with a specific name in the Customer table. However, if the 1:N relationship were implemented as a separate table, then the query would also have to access the 1:N relationship table.
18.4 Assume that there exists a 1:1 relationship between the Receive Inventory and Disburse Cash events. How does the manner in which the relationship between the two events is implemented (i.e., in which table a foreign key is placed) affect the process used to record payments made to suppliers? If the primary key of the Receive Inventory table was included as a foreign key in the Disburse Cash table, then recording payments to suppliers would involve adding a new row to the Disburse Cash table.
If, however, the primary key of the Disburse Cash table was included as a foreign key in the Receive Inventory table, then recording a payment to a supplier would involve not only creating a new row in the Disburse Cash table, but also updating the value of the check number (foreign key) column in the appropriate row of the Receive Inventory table.