Complete
Solutions for Accounting Information System 12e by Marshall B. Romney Paul J. Steinbart
IF You Want To Purchase A+ Work Then Click The Link
Below , Instant Download
CHAPTER 19
SPECIAL TOPICS IN REA
MODELING
19.1 Often it takes several sales calls to obtain the first order from
a new customer. Why then does Figure 19-1 depict the relationship between the
Call on Customer and Take Customer Order events as being 1:1?
19.2 How could an automobile dealer model the use of loaner cars, which
it gives to customers for free whenever they drop off a vehicle for maintenance
that will take longer than one day to complete?
19.3 In what situations would you expect to model a relationship
between an agent and a resource?
19.4 Why is depreciation not represented as an event in the REA data
model?
19.5 How would you model the acquisition of a digital asset, such as
the purchase of software online (the software is downloaded and then installed
on the purchaser’s computer)?
19.6 How are the similarities and differences between the purchase of
services, such as telephone service, and the purchase of raw materials
reflected in an REA data model?
19.7 How would you modify the expenditure cycle REA diagram in Figure
19-4 to include the return of defective products to suppliers for credit?
19.1 We-Fix-Computers, Inc. provides spare
parts and service for a wide variety of computers. Customers may purchase parts
to take home for do-it-yourself repairs, or they may bring their systems in for
repair, in which case they pay for both the parts and the labor associated with
the type of service required. Some services do not include any new parts, just
a labor charge for that service. Individual customers must pay for all parts
purchases in full at the time of sale. Individual customers must pay 50% down
when they bring their computers in for servicing and pay the balance at pickup.
Corporate customers, however, are billed monthly for all sales (parts or
service). Although We-Fix-Computers, Inc. has several different banking accounts,
all sales are deposited intact into its main checking account.
We-Fix-Computers, Inc. purchases its inventory of parts from more
than a dozen different vendors. Orders are usually delivered the next day;
sometimes, however, suppliers ship only partial orders. We-Fix-Computers pays
for some of its purchases COD, but usually pays by the 10th of the month for
all purchases made the prior month. None of its suppliers allows it to make
installment payments.
Required
Draw an integrated REA diagram for We-Fix-Computers’ revenue and
expenditure cycles.
19.2 The Mesa Veterinary Hospital is run by Dr.
Brigitte Roosevelt. She has two employees in the office and has asked you to
develop a database to help better track her data. Dr. Roosevelt currently uses
her personal computer only for word processing, but she is interested in also
using it to maintain pet histories and accounting information. She is excited
about the transition and is counting on you to help her through the process.
She describes her daily activities as follows:
When new customers come to Mesa Veterinary Hospital, the “owners”
of the pets are required to complete an introductory form. This form includes
the following:
• Owner name
• Address
• Day phone
• Night phone
They are also required to provide the following information about
each pet, as some people own many pets:
• Pet name
• Breed
• Color
• Birth date
Dr. Roosevelt would like to enter this information once and then
have the system retrieve it for all subsequent visits.
When customers call to make appointments, one of the office clerks
asks what kind of services they require (e.g., is it a routine exam, a surgery,
etc.). Dr. Roosevelt sees only one pet during each appointment. If she is going
to see one owner’s two pets, then two separate appointments are necessary (but
scheduled back-to-back). For each appointment, Dr. Roosevelt records the pet’s
weight, notes the reason for the appointment, and records her diagnosis.
Depending on the diagnosis, the doctor will possibly prescribe any number of
medications to cure the pet.
Owners are charged $25 for each appointment and must pay
additionally for any medications prescribed for their pets. Dr. Roosevelt
requires all pets to be brought back for another examination prior to refilling
any prescriptions. Customers must pay for services and medication in full at
the conclusion of their visits.
You also learn that Dr. Roosevelt orders drugs and medications
from several different suppliers. She places orders weekly, on Fridays. Suppliers
usually make one shipment to fill each order, but sometimes have to make
additional shipments if they are currently out of stock of one or more items.
In such cases, they always ship the back-ordered item as soon as they receive
it from the manufacturer; they never combine such back orders with subsequent
orders by Dr. Roosevelt.
Suppliers bill Dr. Roosevelt monthly and expect payment in full by
the 15th of the following month. A few suppliers do permit Dr. Roosevelt to
make installment payments. The prices charged by suppliers for a given product
may change several times during the year, so it is important to accurately
store the cost of each item each time it is purchased.
Dr. Roosevelt concludes the interview by requesting that in
addition to the facts mentioned,
she wants the system to store the following attributes:
• Number of pets owned by each customer
• Total charge for the appointment
• Prescription price
• Drug name
• Length of appointment
• Diagnosis
• Date of appointment
• Service requested
REQUIRED
a. Given this brief overview, draw an integrated REA diagram for
the Mesa Veterinary Hospital and include cardinalities.
b. As directed by your instructor, either draw the tables
necessary to implement the integrated REA diagram you developed for the Mesa
Veterinary Hospital or build the tables in a relational DBMS to which you have
access. Be sure to include all attributes from the narrative plus the
additional ones explicitly listed by Dr. Roosevelt at the conclusion of the
interview. Create additional attributes only if necessary.
19.3 Your university hires you to implement a database system for the
library network. You have interviewed several librarians, and the following
summarizes these discussions:
• The library’s main goal is to provide students and professors
with access to books and other publications. The library, therefore, maintains
an extensive collection of materials that are available to anyone with a valid
university identification card.
• The standard procedure for lending materials is that the student
or faculty member comes to one of the three campus libraries and locates the
book or journal on the shelves.
• Each book is assigned three unique numbers. First, the book is
assigned a number by the publisher, called the International Standard Book
Number (ISBN). This number allows the publishers to track each title and the
number changes with each new edition. The second number is the Dewey decimal
number, which is assigned to the title and written on the outside spine of the
book. This number is used to organize the library shelves and is thus helpful
to the students and faculty. It is therefore critical that this number be
available to users on the online inquiry screens. The last number is a
university book ID number. A different number is assigned to every book that is
received so the library can track all copies of each book. This number is
different from the other two numbers such that if the library has three copies
of one book, each will have a unique university book ID number.
• When students or faculty check out books, the system must be
able to track the specific copy that is being borrowed. Each book has a
magnetic strip inserted in its spine, which is used as a security measure. If
someone tries to take a book without checking it out, an alarm sounds.
• In general, students and faculty have equal clout in the
library. Both are able to check
out most books and to check out several books at one time. No one
is allowed to remove periodicals from any library. The length of time that the
book may be borrowed varies, however, depending on who checks it out. Students
are allowed to check out a book for several weeks; faculty may borrow books for
several months.
• When patrons check out books, they take their materials to the
circulation desk. At that time, the librarian scans in each item’s university
book ID number and the borrower’s ID number. The system records a separate loan
event for each book being checked out, assigning each a separate loan number.
At this time, each book’s due date is calculated and marked on a slip located
inside each book’s front cover. Simultaneously, the magnetic strip is
deactivated so the book may be removed from the library.
• After borrowers check out a book, they are expected to return it
by its due date. In reality, everyone is allowed 30 days after the due date
recorded on the checkout slip before the book is officially overdue. At that
point, the book must be returned, and the borrower is assessed a $10 fine. If
the book is permanently lost, then the borrower is fined $75 for the book’s
replacement. All fines must be paid in cash, in full. Students are not allowed
to enroll for subsequent semesters until all library fines are paid; they also
do not receive a diploma until all library fines are paid. Faculty must pay all
outstanding fines by June 30 of each year.
• When a book is returned, the return must be entered into the
system, and a unique return number is used to log the transaction. At that
time, the loan record is updated to show that the book has been returned.
The following attributes have been identified as critical for the
new system:
§ University book ID
§ Book publisher
§ Due date
§ Loan number
§ Checkout date
§ Borrower phone number
§ Cash account number
§ Librarian name
§ Book status (on the shelf
or checked out)
§ Type of borrower (faculty
or student)
§ Librarian college degree
§ Actual return date
§ Borrower ID
§ Borrower name
§ Book title
§ Fine receipt number
§ Amount received
§ Library name
§ Amount of fine
§ Default library where book
is shelved
§ Borrower’s fine balance
owed
§ ISBN number
§ Book return number
§ Dewey decimal number
§ Borrower address
§ Book copyright date
§ Borrower e-mail address
§ Library borrowed from
§ Librarian number
§ Account balance
§ Total number of books in a
specific library
§ Loan status (still
outstanding, or returned)
§ Author name
REQUIRED
1. Draw an REA diagram for the
library system. Remember to include cardinalities.
2. As directed by your
instructor, either create the tables on paper that would be required to
implement your REA diagram or actually build those tables in a relational DBMS
to which you have access. Only use the attributes listed, unless others are
absolutely necessary.
19.4 The XYZ Company sells tools and parts to
automotive repair shops. Shops call in orders; all orders received by noon are
delivered the same day. Between 12:00 and 1:00, the system prints out
schedules. From 1:00 to 5:00, drivers make deliveries according to the printed
schedules. Typically, each driver makes between 25 and 30 deliveries each day.
Each delivery is signed for by a repair shop manager; the portable laptop then
uses wireless communications to transmit information about the delivery back to
the XYZ Company and the information is recorded as another row in the sales
event table. The XYZ Company uses its own trucks to make local deliveries to
its customers. It wants to track information about the use of those trucks:
which employee drove which truck, to which customers did a particular truck
make deliveries, which deliveries are made on which days, what was the starting
and stopping mileage each day?
REQUIRED
a. Draw a partial REA diagram of the XYZ Company’s revenue cycle
to model these
events: Taking Customer Orders, Deliveries, and the Use of
Vehicles. Be sure to
include cardinalities.
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 for the XYZ
Company.
19.5 Assume that Stained Glass Artistry, a new shop
that specializes in making stained glass artwork, has hired you to design an
integrated database that will provide the owners with the accounting
information they need to effectively manage the business. Stained Glass
Artistry makes a wide variety of stained glass windows for sale in its store.
A unique job order is assigned to each production run, which
includes creating multiple copies of the same basic design. When raw materials
are issued to employees, the issuance is documented on a prenumbered raw
material issue form. The different kinds of glass needed for the product, and
other materials such as copper foil or lead, are issued at one time, so that
employees can efficiently produce the design.
Creating a piece of stained glass art involves several different
steps, including cutting, foiling, and soldering. The owners want to track how
much time each employee spends each day performing each of those various tasks.
The owners have developed raw material and direct labor standards
for each design they offer. They want their AIS to track actual costs and
standard costs so that they can generate reports that provide price and
quantity variance information.
The owners also have provided you with the following list of facts
that they want
stored in the database. (Note: You must
create appropriate primary keys for each table;
this is the list of other attributes.) Attributes in Standard
Glass Artistry AIS:
§ Date hired
§ Time started task
§ Time completed task
§ Style of glass (name or
description)
§ Quantity on hand
§ Color of glass
§ Quantity to be produced
§ Actual cost of design
§ Design name
§ Standard quantity of glass
use in design
§ Quantity issued
§ Standard hours to make
design
§ Standard cost of design
§ Date design produced
§ Date of birth
§ Wage rate
§ Employee name
§ Standard cost of glass
REQUIRED
a. Draw an integrated REA diagram for Stained Glass Artistry.
Include both minimum
and maximum cardinalities.
b. Create the set of relational tables required to implement your
REA diagram for Stained
Glass Artistry in a relational database.
19.6 Bernie’s Pet Store sells pet food, toys, and supplies.
Bernie, the owner, is the only person who places orders with suppliers. He is
also the only person who writes checks. Suppliers ship each order individually;
if they are out of an item, they back order it and ship it separately as soon
as it arrives. Bernie pays each supplier monthly for all purchases made the
previous month. Suppliers do not allow him to make installment payments.
Bernie has eight employees, each of whom can check in materials
received from suppliers and sell merchandise to customers. Bernie pays his
employees weekly from a separate checking account used only for payroll
purposes.
All sales are made in-store and are paid for immediately by cash,
check, or credit card.
When employees are not working the cash register or checking in
merchandise, they restock shelves and clean up the premises. Bernie does not
want to track each individual restock
or clean-up event, but does want to know how much time each employee spends
each day doing those tasks. He also wants to track how much time each employee
spends each day receiving inventory and how much time they spend working at the
cash register.
He wants to be able to write queries that would show time spent by
job task (restocking, cleaning, receiving, or sales) for each employee. It is
not practical, however, to try to measure the time spent on individual tasks
(e.g., Bernie does not want employees to track the time they start and finished
unloading a shipment from supplier X, then repeat for supplier Y; similarly, he
does not want to track how long it takes to ring up each individual customer at
the cash register). All he wants is to know how much time each day (e.g., 3.75
hours) each employee spent performing each different type of job.
REQUIRED
Draw an integrated REA diagram for Bernie’s Pet Shop. Be sure to
include both payroll processing and the ability to track how employees use
their time.
19.7 At Big Time University (BTU) students are allowed to
purchase two basketball tickets for each home game. Each ticket contains the
date of the game, and the seat information, such as section, row, and
individual seat number. Students pay for each game individually; that is,
student sporting event passes are not used at BTU. BTU deposits the proceeds from
each game into its bank.
REQUIRED
a. Prepare an REA diagram with cardinalities for the revenue cycle
for BTU’s basketball games. State any assumptions you may have to make
concerning BTU’s business policies and practices.
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.
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
1. 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.
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.
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.
2. 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.
No comments:
Post a Comment