ACC 542 Week 3 Database Concepts and Tools Example

Replacing a Rental Spreadsheet With a Relational Database: An REA Model, Normalized Tables and Four Accounting Queries for a Composite Equipment Rental Company

[Student Name]

University of Phoenix

ACC/542: Accounting Information Systems

Week 3 Assignment

[Instructor Name]

[Date]

The rental company, its data and all figures are composites written for a model paper; methods and research findings come from the sources listed.

A composite company rents excavators, lifts, compactors and generators to contractors from three yards. Its rental coordinators track every rental in one spreadsheet: a row for each rental with the customer's name, address and credit terms, the equipment's description and daily rate, the dates out and in and the invoice number once billed. The spreadsheet has 38,000 rows. Customers' addresses appear hundreds of times, sometimes spelled differently, and the controller suspects that some returned equipment was never billed. A spreadsheet that repeats the same fact on thousands of rows is not a database; it is a set of chances for the fact to disagree with itself. This paper designs a relational database for the rental cycle and shows how queries answer accounting questions.

Modeling the Rental Cycle With REA

The resources, events and agents model organizes accounting data around what the business does. McCarthy (1982) proposed it as a framework for accounting systems in a shared data environment, recording business events and the resources and agents involved rather than only summarized debits and credits. In the rental cycle, the resources are equipment and cash. The events are the rental, the return, the invoice and the cash receipt. The agents are customers, the employees who process rentals and the yards.

Relationships follow from the business. Each rental involves one customer and one or more pieces of equipment; each piece of equipment can be rented many times. A return relates to one rental. An invoice relates to one rental and may be paid by one or more cash receipts, and one cash receipt may pay several invoices.

From Model to Tables

Each entity becomes a table with a primary key that uniquely identifies each row. CUSTOMER holds customer ID, name, address, credit limit and terms. EQUIPMENT holds equipment ID, category, description, yard ID, purchase date and daily rate. RENTAL holds rental ID, customer ID, employee ID, date out and expected return date. RENTAL_LINE holds rental ID and equipment ID, together forming its key, with the agreed daily rate. RETURN holds return ID, rental ID, date in and condition notes. INVOICE holds invoice ID, rental ID, date and amount. CASH_RECEIPT holds receipt ID, customer ID, date and amount, and a linking table, RECEIPT_APPLICATION, records which invoices each receipt paid.

Foreign keys link the tables: customer ID in RENTAL points to CUSTOMER, rental ID in RETURN and INVOICE points to RENTAL and so on. The database enforces referential integrity, so no rental can refer to a customer that does not exist (Codd, 1970).

Normalizing the Old Spreadsheet

The spreadsheet shows why normalization matters. It is not in first normal form, because some rows list several pieces of equipment in one cell; splitting them into separate rows achieves first normal form. It is not in second normal form, because details such as the equipment description depend only on the equipment, not on the whole rental; moving equipment attributes to their own table fixes that. It is not in third normal form, because the customer's address depends on the customer, not the rental; moving customer data to its own table removes that transitive dependency.

The result eliminates update anomalies: when a customer moves, its address changes in one row, not in hundreds. It also eliminates insert anomalies, since a new machine can be recorded before it is ever rented.

Four Accounting Queries

First, revenue by equipment category for the quarter: SELECT category, SUM(amount) FROM INVOICE JOIN RENTAL_LINE and EQUIPMENT on their keys, filtered to invoice dates in the quarter and grouped by category. The query shows that aerial lifts produce 34% of revenue on only 18% of the fleet.

Second, equipment overdue for return: select rentals whose expected return date is before today and for which no RETURN record exists. The yard managers receive this list each morning and call each customer the same day, since an unreturned machine is both lost rent from other customers and a risk of damage or loss.

Third, returns never billed: a query that starts from RETURN and uses a LEFT JOIN to INVOICE on rental ID, keeping only rows where the invoice ID is null. The first run found 47 returns in the past year with no invoice, about $86,000 of unbilled rent.

Fourth, customers over their credit limit: sum open invoices by customer, subtract applied cash and compare the balance with the credit limit in CUSTOMER.

Data Integrity Controls

The database design supports several controls. Primary keys prevent duplicate customers or machines. Referential integrity prevents orphan records, such as an invoice for a rental that does not exist. Field validation restricts daily rates to positive amounts and dates to logical ranges, and a rule prevents recording a return date before the date out. Access controls limit who can change daily rates or credit limits. Romney et al. (2021) note that the reliability of accounting information depends on these database-level controls as much as on procedures.

Migrating the Old Data

Moving 38,000 spreadsheet rows into the new tables is a project of its own. The team first extracted distinct customers and matched variant spellings, reducing 2,900 apparent customers to 1,740 real ones, then assigned each an ID. Equipment was matched to the fixed asset register by serial number, which exposed 22 machines in the register that no longer appear in any rental and may have been sold or lost. Rentals were loaded with their customer and equipment IDs, and every load was reconciled back to the spreadsheet's row counts and revenue totals before the old file was locked. Errors found in migration were corrected at the source, not patched in the new database.

Reporting Beyond the Database

The transactional database is designed for recording events quickly and reliably. For analysis across years, such as utilization by machine and season, the company will copy data nightly into a simple data warehouse organized around a rental fact table and dimensions for date, customer, equipment and yard. Managers can then build utilization and pricing reports without slowing the system that coordinators use all day.

Why Event-Based Design Matters to Accountants

Traditional systems store summarized ledger balances; event-based designs store the underlying events, from which ledgers and many other reports can be generated. The unbilled-returns query is only possible because returns are recorded as events in their own right. A ledger-only system would show revenue from invoices and would never reveal the rentals that produced no invoice.

Conclusion

An REA model of the rental cycle produced a set of normalized, keyed tables that remove the spreadsheet's redundancy and anomalies. Queries on the new database report revenue by category, flag overdue equipment and credit risks and found $86,000 of returns never billed. The design's keys, integrity rules and access controls make the data reliable enough to support both management and audit.

References

Codd, E. F. (1970). A relational model of data for large shared data banks. Communications of the ACM, 13(6), 377-387. https://doi.org/10.1145/362384.362685

McCarthy, W. E. (1982). The REA accounting model: A generalized framework for accounting systems in a shared data environment. The Accounting Review, 57(3), 554-578.

Romney, M. B., Steinbart, P. J., Summers, S. L., & Wood, D. A. (2021). Accounting information systems (15th ed.). Pearson.