dolution.
Directions: Using the information provided, complete the following.
1) Identify the resources, events, and agents involved in The Kayak Roll’s expenditure cycle and the relationships between those entities. Using that information, create an REA diagram using the “crow’s feet” notation style for cardinalities. You may draw the REA diagram on paper or use Microsoft Word or Excel to create the diagram, but please submit your work in pdf format (scan or print as pdf).
2) Based on the REA diagram, determine the number of tables necessary to create a related database in normalized form. Identify that number.
# Tables: _______ (10?) *this is my guess in ()
3) Count the # of each type of relationship
1:1 _______ (0?)
1:M _______ (10?)
M:N _______ (3?)
4) Provide a separate listing of each table along with associated attributes, including the primary key and foreign key(s), if any. You may choose to (1) create the listing on paper and scan and attach a copy, or (2) use Microsoft Word or Excel to create the listings and attach the associated file.
***Here is an example of a table (Order Inventory) you will need with the associated attributes. For every table you’ve identified, you should have a listing of associated attributes like below.
| Order Inventory |
| *PO Number |
| Order_Date |
| # Employee_ID |
| # Supplier_ID |
| Primary Key |
* |
| Foreign Key |
# |
_Case Information:
Expenditure Cycle
Ordering
-The company does not use purchase requisitions, but it does use purchase orders.
-An order from a supplier may be for multiple inventory items.
-Any employee can create a purchase order, but for each purchase order, only one employee is involved.
Receiving
-Each order received from a supplier may be either a fulfilled single order, a partial order, or a combination of orders.
-The company, hoping to expand and wanting proper internal controls, wants to limit the order information seen by a receiving employee. Thus, an order number (or numbers) should exist before receiving inventory, but the receiving employee would be required to enter Vendor and Inventory information without the database retrieving the information from the order. In other words, although receiving is related to ordering, separate resource and agent relationships should exist.
-Each employee may perform receiving duties, but just one employee receives each package (i.e., one employee prepares and signs off on the receiving report).
Cash Disbursements
-The Kayak Roll sometimes pays suppliers separately for each order received. Other times, however, a check is written to cover multiple purchase invoices. Further, sometimes amounts due are paid in installments. The company may have unpaid invoices at any point in time.
-A single employee signs each check.
-Each check is written from a single cash/bank account.
Suppliers (Vendors)
-The company tracks a variety of data on each of its suppliers/vendors. Data for three vendors are listed below
Supplier 1
Supplier ID: 101
Name: Jack’s Kayaks R’ Us
Street Address: 82 W. Main Street
City/State/Zip: Clovis, New Mexico 88101
Contact: Mike J.
Phone: (575) 544-7686
Beg. Balance: $220
Supplier 2
Supplier ID: 102
Name: Longview Lifejackets
Street Address: 76 E. Grapevine
City/State/Zip: Longview, Texas 75601
Contact: Jerry Bradley
Phone: (430) 903-4482
Beg. Balance: $130
Supplier 3
Supplier ID: 103
Name: Maureen’s Paddle Hut
Street Address: 38 Las Vegas Blvd.
City/State/Zip: Las Vegas, Nevada 89101
Contact Maureen Miser
Phone: (702) 882-1563
Beg. Balance: $140
***Here is my attempt at 4)
|
Cash
|
|
*Account Number
|
|
Name
|
|
Balance
|
|
Inventory-Order Inventory
|
|
#Purchase Order Number
|
|
#Product number
|
|
Quantity ordered
|
|
Unit cost
|
|
Inventory-Receive Inventory
|
|
#Receiving report number
|
|
#Product number
|
|
Quantity received
|
|
Product conditions
|
