978-0133428537 Chapter 19 Solution Manual Part 2

subject Type Homework Help
subject Pages 9
subject Words 2436
subject Authors Marshall B. Romney, Paul J. Steinbart

Unlock document.

This document is partially blurred.
Unlock all pages and 1 million more documents.
Get Access
page-pf1
Explanation of cardinalities:
1. One key to the problem is understanding that the Loan Book event represents the
checking out of a single book. As stated in the problem, if a borrower checks out 5 books,
a. 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
page-pf2
processed
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.
page-pf3
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
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.
page-pf5
Explanation of cardinalities:
a) Each row in the Bill of Materials table represents the standards for using one specific
raw material to produce one specific finished good design. Therefore, every row in
the Bill of Materials table is linked to one and only one row in the finished goods
page-pf6
from various classes of employees. It will be discussed in chapter 14. For now, just
explain that each row represents all the time the company acquires from a specific
class of employees (artisans, clerks, management, etc.)
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
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.
page-pf8
Explanation of cardinalities in Bernie’s pet store:
a) Checks may be written to either suppliers or to employees. One table can be used for
page-pf9
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
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.
page-pfa

Trusted by Thousands of
Students

Here are what students say about us.

Copyright ©2022 All rights reserved. | CoursePaper is not sponsored or endorsed by any college or university.