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. In addition to the marketing function the organization also monitors and lobbies legislation that could affect either the licensing of electricians or the practice of the profession.

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. 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. 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 limit by one of META employees. Typically, the amount of credit extended 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 limits. Members also receive promotional mailings after joining with discount opportunities.

Members of the travel club place orders online through METAs Web site, over the phone by calling an 800 number, or by mail. Online orders, about 90% of all orders, are entered into the clubs 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 the clubs 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). 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 [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 companys 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, METAs computer system 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 supplier that carry more of the items needed (i.e. if one supplier carries 1 needed item and second supplier caries 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 and a return is associated with exactly one purchase. When a purchase return is made, META receives a credit memo from the vendor 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 vendor 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 bank loan disbursements (i.e. the cash disbursements for employees and purchases are recorded in this cash disbursement table). Approximately 85% of the cash disbursements are payroll related.

Note: This database is well-designed; thus all agents, resources, typification (category) and reference are added prior to recording events involving them unless otherwise noted in the narrative.

Required

Part 1 - Using the narrative and the data items provided below, complete the UML diagram provided below (classes and multiplicities). Enter your answers in the Practice Quiz posted in Canvas.

Part 2 Using the CORRECT responses from #1 above, design a minimal relational database for META. By minimal, it is meant that you will minimize the number of tables in the database and will concurrently minimizing the number of null values in the entities. Use the following format for the DB design that we practiced in class (see below):

Table Name	Primary Key(s)	Attributes	Foreign Key(s)

Please do not add attributes to this list. Some data items on this list may be used more than once in building the UML model and the relational database (e.g.. primary keys used as foreign keys).

Attribute List:

Actual unit cost of item ordered from supplier	Date of beginning of marketing activity	Purchase Return Number
Actual item order price (with discounts applied)	Date of Cash Disbursement	Quantity ordered by members
Actual item selling price (with discounts applied)	Description of credit code	Quantity ordered from vendor
Amount of cash receipt applied to an invoice	Description of marketing activity	Quantity shipped on an order
Bank of cash Account	Disbursement Check Amount	Quantity received on a PO
Cash Account number	Discount allowed for a marketing activity	Quantity of an item returned
Cash Account Bank ID Number	Employee address (note 1)	Reason for return of an item
Cash account type	Employee Date hired	Receiving report number
Cash Disbursement Number	Employee name	Return Date
Cash Receipt Number	Employee number	Sales Order date
Cash Receipt Total Amount	Invoice number	Sales Order number
Flat Charge applicable to shipper	Invoice Total Amount	Sales Order Total Amount
City	Invoice Date	Shipper ID
Credit code	Receiving Report Date	Shipment type selected on sales order
Credit limit for a code	Item description	Std. unit cost of inventory item
	Item List price	State
Member address (note 1)	Item Number	Supplier name
Member name	Promotion code for a marketing activity	Supplier number
Members Check number	Purchase Order date	Supplier Invoice Number for a purchase receipt
Members number	Purchase order number	Zipcode

Note1: it is assumed that the word address implies a complete mailing address is required by the database.

Note 2: you may use abbreviations on your conversion chart as long as it is unambiguous what your abbreviations mean.

Employee 16 class #7 Class #10 IA Class F2 B 30 Class #5 class 3 26. B Class #11 Purchase/Receiving -- Zipcode class #9 Zipcode - Class #1 15 - Class 3 25- Employee As Cashier 24 Cash Receipt class #4 As Payee Class #12 Class #6 31 relationships to implement The Class objects with the diagonal lines (Employee, Zip Code) are duplicates used for simplifying the UML; there will only be one Employee Class and one Zip Code class in the database Practice UML Exercise: Master Electrician Trade Association (META) Employee 16 class #7 Class #10 IA Class F2 B 30 Class #5 class 3 26. B Class #11 Purchase/Receiving -- Zipcode class #9 Zipcode - Class #1 15 - Class 3 25- Employee As Cashier 24 Cash Receipt class #4 As Payee Class #12 Class #6 31 relationships to implement The Class objects with the diagonal lines (Employee, Zip Code) are duplicates used for simplifying the UML; there will only be one Employee Class and one Zip Code class in the database Practice UML Exercise: Master Electrician Trade Association (META)