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