978-0134474021 Chapter 19 Solutions Manual Part 2

Unlock access to all the studying documents.

View Full Document

REQUIRED

a. Draw an REA diagram for the library system. Remember to include cardinalities.

Accounting Information Systems

Explanation of cardinalities:

1. One key to the problem is understanding that the Loan Book event represents the

2. Another important fact is realizing that books have multiple authors.

3. One final important fact involves recognizing the distinction between physical books and

4. The cardinality from Receive Fines to Loan Book is (1,N) because a loan has to occur

19-2

Accounting Information Systems

b. 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.

Problem 19-3, part b. Table solution

Table Primary Key Other Attributes ( foreign keys in

italics)

Library Library name * Number of books

Books University Book ID Book status, ISBN#, default library

shelved at

Book Title ISBN# Publisher, copyright date, Dewey

Decimal number

owed, phone number

Fine-Loan Fine receipt#

Loan number

Amount of fine

* Library name can be the primary key because it is created by the library system and, therefore,

guaranteed to be unique for each library.

19-3

Accounting Information Systems

19.4 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

Standard cost of glass

19-4

Accounting Information Systems

REQUIRED

a. Draw an integrated REA diagram for Stained Glass Artistry. Include both minimum

a) Each row in the Bill of Materials table represents the standards for using one specific

b) Each row in the Labor Standards table represents the standards for making a

c) Jobs consist of making one or more copies of a specific design. Therefore, each Work

19-5

Accounting Information Systems

d) All raw materials are issued at one time; thus, the relationship between Raw Materials

e) Sometimes there may be a need to obtain additional raw materials, due to breakage.

f) Each specific job operation is linked to one, and only one, Work in Process, but any

g) The Employee Services entity is an abstract entity that represents the time acquired

b. Create the set of relational tables required to implement your REA diagram for Stained

Glass Artistry in a relational database.

Table Name Primary Key(s)

Other Attributes (foreign keys in italics,

others in normal font)

Raw Materials Raw Material

number

Style of glass, beginning quantity on

hand, color of glass, standard cost of glass

Employee Employee number Name, date hired, wage rate, date of birth

Employee Services Category number

Issue Raw Materials

Raw Materials Issue

number

19-6

Accounting Information Systems

19.5 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

Accounting Information Systems

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.

Table Name Primary Key Other Fields(foreign keys in italics,

others in normal font)

Take Order Order Number Customer Number, Employee Number,

Amount

Order-Inventory

Delivery-Inventory Delivery Number, Item Number Quantity

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.

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.

19-8

Accounting Information Systems

REQUIRED

Draw an integrated REA diagram for Bernie’s Pet Shop. Be sure to include both payroll

a) Checks may be written to either suppliers or to employees. One table can be used for

both types of checks. The primary key of that table would be a concatenated key

b) Bernie pays employees weekly. Each day an employee works a new row is created in the

c) The Employee Time resource represents the time acquired from various classes of

employees. Since any one employee only falls into one category (i.e., full-time,

19-9

Accounting Information Systems

d) The Use Employee time event is used to track how employees spend their time. A row

would be created for each block of time an employee spent performing a particular type

of task. An attribute in this table would be a text field describing what an employee did

during that block of time. For example, if the employee restocked shelves from 8:00 am

to 11:00 am, there would be one row in the Use Employee Time table for that block of

e) The Employee Time resource is shown in dashed lines because it is not likely to be

implemented in a table.

19-10