b. Implement your model in a set of relational tables. Be sure to specify primary keys,
foreign keys, and identify at least one other attribute that should be included in each table.
Table Name Primary Key(s)
Other Attributes (foreign keys in
italics, others in normal font)
19.8 Small contractors often rent special equipment for specific jobs. They need to track
the equipment that is rented, when it is returned, and payments made to the rental
company.
REQUIRED
a. Draw a partial REA diagram for the acquisition, payment, and return of rental
equipment. Be sure to include cardinalities and state any assumptions you made
when specifying those cardinalities.
This solution is based on the following assumptions:
1. Each Rent event is independent of every other Rent event. For example, each time the
b. Create a set of tables (either on paper or in a relational DBMS to which you have
access) to implement the REA model you developed.
Table Name Primary Key Other attributes (foreign keys)
Rented Equipment Equipment number Description,
Cash GL Account Number Name, beginning balance
Return Rented Items Return number Date, time, vendor number,
employee number, rental
Rented Items
number
SUGGESTED ANSWERS TO THE CASES
Case 19.1 This case involves creating a database from an integrated REA diagram and
then using the REA diagram to guide the writing of queries to prepare financial statements.
Required
a. Create the tables necessary to implement Figure 19-9 in a relational database. Be sure to
include primary keys and other relevant attributes in each table.
Table Name Primary Key(s)
Other Attributes (foreign keys in
italics, others in normal font)
Customer Customer number Name, address, phone, beginning
account balance, credit limit
Employee Employee number Name, date hired, wage rate, date of
birth, position
Use Equipment Machine Operation Number Equipment ID number, W.I.P. number,
Machine Operation List number, Time
Started, Time Finished, Date
Equipment Operations
List
Machine Operation List
Number
Item number, standard time
Job Operations List Job Operations List Number Standard time, Item number
Transfer Agent number, employee
(Treasurer) number, GLAccount
number
Transfer Agent Transfer Agent Number Name, Address, Phone
Customer Order –
Finished Goods
Order Number, Item
Number
Quantity ordered, unit sales price
b. Write the query, or set of queries, necessary to generate as
many elements of financial statements as possible. For
example, write the query or set of queries that would be used
to calculate the amount of cash on hand, the total of accounts
receivable, the total value of raw materials, inventory on hand,
etc.
The actual syntax will depend on the software used. The following logic describes the queries
that can be used to provide most of the information needed to construct a simple income
statement and balance sheet:
2. To derive total actual Cost of Goods Sold requires assumptions about inventory
costing method (LIFO, FIFO, etc.). However, it is straightforward to calculate the
standard cost of goods sold as follows: query the Finished Goods Inventory, the
3. Only three expenses can be calculated from the model: wages, general
administrative expenses, and depreciation.
a. To calculate wages expense: Sum hours worked (from the Time Worked
a. Begin by retrieving the sum of the beginning balance attribute from the
suppliers table.
b. Then calculate the total of all purchases.