Unlock document.
This document is partially blurred.
Unlock all pages and 1 million more documents.
Get Access
CHAPTER 19
SPECIAL TOPICS IN REA MODELING
SUGGESTED ANSWERS TO DISCUSSION QUESTIONS
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?
Relationships between agents and resources can be modeled for two reasons.
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)?
There is very little difference in modeling an event where a physical resource is sold as
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?
In terms of the REA model, the two types of transactions are handled much the same. In
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?
SUGGESTED ANSWERS TO PROBLEMS
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.
Table Primary Key Other Attributes ( foreign keys in italics)
Services Service number Name, standard cost, list price, standard time to
perform
Receive Cash Receipt number Date, amount, customer number, employee
number, GLAccount number
Pets Pet number Pet name, breed, color, birth date, customer
number
Drugs-Disburs
e Cash
Check Number
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
a. Draw an REA diagram for the library system. Remember to include cardinalities.
a. REA diagram solution.
