System Information for the Expenditure Cycle

Yamato orders and receives Chocolate as well as supplies and other items. The order process begins after the buyer is informed that more stock or supplies are needed and they issue a purchase order. Each order is assigned a sequential unique number $($purchase order #$).$ We want to include which buyer was responsible for the order. The order can be for more than one item of finished goods and is input into the system at the time of the order. Yamato uses the actual negotiated price for purchases and standard costs to calculate purchase variances.

The receiving employee receives the items for each order in the receiving department. For orders, partial deliveries are accepted, but only one order will be delivered at a time. Each delivery is assigned a sequential unique number, which will be used as the receiving number. We also want to include which receiving clerk was responsible for the receipt. The receiving clerk enters the information into the system when the goods are received.

Most orders are required to be paid within $30$ days of receipt of the order. However, for some special orders partial payments are allowed $(50\%$ within $30$ days and the remainder in $60$ days$).$ Additionally, we are allowed to pay for more than one delivery on a payment. The cashier prepares a check and a remittance advice showing which invoices are being paid $($the RA includes our check # and each invoice number and amount$).$ Cash disbursements are processed by a cashier, and are written with checks from the company$$s main bank account. Employees and suppliers are added to the database before any transaction with them takes place.

Yamato wants to use this system to produce financial information as well as management information about purchases. Some of this information will be prepared by product type.

Requirements for Assignment $3$

$1)$ Prepare the REA model $($HINTS: includes $1$ type and $1$ commitment to purchase. There are $10$ entities and $3$ many$-$to$-$many relationships$).$

$2)$ Determine the appropriate cardinalities.

$3)$ Assign the attributes to tables. $(13$ tables$-10$ entities, $3$ junction tables for the many$-$to$-$many$)$

$4)$ Be sure to include the primary key $($PK$),$ the other attributes

and the foreign keys $($FK$).$

Required Attributes

Receiving $($sequential$)$ number Actual purchase price

Purchase order number Item type #

Supplier Name Check number

Cash Account Name Bank

Beginning inventory on hand Unit budgeted sales price

Item number Supplier Number

Quantity Ordered Supplier Telephone Number

Receipt Date $($Receiving Date$)$ Unit budgeted cost

Quantity received Item description

Order Date Expected delivery date

Check date Amount paid for each invoice

Supplier contact name Buyer$$s Telephone #

Cash Account # Item type description

Buyer$$s Employee# Cahier employee #

Buyer$$s Name $($Note $1)$ Cashier Name $($Note $1)$

Receiving person$$s employee # Receiving person$$s name $($Note $1)$

Supplier Invoice # Supplier address $($Note $1)$

Note $1$: Usually we would separate first and last name for people and street, city, state and zip for addresses. You can use one attribute for names and addresses.Base Template with Commitment $($need to add types and Junction Tables$)$

Minimum $-$ does an instance of A have to a

relationship with any instances of $B$

may or may not participate

Maxium $-$ can an instance of A particpate with olny one