Master Electrician Trade Association (META)

The Master Electrician Trade Association (META) is an organization that internationally markets and sells training books, proficiency 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 benefits 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 first 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 notified immediately of order acceptance (rejection). Once the orders are entered, a sales order notification 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)5-day, and FedEx(Federal Express)Overnight]. Delivery charges are a flat 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 notifies 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 notification to accounts receivable.

Members who do not pay within 30 days are charged 6% interest on their outstanding balance. Members can check on-line at any time to determine their outstanding balance. This information is presented on-demand 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 from 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 reflect 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 1 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 reflects 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 fill 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 verifies 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 from 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 verification 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 typification (category) tables etc. are added prior to recording events involving them unless otherwise noted in the narrative. For simplification 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 a REA Data Modeling Quiz #2 - Part A quiz in Canvas where you are to record your REA entities and the cardinalities. Once you've submitted Part A, the Part B link will open. Follow the instructions in Part B to complete requirement #2 below. You will need to do Part A well before the assignment is due in order to complete Part B. Both Parts A and B of the quiz should be submitted by the due date/time.

2. Use Microsoft Excel or Word to build a conversion chart for META. Indicate the primary key(s), foreign keys(s), and other attributes for each table.