Group Project - Kim's Flower Inc. Purchasing (Expenditure) Cycle

Background:

Kim's Flower Inc. is operated by Kim Burke, the Chief Executive Officer (CEO), and her associated employees. She has decided to develop a database system to track her purchases, floral inventory, accounts payable, and cash payments. However, she does not have time to do the development herself. Therefore, she has hired you and your team members to design and implement the system (i.e., flower purchasing system) for her. She describes the requirements of her system as follows: 

Whenever flowers are needed, a purchasing clerk prepares a purchase order by completing a purchase order form. The purchase order form includes purchase order number, order date, item number, quantity ordered, order price, employee number (preparer), employee number (authorizer), and vendor number. To accommodate all of floral needs, the purchasing clerk may need to prepare several purchase orders, as Kim's Flower Inc. orders flowers from several vendors. Also, a purchase order can be made for several types of flowers from a single vendor. When the purchase order form is prepared, the purchasing clerk forwards the form to the CEO, Kim Burke, who approves the order. The approved purchase order then sends to a vendor.

Then the vendor will ship the flowers in the order. When the order is received, a warehouse department clerk inspects the quality of flowers and verifies the quantity of flowers based on the purchase order and the packing slip included in the shipment. The clerk then prepares a receiving report, forwards the report to an accounting payable clerk for payment process, and updates inventory records. The receiving report includes receipt number, receipt date, quantity received, item condition, purchase order number, employee number (receiver), and vendor number. Also, an invoice is delivered to the accounts payable clerk from the vendor.

At the end of each month, the accounts payable clerk prepare an purchase order payment form for all amounts charged through the end of the month by verifying invoices, purchase orders, and receiving reports. The purchase order payment form includes payment number, payment date, payment amount, employee number (preparer), employee number (authorizer), receipt number, vendor number, and account number. The form is then forwarded to the CEO. When the CEO authorizes the payment form, the accounts payable clerk prepares a check and mails it to the vendor. Finally, a monthly summary report of purchase orders and payments is prepared for each month and forwarded to the CEO.

(Note: If the business rules are not clear, use typical cardinalities.)

Tasks:

1. Prepare an REA diagram describing Kim's Flower Inc.'s database. You may repeat a box for an individual entity if necessary to make your drawing neat. Kim would like for you to consider that, eventually, as new stores are opened, she will have store managers in each store that will perform the tasks she now performs. As a result, the system should be planned to always store information about the employee associated with transactions, whether that employee enters information, performs a task (such as delivery) or formally authorizes the transaction.

Hints:

(1) Each item on a floral receipt may be from different purchase orders. Different items on a single purchase order may be received at different times using different receipts.

(2) Each check may pay for several receipts. However, a single receipt will always be paid for using the same check.

2. Prepare the context diagram and the level 0 DFD.

1) Context Diagram

Hint:  Source/Destination - CEO, Purchasing Clerk, Vendor, Warehouse Clerk

2) Level-0 DFD

Hints: Main functions - 1) Receive Purchase Order, 2) Receive Order, 3) Make Payment, and 4) Generate Report

Data Store - Purchase Order, Flower Inventory, Vendor, Cash

3. For the list of attributes for each entity, complete the table below. Use underline to indicate the identifier of each entity.