\fMaster Electrician Trade Association (META) The Master Electrician Trade Association (META) is an organization that internationally markets and sells training books, prociency tools and gadgets designed to be used by electricians. An electrician licensed by a governmental entity (in the United States this licensing is through the state of domicile) is entitled to membership; however, the individual must join the organization to receive the benets of membership. Hearing that you have credentials in database design and implementation, META approaches you to develop and implement an accounting information system for their retail functions. META uses several different kinds of advertising, including radio, mail, Web site ads, and trade magazines to attract new members. META would like to keep track of each type of advertising campaign and the employee who is in charge of each campaign. Advertisements contain a promotional code members can use to receive a special discount for their rst order after joining the club. META records the promotional codes on orders to gauge the effectiveness of their marketing and evaluate the assigned marketing employee. META estimates that about 35% of their marketing activities are ultimately referenced on a sales order. An employee can only be assigned to one advertising campaign, and there is exactly one employee per campaign. There is no cost to join the club, but you do have to be a member to place an order. As soon as members join, they are assigned a credit rating type. Typically, the amount of credit limit extended is based on the assigned credit rating type, which is based on the length of membership. The initial credit limit is low, but is increased as time passes or timely payments on account occur. META only keeps one credit rating on a member and does not attempt to keep a record of changes to credit ratings. Members also receive promotional mailings after joining with discount opportunities. Members place orders online through META's Web site, over the phone by calling an 800 number, or by mail. Online orders, about 90% of all orders, are entered into META's computer system automatically. META estimates that approximately 45% of their orders have promotional codes with discounts. Telephone and mail orders are entered into the computer system by META's sales representatives. All sales have orders; no bricks- and-mortar store exists for META. Customers indicate on the sales order how quickly they want their order delivered (slow, medium, or fast delivery). Checking for customer credit limit compliance is done automatically by the computer system prior to entering the sales order into the system. Members are notied immediately of order acceptance (rejection). Once the orders are entered, a sales order notication is sent to the shipping department. A shipping clerk retrieves the ordered items from inventory and then packs and ships each order. The shipping clerk selects the shipper for the sale at the time the order is packed. META uses three different shippers depending on the speed of delivery the members selects at the time of the order [e. g. USPS (United States Postal Service) 7-day, UPS(United Parcel Service)5day, and FedEx(Federal Express)Overnight]. Delivery charges are a at rate for the shipment type selected. To reduce member confusion, META only ships complete orders and does not combine orders together; if an item is temporarily out-of-stock, it noties the members the entire shipment will be delayed. Once an order has been shipped, the computer system sends an invoice to the member and sends a notication to accounts receivable. Members who do not pay within 30 days are charged 6% interest on their outstanding balance. Members can check online at any time to determine their outstanding balance. This information is presented ondemand by the members and no record is kept of these requests in the AIS. When making payments, members may pay part or all of their remaining balance. Sometimes a member pays for more than one invoice at a time. A clerk in the cash receipts department processes all cash receipts and makes deposits into one of the company's bank accounts. Almost all cash receipts come from members, but occasionally META receives cash om other sources. When a particular inventory item meets its reorder point, META's computer system automatically generates a purchase order and sends the purchase order to the appropriate supplier; the purchase order will reect the current unit cost for the suppler selected. If multiple items reach their reorder point at the same time, the system will favor suppliers that carry more of the items needed (i.e. if one supplier carries I needed item and second supplier carries 2 or more of the items needed, the system will place the order with the second supplier.) The actual current unit cost may differ from the standard unit cost as the standard unit cost reects the average cost across all suppliers of a particular item. There are at least two, but often more, suppliers for each item in inventory to insure availability; but of course each order is associated with exactly one supplier. Suppliers ll each order individually; sometimes, however, they are out of stock of an item. In such cases, they immediately ship what is in stock and then make a second delivery for items that they have to back order. Once an inventory order is received and the receiving clerk veries a valid purchase order, the receipt is counted and sent to inventory. Occasionally, the receiving clerk must return an entire order or individual items within an order because the shipment arrives damaged (inclement weather conditions during shipping might account for this) or the wrong item was shipped. The receiving clerk is able to determine the need for a return immediately upon receipt; therefore, if a purchase is returned it is only associated with one return. When a purchase return is made, META receives a credit memo om the supplier for the returned items. Since META has well established relationships with their suppliers and will place orders with them in the future, no cash is ever received for a purchase return. The cash disbursements clerk pays all supplier invoices individually as they become due after verication of receipt to take advantage of any purchase discounts. META also pays employees every two weeks and periodically writes checks to pay off their bank loan. When you discussed how to handle the disbursements with META, they wanted to separately record purchasing cash disbursements (i.e. the cash disbursements for employees and bank loans are not recorded in this cash disbursement table). Note: This database is well-designed; thus all agents, resources, and typication (category) tables etc. are added prior to recording events involving them unless otherwise noted in the narrative. For simplication purposes, the zip code table is not included. Required 1. Complete the REA diagram: Using the partially completed REA model, identify the remaining entities and cardinalities. There is an REA Data Modeling Quiz #2 , Part A in Canvas where you are to record your REA entities and the cardinalities. After you complete this portion of the quiz, you will have access to the correct responses. You will use the correct responses to complete requirement #2 below. You will need to do Part A well before the assignment is due in order to complete Part B. 2. In a Microsoft Word or Excel le, use the list of attributes provided below to convert your REA model diagram into a set of relational database tables using the format below. You may use the attribute numbers in place of the full attribute name. Indicate the primary key(s), foreign keys(s), and other attributes for each table. Submit your conversion chart in Canvas using the REA Data Modeling Quiz #2 Part B link BY THE DUE DAT E/TIME. Table Name Primary Key(s) Other Attributes Foreign Key(s) 1. Unit cost of item ordered from supplier 4. Actual item order price (with discounts applied) 7. Actual item selling price (with discounts applied) 2. Date of beginning of advertising campaign 5. Date of Cash Disbursement 8. Description of credit code 3. Purchase Return Number 6. Quantity ordered by members 9. Quantity ordered from supplier 10. Amount of cash receipt applied to an invoice 11. Description of advertising campaign 12. Quantity shipped on an order 13. Bank Name 14. Cash Disbursement Amount 15. Quantity received on a Purchase 16. Cash Account number 17. Discount allowed for an advertising campaign 18. Quantity of an item returned 19. Bank Account Number 20. Employee address 21. Reason for return of an item 22. Cash account type 25. Cash Disbursement Number 23. Employee Date hired 26. Employee rst name 24. Receiving report number 27. Return Date 28. Cash Receit Number 31. Cash Receipt Total Amount 29. Emlo ee number 32. Invoice number 30. Sales Order date 33. Sales Order number 34. Flat Charge applicable to shipper 35. Invoice Date 36. Sales Order Total Amount 37. Member rst name 38. Receiving Report Date 39. Shipper ID 40. Credit rating code 41. Item description 42. Shipment type selected on sales order 43. Credit limit for a code 44. Item List price 45. Std. unit cost of inventory item 46. Employee last name 49. Member address 47. Item Number 50. Advertising campaign code 48. Member number 51. Supplier name 52. Member last name 53. Purchase Order date 54. Supplier number 55. Member Check number 56. Purchase order number 57. Supplier Invoice Number for a purchase receipt Entity #2 A 11 B Entity #3 A 12 Purchase Return B B B ' Entity #0. B A I A A B Entity #9 27 Entity #11 Cash E B B B A A 20 1 [J Entity #5 B A 21 A 1 3 Entity #12 B B Entity #6 A 14 B Entity #7 A