Unlock document.
This document is partially blurred.
Unlock all pages and 1 million more documents.
Get Access
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
©2018 Pearson Education, Inc.
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
©2018 Pearson Education, Inc.
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
©2018 Pearson Education, Inc.
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
©2018 Pearson Education, Inc.
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
©2018 Pearson Education, Inc.
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
©2018 Pearson Education, Inc.
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
©2018 Pearson Education, Inc.
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
©2018 Pearson Education, Inc.
