978-0133428537 Chapter 19 Solution Manual Part 1

Unlock access to all the studying documents.

View Full Document

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.