Week 4 Assignments From The Reading

Assignments from the Reading
Week 4

Neal Hennessy

Case Analysis 13-22
1. Identify the resources, events, and agents within Martin's revenue process.
Resources: Cash, Inventory
Events: Sale of Goods/Customer Order, Shipment of Goods, Receive Payment/Cash Collections
Agents: Salesperson, Customer, Shipping Clerk, Billing Clerk
2. Develop an E-R diagram for this process.

3. With a particular DBMS in mind, design the tables for this revenue process. Note that you will need tables for each resource, event, and agent, as well as tables for each many-to-many relationship.
Inventory Table

Item Number
Item Description
Item Cost
Beginning Inventory
1001
Business Shoe
$12.13
1192
1002
Running Shoe
$9.84
1941
1003
Shoe Insert
$3.29
734
1004
Multipurpose Sock
$2.14
958

Cash Table

Invoice Number
Invoice date
Amount Paid

A101
1/3/2015
$207.44

A102
1/9/2015
$292.31

A103
1/11/2015
$81.80

A104
1/16/2015
$547.70

Sales Table

Order Number
Employee Number
Customer Number
Order Value
2100
AB21
12A
$207.44
2200
DH40
13A
$292.31
2300
AB21
12A
$81.80
2400
RN19
12B
$547.70

Shipping Table

Order Number
Destination State
Ship Date
Tracking Number
2100
WA
1/6/2015
IZE236578
2200
CA
1/11/2015
IZE603982
2300
MT
1/11/2015
IZE228011
2400
NY
1/17/2015
IZE382168

Receipt of Payment Table

Invoice Number
Invoice Date
Date Paid

A101
1/3/2015
2/1/2015

A102
1/9/2015
2/7/2015

A103
1/11/2015
2/2/2015

A104
1/16/2015
2/9/2015

Salesperson Table

Employee Number
Employee Name
Contact Number
DOH
AB21
Aaron Brown
509-123-4567
3/4/1997
RN19
Rochelle Nelson
509-234-5678
6/21/2011
BK73
Brian King
509-345-6789
11/14/2010
DH40
David Harper
509-456-7890
7/9/2013

Customer Table

Customer Number
Customer Name
State
Credit Limit
12A
Foot World
509-567-8901
$5,000
13A
Runners United
509-678-9012
$7,500
14A
Footwear Depot
509-789-0123
$3,000
12B
Feet 4 All
509-890-1234
$8,000

Shipping Clerk Table

Employee Number
Employee Name
Contact Number
DOH