Kim Flowers Procurement Cycle The third stage of the project requires students to implement their logical data model in MS-Access 2013. Read carefully the instructions below before you start working on your project. 1. Create the tables from “project stage 2” in Microsoft Access. In each table, define the primary key and foreign keys. For each attribute, define the data type, provide a field description, and define the field properties. For each table, there should be at least one validation rule and a corresponding validation text. I would also like to see some default value entrees and captions in each table. Make sure that you follow the naming notation (tbl rpt) as it is covered in the Access book. 2. Populate your tables with fictitious data entries; you should sufficiently populate your tables so that you can demonstrate your design (about 20 rows for events and 10 for agents and resources). 3. Establish relationships between all the tables you created (based on your design). Make sure that you enforce the referential integrity in all your relationships. 4. Create some queries and explain why these queries are important for managers/decision makers. You should have at least four multiple-table queries in your design, and three other queries. At least one of your queries should perform calculation using “Expressions” (as described in chapter 4 of the access book). Your queries will be evaluated based on the above requirements. 5. Create at least three forms; one of which should be a multiple table form. Describe the function of the form, who would use it and its purpose. 6. Create at least three reports for management/decision makers; describe what benefits these reports provide for decision makers, and how often they will be used. One of your reports should display a summary value, and a calculated field using the expression builder (as described in chapter 5). 7. Document the existing internal control structure for your company (based on your current design), and recommend additional desirable controls. ( Control examples are provided in the “Sample Controls” table in in chapter 10) 8. Describe any additional (for extra credit) work that you did. 9. Summarize what benefits did your company gain from adopting the database approach (for that purpose you can assume that prior to your implementation, your company had a computerized/manual accounting system) Extra credit will be assessed for any additional features that were not required. The overall grade for the project will be given based on the compliance with the above instruction, and the overall effort given to the design and implementation of the database. The project should be submitted via email. The subject of your email should follow the required convention. 1 2 Floral Inv (ItemNo, Description, QOH, ListPrice) PurchaseOrders (PONo, Date, EmployeeNo* (preparer), EmployeeNo2* (Authorizer), VendorNo*) PurchaseOrderDetail (PONo*, ItemNo*, Qty, Price) Employee (EmployeeNo EmployeeName, DateStarted, Address, Phone#, Category, Grade Salary, commission) The above table contains all the internal agent (i.e employees of the company) • APClerk • Kim • FloralDesigner • PurchaseAgent Vendor (VendorNO, VendorName, Address, Phone#, Balance, ) CashAccounts (AccountNo, Bank, Type, DateStarted, Balance) Floral Receipt ( ReceiptNo, Date, EmployeeNo* (receiver), VendorNo*) ReceiptDetail/ReceiptsApplied – This table takes care of the M:M relationships between Receipts and POs and Receipts and Items; because there is relationship between items and POs, only one table is needed. If there had been no relationship between items and POs, two separate tables would have been appropriate ReceiptFlowersPO (ReceiptNo*, ItemNo*, PONo*, Qty, Condition) Payment (VoucherNO, EmployeeNo* (preparer), EmployeeNo2* (Authorizer), VendorNo *, AccountNo*,Date, Amount) ##CashPaymentsApplied (VoucherNo*, ReceiptNO*) FlowerDisposal (DisposalNO, EmployeeNo* (authorizer) Date) FlowerDisplosalDetail ( DisposalNo*, ItemNo*, QtyDisposed, Reason) 3 1 Running head: KIM’S FLOWER EXPENDITURE CYCLE Kim’s Flower Expenditure Cycle Name Yasser bakraa Saud alarifi Mohammed alsumali Tutor Institution Date 2 KIM’S FLOWER EXPENDITURE CYCLE 2NF Inventory (Inventory#, Description, ListPrice, BeginningQOH) (1 NF)(2NF) Purchase Order (PurchaseOrder#, Date, Amount, Vendor#*, Employee#*) (1 NF)(2NF) Floral Receipt (Receipt#, Date, Amount, Vendor#*, Employee#*) 2 NF Employee (Employee#, EmployeeName, Phone#, DateStarted, Position) 2 NF Vendor (Vendor#, VendorName, Address, Phone#, AccountBalance) 2 NF Cash (Check#, AccountType, BalanceBeginningCurrentPeriod) (1 NF)(2NF) Cash Payments (Payment#, Date, Amount, Supplier#*, Employee#*, Check#*) (1 NF)(2NF) Inventory-Purchase Order (Inventory#*, PurchaseOrder#*, QuantityOrdered) (1 NF)(2NF) Inventory-Floral Receipts (Inventory#*, Receipt#*, QuantityReceived) (1 NF)(2NF) Purchase Order-Floral Receipts (PurchaseOrder#*, Receipt#*) (1 NF)(2NF) Floral Receipts-Cash Payments (Receipt#*, Payment#*, AmountAppliedtoInvoice) 3 KIM’S FLOWER EXPENDITURE CYCLE REA DIAGRAM OF KIM’S FLOWERS EXPENDITURE CYCLE I. IDENTIFY EVENTS “The first stage is to classify all important events or transactions using the simple ‘give – to - get the exchange.” i. Enter Purchase Order ii. Prints buying orders & send to vendors recorded as “Floral Receipts.” Pay for end month amounts charged, call. “Cash Payments” (Cheggy Study, 2018). II. IDENTIFY AGENTS AND RESOURCES “Then, we have to identify the properties involved with these events.” We determined that there are more than one resources entangled with these events: i. Flowers ii. Cash “Next, come up with the agents or parties needed to make these events occur.” (Note: There are usually not less than two agents involved in each event) The following are the agents included in the Purchase Order event: i. Buying Agent ii. Kim iii. Vendor Floral Receipts event includes the following agents: 4 KIM’S FLOWER EXPENDITURE CYCLE a. Vendor b. Floral Designer The following are the agents included in the Cash Payments events: i. Accounts Payable Clerk ii. Kim iii. Vendor III. DEFINE AND SHOW CARDINALITIES OF RELATIONSHIPS 1. Identify event cardinality – agent relationships. 2. Identify event cardinality – resource relationships. 3. Identify event cardinality – event relationships. Step 1: Define the cardinalities of event agent relationships When conversing with Kim, we comprehend that an acquiring specialist arranges the blossoms from the sellers. One and just a single acquiring operator can submit a particular request in the PC framework. In any case, they can arrange more than one kind of blossom with one seller without a moment's delay. Kim is the person who supports these requests. Since they are not difficult to track, one botanical fashioner records the receipt of blooms from a merchant on a different screen in her bookkeeping framework. Just a single A/P Clerk makes the checks for sellers, and just Kim approves them and sends them out. In this manner, the most extreme cardinality on the operator side of the connections among occasions and inward specialists is dependably 1 (point, 2018). A representative should dependably take part in each much whether it be putting in a request, accepting it, or dispensing checks. In this manner, the base cardinality on the specialist side of the connections among occasions and inside operators is one also (Cheggy Study, 2018). 5 KIM’S FLOWER EXPENDITURE CYCLE Buy orders, flower receipts, and money installments all include merchants. Every occasion must be connected to a specific merchant: A buy arrange must recognize and be sent to a seller. Every occasion must be connected to one merchant because each request is set with a particular seller, every conveyance originates from a particular seller, and every installment is made to a particular seller. In this way, the base and most extreme cardinalities on the specialist side of the connections among occasions and outside operators is 1 (point, 2018). In the PC framework, Kim realizes that while data about representatives and sellers can be put away inconclusively, data about occasions are put away for a monetary cycle. Accordingly, toward the start of the period, no inside or outer operators would be connected to any action. Thus, the base cardinality on the occasion side of the event - agent relationship is 0. It is likewise conceivable that an occasion can't happen all the while between all outer and inside specialists, additionally making the cardinality 0. Be that as it may, operators may take an interest in an occasion on different occasions. For instance, the acquiring specialist can put in various requests in the PC and Kim can approve different requests. Sellers can likewise get numerous buy requests and money installments. While the A/P Clerk can create numerous checks and Kim can sign and convey various checks. Subsequently, the most extreme cardinality on the even side of the occasion -agent relationship is many (Cheggy Study, 2018). Stage 2: Identify cardinalities of occasion -resource relationships For Kim's Flower Shop, each buying request and botanical receipt must include something like one blossom stock thing. However, it can likewise have more than one stock thing. Subsequently, on the stock side of the stock -event relationship, the base cardinality is 1, and the most extreme cardinality is many (point, 2018). 6 KIM’S FLOWER EXPENDITURE CYCLE Since stock data is put away inconclusively and occasion data is put away for the financial period, there might be where no stock being requested or gotten. Be that as it may, during the year a specific stock thing might be requested or gotten on numerous occasions. Subsequently, the base cardinality is 0, and the greatest cardinality is numerous on the occasion side of the considerable number of connections between the blossom stock and the occasions associated with it. While there can be a wide range of general record money accounts that an installment originate from, every particular installment should just originate from one particular record. In this manner, Kim's organization will have a base and a most extreme cardinality of one on the Cash side of the Cash--Cash Payment relationship. Since money installments is an occasion, the data on it is held for one financial cycle. Be that as it may, one money record can have many money installments from it. In this way on the Cash Payment side of the Cash - Cash Payment relationship has a base cardinality of 0 and the greatest cardinality of many. Stage 3: Identify cardinalities of event - event connections In Kim's Flower Shop, arrange to happen before they are conveyed, and a few requests can incorporate things that are out of stock. This way, a portion of the blooms may precede those that are out of stock come. In this way on the Floral Receipt side of the Purchase (Cheggy Study, 2018) Order - Floral Receipt relationship, the base cardinality is 0, and the most extreme cardinality is many. It is Kim’s business rehearse that everything on a botanical receipt might be from the other buying arrangement. Be that as it may, each bloom stock got must originate from a buy arrange. Accordingly, the base cardinality on the Purchase Order side of the Purchase Order Floral (Cheggy Study, 2018). Receipt relationship is zero, and the most extreme cardinality is many. It is feasible for Kim's organization to get stock and not pay for it immediately. Likewise, a separate receipt will 7 KIM’S FLOWER EXPENDITURE CYCLE dependably be paid for utilizing a similar check. In this way in the event- resource connection between Floral Receipts and Cash Payment, on the Cash Payment side, the base cardinality is 0, and the greatest cardinality is 1. Kim's organization permits that each check may pay for a few receipts. Each money installment must have a botanical receipt to run with it. Subsequently, on the Floral Receipt t side of the event - event relationship, the base cardinality is 1, and the most extreme cardinality is many (Cheggy Study, 2018). 8 KIM’S FLOWER EXPENDITURE CYCLE Reference Cheggy Study. (2018). REA Diagram. Retrieved from Cheggy Study: https://www.chegg.com/homework-help/questions-and-answers/q-prepare-rea-diagrambackground-kim-s-flower-inc-operated-kim-burke-chief-executive-offic-q19771112 point, T. (2018). Database - First Normal Form (1NF). Retrieved from Tutorials point: https://www.tutorialspoint.com/sql/first-normal-form.htm